Posts

Showing posts from December, 2006

SMS SQL for Metering Data

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---- About the Author Emmanuel Tsouris is a Systems Management veteran and Developer Advocate specializing in PowerShell and Cloud Automation. He maintains DotVBS to preserve legacy knowledge for the "archaeologist admin." Ready to move from VBScript to the Cloud? Check out his book, Pro PowerShell for Amazon Web Services . Visit EmmanuelTsouris.com for his latest ...
Post a Comment
Read more

SMS SQL for Computers and their Advertisement Status

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 ---- About the Author Emmanuel Tsouris is a Systems Management veteran and Developer Advocate specializing in PowerShell and Cloud Automation. He maintains DotVBS to preserve legacy knowledge for the "archaeologist admin." Ready to move from VBScript to the Cloud? Check out his book, Pro PowerShell for Amazon Web Services . ...
Post a Comment
Read more

SMS SQL for Distribution Point Status and Package Views

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---- About the Author Emmanuel Tsouris is a Systems Management veteran and Developer Advocate specializing in PowerShell and Cloud Automation. He maintains DotVBS to preserve legacy knowledge for the "archaeologist admin." Ready to move from VBScript to the Cloud? Check out his book, Pro PowerShell for Amazon Web Services . Visit EmmanuelTsouris.com for his latest projects. ...
Post a Comment
Read more

SMS SQL for Joining Status Message and Attribute Views

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---- About the Author Emmanuel Tsouris is a Systems Management veteran and Developer Advocate specializing in PowerShell and Cloud Automation. He maintains DotVBS to preserve legacy knowledge for the "archaeologist admin." Ready to move from VBScript to the Cloud? Check out his book, Pro PowerShell for Amazon Web Services . Visit EmmanuelTsouris.com for his latest projects. As an Amazon Assoc...
Post a Comment
Read more