≡ Menu

Get a SQL Query from Status Message Viewer

I needed to run a T-SQL query based off of what I was viewing in Status Message Viewer — in my particular case, it was from SMS_POLICY_PROVIDER. I was poking around, and clicked on View –> Query Information. I got something like the following:

Select * from SMS_StatusMessage as stat
left outer join SMS_StatMsgAttributes as att
on stat.recordid = att.recordid
left outer join SMS_StatMsgInsStrings as ins on stat.recordid = ins.recordid
WHERE (COMPONENT="SMS_POLICY_PROVIDER")
AND (stat.Time>='2014/08/18 16:14:15.000')
AND (SiteCode="ABC")
AND (MachineName="P01.ABC.COM")
order by stat.Time Desc

This is a WQL query. To convert from WQL to T-SQL, you typically can change the following to make it work in T-SQL:

  1. Change the table names from SMS_* to v_*
  2. Search and replace double quotes to single quotes.

The above example will look like this:

Select * from v_StatusMessage as stat
left outer join v_StatMsgAttributes as att
on stat.recordid = att.recordid
left outer join v_StatMsgInsStrings as ins
on stat.recordid = ins.recordid
WHERE (COMPONENT='SMS_POLICY_PROVIDER')
AND (stat.Time>='2014/08/18 16:14:15.000')
AND (SiteCode='ABC')
AND (MachineName='P01.ABC.COM')
order by stat.Time Desc

Put that in SQL Server Management Studio and successfully query your database. Now, this will give you a LOT more rows than what you see in Status Message Viewer. This is because you are pulling information from the joined tables for the related status message attributes and insert strings, and the Status Message Viewer cleans that up for your viewing pleasure.  Note that the additional info is certainly important, as it contains the parameters for the packages you need (Package ID, Name, Deployment ID, or whatever).  Also note that your script is now “dated” since the time is hardcoded. If you just want to view the base records from the last hour, to confirm you are looking at the same amount of records in SQL that you are also viewing in Status Message Viewer from the last hour, then change it to:

Select distinct stat.* from v_StatusMessage as stat
left outer join v_StatMsgAttributes as att
on stat.recordid = att.recordid
left outer join v_StatMsgInsStrings as ins on stat.recordid = ins.recordid
WHERE (COMPONENT='SMS_POLICY_PROVIDER')
AND (stat.Time>= DATEADD(HOUR, -1, SYSDATETIME() ) )
AND (SiteCode='ABC')
AND (MachineName='P01.ABC.COM')
order by stat.Time Desc

IMPORTANT NOTE: This assumes that SYSDATETIME() matches your existing time. You may need to do a SELECT SYSDATETIME() to see if it actually matches your time, and adjust the DATEADD function accordingly. There is probably a more accurate way to convert based on time zone. Adjust for your particular situation.

Now, to get to the information I needed.  I wanted to calculate the number of policy updates (MessageID = 5101) that were occuring per package within the last hour, as we were experiencing a corruption issue that was causing the policy provider to go nuts on specific packages, causing frequent policy updates. With a little T-SQL magic, I was able to get the results I needed:

Select distinct stat.RecordID,
(insPid.InsStrValue + ' - ' + insN.InsStrValue) as Pkg
into #tmpRecords
from v_StatusMessage as stat
left outer join v_StatMsgAttributes as att on stat.recordid = att.recordid
left outer join v_StatMsgInsStrings as insN on stat.recordid = insN.recordid
left outer join v_StatMsgInsStrings as insPid on stat.recordid = insPid.recordid

WHERE (COMPONENT='SMS_POLICY_PROVIDER')
AND (stat.Time>= DATEADD(HOUR, -1, SYSDATETIME() ) )
AND (SiteCode='ABC')
AND (MachineName='P01.ABC.COM')
and stat.MessageID = 5101
and insN.InsStrIndex = 0
and insPid.InsStrIndex = 1;

select Pkg, count(*) as Total
from #tmpRecords
group by Pkg
order by count(*) desc;
drop table #tmpRecords;

 

{ 0 comments… add one }

Leave a Comment