Little help with an SMS report (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003



Message

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

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



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

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.



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

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-%"



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

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



sconnell -> Eschloss (5/1/2008 12:50:57 PM)

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?



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

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



phaustein -> RE: Eschloss (5/4/2008 11:10:18 PM)

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?



mgroenewald -> RE: Eschloss (5/8/2008 6:39:08 AM)

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



jnelson993 -> RE: Eschloss (5/8/2008 12:35:03 PM)

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




mgroenewald -> RE: Eschloss (5/9/2008 1:35:39 AM)

Hi John
Thank you
I must have been sleeping when I changed it [:D]



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.5