ConfigMgr SQL Queries: Devices Query

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.