Creating a Simple ConfigMgr 2012 R2 Dashboard Using SMSProv.log and SSRS
I recently saw a cool ConfigMgr dashboard by the guys over at CoreTech (https://coretech.dk/products/dashboard/) and I got the idea to try and mirror the look and feel of their dashboard.
Here are the things I wanted to capture in my dashboard:
- Software Update Deployment Compliance
- System Center Endpoint Definition Compliance
- Client Health
- Top 5 Threats Detected
- Overall Definition Status
- Count of OS Type
If a ConfigMgr administrator needed to, they could view each of those items, in difference places, in the ConfigMgr console. However, it becomes interesting if this data is needed by someone who doesn't have access to the console. With SSRS, and using the SMSProv.log we can easily build a simple dashboard! That dashboard could then be used with a subscription in SSRS and delivered on a schedule, etc.
When a node in the console is viewed, the underlying SQL that is running will be exposed in the SMSProv.log. For example, let's look at our Software Update Groups node in the console and view the SMSProv.log:
Here are the SQL queries grabbed from the SMSProv.log and used for my datasets in SSRS:
Dataset1:
select all
SMS_AuthorizationList.ApplicabilityCondition,SMS_AuthorizationList.AssociatedAutoRuleID,SMS_AuthorizationList.CI_ID,
SMS_AuthorizationList.CI_UniqueID,SMS_AuthorizationList.CIType_ID,SMS_AuthorizationList.CIVersion,SMS_AuthorizationList.ContainsExpiredUpdates,
SMS_AuthorizationList.ContainsSupersededUpdates,SMS_AuthorizationList.CreatedBy,SMS_AuthorizationList.DateCreated,SMS_AuthorizationList.DateLastModified,
SMS_AuthorizationList.EffectiveDate,SMS_AuthorizationList.EULAAccepted,SMS_AuthorizationList.EULAExists,SMS_AuthorizationList.EULASignoffDate,SMS_AuthorizationList.EULASignoffUser,
SMS_AuthorizationList.IsUserCI,SMS_AuthorizationList.InUse,SMS_AuthorizationList.IsBroken,SMS_AuthorizationList.IsBundle,SMS_AuthorizationList.IsChild,SMS_AuthorizationList.IsDeployed,
SMS_AuthorizationList.IsEnabled,SMS_AuthorizationList.IsExpired,SMS_AuthorizationList.IsHidden,SMS_AuthorizationList.IsLatest,SMS_AuthorizationList.IsProvisioned,SMS_AuthorizationList.IsQuarantined,
SMS_AuthorizationList.IsSuperseded,SMS_AuthorizationList.IsUserDefined,SMS_AuthorizationList.LastModifiedBy,SMS_AuthorizationList.LastStatusTime,SMS_AuthorizationList.Description,
SMS_AuthorizationList.DisplayName,SMS_AuthorizationList.CIInformativeURL,SMS_AuthorizationList.LocaleID,SMS_AuthorizationList.ModelID,SMS_AuthorizationList.ModelName,
SMS_AuthorizationList.NumberOfCollectionsDeployed,SMS_AuthorizationList.NumCompliant,SMS_AuthorizationList.NumNonCompliant ,
SMS_AuthorizationList.NumTotal,SMS_AuthorizationList.NumUnknown ,SMS_AuthorizationList.PercentCompliant,SMS_AuthorizationList.PermittedUses,
SMS_AuthorizationList.PlatformType,SMS_AuthorizationList.SDMPackageVersion,SMS_AuthorizationList.SedoObjectVersion,
SMS_AuthorizationList.SourceSite from fn_ListAuthListCIs(1033) AS SMS_AuthorizationList where SMS_AuthorizationList.CI_ID = 16829449
Dataset2:
select all SMS_CH_SummaryCurrent.ClientsActive,SMS_CH_SummaryCurrent.ClientsHealthUnknown,SMS_CH_SummaryCurrent.ClientsHealthy,
SMS_CH_SummaryCurrent.ClientsHealthyActive,SMS_CH_SummaryCurrent.ClientsHealthyInactive,SMS_CH_SummaryCurrent.ClientsInactive,SMS_CH_SummaryCurrent.ClientsRemediationSuccess,
SMS_CH_SummaryCurrent.ClientsRemediationTotal,SMS_CH_SummaryCurrent.ClientsTotal,SMS_CH_SummaryCurrent.ClientsUnhealthy,SMS_CH_SummaryCurrent.ClientsUnhealthyActive,
SMS_CH_SummaryCurrent.ClientsUnhealthyInactive,SMS_CH_SummaryCurrent.CollectionID from v_CH_ClientSummaryCurrent AS SMS_CH_SummaryCurrent where SMS_CH_SummaryCurrent.CollectionID = N'SMSDM003'
Dataset3:
select top 5 SMS_TopThreatsDetected.CollectionID,SMS_TopThreatsDetected.MemberCount,SMS_TopThreatsDetected.Rank,SMS_TopThreatsDetected.ThreatCategoryID,SMS_TopThreatsDetected.ThreatID,
SMS_TopThreatsDetected.ThreatName,SMS_TopThreatsDetected.TotalMemberCount from vSMS_TopThreatsDetected AS SMS_TopThreatsDetected where SMS_TopThreatsDetected.CollectionID = N'SMS00001' order by SMS_TopThreatsDetected.Rank
Dataset4:
select all __ointProtectionHealthStatus0.ApplyPolicyFailedCount,__ointProtectionHealthStatus0.ApplyPolicySucceededCount,__ointProtectionHealthStatus0.CollectionID,__ointProtectionHealthStatus0.InstallFailedCount,__ointProtectionHealthStatus0.InstallRebootPendingCount,
__ointProtectionHealthStatus0.NoSignatureCount,__ointProtectionHealthStatus0.OverallNotClientCount,__ointProtectionHealthStatus0.OverallStatusAtRiskCount,__ointProtectionHealthStatus0.OverallStatusInactiveCount,__ointProtectionHealthStatus0.OverallStatusNotSupportedCount,
__ointProtectionHealthStatus0.OverallStatusNotYetInstalledCount,__ointProtectionHealthStatus0.OverallStatusProtectedCount,__ointProtectionHealthStatus0.OlderThan7DaysCount,__ointProtectionHealthStatus0.UpTo1DayOldCount,__ointProtectionHealthStatus0.UpTo3DaysOldCount,
__ointProtectionHealthStatus0.UpTo7DaysOldCount,__ointProtectionHealthStatus0.TimeLastUpdated,__ointProtectionHealthStatus0.TotalMemberCount,__ointProtectionHealthStatus0.TotalOperationalIssueCount,__ointProtectionHealthStatus0.UnhealthyCount from vSMS_EndpointProtectionHealthStatus AS __ointProtectionHealthStatus0 where __ointProtectionHealthStatus0.CollectionID = N'SMS00001'
Dataset5:
select all
SMS_AuthorizationList.ApplicabilityCondition,SMS_AuthorizationList.AssociatedAutoRuleID,SMS_AuthorizationList.CI_ID,SMS_AuthorizationList.CI_UniqueID,SMS_AuthorizationList.CIType_ID,SMS_AuthorizationList.CIVersion,
SMS_AuthorizationList.ContainsExpiredUpdates,SMS_AuthorizationList.ContainsSupersededUpdates,SMS_AuthorizationList.CreatedBy,SMS_AuthorizationList.DateCreated,SMS_AuthorizationList.DateLastModified,
SMS_AuthorizationList.EffectiveDate,SMS_AuthorizationList.EULAAccepted,SMS_AuthorizationList.EULAExists,SMS_AuthorizationList.EULASignoffDate,SMS_AuthorizationList.EULASignoffUser,
SMS_AuthorizationList.IsUserCI,SMS_AuthorizationList.InUse,SMS_AuthorizationList.IsBroken,SMS_AuthorizationList.IsBundle,SMS_AuthorizationList.IsChild,SMS_AuthorizationList.IsDeployed,
SMS_AuthorizationList.IsEnabled,SMS_AuthorizationList.IsExpired,SMS_AuthorizationList.IsHidden,SMS_AuthorizationList.IsLatest,SMS_AuthorizationList.IsProvisioned,SMS_AuthorizationList.IsQuarantined,
SMS_AuthorizationList.IsSuperseded,SMS_AuthorizationList.IsUserDefined,SMS_AuthorizationList.LastModifiedBy,SMS_AuthorizationList.LastStatusTime,SMS_AuthorizationList.Description,
SMS_AuthorizationList.DisplayName,SMS_AuthorizationList.CIInformativeURL,SMS_AuthorizationList.LocaleID,SMS_AuthorizationList.ModelID,SMS_AuthorizationList.ModelName,SMS_AuthorizationList.NumberOfCollectionsDeployed,
SMS_AuthorizationList.NumCompliant,SMS_AuthorizationList.NumNonCompliant ,SMS_AuthorizationList.NumTotal,SMS_AuthorizationList.NumUnknown ,SMS_AuthorizationList.PercentCompliant,SMS_AuthorizationList.PermittedUses,
SMS_AuthorizationList.PlatformType,SMS_AuthorizationList.SDMPackageVersion,SMS_AuthorizationList.SedoObjectVersion,SMS_AuthorizationList.SourceSite from fn_ListAuthListCIs(1033) AS SMS_AuthorizationList
where SMS_AuthorizationList.CI_ID = 16829448
Dataset6:
SELECT Caption0 as 'Operating System', COUNT(*) AS 'Total'
FROM v_GS_OPERATING_SYSTEM OS
GROUP BY Caption0
ORDER BY Caption0
(Dataset1 and Dataset5 will need the CI_ID to be modified if used in another environment, Dataset6 is a simple custom query. The data source for your environment will have to be defined.)
After getting a little creative inside SSRS I came up with a color scheme that looked similar to the Core Tech Dashboard and here is what the finished result looks like:
I've included the RDL for download below.
UPDATE: Some of the above queries are cutoff. But you can find the complete query in the RDL, under the properties of each dataset:
Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of included script samples are subject to the terms specified
in the Terms of Use .