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 .

 

ConfigMgr Dashboard - Quick Example.zip

Comments

  • Anonymous
    January 01, 2003
    Thanks you it's very interesting
  • Anonymous
    January 01, 2003
    Thanks guys!
  • Anonymous
    January 01, 2003
    Hi SteveO,

    The CI_ID correspond to the Config Item ID of the Software Update Group, you have 2 ways:
    Directly in the database with a query or the easiest way, in your CM12 console, under Software LibraryOverviewSoftware UpdatesSoftware Update Groups, you can add the column "Config Item ID".
  • Anonymous
    January 01, 2003
    Great dashboard !! Thanks for sharing.
    Just few comment. I think there is an error in Dataset 3 "vSMS_TopThreatsDetected". The name is of the view is v_TopThreatsDetected.
    And in order to adapt it, you will have to change the collection ID in Dataset 3 and 4, if the deployment are not done on the "All Systems" (ID:SMS000001) collection.
  • Anonymous
    January 01, 2003
    My mistake, the view's name is OK (vSMS_TopThreatsDetected) !!
  • Anonymous
    January 01, 2003
    Hi Mike,

    A simple way, might be to add a prompt to the dashboard for the SUG.

    Mike-
  • Anonymous
    January 05, 2015
    Very nice!
  • Anonymous
    January 06, 2015
    thank you very much for sharing
    SSRS is always an enigma:)
  • Anonymous
    January 08, 2015
    Love this idea. How do I go about getting the CI_ID for Dataset 1 and Dataset 5?
  • Anonymous
    January 09, 2015
    The comment has been removed
  • Anonymous
    February 20, 2015
    Thanks, looks very nice. Can we get the client information (with hostnames)from this dashboard directly . Please confirim.