SCCM SQL Queries

Here are queries I've used that 

Count of all operating systems in the environment

select caption0, Count (Caption0) from [dbo].[v_GS_OPERATING_SYSTEM] group by Caption0

Count of all SCCM client version

select Client_Version, Count(Client_Version) from [dbo].[v_ActiveClients] group by Client_Version

Count of Windows 10 Versions

select Caption0 As 'Operating System Name', 
Case When BuildNumber0 = '16299' Then '1709'
when BuildNumber0 = '17134' THEN '1803'
END AS 'Version',
Count(BuildNumber0 ) as '# of Systems'
 from [dbo].[v_GS_OPERATING_SYSTEM]
Where Caption0 not like '%server%' and Caption0 like '%Windows 10 enterprise%' and Caption0 not like '%LTSB%'
group by BuildNumber0, Caption0

List of all Windows 10 Enterprise by Version 
(Modify to add more Case items for the versions in your environment)

SELECT        v_R_System.Name0, v_GS_OPERATING_SYSTEM.Caption0, 

Case When BuildNumber0 = '16299' Then '1709'

when BuildNumber0 = '17134' THEN '1803'

END AS 'Version'

FROM            v_GS_OPERATING_SYSTEM INNER JOIN

                         v_R_System ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID

 Where Caption0 not like '%server%' and Caption0 like '%Windows 10 enterprise%' and Caption0 not like '%LTSB%'

List of Windows Update Agent Version

SELECT        v_R_System.Name0 AS 'Computer Name', v_GS_WINDOWSUPDATEAGENTVERSION.Version0 AS 'Windows Update Version', 
v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System', v_R_System.Client0, v_R_System.Obsolete0 
                         
FROM            v_GS_WINDOWSUPDATEAGENTVERSION INNER JOIN
                         v_R_System ON v_GS_WINDOWSUPDATEAGENTVERSION.ResourceID = v_R_System.ResourceID INNER JOIN
                         v_GS_OPERATING_SYSTEM ON v_GS_WINDOWSUPDATEAGENTVERSION.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
Where Client0 = '1' and Obsolete0 = '0'

ORDER BY 'Windows Update Version'

List of Failed Distributed Packages

SELECT        v_DistributionPointInfo.ServerName AS 'DP Name', v_ContentDistribution.PkgID AS 'Package ID', 
                         CASE WHEN v_ContentDistribution.State = '2' THEN 'Failed' WHEN v_ContentDistribution.State = '0' THEN 'Success' END AS 'Distribution State', v_Package.Name AS 'Package Name', 
                         v_DistributionPointInfo.SiteCode
FROM            v_ContentDistribution INNER JOIN
                         v_Package ON v_ContentDistribution.PkgID = v_Package.PackageID INNER JOIN
                         v_DistributionPointInfo ON v_ContentDistribution.DPID = v_DistributionPointInfo.ID
WHERE        (v_ContentDistribution.State != '0')

CMPivot Join Query Example - SCCM / Intune

I find there are lack of query with joins example and created this from a need to see what model had the new version installed.  We deployed an application to a location and thought the issue was system type related.

Replace the bolded items with the actual names.

The first query is based on a specific naming convention.   

InstalledSoftware | where ProductName contains 'ProductName' and ProductVersion == 'ProductVersion' | join  kind=inner (Device | where Device contains 'DeviceName') | project  Device, Model, ProductName, ProductVersion

This query will return all devices that matches the product conditions.

 InstalledSoftware | where ProductName contains 'ProductName' and ProductVersion == 'ProductVersion' | join  kind=inner (Device) | project  Device, Model, ProductName, ProductVersion