I recently released my latest Configuration Manager Power BI current branch dashboard with PowerON last month; The ConfigMgr devices SQL query is something I’d thought I generally share as really useful.

This SQL query contains the majority of the columns you’ll need to get you going from Device OS, Chassis Types, AD site, IP addresses and more! I also tidy a few things up like device manufacturer which is a mismatch depending on who the provider is.

ConfigMgr Devices SQL Query

</pre> DECLARE @Now DateTime = GetDate() ;WITH Devices AS ( SELECT distinct ci.MachineID [ResourceID], ci.Name, ci.[ClientVersion] [Client Version] , --ci.[IsClient] [Client], ci.[LastStatusMessage] [Last Status Message], ci.[LastPolicyRequest] [Last Policy Request], ci.[LastDDR] [Last DDR], ci.[LastHardwareScan] [Last Hardware Scan], ci.[LastSoftwareScan] [Last Software Scan], ci.[LastMPServerName] [Last MP Server], CH.ClientActiveStatus [Client Active Status], CH.ClientStateDescription [Client State], CH.ExpectedNextPolicyRequest [Expected Next Policy Request], ci.[Domain], ci.ADSiteName [AD Site], --SYS.AD_Site_Name0 [AD Site], ci.LastActiveTime [Last Active Time], ci.isvirtualmachine [Is VM], case When DATEDIFF(dd,ci.LastActiveTime,@Now) between 0 and 7 then 'Past Week' When DATEDIFF(dd,ci.LastActiveTime,@Now) between 8 and 14 then 'Last 2 Weeks' When DATEDIFF(dd,ci.LastActiveTime,@Now) between 15 and 21 then 'Last 3 Weeks' When DATEDIFF(dd,ci.LastActiveTime,@Now) between 22 and 29 then 'Last 4 Weeks' When DATEDIFF(dd,ci.LastActiveTime,@Now) between 30 and 60 then 'Last 2 Months' When DATEDIFF(dd,ci.LastActiveTime,@Now) between 61 and 89 then 'Last 3 Months' When DATEDIFF(dd,ci.LastActiveTime,@Now) >= 90 then 'Over 3 Months' Else 'Never' End As 'Last Active', ci.Username [Last Logon Username], ci.CNLastOnlineTime [Last Online Time], ci.CNLastOfflineTime [Last Offline Time], SYS.Creation_Date0 [Creation Date], OPSYS.Caption0 as OS, Case When OPSYS.Caption0 like '%Windows 7%' Then 'Windows 7 Professional' When OPSYS.Caption0 like '%Windows 8.1%' Then 'Windows 8.1' When OPSYS.Caption0 like '%2015 LTSB%' Then 'Windows 10 2015 LTSB' When OPSYS.Caption0 like '%2016 LTSB%' Then 'Windows 10 2016 LTSB' When OPSYS.Caption0 like '%Windows 10%' Then 'Windows 10' Else '' End As 'OS Support Name' , OPSYS.Version0 [OS Version], CASE WHEN ci.[DeviceOS] like '%Workstation%' THEN 'Workstation' WHEN ci.[DeviceOS] like '%Server%' THEN 'Server' ELSE NULL END AS 'OS Type', STUFF((SELECT (N','+IPAddr.IP_Addresses0) AS [text()] FROM v_RA_System_IPAddresses IPAddr WHERE ci.MachineID = IPAddr.ResourceID for xml path(N'')) ,1,1,N'') as [IP Addresses], -- if there are multiple IP address then combine them together --CSYS.Manufacturer0 [Manufacturer], CASE when CSYS.Manufacturer0 Like 'Microsoft%' then 'Microsoft' when CSYS.Manufacturer0 = 'LENOVO' then 'Lenovo' when CSYS.Manufacturer0 Like 'Dell%' then 'Dell' when CSYS.Manufacturer0 = 'Hewlett-Packard' then 'HP' ELSE CSYS.Manufacturer0 END AS 'Manufacturer', CSYS.Model0 [Model], --Processor.Name0 [CPU], --CASE --when Processor.Manufacturer0 like '%Intel%' then 'Intel' --when Processor.Manufacturer0 Like '%AMD%' then 'AMD' --ELSE Processor.Manufacturer0 --END AS 'Processor', bios.SerialNumber0 'Serial Number', --Processor.Manufacturer0 [Processor], --Processor.MaxClockSpeed0 [Max Clock Speed], MEM.TotalPhysicalMemory0 / 1024 As [Physical Memory (MB)], Ram.[Memory Slots], CASE WHEN chs.ChassisTypes0 = 1 THEN 'Other' WHEN chs.ChassisTypes0 = 2 THEN 'Unknown' WHEN chs.ChassisTypes0 = 3 THEN 'Desktop' WHEN chs.ChassisTypes0 = 4 THEN 'Low Profile Desktop' WHEN chs.ChassisTypes0 = 5 THEN 'Pizza Box' WHEN chs.ChassisTypes0 = 6 THEN 'Mini Tower' WHEN chs.ChassisTypes0 = 7 THEN 'Tower' WHEN chs.ChassisTypes0 = 8 THEN 'Portable' WHEN chs.ChassisTypes0 = 9 THEN 'Laptop' WHEN chs.ChassisTypes0 = 10 THEN 'Notebook' WHEN chs.ChassisTypes0 = 11 THEN 'Hand Held' WHEN chs.ChassisTypes0 = 12 THEN 'Docking Station' WHEN chs.ChassisTypes0 = 13 THEN 'All in One' WHEN chs.ChassisTypes0 = 14 THEN 'Sub Notebook' WHEN chs.ChassisTypes0 = 15 THEN 'Space-Saving' WHEN chs.ChassisTypes0 = 16 THEN 'Lunch-Box' WHEN chs.ChassisTypes0 = 17 THEN 'Main System Chassis' WHEN chs.ChassisTypes0 = 18 THEN 'Expansion Chassis' WHEN chs.ChassisTypes0 = 19 THEN 'Sub Chassis' WHEN chs.ChassisTypes0 = 20 THEN 'Bus Expansion Chassis' WHEN chs.ChassisTypes0 = 21 THEN 'Peripheral Chassis' WHEN chs.ChassisTypes0 = 22 THEN 'Storage Chassis' WHEN chs.ChassisTypes0 = 23 THEN 'Rack Mount Chassis' WHEN chs.ChassisTypes0 = 24 THEN 'Sealed-Case PC' ELSE NULL END AS 'Chassis Type', --chs.SerialNumber0 [Serial Number ID], --chs.[SMBIOSAssetTag0], scu.TopConsoleUser0 [Top Console User], frm.SecureBoot0 [Secure Boot], frm.UEFI0 [UEFI], tpm.SpecVersion0 [TPM Spec Version], tpm.IsActivated_InitialValue0 [TPM Activated], tpm.IsEnabled_InitialValue0 [TPM Enabled], tpm.IsOwned_InitialValue0 [TPM Owned], tps.IsReady0 [TPM Ready], tps.Information0 [TPM Information], tps.IsApplicable0 [TPM Applicable], --csys.systemtype0 [Architecture], REPLACE (csys.systemtype0,'-based PC','') [Architecture], bios.Manufacturer0 [Bios Manufacturer], bios.SMBIOSBIOSVersion0 [Bios Version], bios.ReleaseDate0 [Bios Released Date], ci.IsObsolete [Obsolete] FROM vSMS_CombinedDeviceResources ci LEFT JOIN v_R_System SYS on ci.MachineID = SYS.ResourceID LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID --LEFT JOIN v_GS_PROCESSOR Processor on Processor.ResourceID = SYS.ResourceID LEFT JOIN (SELECT a.Resourceid, a.RevisionID, Caption0, Version0, a.GroupID, a.TimeStamp FROM v_GS_OPERATING_SYSTEM a INNER JOIN (SELECT Distinct Resourceid, MAX(TimeStamp) rev FROM v_GS_OPERATING_SYSTEM GROUP BY Resourceid) b ON a.Resourceid = b.Resourceid AND a.TimeStamp = b.rev ) OPSYS ON OPSYS.ResourceID = SYS.ResourceID LEFT JOIN (SELECT a.Resourceid, a.RevisionID, TotalPhysicalMemory0, a.GroupID, a.TimeStamp FROM v_GS_X86_PC_MEMORY a INNER JOIN (SELECT Distinct Resourceid, MAX(TimeStamp) rev FROM v_GS_X86_PC_MEMORY GROUP BY Resourceid) b ON a.Resourceid = b.Resourceid AND a.TimeStamp = b.rev ) MEM ON MEM.ResourceID = SYS.ResourceID LEFT JOIN (SELECT a.Resourceid, a.RevisionID, a.ChassisTypes0, a.SerialNumber0, a.SMBIOSAssetTag0, a.GroupID, a.TimeStamp FROM v_GS_SYSTEM_ENCLOSURE a INNER JOIN (SELECT Resourceid, MAX(TimeStamp) rev FROM v_GS_SYSTEM_ENCLOSURE GROUP BY Resourceid) b ON a.Resourceid = b.Resourceid AND a.TimeStamp = b.rev Where GroupID = 1) chs ON chs.ResourceID = sys.ResourceID LEFT JOIN (select ResourceID, COUNT(RAM.ResourceID) [Memory Slots] from v_GS_PHYSICAL_MEMORY RAM Group By ResourceID) Ram ON Sys.ResourceID = Ram.ResourceID Left JOIN (SELECT a.Resourceid, a.RevisionID, a.[TopConsoleUser0], a.TimeStamp FROM v_GS_SYSTEM_CONSOLE_USAGE a INNER JOIN (SELECT Resourceid, MAX(TimeStamp) rev FROM v_GS_SYSTEM_CONSOLE_USAGE GROUP BY Resourceid) b ON a.Resourceid = b.Resourceid AND a.TimeStamp = b.rev ) scu ON scu.ResourceID = sys.ResourceID LEFT JOIN (SELECT a.Resourceid, a.RevisionID, a.SecureBoot0, a.UEFI0, a.TimeStamp FROM v_GS_FIRMWARE a INNER JOIN (SELECT Resourceid, MAX(TimeStamp) rev FROM v_GS_FIRMWARE GROUP BY Resourceid) b ON a.Resourceid = b.Resourceid AND a.TimeStamp = b.rev) frm ON SYS.ResourceID = frm.ResourceID LEFT JOIN (SELECT a.Resourceid, a.RevisionID, a.IsReady0, a.Information0, a.IsApplicable0, a.TimeStamp FROM v_GS_TPM_STATUS a INNER JOIN (SELECT Resourceid, MAX(TimeStamp) rev FROM v_GS_TPM_STATUS GROUP BY Resourceid) b ON a.Resourceid = b.Resourceid AND a.TimeStamp = b.rev) tps ON SYS.ResourceID = tps.ResourceID LEFT JOIN (SELECT a.Resourceid, a.RevisionID, a.SpecVersion0, a.IsActivated_InitialValue0, a.IsEnabled_InitialValue0, a.IsOwned_InitialValue0, a.TimeStamp FROM v_GS_TPM a INNER JOIN (SELECT Resourceid, MAX(TimeStamp) rev FROM v_GS_TPM GROUP BY Resourceid) b ON a.Resourceid = b.Resourceid AND a.TimeStamp = b.rev) tpm ON SYS.ResourceID = tpm.ResourceID LEFT JOIN (SELECT a.Resourceid, a.RevisionID, a.[Manufacturer0], a.[ReleaseDate0], a.[SMBIOSBIOSVersion0], a.SerialNumber0, a.TimeStamp FROM [v_GS_PC_BIOS] a INNER JOIN (SELECT Resourceid, MAX(TimeStamp) rev FROM v_GS_PC_BIOS GROUP BY Resourceid) b ON a.Resourceid = b.Resourceid AND a.TimeStamp = b.rev) bios ON SYS.ResourceID = bios.ResourceID LEFT JOIN v_CH_ClientSummary CH on SYS.ResourceID = CH.ResourceID Where ci.ClientType =1 and ci.EAS_DeviceID IS NULL ) Select Distinct * from Devices <pre>

Update 02/08/2018 – Updated above code to show the latest query which has timestamp amendments.