Shows you the computer, file name, file path, usage count, and last usage. Must have metering enabled, with some valid rules defined.
----Begin SQL----
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
----End SQL----
Computers and their Advertisement Status
----Begin SQL----
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
----End SQL----
Shows install status for a specific package on your distribution points. Replace package id '00000000' with your package ID.
----Begin SQL----
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
Where PCK.PackageId = '00000000'
ORDER BY DPS.PackageID
----End SQL----
Simple query joining status message and attribute views.
----Begin SQL----
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
----End SQL----
