Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PowerON PowerBI SCCM 1806 Dashboard Fixes
select
SYS.ResourceID,
OPSYS.Caption0 [Operating System],
sys.Build01 [OS Version],
WSLN.Value Version,
CASE WSS.Branch
WHEN '0' THEN 'Current Branch'
WHEN '1' THEN 'Current Branch for Business'
WHEN '2' THEN 'Long Term Servicing Branch'
END as 'Branch',
CASE WSS.State
WHEN '1' THEN 'Release Ready'
WHEN '2' THEN 'Business Ready'
WHEN '3' THEN 'Expiring Soon'
WHEN '4' THEN 'Near End of Life'
END as 'State',
CASE WSLN.Value
WHEN '2015 LTSB' THEN 'Windows 10, 2015 LTSB'
WHEN '2016 LTSB' THEN 'Windows 10, 2016 LTSB'
WHEN '1507' THEN 'Windows 10, released July 2015 (version 1507)'
WHEN '1511' THEN 'Windows 10, version 1511'
WHEN '1607' THEN 'Windows 10, version 1607'
WHEN '1703' THEN 'Windows 10, version 1703'
WHEN '1709' THEN 'Windows 10, version 1709'
WHEN '1803' THEN 'Windows 10, version 1803'
END as 'OS Support Name',
WSLN.LocaleID,
WSLN.Name
--SYS.User_Name0 [Primary User],
from v_R_System SYS
JOIN vSMS_WindowsServicingStates WSS on SYS.OSBranch01 = WSS.Branch and SYS.Build01 = WSS.Build
JOIN vSMS_WindowsServicingLocalizedNames WSLN on WSS.Name = WSLN.Name
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 SYS.ResourceID = OPSYS.ResourceID and OPSYS.Caption0 like '%Windows 10%'
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(TotalSecurityLogTime0) as logtime, MAX(Timestamp) as rev FROM v_GS_SYSTEM_CONSOLE_USAGE GROUP BY Resourceid) b ON a.Resourceid = b.Resourceid AND a.TotalSecurityLogTime0 = b.logtime 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.