a prompt status message query
I deliver our Configuration Manager RAP as a Service offering many times for our Microsoft Premier customers. The risk assessment looks at a great deal of data, including the frequency of when warning and error status messages are being submitted by clients or site servers. For example, "Site server not successful installing some clients via Client Push Installation: Warning status message ID 3015". Ok, so I work with the customer remediating this issue, we make some changes to how their client push is configured and they want to be able to see if the issue is improving. Maybe they were seeing an average of 45 of those messages on a given day before the changes. How many are they seeing now? Great question. So how do we find out? There are reports that can tell you this (All messages for a specific message ID, for example). However, I find it a bit quicker and easier just to run a status message query that prompts for the message ID. This is nothing fancy, just a quick status message query based on the message ID entered at the prompt. Enjoy the video. There is no audio. Feel free to add your own soundtrack. And you might need to right click on the video and select zoom unless you have really good eyesight.
[video width="1024" height="768" wmv="https://msdnshared.blob.core.windows.net/media/2016/06/promptedmessagequery.wmv"][/video]
adding the query, plus a command to limit results based on date.
select SMS_StatusMessage.*, SMS_StatMsgInsStrings.*, SMS_StatMsgAttributes.* from SMS_StatusMessage left join SMS_StatMsgInsStrings on SMS_StatMsgInsStrings.RecordID = SMS_StatusMessage.RecordID left join SMS_StatMsgAttributes on SMS_StatMsgAttributes.RecordID = SMS_StatusMessage.RecordID where SMS_StatusMessage.MessageID = ##PRM:SMS_StatusMessage.MessageID## and SMS_StatusMessage.Time >= ##PRM:SMS_StatusMessage.Time##