Little help with an SMS report

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:

Little help with an SMS report

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

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Little help with an SMS report

Page: [1]

Message

<< Older Topic Newer Topic >>

Little help with an SMS report - 8/3/2007 9:54:30 AM

jdaly611

Posts: 7
Score: 0
Joined: 7/31/2007
Status: offline

I am trying to create an inventory report that will detail the important computer info for quick refference. I would like the report to display the ComputerName, UserName, Manufacturer, # of CPU's, ProcessorSpeed, HDD size, HDD Free, FileSystem type, and total memory. I am about 90% of the way done with this report I just cant seem to get the ProcessorSpeed in there without messing up my results. Below is my SQL query for the report.

select distinct
V_GS_COMPUTER_SYSTEM.Name0 as "Computer Name",
V_GS_COMPUTER_SYSTEM.UserName0,
V_GS_COMPUTER_SYSTEM.Manufacturer0,
V_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as "CPUs",
V_GS_COMPUTER_SYSTEM.Model0,
V_GS_LOGICAL_DISK.Name0,
V_GS_LOGICAL_DISK.Size0 as "HDD Size",
V_GS_LOGICAL_DISK.FreeSpace0 as "HDD free",
V_GS_LOGICAL_DISK.FileSystem0,
V_GS_X86_PC_MEMORY.TotalPhysicalMemory0

from V_GS_COMPUTER_SYSTEM
INNER JOIN V_GS_LOGICAL_DISK
ON V_GS_COMPUTER_SYSTEM.ResourceID = V_GS_LOGICAL_DISK.ResourceID
INNER JOIN V_GS_X86_PC_MEMORY
on V_GS_COMPUTER_SYSTEM.ResourceID = V_GS_X86_PC_Memory.ResourceID

where
V_GS_LOGICAL_DISK.Name0 = 'C:'
and
V_GS_LOGICAL_DISK.Freespace0 is not null
and
V_GS_LOGICAL_DISK.Drivetype0 = 3
and
V_GS_LOGICAL_DISK.FileSystem0 = 'NTFS'
and
V_GS_COMPUTER_SYSTEM.Name0 like "asma-%"

If someone could help me figure out why I cant get the processor speed in there I would appreciate it.

Thanks

Post #: 1

RE: Little help with an SMS report - 8/3/2007 12:09:21 PM

skissinger

Posts: 1677
Score: 103
Joined: 9/13/2001
From: Sherry Kissinger
Status: offline

By 'messing up your results', do you mean you end up with multiple rows for some computers? That would be normal if those computers happen to have multiple CPUs--it wants to report a row for each processor; even though the speed is the same for each processor it's still a unique value, and wants to report a row for each value it found.

_____________________________

mofmaster@smsexpert.com (version 2007) | http://www.smsexpert.com | http://www.sccmexpert.com
Microsoft MVP - SMS

Inventory Manager 2007 is here!

(in reply to jdaly611)

Post #: 2

RE: Little help with an SMS report - 8/3/2007 12:49:05 PM

eschloss

Posts: 559
Score: 21
Joined: 9/7/2004
From: Cincinnati
Status: offline

Try this. It includes the processor speed.

select distinct
V_GS_COMPUTER_SYSTEM.Name0 as "Computer Name",
V_GS_COMPUTER_SYSTEM.UserName0,
V_GS_COMPUTER_SYSTEM.Manufacturer0,
V_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as "CPUs",
v_GS_PROCESSOR.NormSpeed0 as "Clock Speed",
V_GS_COMPUTER_SYSTEM.Model0,
V_GS_LOGICAL_DISK.Name0,
V_GS_LOGICAL_DISK.Size0 as "HDD Size",
V_GS_LOGICAL_DISK.FreeSpace0 as "HDD free",
V_GS_LOGICAL_DISK.FileSystem0,
V_GS_X86_PC_MEMORY.TotalPhysicalMemory0

from V_GS_COMPUTER_SYSTEM
INNER JOIN V_GS_LOGICAL_DISK
ON V_GS_COMPUTER_SYSTEM.ResourceID = V_GS_LOGICAL_DISK.ResourceID
INNER JOIN V_GS_X86_PC_MEMORY
on V_GS_COMPUTER_SYSTEM.ResourceID = V_GS_X86_PC_Memory.ResourceID
INNER JOIN v_GS_PROCESSOR on V_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PROCESSOR.ResourceID
where
V_GS_LOGICAL_DISK.Name0 = 'C:'
and
V_GS_LOGICAL_DISK.Freespace0 is not null
and
V_GS_LOGICAL_DISK.Drivetype0 = 3
and
V_GS_LOGICAL_DISK.FileSystem0 = 'NTFS'
and
V_GS_COMPUTER_SYSTEM.Name0 like "asma-%"

(in reply to skissinger)

Post #: 3

RE: Little help with an SMS report - 8/6/2007 11:16:58 AM

jdaly611

Posts: 7
Score: 0
Joined: 7/31/2007
Status: offline

Sorry for the delay in responding I never got a forum notice that you replied. I just tried out the report and I didnt have NormSpeed as an option so I used max clock speed. The report looks pretty good, there are about 3 entries that show up twice but thats not a big deal.

Is there any way you know of to group the computers by the computer name if there are duplicates?

Thanks

(in reply to eschloss)

Post #: 4

Eschloss - 5/1/2008 12:50:57 PM

sconnell

Posts: 47
Score: 0
Joined: 7/6/2007
Status: offline

tried running that report and could not get the normspeed line to work so I just removed it but my report found no matching records? Any ideas?

(in reply to jdaly611)

Post #: 5

RE: Little help with an SMS report - 5/4/2008 11:06:19 PM

phaustein

Posts: 859
Score: 32
Joined: 3/21/2005
From: Washington, DC
Status: offline

Try adding this to the end of the statement

Order by V_GS_COMPUTER_SYSTEM.Name0

That should put them together on the list.

quote:

ORIGINAL: jdaly611

Sorry for the delay in responding I never got a forum notice that you replied. I just tried out the report and I didnt have NormSpeed as an option so I used max clock speed. The report looks pretty good, there are about 3 entries that show up twice but thats not a big deal.

Is there any way you know of to group the computers by the computer name if there are duplicates?

Thanks

_____________________________

Hope this helps.
Paul

(in reply to jdaly611)

Post #: 6

RE: Eschloss - 5/4/2008 11:10:18 PM

phaustein

Posts: 859
Score: 32
Joined: 3/21/2005
From: Washington, DC
Status: offline

Could you post your modified query?

quote:

ORIGINAL: sconnell

tried running that report and could not get the normspeed line to work so I just removed it but my report found no matching records? Any ideas?

_____________________________

Hope this helps.
Paul

(in reply to sconnell)

Post #: 7

RE: Eschloss - 5/8/2008 6:39:08 AM

mgroenewald

Posts: 186
Score: 0
Joined: 7/2/2004
Status: offline

Hi try this Query - Hope it helps
I change one of my reports - but it times out when I include no of processors
Mike

SELECT sys.resourceid,
     sys.ad_site_name0,
     pr.family0,
     pr.maxclockspeed0,
--pn.NumberOfProcessors0,
     MAX(mem.totalphysicalmemory0) as totalphysicalmemory0,
     ld.FreeSpace0,
ld.FileSystem0,
     os.csdversion0,
      os.Caption0 as OS,
      sys.name0 as ComputerName
FROM dbo.v_r_system AS sys
     JOIN dbo.v_gs_processor AS pr
       ON (sys.resourceid = pr.resourceid)
     JOIN dbo.v_gs_x86_pc_memory AS mem
       ON (sys.resourceid = mem.resourceid)
     JOIN dbo.v_gs_logical_disk AS ld
       ON (sys.resourceid = ld.resourceid)
     JOIN dbo.v_gs_operating_system AS os
       ON (sys.resourceid = os.resourceid)
     --JOIN dbo.v_gs_computer_system as pn
--on (sys.resourceid = os.resourceid)
WHERE os.version0 = '5.1.2600'
AND ld.DriveType0 = 3
AND ld.DeviceID0 = 'C:'
AND pr.DeviceID0 = 'CPU0'
AND sys.decommissioned0 = 0
AND sys.obsolete0 = 0
AND sys.client0 = 1
GROUP BY sys.resourceid,
     sys.ad_site_name0,
     pr.family0,
     pr.maxclockspeed0,
--pn.NumberOfProcessors0,
     ld.FreeSpace0,
ld.FileSystem0,
     os.csdversion0,
      os.Caption0,
     sys.name0

(in reply to phaustein)

Post #: 8

RE: Eschloss - 5/8/2008 12:35:03 PM

jnelson993

Posts: 596
Score: 67
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Yours is timing out because of your join criteria on the v_GS_Computer_System view

JOIN dbo.v_gs_computer_system AS pn
    ON (sys.resourceid = os.resourceid)

should be

JOIN dbo.v_gs_computer_system AS pn
    ON (sys.resourceid = pn.resourceid)

Try this:

SELECT
sys.resourceid,
sys.ad_site_name0,
pr.family0,
pr.maxclockspeed0,
pn.NumberOfProcessors0,
MAX(mem.totalphysicalmemory0) AS totalphysicalmemory0,
ld.FreeSpace0,
ld.FileSystem0,
os.csdversion0,
os.Caption0                    AS OS,
sys.name0                      AS ComputerName
FROM
dbo.v_r_system AS sys
JOIN dbo.v_gs_processor AS pr
    ON (sys.resourceid = pr.resourceid)
JOIN dbo.v_gs_x86_pc_memory AS mem
    ON (sys.resourceid = mem.resourceid)
JOIN dbo.v_gs_logical_disk AS ld
    ON (sys.resourceid = ld.resourceid)
JOIN dbo.v_gs_operating_system AS os
    ON (sys.resourceid = os.resourceid)
JOIN dbo.v_gs_computer_system AS pn
    ON (sys.resourceid = pn.resourceid)
WHERE
os.version0 = '5.1.2600'
AND ld.DriveType0 = 3
AND ld.DeviceID0 = 'C:'
AND pr.DeviceID0 = 'CPU0'
AND sys.decommissioned0 = 0
AND sys.obsolete0 = 0
AND sys.client0 = 1
GROUP BY
sys.resourceid,
sys.ad_site_name0,
pr.family0,
pr.maxclockspeed0,
pn.NumberOfProcessors0,
ld.FreeSpace0,
ld.FileSystem0,
os.csdversion0,
os.Caption0,sys.name0

_____________________________

Number2 (John Nelson)
Wells Fargo
MyITForum - Forum Posts
MyITForum - Blog

(in reply to mgroenewald)

Post #: 9

RE: Eschloss - 5/9/2008 1:35:39 AM

mgroenewald

Posts: 186
Score: 0
Joined: 7/2/2004
Status: offline

Hi John
Thank you
I must have been sleeping when I changed it