The following sample queries demonstrate how to join some of the most commonly used status message views to other views.
Joining Status Message and Status Message Attribute Views
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',
FROM v_StatusMessage SM LEFT OUTER JOIN
ON SM.RecordID = SMA.RecordID
GROUP BY SM.Component, SM.MessageID,
ORDER BY SM.Component, SM.MessageID
Joining Distribution Point Status and Package Views
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,
FROM v_PackageStatusDistPointsSumm DPS INNER JOIN
ON DPS.PackageID =
ORDER BY DPS.PackageID
Joining Advertisement Status, Advertisement, Collection, and Resource Views
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,
COL.Name AS TargetedCollection,
FROM v_ClientAdvertisementStatus CAS INNER JOIN
ON CAS.ResourceID = SYS.ResourceID
INNER JOIN v_Advertisement ADV
ON CAS.AdvertisementID =
ADV.AdvertisementID INNER JOIN
v_Collection COL ON ADV.CollectionID
ORDER BY SYS.Netbios_Name0,
Joining Software Metering Status, Software Inventory, and Resource Views
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,
FROM v_MonthlyUsageSummary MUS INNER JOIN
ON MUS.ResourceID = SYS.ResourceID
INNER JOIN v_GS_SoftwareFile SF
ON MUS.FileID = SF.FileID
ORDER BY SYS.Netbios_Name0, SF.FileName,
Joining Software Updates Status, Discovery
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.
FROM v_R_System INNER JOIN
v_UpdateComplianceStatus.ResourceID INNER JOIN v_UpdateInfo
v_UpdateInfo.CI_ID INNER JOIN v_StateNames ON
WHERE (v_StateNames.TopicType = 402) AND
(v_R_System.Netbios_Name0 LIKE 'VISTACLIENT1')
ORDER BY v_StateNames.StateName,