SMS SQL for Count of Status Messages

The following SQL query returns a count of status messages by year, month, day, hour. The dateadd function subtracts 3 days from todays date, giving us counts for the last 3 days.

----Begin SQL----

--Count of Status Messages Broken Down by Year, Month, Day, Hour
Select Datepart(yy,time), Datepart(mm,time), Datepart(dd,time), Datepart(hh,time), Count(*)
FROM StatusMessages
Where Time > dateadd(dd, -3, getdate())
Group By Datepart(yy,time), Datepart(mm,time), Datepart(dd,time), Datepart(hh,time)

----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 Associate, I earn from qualifying purchases at no cost to you.

Comments

Post a Comment

Popular posts from this blog

SMS "Program failed (run time exceeded)" 10070

When a program fails due to error 10070, it does so because the Run Time was Exceeded. SMS Allows a program to run for 12 hours, afterwhich it will issue the failed status message if the program hasn't completed successfully. Note: The Maximum allowed run time within program properties is generally a guide for users running the program themselves, and is not enforced. 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 Associate, I earn from...
Read more

SMS "Waiting for User Condition" 10036

Status Message ID 10036, or "Waiting for User Condition" can occasionally grind advertisements to a halt. Make sure the environment and user condition settings for a particular program have been configured appropriately and are not excluding a large portion of your intended audience. Related Articles SMS WQL for All Clients where Last Status is Waiting for User Condition SMS Status Message ID's 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. ...
Read more

Reading the SCCM Client GUID

Recently I encountered an issue with the SCCM client GUID missing from the  %windir%\SMSCFG.INI file. We wanted to write a script to put the GUID back, so I was asked how to read the SCCM Client GUID from a script or command line. VBScript: Set objWMIService = GetObject("winmgmts:\\.\root\ccm") Set colItems = objWMIService.ExecQuery("Select ClientID From CCM_Client") For Each objItem in colItems Wscript.Echo "Client ID" & objItem.ClientID Next WMIC via commandline: wmic /namespace:\\root\ccm path ccm_client get clientid PowerShell: Get-WMIObject -namespace root\ccm ccm_client clientid | Select-Object clientid Suggested Reading: MSDN Article on How to Get the Unique Identifier Value for a Client (Includes Code Snippets) MSDN Article on Using the Get-WMiObject Cmdlet About the Author Emmanuel Tsouris is a Systems Management veteran and Dev...
Read more