Analytics Plus – Reports for SCCM

A while back I wrote a post on how to install and configure Analytics Plus from ManageEngine and how to get data from the System Center Configuration Manager database to create reports and dashboards.

In this post I will share with you SQL queries that might come in handy when you are about to hand in reports or when you are creating your own dashboards for monitoring.

If you haven’t already installed Analytics Plus, click HERE to get started.

Note: When copying the SQL-Query to Analytics Plus, make sure to use the button to copy to clipboard or show the source. Otherwise the formatting might be corrupted.

Content Distribution Status

Based on the data gathered with this query, the report will show you the status of your distribution points. I prefer to use a “pie chart” for this one since there are three major categories;

• Success
• In Progress
• Failed

Query:

SELECT     v_Package.Name AS Package_Name, 
		   SMS_DistributionDPStatus.PackageID,
		   'Package Type (Text)' =
				Case
					when v_Package.PackageType = 0 Then 'Software Distribution Package'
					when v_Package.PackageType = 3 Then 'Driver Package'
					when v_Package.PackageType = 4 Then 'Task Sequence Package'
					when v_Package.PackageType = 5 Then 'Software Update Package'
					when v_Package.PackageType = 6 Then 'Device Setting Package'
					when v_Package.PackageType = 7 Then 'Virtual Package'
					when v_Package.PackageType = 8 Then 'Application'
					when v_Package.PackageType = 257 Then 'Image Package'
					when v_Package.PackageType = 258 Then 'Boot Image Package'
					when v_Package.PackageType = 259 Then 'Operating System Install Package'
				Else
					'Unknown'
				End, 
		   SMS_DistributionDPStatus.Name AS DP_Name,
		   CASE
			    WHEN MessageState = 1 THEN 'Success'
				WHEN MessageState = 2 THEN 'In Progress'
				WHEN MessageState = 4 THEN 'Error'
			Else
				'Unknown'
			End as 'Content_Status',
           CASE 
				WHEN MessageCategory = 31 THEN 'Success' 
				WHEN MessageCategory = 76 THEN 'In Progress' 
				WHEN MessageCategory = 13 THEN 'Failed to distribute content'
				WHEN MessageCategory = 70 THEN 'Failed to update package'
				WHEN MessageCategory = 78 THEN 'Failed to connect to remote distribution point'
				WHEN MessageCategory = 51 THEN 'Content transfer manager was instructed to send content to the distribution point'
				WHEN MessageCategory = 37 THEN 'Content transfer manager was instructed to send content to the distribution point'
				WHEN MessageCategory = 1  THEN 'Waiting for prestage content'
				WHEN MessageCategory = 77 THEN 'Waiting for prestage content'
				WHEN MessageCategory = 62 THEN 'Content hash has been sucessfully verified'
				WHEN MessageCategory = 7  THEN 'The source folder for content does not exist'
				WHEN MessageCategory = 72 THEN 'Content is being redistributed to distribution point'
				WHEN MessageCategory = 4 THEN 'Content was successfully refreshed'
				WHEN MessageCategory = 998 THEN 'Content transfer manager was instructed to send content to the distribution point'
				WHEN MessageCategory = 165 THEN 'Content was distributed to distribution point'
				WHEN MessageCategory = 61 THEN 'Failed to validate content hash'
				WHEN MessageCategory = 24 THEN 'Failed to connect to distribution point'
				ELSE 'Unknown' 
		   END 
		   AS 'Detailed_Status', 
		   SMS_DistributionDPStatus.MessageState,
		   SMS_DistributionDPStatus.MessageCategory, 
           SMS_DistributionDPStatus.ObjectTypeID, 
           SMS_DistributionDPStatus.LastUpdateDate, 
           SMS_DistributionDPStatus.InsString3, 
           v_Package.PkgSourcePath, 
           v_Package.PackageType
FROM         vSMS_DistributionDPStatus AS SMS_DistributionDPStatus INNER JOIN
                      v_Package ON SMS_DistributionDPStatus.PackageID = v_Package.PackageID   
           order by SMS_DistributionDPStatus.PackageID

When used in a pie chart, the result will be similar to this:

Computers missing patches

Another report that is useful at least once a month is a list of computers that are missing patches. There will always be computers that are missing a few patches, so based on the data from this query, we will create a “Pivot View” to see the name of the computer and the number of missing patches.

Query:

SELECT     v_R_System.Name0, v_Update_ComplianceStatus.Status, v_UpdateInfo.BulletinID, v_UpdateInfo.ArticleID, v_UpdateInfo.Description, v_UpdateInfo.Title,
                      v_UpdateInfo.DatePosted
FROM         v_R_System INNER JOIN
                      v_Update_ComplianceStatus ON v_R_System.ResourceID = v_Update_ComplianceStatus.ResourceID INNER JOIN
                      v_UpdateInfo ON v_Update_ComplianceStatus.CI_ID = v_UpdateInfo.CI_ID
WHERE     (v_Update_ComplianceStatus.Status = 2)

From this query I select the following columns:

• Name0 (Name of client)
• ArticleID (KB-number)
• Title
• DatePosted (Date when released)

The created Pivot View will look similar to the one here, depending on your data of course, but you see the name of the client, number of missing patches and the date of the oldest patch that isn’t deployed.