Customized Hardware Inventory Report

[jamieralphsmith]

I'm Having a little trouble writing a report. My goal is to report back the following fields:

Last Time the Hardware inventory was run, Computer Name, Asset Tag (Bios Serial Number), IP Address, Operating System, OS Service Pack level, time the computer was Last Rebooted, Last Logged in User, Domain Name, OU, and AD Site.

 

I've managed to gather most everything but I'm getting duplicate entries. My report returns over 13000 rows when I only have 6000 clients. Much of this is coming from the IP Address field since if the computer has an IPv6 address it returns another row. Below is my query and I appreciate any help anyone can give since I am very much a beginner in SQL.

 

 

SELECT DISTINCT

v_GS_Workstation_Status.LastHWScan,

v_R_System.Netbios_Name0,

v_GS_PC_BIOS.SerialNumber0,

v_RA_System_IPAddresses.IP_Addresses0,

v_GS_Operating_System.Caption0 as C054,

v_GS_Operating_System.Version0,

v_GS_Operating_System.LastBootUpTime0,

v_R_System.User_Name0,

v_R_System.Resource_Domain_OR_Workgr0,

v_R_System.AD_Site_Name0

 

FROM v_R_System

JOIN v_GS_Workstation_Status on v_R_System.ResourceID = v_GS_Workstation_Status.ResourceID

JOIN v_RA_System_IPAddresses on v_R_System.ResourceID = v_RA_System_IPAddresses.ResourceID

JOIN v_GS_PC_BIOS on v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID

JOIN v_GS_OPERATING_SYSTEM on v_R_System.ResourceID = v_GS_Operating_System.ResourceID

 

ORDER BY

v_GS_Workstation_Status.LastHWScan,

v_R_System.Netbios_Name0,

v_GS_PC_BIOS.SerialNumber0,

v_RA_System_IPAddresses.IP_Addresses0,

v_GS_Operating_System.Caption0,

v_GS_Operating_System.Version0,

v_GS_Operating_System.LastBootUpTime0,

v_R_System.User_Name0,

v_R_System.Resource_Domain_OR_Workgr0,

v_R_System.AD_Site_Name0

[GarthMJ]

The simple anwser is remove IP address from the report. I almost never use IP address within any or my reports because of this issue.

[chettyrajesh]

Hi,

 

Have modified th eSQL query to exclude IPv6 also if you have multiple IPv4 IP's then it will com in one single row.

 

SELECT v_R_System.Netbios_Name0, CASE WHEN v_GS_PC_BIOS.SerialNumber0 LIKE 'vmware%' THEN 'VM' ELSE v_GS_PC_BIOS.SerialNumber0 END AS [serial No],

v_GS_PC_BIOS.Manufacturer0, v_GS_PROCESSOR.Name0 AS [Processor],

CASE WHEN v_GS_PROCESSOR.ADDRESSWIDTH0 = '32' THEN '32 Bit' ELSE '64 Bit' END AS [Processor Type],

CASE WHEN v_GS_PROCESSOR.IS64BIT0 = '1' THEN 'Yes' ELSE 'No' END AS [64 Bit Supported],

v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 / 1024 AS [Memory(MB)], c.IPAddress AS [iP Address], v_GS_Operating_System.Caption0 AS [Operating system],

v_GS_Operating_System.Version0 AS [Operating system version], v_GS_OPERATING_SYSTEM.ServicePackMajorVersion0, CONVERT(nvarchar(10),

v_GS_OPERATING_SYSTEM.InstallDate0, 101) AS [installed Date], CONVERT(nvarchar(10), v_GS_Operating_System.LastBootUpTime0, 101) AS [Rebooted On],

v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0

FROM v_R_System INNER JOIN

(SELECT t1.resourceid AS rsid2, IPAddress = substring

((SELECT (IP_Addresses0 + ', ')

FROM v_RA_System_IPAddresses t2

WHERE t2.IP_Addresses0 NOT LIKE '169%' AND IP_Addresses0 NOT LIKE '0.%' AND IP_Addresses0 NOT LIKE '%::%' AND

IP_Addresses0 NOT LIKE '192.%' AND t1.resourceid = t2.resourceid

ORDER BY resourceid FOR xml path('')), 1, 50000)

FROM v_RA_System_IPAddresses t1

GROUP BY resourceid) c ON c.rsid2 = v_R_System.resourceid JOIN

 

v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID JOIN

v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_Operating_System.ResourceID JOIN

v_GS_X86_PC_MEMORY ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID JOIN

v_GS_PROCESSOR ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID

ORDER BY v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_Operating_System.Caption0,

v_GS_Operating_System.Version0, v_GS_Operating_System.LastBootUpTime0, v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0,

v_R_System.AD_Site_Name0

[joengelhart]

Hi,

 

Have modified th eSQL query to exclude IPv6 also if you have multiple IPv4 IP's then it will com in one single row.

 

SELECT v_R_System.Netbios_Name0, CASE WHEN v_GS_PC_BIOS.SerialNumber0 LIKE 'vmware%' THEN 'VM' ELSE v_GS_PC_BIOS.SerialNumber0 END AS [serial No],

v_GS_PC_BIOS.Manufacturer0, v_GS_PROCESSOR.Name0 AS [Processor],

CASE WHEN v_GS_PROCESSOR.ADDRESSWIDTH0 = '32' THEN '32 Bit' ELSE '64 Bit' END AS [Processor Type],

CASE WHEN v_GS_PROCESSOR.IS64BIT0 = '1' THEN 'Yes' ELSE 'No' END AS [64 Bit Supported],

v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 / 1024 AS [Memory(MB)], c.IPAddress AS [iP Address], v_GS_Operating_System.Caption0 AS [Operating system],

v_GS_Operating_System.Version0 AS [Operating system version], v_GS_OPERATING_SYSTEM.ServicePackMajorVersion0, CONVERT(nvarchar(10),

v_GS_OPERATING_SYSTEM.InstallDate0, 101) AS [installed Date], CONVERT(nvarchar(10), v_GS_Operating_System.LastBootUpTime0, 101) AS [Rebooted On],

v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0

FROM v_R_System INNER JOIN

(SELECT t1.resourceid AS rsid2, IPAddress = substring

((SELECT (IP_Addresses0 + ', ')

FROM v_RA_System_IPAddresses t2

WHERE t2.IP_Addresses0 NOT LIKE '169%' AND IP_Addresses0 NOT LIKE '0.%' AND IP_Addresses0 NOT LIKE '%::%' AND

IP_Addresses0 NOT LIKE '192.%' AND t1.resourceid = t2.resourceid

ORDER BY resourceid FOR xml path('')), 1, 50000)

FROM v_RA_System_IPAddresses t1

GROUP BY resourceid) c ON c.rsid2 = v_R_System.resourceid JOIN

 

v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID JOIN

v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_Operating_System.ResourceID JOIN

v_GS_X86_PC_MEMORY ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID JOIN

v_GS_PROCESSOR ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID

ORDER BY v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_Operating_System.Caption0,

v_GS_Operating_System.Version0, v_GS_Operating_System.LastBootUpTime0, v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0,

v_R_System.AD_Site_Name0

 

This was extremely useful to me! Appricate it! I took a few things out for my inviroment but was wondering if it is possible to add Total Minutes on Console and Last Console use to this report?

 

Mine looks like this now:

 

SELECT CASE WHEN v_GS_PC_BIOS.SerialNumber0 LIKE 'vmware%' THEN 'VM' ELSE v_GS_PC_BIOS.SerialNumber0 END AS [serial No],

v_GS_PC_BIOS.Manufacturer0, v_GS_PROCESSOR.Name0 AS [Processor],

v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 / 1024 AS [Memory(MB)], c.IPAddress AS [iP Address],

CONVERT(nvarchar(10),

v_GS_OPERATING_SYSTEM.InstallDate0, 101) AS [installed Date], CONVERT(nvarchar(10), v_GS_Operating_System.LastBootUpTime0, 101) AS [Rebooted On],

v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0

 

FROM v_R_System INNER JOIN

(SELECT t1.resourceid AS rsid2, IPAddress = substring

((SELECT (IP_Addresses0 + ', ')

 

FROM v_RA_System_IPAddresses t2

WHERE t2.IP_Addresses0 NOT LIKE '169%' AND IP_Addresses0 NOT LIKE '0.%' AND IP_Addresses0 NOT LIKE '%::%' AND

IP_Addresses0 NOT LIKE '192.%' AND t1.resourceid = t2.resourceid

ORDER BY resourceid FOR xml path('')), 1, 50000)

 

FROM v_RA_System_IPAddresses t1

GROUP BY resourceid) c ON c.rsid2 = v_R_System.resourceid JOIN

 

v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID JOIN

v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_Operating_System.ResourceID JOIN

v_GS_X86_PC_MEMORY ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID JOIN

v_GS_PROCESSOR ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID

ORDER BY v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_Operating_System.Caption0,

v_GS_Operating_System.Version0, v_GS_Operating_System.LastBootUpTime0, v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0,

v_R_System.AD_Site_Name0