myITforum.com Community Forum 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:


  


Assistance with Report for all Servers and info

 
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 >> Assistance with Report for all Servers and info Page: [1]
Login
Message << Older Topic   Newer Topic >>
Assistance with Report for all Servers and info - 5/7/2008 1:29:02 PM   
mmoore962

 

Posts: 44
Score: 0
Joined: 3/8/2007
Status: offline 		We are using the below report to display information on all servers but need assistance to gather additional information. In the report we need to also display the following information: Disk Drives, Disk Partitions and Logical Disk info. Does anyone already have a report that displays all the information for all servers?  Thanks in advance for any help given.




select  distinct v_R_System.ResourceID,
v_R_System.Netbios_Name0 AS "Computer Name",
v_R_System.Resource_Domain_OR_Workgr0 AS "Domain/Workgroup",
v_Site.SiteName as "SMS Site Name",
v_GS_OPERATING_SYSTEM.Caption0 AS "Operating System",
v_GS_OPERATING_SYSTEM.CSDVersion0 AS "Service Pack Level",
v_GS_OPERATING_SYSTEM.InstallDate0 AS "Install Date",
v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS "Serial Number",
v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 AS "Asset Tag",
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS "Manufacturer",
v_GS_COMPUTER_SYSTEM.Model0 AS "Model",
v_GS_COMPUTER_SYSTEM.NumberofProcessors0 AS "# of Processors",
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS "Memory (KBytes)",
v_GS_PROCESSOR.NormSpeed0 AS "Processor (GHz)",
(Select sum(Size0) from v_GS_LOGICAL_DISK
inner join v_FullCollectionMembership
on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
where v_GS_LOGICAL_DISK.ResourceID =v_R_System.ResourceID and
v_FullCollectionMembership.CollectionID = @CollectionID) As "Disk Space (MB)",
(Select sum(v_GS_LOGICAL_DISK.FreeSpace0) from v_GS_LOGICAL_DISK
inner join v_FullCollectionMembership
on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )
where v_GS_LOGICAL_DISK.ResourceID =v_R_System.ResourceID and
v_FullCollectionMembership.CollectionID = @CollectionID) As "Free Disk Space (MB)"
from v_R_System
inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID)
inner join v_GS_SYSTEM_ENCLOSURE on (v_GS_SYSTEM_ENCLOSURE.ResourceID = v_R_System.ResourceID)
inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID)
inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID)
inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System.ResourceID)
inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System.ResourceID)
inner join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)
inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)
left join v_GS_SYSTEM_CONSOLE_USAGE on (v_GS_SYSTEM_CONSOLE_USAGE.ResourceID = v_R_System.ResourceID)
Where v_FullCollectionMembership.CollectionID = @CollectionID
Order by v_R_System.Netbios_Name0
Post #: 1
RE: Assistance with Report for all Servers and info - 5/7/2008 1:39:28 PM   
jmlewis

 

Posts: 39
Score: 2
Joined: 7/28/2007
Status: offline 		are you needing the # of disks, # of partitions? and what specific logical disk info? you are joining the view v_gs_logical_disk so i would imagine that you can probably pull what you need from there.

(in reply to mmoore962)
Post #: 2
RE: Assistance with Report for all Servers and info - 5/7/2008 1:44:28 PM   
mmoore962

 

Posts: 44
Score: 0
Joined: 3/8/2007
Status: offline 		Yes, that's what I'm looking for. I don't know how to join the views together. Do you have a report that already displays this info? If so can you share it?

Thx.

(in reply to jmlewis)
Post #: 3
RE: Assistance with Report for all Servers and info - 5/7/2008 1:57:30 PM   
jmlewis

 

Posts: 39
Score: 2
Joined: 7/28/2007
Status: offline 		modified your existing code. added a count for partitions and a count for disks by joining the view v_gs_disk. hope this works. not sure what other information you wanted to add though. alot more information is available in the view v_gs_disk

SELECT DISTINCT
                     v_R_System.ResourceID, v_R_System.Netbios_Name0 AS [Computer Name], v_R_System.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
                     v_Site.SiteName AS [SMS Site Name], v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
                     v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], v_GS_OPERATING_SYSTEM.InstallDate0 AS [Install Date],
                     v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 AS [Asset Tag],
                     v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, v_GS_COMPUTER_SYSTEM.Model0 AS Model,
                     v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [# of Processors], v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
                     v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
                         (SELECT     SUM(v_GS_LOGICAL_DISK.Size0) AS Expr1
                           FROM          v_GS_LOGICAL_DISK INNER JOIN
                                                  v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID
                           WHERE      (v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID) AND (v_FullCollectionMembership.CollectionID = @CollectionID))
                     AS [Disk Space (MB)],
                         (SELECT     SUM(v_GS_LOGICAL_DISK_2.FreeSpace0) AS Expr1
                           FROM          v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_2 INNER JOIN
                                                  v_FullCollectionMembership AS v_FullCollectionMembership_2 ON
                                                  v_FullCollectionMembership_2.ResourceID = v_GS_LOGICAL_DISK_2.ResourceID
                           WHERE      (v_GS_LOGICAL_DISK_2.ResourceID = v_R_System.ResourceID) AND (v_FullCollectionMembership_2.CollectionID = @CollectionID))
                     AS [Free Disk Space (MB)], v_GS_DISK.Partitions0 AS Count_Partitions, COUNT(v_GS_DISK.ResourceID) AS Count_HD
FROM         v_R_System INNER JOIN
                     v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_GS_SYSTEM_ENCLOSURE ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_GS_X86_PC_MEMORY ON v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_GS_PROCESSOR ON v_GS_PROCESSOR.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_FullCollectionMembership AS v_FullCollectionMembership_1 ON v_FullCollectionMembership_1.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_Site ON v_FullCollectionMembership_1.SiteCode = v_Site.SiteCode INNER JOIN
                     v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_1 ON v_GS_LOGICAL_DISK_1.ResourceID = v_R_System.ResourceID AND
                     v_GS_LOGICAL_DISK_1.DeviceID0 = SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0, 1, 2) INNER JOIN
                     v_GS_DISK ON v_R_System.ResourceID = v_GS_DISK.ResourceID LEFT OUTER JOIN
                     v_GS_SYSTEM_CONSOLE_USAGE ON v_GS_SYSTEM_CONSOLE_USAGE.ResourceID = v_R_System.ResourceID
WHERE     (v_FullCollectionMembership_1.CollectionID = @CollectionID)
GROUP BY v_R_System.ResourceID, v_R_System.Netbios_Name0, v_R_System.Resource_Domain_OR_Workgr0, v_Site.SiteName,
                     v_GS_OPERATING_SYSTEM.Caption0, v_GS_OPERATING_SYSTEM.CSDVersion0, v_GS_OPERATING_SYSTEM.InstallDate0,
                     v_GS_SYSTEM_ENCLOSURE.SerialNumber0, v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0, v_GS_COMPUTER_SYSTEM.Manufacturer0,
                     v_GS_COMPUTER_SYSTEM.Model0, v_GS_COMPUTER_SYSTEM.NumberOfProcessors0, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,
                     v_GS_PROCESSOR.NormSpeed0, v_GS_DISK.Partitions0
ORDER BY v_R_System.Netbios_Name0

(in reply to mmoore962)
Post #: 4
RE: Assistance with Report for all Servers and info - 5/7/2008 2:00:40 PM   
mmoore962

 

Posts: 44
Score: 0
Joined: 3/8/2007
Status: offline 		thanks. i'll test it now.

(in reply to jmlewis)
Post #: 5
RE: Assistance with Report for all Servers and info - 5/8/2008 6:37:47 AM   
mmoore962

 

Posts: 44
Score: 0
Joined: 3/8/2007
Status: offline 		I gave the modified report a shot and everything looks good. I also added the v_GS_DISK.Size0 to get the size. However, the columun that is displayed "Count_HD". What exactly is that? I'm looking at a server now and know it has the following info:
5 Physical disks
(2) Disks are RAID 1, and broken up into 2 partitions
(3) Disks are RAID 5 as one big partition

The Count_HD column displays the number 4. Also, regarding the "InstallDate0". This displays the date and time the OS was installed. How do I get just the date?

Thx in advance.

< Message edited by mmoore962 -- 5/8/2008 6:41:12 AM >

(in reply to mmoore962)
Post #: 6
RE: Assistance with Report for all Servers and info - 5/8/2008 8:52:57 AM   
jmlewis

 

Posts: 39
Score: 2
Joined: 7/28/2007
Status: offline 		the count hd references the number of disks that are listed for the particular server in that table. that was the only way that i could see to accurately reference the # disks.

see the report below: it has a convert statement to show only the date

SELECT DISTINCT
                     v_R_System.ResourceID, v_R_System.Netbios_Name0 AS [Computer Name], v_R_System.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
                     v_Site.SiteName AS [SMS Site Name], v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
                     v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], CONVERT(varchar, v_GS_OPERATING_SYSTEM.InstallDate0, 101) AS [Install Date],
                     v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number], v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 AS [Asset Tag],
                     v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, v_GS_COMPUTER_SYSTEM.Model0 AS Model,
                     v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [# of Processors], v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],
                     v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
                         (SELECT     SUM(v_GS_LOGICAL_DISK.Size0) AS Expr1
                           FROM          v_GS_LOGICAL_DISK INNER JOIN
                                                  v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID
                           WHERE      (v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID) AND (v_FullCollectionMembership.CollectionID = @CollectionID))
                     AS [Disk Space (MB)],
                         (SELECT     SUM(v_GS_LOGICAL_DISK_2.FreeSpace0) AS Expr1
                           FROM          v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_2 INNER JOIN
                                                  v_FullCollectionMembership AS v_FullCollectionMembership_2 ON
                                                  v_FullCollectionMembership_2.ResourceID = v_GS_LOGICAL_DISK_2.ResourceID
                           WHERE      (v_GS_LOGICAL_DISK_2.ResourceID = v_R_System.ResourceID) AND (v_FullCollectionMembership_2.CollectionID = @CollectionID))
                     AS [Free Disk Space (MB)], v_GS_DISK.Partitions0 AS Count_Partitions, COUNT(v_GS_DISK.ResourceID) AS Count_HD
FROM         v_R_System INNER JOIN
                     v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_GS_SYSTEM_ENCLOSURE ON v_GS_SYSTEM_ENCLOSURE.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_GS_X86_PC_MEMORY ON v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_GS_PROCESSOR ON v_GS_PROCESSOR.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_FullCollectionMembership AS v_FullCollectionMembership_1 ON v_FullCollectionMembership_1.ResourceID = v_R_System.ResourceID INNER JOIN
                     v_Site ON v_FullCollectionMembership_1.SiteCode = v_Site.SiteCode INNER JOIN
                     v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_1 ON v_GS_LOGICAL_DISK_1.ResourceID = v_R_System.ResourceID AND
                     v_GS_LOGICAL_DISK_1.DeviceID0 = SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0, 1, 2) INNER JOIN
                     v_GS_DISK ON v_R_System.ResourceID = v_GS_DISK.ResourceID LEFT OUTER JOIN
                     v_GS_SYSTEM_CONSOLE_USAGE ON v_GS_SYSTEM_CONSOLE_USAGE.ResourceID = v_R_System.ResourceID
WHERE     (v_FullCollectionMembership_1.CollectionID = @CollectionID)
GROUP BY v_R_System.ResourceID, v_R_System.Netbios_Name0, v_R_System.Resource_Domain_OR_Workgr0, v_Site.SiteName,
                     v_GS_OPERATING_SYSTEM.Caption0, v_GS_OPERATING_SYSTEM.CSDVersion0, v_GS_OPERATING_SYSTEM.InstallDate0,
                     v_GS_SYSTEM_ENCLOSURE.SerialNumber0, v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0, v_GS_COMPUTER_SYSTEM.Manufacturer0,
                     v_GS_COMPUTER_SYSTEM.Model0, v_GS_COMPUTER_SYSTEM.NumberOfProcessors0, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,
                     v_GS_PROCESSOR.NormSpeed0, v_GS_DISK.Partitions0
ORDER BY v_R_System.Netbios_Name0

(in reply to mmoore962)
Post #: 7
Page:   [1]
All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Assistance with Report for all Servers and info Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts



  
Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.406