Show all Pending Content Distribution for a Specific Package

The following SQL query displays all pending content distribution for a specific package. This will display all distribution points that are not currently in state 0 for a specific package. Replace the package ID for ‘packageid’ in the where clause. /* Specific Content Pending to All DPs */ select p.pkgid as ‘PKGID’, s.installstatus as ‘Content Status’, p.name as…

Show all Pending Content Distribution to a Specific DP

The following SQL query displays all pending content distribution to a specific distribution point. This will display all packages that are not currently in state 0 to a specific distribution point. Replace the name of the distribution point for ‘DPNAME%’. /* All Content Pending to a Specific DP */ select p.pkgid as ‘PKGID’, s.installstatus as ‘Content Status’,…

Show all Pending Content Distribution

The following SQL query display all pending content distribution to all distribution points. This will display all packages that are not currently in state 0. /* All Content in Flight to All DPs */ select p.pkgid as ‘PKGID’, s.installstatus as ‘Content Status’, p.name as ‘Content Name’,(p.SourceSize/1024) as ‘Content Size(MB)’, s.state as ‘Content State’, Count(substring(servername,0,charindex(‘.’,servername))) as…

Count Duplicate Names

The following SQL query counts duplicate computer names. The first query looks for duplicate names only. If you have an issue where client records are not populating canonical names (OU path in AD), the second query look for duplicate names that have at least one client record with NULL for the canonical name. /* Count Duplicate Names */…

Count Windows Update Agent versions

The following SQL query counts the Windows Update Agent (WUA) versions by workstations and servers. The WUA is updated via Windows Updates and is the component on Windows systems that applies and reports compliances for patches. /*  Count Windows Update Agents by Platform and WUA versions */ SELECT ‘Workstation’ as ‘Platform’, WUAV.Version0 as ‘Version’, count(*) as ‘Count’…

Count SCCM Client Versions

The following SQL query counts the SCCM client versions by workstations and servers. Note: this will only return count of Windows based clients. If you have Linux/Unix based or Mac, the where clause should be modified to remove Operating_System_Name_and0 criteria. /* Count CM Client Versions by Workstations and Servers */ select ‘Workstations’ as ‘Platform’, Client_Version0,…

Show Maintenance Windows for a Computer

The following SQL query displays a list of maintenance windows for a given computer (SCCM client). Computer name in this example is ‘Computer1’. SELECT FCM.Name AS ‘Client Name’, C.Name AS ‘Collection Name’, SW.CollectionID, SW.Name, SW.Description, SW.StartTime, SW.Duration, SW.IsEnabled FROM v_ServiceWindow SW JOIN v_Collection C ON C.CollectionID = SW.CollectionID JOIN v_FullCollectionMembership FCM ON FCM.CollectionID = C.CollectionID WHERE…