Sample Queries for Status and Alerts in Configuration Manager

The following query lists status messages, by status message ID, the component that created the status message, the count of the status message reported by the component, the attribute value, and the computer name where the component is installed. The attribute value could be a package ID for a package status message, a collection ID for a collection status message, a user name for a status message concerning a user, and so forth. The v_StatusMessage view is joined to the v_StatMsgAttributes view by using the RecordID column.

SELECT SM.Component, SM.MessageID, 
  COUNT(*) AS 'Count', SMA.AttributeValue, SM.MachineName 
FROM v_StatusMessage SM LEFT OUTER JOIN v_StatMsgAttributes SMA 
  ON SM.RecordID = SMA.RecordID 
GROUP BY SM.Component, SM.MessageID, SM.MachineName, SMA.AttributeValue 
ORDER BY SM.Component, SM.MessageID 

The following query lists the distribution points that have been selected for each package and the installation status for the distribution point. The v_PackageStatusDistPointSumm view is joined to the v_Package view by using the PackageID column.

SELECT DPS.PackageID, PCK.Name, PCK.SourceSite, 
  DPS.ServerNALPath, DPS.InstallStatus 
FROM v_PackageStatusDistPointsSumm DPS INNER JOIN v_Package PCK 
  ON DPS.PackageID = PCK.PackageID 
ORDER BY DPS.PackageID 

The following query lists the clients that have been targeted for a deployment, the deployment ID, the deployment name, the collection that was targeted in which the client is a member, and the last status message received from the client for the deployment. The v_ClientAdvertisementStatus view is joined to the v_R_System view by using the ResourceID column and the v_Advertisement view by using the AdvertisementID column. The v_Advertisement view is joined to the v_Collection view by using the CollectionID column. The results are sorted by NetBIOS name and then by advertisement ID.

SELECT SYS.Netbios_Name0, ADV.AdvertisementID, ADV.AdvertisementName, 
  COL.Name AS TargetedCollection, CAS.LastStatusMessageIDName 
FROM v_ClientAdvertisementStatus CAS INNER JOIN v_R_System SYS 
  ON CAS.ResourceID = SYS.ResourceID INNER JOIN v_Advertisement ADV 
  ON CAS.AdvertisementID = ADV.AdvertisementID INNER JOIN 
  v_Collection COL ON ADV.CollectionID = COL.CollectionID 
ORDER BY SYS.Netbios_Name0, ADV.AdvertisementID

The following query lists the software metering usage data for files defined in the software metering rules. The NetBIOS name of the client, file name, file path, how many times the file has run on the computer, and last usage date are retrieved. The results are sorted by NetBIOS name, and then file name, and then file path. The v_MonthlyUsageSummary view is joined to the v_R_System view by using the ResourceID column and to the v_GS_SoftwareFile view by using the FileID column.

SELECT SYS.Netbios_Name0, SF.FileName, SF.FilePath, 
  MUS.UsageCount, MUS.LastUsage 
FROM v_MonthlyUsageSummary MUS INNER JOIN v_R_System SYS 
  ON MUS.ResourceID = SYS.ResourceID INNER JOIN v_GS_SoftwareFile SF 
  ON MUS.FileID = SF.FileID 
ORDER BY SYS.Netbios_Name0, SF.FileName, SF.FilePath 

The following query lists the enforcement state reported by the VISTACLIENT1 client computer for all software updates that have been assigned to the client. The article ID, bulletin ID, and title for the software update are listed, as well as the enforcement state, the date for the last enforcement scan on the client, and the date when the last enforcement state message was sent from the client. The results are filtered by a topic type of 402, which is the topic type for enforcement state messages, and for the VISTACLIENT1 client. The results are also sorted by state name, and then by the date the software update was last modified. The v_UpdateComplianceStatus status view is joined to the v_R_System discovery view by using the ResourceID column. The v_UpdateComplianceStatus view is joined to the v_UpdateInfo software updates view by using the CI_ID column. The v_UpdateComplianceStatus view is joined to the v_StateNames status view by using the LastEnforcementMessageID and StateID columns, respectively.

SELECT v_UpdateInfo.ArticleID, v_UpdateInfo.BulletinID, v_UpdateInfo.Title, 
  v_StateNames.StateName, v_UpdateComplianceStatus.LastStatusCheckTime, 
  v_UpdateComplianceStatus.LastEnforcementMessageTime 
FROM v_R_System INNER JOIN v_UpdateComplianceStatus ON 
  v_R_System.ResourceID = v_UpdateComplianceStatus.ResourceID INNER JOIN v_UpdateInfo ON 
  v_UpdateComplianceStatus.CI_ID = v_UpdateInfo.CI_ID INNER JOIN v_StateNames ON 
  v_UpdateComplianceStatus.LastEnforcementMessageID = v_StateNames.StateID 
WHERE (v_StateNames.TopicType = 402) AND (v_R_System.Netbios_Name0 LIKE 'VISTACLIENT1') 
ORDER BY v_StateNames.StateName, v_UpdateInfo.DateLastModified