Status View Sample Queries
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 such as the machine name where the status message originated, the component that initiated the message, the message ID, how many of these messages have been sent, and the attribute value for the status message. The attribute value would be the package ID for a package status message, a collection ID for a collection status message, 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.MachineName, SM.Component, SM.MessageID,
COUNT(*) AS 'Count', SMA.AttributeValue
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
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 PCK.SourceSite, DPS.PackageID, PCK.Name, DPS.ServerNALPath,
DPS.InstallStatus
FROM v_PackageStatusDistPointsSumm DPS INNER JOIN v_Package PCK
ON DPS.PackageID = PCK.PackageID
ORDER BY DPS.PackageID
Joining Advertisement Status, Advertisement, Collection, and Resource Views
The following query lists the SMS clients that have been targeted, such as an advertisement, the ID and name of the advertisement, the name of the collection that was targeted, and the last status message name that the client sent to the site server. 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.
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
Joining Software Metering Usage, 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, and the last usage date are all listed. The v_MonthlyUsageSummary view is joined to the v_R_System view by using the ResourceID column and 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