I have a love with dashboards.  I’m not sure where it came from, but I think they are cool.  I think System Center Configuration Manager is cool.  What kind of program can delete an entire network in 15 min?  I have been on a mission to combine these two loves of my life.  I have created a dashboard with SQL SSRS that does a pretty good job at letting me know the client health of my network.

On the left side is the basic client health.  I use it to make sure that my clients are all checking in, running scans, and getting all the updates needed.   The Endpoint Protection section tells me how old my virus definitions are.   Finally the bottom right displays the errors on each SCCM component.  This is very helpful for seeing what might be having issues on a day to day basis.   It appears my AMT project isn’t going so well right now…

A few people have asked for the queries for this.  Some of them have been pulled from other built in reports in SCCM.  The remainder are ones that I have gathered from the IT @ Microsoft site.  They have a 5 or 6 part blog on their dashboards. Let me know if there are any other SQL queries you find interesting!

Endpoint virus definitions

select UpTo1DayOldCount,UpTo3DaysOldCount, UpTo7DaysOldCount, OlderThan7DaysCount, TotalMemberCount from v_EndpointProtectionHealthStatus
where CollectionID=’Collection ID Here‘

Hardware and Software Scans

DECLARE @NullVal datetime

SELECT sites.SMS_Assigned_Sites0   AS AssignedSite

,TotalSys.Total AS TotalActiveClients

,SuccSys.Succ AS HWSuccess

,SuccSW.Succ AS SWSuccess

,CONVERT(decimal(5,2),(SuccSys.Succ*100.00/TotalSys.Total)) AS ‘HW Percentage’

,CONVERT(decimal(5,2),(SuccSW.Succ*100.00/TotalSys.Total)) AS ‘SW Percentage’

FROM v_RA_system_smsassignedsites sites

INNER JOIN (

SELECT sit.SMS_Assigned_Sites0 AS AssSite

,COUNT(DISTINCT sis.Netbios_Name0) AS Succ

FROM v_RA_System_SMSAssignedSites sit

INNER JOIN v_R_System sis

ON sit.ResourceID = sis.ResourceID

INNER JOIN v_gs_workstation_status sts

ON sis.ResourceID = sts.ResourceID

AND sis.Client0 = 1

AND sis.Obsolete0 = 0

AND sis.Active0 = 1

AND sts.LastHWScan > 7

GROUP BY sit.sms_assigned_sites0

) SuccSys

ON sites.SMS_Assigned_Sites0 = SuccSys.AssSite

INNER JOIN (

SELECT sit.SMS_Assigned_Sites0 AS AssSite

,COUNT(DISTINCT sis.Netbios_Name0) AS Succ

FROM v_RA_System_SMSAssignedSites sit

INNER JOIN v_R_System sis

ON sit.ResourceID = sis.ResourceID

INNER JOIN v_GS_LastSoftwareScan sts

ON sis.ResourceID = sts.ResourceID

AND sis.Client0 = 1

AND sis.Obsolete0 = 0

AND sis.Active0 = 1

AND sts.LastScanDate > 10

GROUP BY sit.SMS_Assigned_Sites0

) SuccSW

ON SuccSW.AssSite = sites.SMS_Assigned_Sites0

INNER JOIN (

SELECT sit.SMS_Assigned_Sites0 AS AssSite

,COUNT(DISTINCT sis.Netbios_Name0) AS Total

FROM v_RA_System_SMSAssignedSites sit

INNER JOIN v_R_system sis

ON sit.ResourceID = sis.ResourceID

AND sis.Client0 = 1

AND sis.Obsolete0 = 0

AND sis.Active0 = 1

GROUP BY sit.SMS_Assigned_Sites0

) TotalSys

ON sites.SMS_Assigned_Sites0 = TotalSys.Asssite

GROUP BY sites.SMS_Assigned_Sites0

,TotalSys.Total

,SuccSys.Succ

,SuccSW.Succ

ORDER BY 4 DESC

Client Active

WITH CollectionSummary(CollectionID, ClientsActive, ClientsActiveHealthyOrActiveNoResults, ClientsActiveUnhealthy) AS
(
SELECT CollectionID,
SUM(CASE WHEN ClientActiveStatus = 1 THEN 1
ELSE 0 END) AS ClientsActive,
SUM(CASE WHEN ClientState=1 OR ClientState=3 THEN 1
ELSE 0 END) AS ClientsActiveHealthyOrActiveNoResults,
SUM(CASE WHEN ClientState=2 THEN 1
ELSE 0 END) AS ClientsActiveUnhealthy
FROM v_FullCollectionMembership AS cm
LEFT OUTER JOIN v_CH_ClientSummary AS cs ON cm.ResourceID = cs.ResourceID
LEFT OUTER JOIN v_R_System AS rsys ON rsys.ResourceID = cs.ResourceID

GROUP BY cm.CollectionID
)

SELECT SiteID AS CollectionID,
ISNULL(ClientsActive, 0) AS ClientsActive,
ISNULL(ClientsActiveHealthyOrActiveNoResults, 0) AS ClientsActiveHealthyOrActiveNoResults,
ISNULL(ClientsActiveUnhealthy, 0) AS ClientsActiveUnhealthy
FROM v_Collections coll
LEFT OUTER JOIN CollectionSummary summary ON coll.SiteID = summary.CollectionID
WHERE coll.SiteID = ‘SMS00001’

SMS Component issues

SELECT Top 5
Component
,COUNT(*) as ‘Count’
, MachineName
FROM v_StatusMessage sm WITH (NOLOCK)
WHERE
ModuleName = ‘SMS Server’
AND Sm.Severity != 1073741824
AND Time > DATEADD(hour, -24, GetutcDate())
AND SiteCode in (‘Insert Site Code’)
GROUP BY
MachineName, Component
ORDER BY count DESC

WSUS Compliance 

SELECT
vc.Name,at.title,
CollectionID=’Insert Collection ID‘,
Status=sn.StateName,
NumberOfComputers=count(*),
Per_Compliant =convert(numeric(5,2), (isnull(count(*), 0)* 100.00 / isnull(nullif((SELECT count(*) FROM v_ClientCollectionMembers WHERE CollectionID=’Insert Collection ID‘) , 0), 1)))
FROM v_ClientCollectionMembers cm
JOIN v_Collection vc on vc.collectionID = cm.collectionid
JOIN v_UpdateGroupStatus_Live cs on cs.CI_ID in (SELECT CI_ID FROM v_authlistinfo WHERE Title = ‘Update Group Name‘) AND cs.ResourceID=cm.ResourceID
LEFT JOIN v_StateNames sn on sn.TopicType=300 AND sn.StateID=isnull(cs.Status, 0)
JOIN v_authlistinfo at on cs.CI_ID=at.CI_ID
WHERE
cm.CollectionID=‘Insert Collection ID’ and sn.StateName = ‘Compliant’
GROUP BY
sn.StateName,vc.Name, at.title
ORDER BY
count(*) desc

Heartbeat and WSUS Scans

DECLARE @olddate datetime
SET @olddate=DATEADD(hour,-24, GETUTCDATE())

SELECT tot.SiteCode
,tot.TotalClient
,ptc.ScanTotal [ScanSuccess]
,CONVERT(decimal(5,2),(ptc.ScanTotal*100.00/tot.TotalClient)) [ScanSuccessPerc]
,hrt.HBCount [Heartbeat]
,CONVERT(decimal(5,2),(hrt.HBCount*100.00/tot.TotalClient)) [HeartbeatPerc]
FROM (
SELECT sit.SMS_Assigned_Sites0 [SiteCode]
,COUNT(1) TotalClient
FROM v_R_System sis
INNER JOIN v_RA_System_SMSAssignedSites sit
ON sis.ResourceID = sit.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
GROUP BY sit.SMS_Assigned_Sites0
) tot
LEFT OUTER JOIN (
SELECT sit.SMS_Assigned_Sites0 [SiteCode]
,COUNT(1) [ScanTotal]
FROM v_updateScanStatus upp
INNER JOIN v_statenames stn
ON upp.LastScanState = stn.StateID
AND stn.TopicType = ‘501’
AND stn.StateName = ‘Scan completed’
INNER JOIN v_RA_System_SMSAssignedSites sit
ON upp.ResourceID = sit.ResourceID
AND upp.LastScanPackageLocation LIKE ‘http%’
AND upp.LastScanTime > @olddate
GROUP BY upp.LastScanState
,sit.SMS_Assigned_Sites0
) ptc
ON tot.SiteCode = ptc.SiteCode
LEFT OUTER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS [SiteCode]
,COUNT (sis.name0) AS [HBCount]
FROM v_R_System sis
INNER JOIN (
SELECT a.ResourceID, a.AgentSite, b.AgentTime
FROM v_AgentDiscoveries a
INNER JOIN (
SELECT ResourceID, MAX(AgentTime) AS AgentTime
FROM v_AgentDiscoveries
WHERE AgentName LIKE ‘%Heartbeat%’
AND AgentTime > @olddate
GROUP BY ResourceID
) b
ON a.ResourceID = b.ResourceID
AND a.AgentTime = b.AgentTime
) hrt
ON sis.ResourceId = hrt.ResourceID
INNER JOIN v_RA_System_SMSAssignedSites sit
ON sis.resourceID = sit.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
GROUP BY sit.SMS_Assigned_Sites0
) hrt
ON tot.SiteCode = hrt.SiteCode
LEFT OUTER JOIN (
SELECT sub.[Site] AS [SiteCode]
,SUM(CASE sub.HealthState WHEN 1 THEN sub.Cnt END) AS [MPComunicatonSuccess]
,SUM(CASE sub.HealthState WHEN 2 THEN sub.Cnt END) AS [MPComunicatonFailure]
,SUM(sub.Cnt) AS [MPComunicatonTotal]
FROM (
SELECT sit.SiteCode [Site]
,chs.HealthState
,COUNT(chs.HealthState) [Cnt]
FROM v_Site sit
INNER JOIN v_ClientHealthState chs
ON sit.SiteCode = chs.AssignedSiteCode
AND chs.HealthType = ‘1000’
AND chs.LastHealthReportDate > @olddate
AND sit.[Type] = 2
GROUP BY sit.SiteCode
,chs.HealthState
) sub
GROUP BY sub.[Site]
) mpc
ON tot.SiteCode = mpc.SiteCode