SQL to generate Asset Information – Configuration Manager SCCM 2012
SELECT DISTINCT s.Netbios_Name0 AS ComputerName,
s.Operating_System_Name_and0 AS OSName,
pr.Name0 AS ProcessorTypeSpeed,
pr.Manufacturer0 Manufacturer,
pr.NumberOfCores0 Cores,
pr.NumberOfLogicalProcessors0 LgicalProcessorCount,
case when pr.DataWidth0=64 then '64 bit'else'32 bit' end DataWidth,
m.TotalPhysicalMemory0/1024.00 AS MemoryMB,
GS1.TotalVirtualMemorySize0 VirtualMemory,
GS1.TotalVisibleMemorySize0 VisibleMemory,
ip.IPAddress0,
T1.COL AS TotalDriveSize,
LastBootUpTime0,
DATEDIFF(Day,GS1.LastBootUpTime0, GETDATE()) AS [Days since last boot]
FROM v_R_System_Valid s
INNER JOIN v_GS_PROCESSOR pr ON s.ResourceID = pr.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM gs ON s.ResourceID = gs.ResourceID
INNER JOIN v_GS_NETWORK_ADAPTER ON s.ResourceID = v_GS_NETWORK_ADAPTER.ResourceID
INNER JOIN v_GS_X86_PC_MEMORY m ON s.ResourceID = m.ResourceID
INNER JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION ip ON s.ResourceID = ip.ResourceID
-- INNER JOIN v_GS_LOGICAL_DISK AS ld ON s.ResourceID = ld.ResourceID
INNER JOIN
( SELECT RESOURCENAME,
col
FROM
(
SELECT DISTINCT TAB.Netbios_Name0 RESOURCENAME,
(
SELECT COL.deviceid0 +' '+ cast(COL.Size0/1024.00 AS varchar(20))+' '
FROM v_GS_LOGICAL_DISK COL
WHERE
COL.ResourceID = TAB.ResourceID AND COL.DriveType0=3
FOR XML PATH ('')
) COL
FROM v_R_System_Valid TAB
)T
where T.COL is NOT NULL
) T1 on T1.RESOURCENAME=s.Netbios_Name0
INNER JOIN V_GS_OPERATING_SYSTEM GS1 on GS1.ResourceID=s.ResourceID
WHERE
s.Operating_System_Name_and0 LIKE '%Windows NT Server%'
AND
ip.IPAddress0 IS NOT NULL AND ip.DefaultIPGateway0 IS NOT NULL
2015-12-11
653 reads