simple report help, i stink @sql

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

simple report help, i stink @sql

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> simple report help, i stink @sql

Page: [1]

Message

<< Older Topic Newer Topic >>

simple report help, i stink @sql - 8/14/2008 12:46:44 PM

shadster

Posts: 22
Score: 0
Joined: 8/6/2008
Status: offline

ok, I stink at SQL, but have been running sccm/sms for over a year now... but now we are having an issue with several models with a certain video card that are blue screening and i need to create a custom report to help me determine how many are actually choking... or will possibly choke.

Something like: model, bios, video card, ip, machine name where if i entered a model number it would generate the appropriate info... or even by video card (or even both, like 755 & fx 5200) so i can count them up for management sake

Any ideas /tips would be greatly appreciated

Thanks in advance

Post #: 1

RE: simple report help, i stink @sql - 8/15/2008 3:19:58 PM

rbennett806

Posts: 825
Score: 13
Joined: 6/14/2006
Status: offline

Have you checked out the built-in report titled "Computers with a specific video card"? Is that what you're wanting?

SELECT Distinct SYS.Netbios_Name0, Video.Name0 as C055, Video.DriverVersion0,    Video.InstalledDisplayDrivers0,
               Video.DeviceID0, Video.CurrentBitsPerPixel0, Video.CurrentHorizontalResolution0, Video.CurrentVerticalResolution0,
               Video.CurrentRefreshRate0
FROM v_R_System SYS
JOIN v_GS_VIDEO_CONTROLLER Video on SYS.ResourceID = Video.ResourceID
Where Video.Name0 LIKE @variable
ORDER BY SYS.Netbios_Name0

begin
if (@__filterwildcard = '')
SELECT Distinct Video.Name0 as C055 FROM v_GS_VIDEO_CONTROLLER Video ORDER BY Video.Name0
else
SELECT Distinct Video.Name0 as C055 FROM v_GS_VIDEO_CONTROLLER Video
WHERE Video.Name0 like @__filterwildcard
ORDER BY Video.Name0
end

(in reply to shadster)

Post #: 2

RE: simple report help, i stink @sql - 8/18/2008 8:38:15 AM

shadster

Posts: 22
Score: 0
Joined: 8/6/2008
Status: offline

yeah i know of that report, but it only gives you a count per type... where i was looking for a way to enter computer model and then give me a list of their video cards, or enter a video card and have it break them down by model....

thanks

(in reply to shadster)

Post #: 3

RE: simple report help, i stink @sql - 8/18/2008 10:18:20 AM

egroff

Posts: 8
Score: 2
Joined: 10/28/2007
Status: offline

Start with this which will give you everything:
SELECT DISTINCT
      v_R_System.Name0, v_R_System.User_Domain0, v_R_System.User_Name0, v_GS_COMPUTER_SYSTEM.Manufacturer0,
                     v_GS_COMPUTER_SYSTEM.Model0, v_GS_VIDEO_CONTROLLER.Name0 AS Expr1
FROM         v_R_System INNER JOIN
                     v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                     v_GS_VIDEO_CONTROLLER ON v_R_System.ResourceID = v_GS_VIDEO_CONTROLLER.ResourceID

Then you can get rid of obvious duplicates with something like this:
SELECT DISTINCT
                     v_R_System.Name0, v_R_System.User_Domain0, v_R_System.User_Name0, v_GS_COMPUTER_SYSTEM.Manufacturer0,
                     v_GS_COMPUTER_SYSTEM.Model0, v_GS_VIDEO_CONTROLLER.Name0
FROM         v_R_System INNER JOIN
                     v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                     v_GS_VIDEO_CONTROLLER ON v_R_System.ResourceID = v_GS_VIDEO_CONTROLLER.ResourceID
WHERE     (NOT (v_GS_VIDEO_CONTROLLER.Name0 LIKE '%Secondary')) AND
                     (NOT (v_GS_VIDEO_CONTROLLER.Name0 LIKE '%Mirror%')) AND
                     (NOT (v_GS_VIDEO_CONTROLLER.Name0 LIKE '%Unicenter%'))

You should be able to add line to be more specific to Model by using the same idea as was used for the Video card. Maybe something like this:
SELECT DISTINCT
                     v_R_System.Name0, v_R_System.User_Domain0, v_R_System.User_Name0, v_GS_COMPUTER_SYSTEM.Manufacturer0,
                     v_GS_COMPUTER_SYSTEM.Model0, v_GS_VIDEO_CONTROLLER.Name0 AS Expr1
FROM         v_R_System INNER JOIN
                     v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                     v_GS_VIDEO_CONTROLLER ON v_R_System.ResourceID = v_GS_VIDEO_CONTROLLER.ResourceID
WHERE     (NOT (v_GS_VIDEO_CONTROLLER.Name0 LIKE '%Secondary')) AND (NOT (v_GS_VIDEO_CONTROLLER.Name0 LIKE '%Mirror%')) AND
                     (NOT (v_GS_VIDEO_CONTROLLER.Name0 LIKE '%Unicenter%')) AND (v_GS_COMPUTER_SYSTEM.Model0 LIKE '%745')

I hope this helps.

(in reply to shadster)

Post #: 4

RE: simple report help, i stink @sql - 8/19/2008 8:44:48 AM

shadster

Posts: 22
Score: 0
Joined: 8/6/2008
Status: offline

awsome egroff...

very much obliged, you are a gentelman and a scholar =)

(in reply to egroff)

Post #: 5