SCCM SQL Qury - SCCM Client Distribution Point

SCCM SQL Qury - SCCM Client Distribution Point

This Query will help to identify SCCM client DP details, if you want to know from which DP you client download the content, Use the below SQL query

select
rsys.Name0 as 'Client Name',
"Content Source Type" = CASE cdh.DistributionPointType
WHEN 1 THEN 'Cloud DP (Distribution Point)'
WHEN 2 THEN 'Management Point'
WHEN 3 THEN 'Peer Cache'
WHEN 4 THEN 'DP (Distribution Point)'
WHEN 5 THEN 'BranchCache'
END
,
cdh.HostName as 'Content Source Host Name',
pkg.Name as'Content Name',
cdh.ContentID,
cdh.BytesDownloaded
from
v_R_System as rsys
join v_ClientDownloadHistoryDP_BG as cdh on rsys.ResourceID = cdh.ClientId
join v_Content as con on con.Content_UniqueID = cdh.ContentID
join v_Package as pkg on con.PkgID = pkg.PackageID
where pkg.name = <your package, application, update name>

SCCM - SQL Query SUP WSUS Syncronization Custom Report

SCCM - SQL Query SUP WSUS Syncronization Custom Report

SELECT SiteCode, WSUSServerName, WSUSSourceServer, SyncCatalogVersion, LastSuccessfulSyncTime,
CASE [LastSyncState]
WHEN 6700 THEN 'WSUS Sync Manager Error'
WHEN 6701 THEN 'WSUS Synchronization Started'
WHEN 6702 THEN 'WSUS Synchronization Done'
WHEN 6703 THEN 'WSUS Synchronization Failed'
WHEN 6704 THEN 'WSUS Synchronization In Progress Phase Synchronizing WSUS Server'
WHEN 6705 THEN 'WSUS Synchronization In Progress Phase Synchronizing SMS Database'
WHEN 6706 THEN 'WSUS Synchronization In Progress Phase Synchronizing Internet facing WSUS Server'
WHEN 6707 THEN 'Content of WSUS Server is out of sync with upstream server'
WHEN 6709 THEN 'SMS Legacy Update Synchronization started'
WHEN 6710 THEN 'SMS Legacy Update Synchronization done'
WHEN 6711 THEN 'SMS Legacy Update Synchronization failed'
END AS 'Last Sync State', LastSyncStateTime, (Datediff(d,LastSyncStateTime,GETDATE())) as 'DateDiff'

FROM vSMS_SUPSyncStatus

SCCM - SQL Query Machine without boundaries

SCCM - SQL Query Machine without boundaries 

SELECT DISTINCT

v_R_System.Name0,

v_R_System.Client0,

v_RA_System_IPAddresses.IP_Addresses0,

v_RA_System_IPSubnets.IP_Subnets0,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0

FROM v_R_System LEFT OUTER JOIN

v_RA_System_IPSubnets ON v_R_System.ResourceID = v_RA_System_IPSubnets.ResourceID LEFT OUTER JOIN

v_RA_System_IPAddresses ON v_R_System.ResourceID = v_RA_System_IPAddresses.ResourceID LEFT OUTER JOIN

v_RA_System_SMSAssignedSites ON v_R_System.ResourceID = v_RA_System_SMSAssignedSites.ResourceID

WHERE (v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 IS NULL)

AND (NOT (v_RA_System_IPAddresses.IP_Addresses0 IS NULL))

AND (v_R_System.Client0 IS NULL)

AND (NOT (v_RA_System_IPSubnets.IP_Subnets0 IS NULL))


order by v_RA_System_IPSubnets.IP_Subnets0

SCCM - SQL Query Boundary / Boundary Group with Site System Details

SCCM - SQL Query Boundary / Boundary Group with Site System Details

SELECT BG.GROUPID,
       B.BOUNDARYID,
       B.BOUNDARYTYPE,
       BG.NAME [BOUNDARY GROUP NAME],
       B.DISPLAYNAME [BOUNDARY NAME],
       B.VALUE,
       CASE B.BOUNDARYTYPE
           WHEN '1' THEN 'AD Site'
           WHEN '0' THEN 'IP Subnet'
           WHEN '3' THEN 'IP Range'
       END AS 'Type',
       SUBSTRING(BGS.SERVERNALPATH, CHARINDEX('\\', BGS.SERVERNALPATH)+2,(CHARINDEX('"]', BGS.SERVERNALPATH) - CHARINDEX('\\', BGS.SERVERNALPATH))-3) AS 'Site System'
FROM VSMS_BOUNDARY B
INNER JOIN VSMS_BOUNDARYGROUPMEMBERS BGM ON B.BOUNDARYID = BGM.BOUNDARYID
INNER JOIN VSMS_BOUNDARYGROUP BG ON BG.GROUPID = BGM.GROUPID
INNER JOIN VSMS_BOUNDARYGROUPSITESYSTEMS BGS ON BGS.GROUPID = BGM.GROUPID

ORDER BY BG.GROUPID ASC


---------********--------------

SELECT DISTINCT BG.GROUPID,
BG.NAME [BOUNDARY GROUP NAME],
SUBSTRING(BGS.SERVERNALPATH, CHARINDEX('\\', BGS.SERVERNALPATH)+2,(CHARINDEX('"]', BGS.SERVERNALPATH) - CHARINDEX('\\', BGS.SERVERNALPATH))-3) AS 'Site System'
FROM VSMS_BOUNDARY B
INNER JOIN VSMS_BOUNDARYGROUPMEMBERS BGM ON B.BOUNDARYID = BGM.BOUNDARYID
INNER JOIN VSMS_BOUNDARYGROUP BG ON BG.GROUPID = BGM.GROUPID
INNER JOIN VSMS_BOUNDARYGROUPSITESYSTEMS BGS ON BGS.GROUPID = BGM.GROUPID
ORDER BY BG.GROUPID ASC