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 an advertisement, the advertisement ID, the advertisement name, the collection that was targeted in which the client is a member, and the last status message received from the client for the advertisement. 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

Concepts