Build a report of All Hardware types and thier IP Address

We are in the middle of upgrading all WinXP hardware and i am using the below report to determine the Harware type/make/model and top console user but i need to also retrieve the IP address.

SELECT  distinct
 CS.name0 as 'Computer Name',
 CS.domain0 as 'Domain',
 CS.UserName0 as 'User',
 BIOS.SerialNumber0 as 'Bios serial',
 SE.SerialNumber0 as 'System Enclosure serial',
 CS.Manufacturer0 as 'Manufacturer',
 CS.Model0 as 'model',
 OS.Caption0 as 'OS',
 RAA.SMS_Assigned_Sites0 as 'Site',
 RAM.TotalPhysicalMemory0 as 'Total Memory',
 sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
 sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
 CPU.CurrentClockSpeed0 as 'CPU Speed'
from 
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
 right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID 
 right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID 
 right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
 right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
 right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
 right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   
 right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
where
 LDisk.DriveType0 =3
group by
 CS.Name0,
 CS.domain0,
 CS.Username0,
 BIOS.SerialNumber0,
 SE.SerialNumber0,
 CS.Manufacturer0,
 CS.Model0,
 OS.Caption0,
 RAA.SMS_Assigned_Sites0,
 RAM.TotalPhysicalMemory0,
 CPU.CurrentClockSpeed0

March 16th, 2014 2:16pm

I don't recommend adding IP address to reports, as it will cause duplicate rows.

Free Windows Admin Tool Kit Click here and download it now
March 16th, 2014 3:41pm

I agree with Garth.

But if you really want it the below should work

SELECT  distinct 
 CS.name0 as 'Computer Name', 
 CS.domain0 as 'Domain', 
 CS.UserName0 as 'User', 
 BIOS.SerialNumber0 as 'Bios serial', 
 SE.SerialNumber0 as 'System Enclosure serial',
 CS.Manufacturer0 as 'Manufacturer', 
 CS.Model0 as 'model', 
 OS.Caption0 as 'OS', 
 RAA.SMS_Assigned_Sites0 as 'Site', 
 RAM.TotalPhysicalMemory0 as 'Total Memory', 
 sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size', 
 sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space', 
 CPU.CurrentClockSpeed0 as 'CPU Speed' ,
 IP.IP_Addresses0
from  
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
 right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID  
 right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID  
 right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
 right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID 
 right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID 
 right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID    
 right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID 
 right join dbo.v_RA_System_IPAddresses IP on IP.ResourceID = CS.ResourceID
where 
 LDisk.DriveType0 =3
group by 
 CS.Name0, 
 CS.domain0,
 CS.Username0, 
 BIOS.SerialNumber0, 
 SE.SerialNumber0,
 CS.Manufacturer0, 
 CS.Model0, 
 OS.Caption0, 
 RAA.SMS_Assigned_Sites0,
 RAM.TotalPhysicalMemory0, 
 CPU.CurrentClockSpeed0,
 IP.IP_Addresses0

March 17th, 2014 3:56am

Thank you i will try.
Free Windows Admin Tool Kit Click here and download it now
March 17th, 2014 6:17am

This did not work
March 25th, 2014 8:57am

I ran the query & it work just fine.

Did you get any error?

Free Windows Admin Tool Kit Click here and download it now
March 25th, 2014 11:19am

The following works for me when I tested it on SSMS (SQL Server Management Studio)

SELECT distinct
	CS.name0 as 'Computer Name',
	CS.domain0 as 'Domain',
	CS.UserName0 as 'User',
	BIOS.SerialNumber0 as 'Bios serial',
	SE.SerialNumber0 as 'System Enclosure serial',
	CS.Manufacturer0 as 'Manufacturer',
	CS.Model0 as 'model',
	OS.Caption0 as 'OS',
	RAA.SMS_Assigned_Sites0 as 'Site',
	RAM.TotalPhysicalMemory0 as 'Total Memory',
	sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
	sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
	CPU.CurrentClockSpeed0 as 'CPU Speed',
	IP.IP_Addresses0 as 'IP Address'

from
	v_GS_COMPUTER_SYSTEM CS
	right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
	right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID
	right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
	right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
	right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
	right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
	right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID
	right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
	right join v_RA_System_IPAddresses IP on IP.ResourceID = CS.ResourceID

where
	LDisk.DriveType0 =3

group by
	CS.Name0,
	CS.domain0,
	CS.Username0,
	BIOS.SerialNumber0,
	SE.SerialNumber0,
	CS.Manufacturer0,
	CS.Model0,
	OS.Caption0,
	RAA.SMS_Assigned_Sites0,
	RAM.TotalPhysicalMemory0,
	CPU.CurrentClockSpeed0,
	IP.IP_Addresses0

note that what @Vincez did was to add join to another table 'v_RA_System_IPAddresses'

and select column IP_Addresses0 from the table

please try again.

March 25th, 2014 11:33am

Sorry this is my error

Free Windows Admin Tool Kit Click here and download it now
March 25th, 2014 11:38am

Sorry this is my error


This error is likely caused by that fact this report is pulling too much data and you are using the old ASP reports. Either limited the data that is returned or use the SSRS reports.
March 25th, 2014 2:31pm

Error 500 sounds like a timeout error.

Check this article here http://technet.microsoft.com/en-us/library/bb680885.aspx to see if increasing the row count value resolve your

Free Windows Admin Tool Kit Click here and download it now
March 25th, 2014 2:33pm

Have a read on this There is a limit of 10000 rows that reports via sccm console can show http://blogs.technet.com/b/dominikheinz/archive/2011/09/08/sccm-report-cannot-be-displayed-error-500.aspx
March 25th, 2014 7:52pm

hi mr Jones,

I too have issues when querying a table that returns 200k+ rows.

so my alternative is using SSMS.


If you don't mind, can you tell me more about SSRS report that you're using, how-to get it configured?

Thanks!

Free Windows Admin Tool Kit Click here and download it now
March 25th, 2014 10:44pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics