SQL Queries for SCCM Sites

The below queries are useful for assessing the site and hierarchy information from SCCM Site servers.

1.Site Roles

select SiteCode, RoleName, ServerName from SysResList

2.Hierarchy information including site version

select * from sites

3.Total Secondary sites

select CAST(Version as varchar) 'Category', count(*) 'SiteCount' from v_Site (NOLOCK) where Type<>1 group by Version UNION select 'Total Primary Sites' 'Category', count(*) 'SiteCount' from v_Site (NOLOCK) where Type<>1 ORDER BY Category DESC

4.Client count by site

select sms_assigned_sites0, count(distinct name0) from v_RA_System_SMSAssignedSites ass join v_R_System sys on ass.resourceID=sys.resourceID

where client0=1 and obsolete0=0 and active0=1

group by sms_assigned_sites0 order by count(distinct name0) desc

5.MP Communication Status in last 7 days

declare @olddate datetime, @NullVal datetime

set @olddate=DATEADD(day,-7, getdate())

set @NullVal = CONVERT(datetime,'1/1/1980')

select distinct v_site.sitecode as Site,

(select count(*) from v_ClientHealthState

where healthtype ='1000' and healthstate ='1'and lasthealthreportdate >@olddate and assignedsitecode =v_site.sitecode) as "Client successfully communicating with MP",

(select count(*) from v_ClientHealthState

where healthtype ='1000' and healthstate ='2'and lasthealthreportdate >@olddate and assignedsitecode =v_site.sitecode ) as "Client failing to communicate with MP",

(select count(*) from v_ClientHealthState

where healthtype ='1001' and healthstate ='1'and lasthealthreportdate >@olddate and assignedsitecode =v_site.sitecode ) as "Client successfully retrieving certificate",

(select count(*) from v_ClientHealthState

where healthtype ='1001' and healthstate ='2'and lasthealthreportdate >@olddate and assignedsitecode =v_site.sitecode ) as "Client failing to retrieve certificate"

from v_site join v_ClientHealthState on v_site.sitecode =v_ClientHealthState.assignedsitecode

and v_site.type=2

order by v_site.sitecode

6.WSUS Scan status in last 10 hours

 

select count(*)as clients,statename,upp.lastscanpackagelocation,sit.sms_assigned_sites0

from v_updateScanStatus upp

  join v_statenames stat on stat.stateid = upp.lastscanstate

 join v_RA_System_SMSAssignedSites sit on sit.resourceid = upp.resourceid

and stat.topictype ='501' and upp.lastscanpackagelocation like'http%'

and lastscantime >DATEADD(hour,-10, getutcdate())and lastscantime < getutcdate()

group by upp.lastscanstate,stat.statename,upp.lastscanpackagelocation,sit.sms_assigned_sites0

order by sit.sms_assigned_sites0,clients desc

7.SCCM Clients installed per each AD-Site

select

    AD_Site_Name0 as 'AD Site Name',count(DISTINCT name0) as 'Clients Installed'

FROM v_R_System where Active0 = 1 and Client0 =1 and Obsolete0 != 1 and ad_site_name0 != 'null' group by AD_Site_Name0