Get System Names from SCCM Status Messages, the easy way

If you need to get the machines referenced by GUID from a particular status message, use this SQL Query.

Select distinct Name0

from [SMS_TLA].[dbo].[StatusMessages] as stat
left outer join [SMS_TLA].dbo.StatusMessageAttributes as att on stat.recordid = att.recordid
left outer join [SMS_TLA].dbo.StatusMessageInsStrs as ins on stat.recordid = ins.recordid
left outer join [SMS_TLA].dbo.v_StatusMessage as VStat on stat.recordid = VStat.recordid
inner join [SMS_TLA].dbo.v_R_System as SD on ins.InsStrValue = SD.SMS_Unique_Identifier0
join [SMS_TLA].dbo.ClientKeyData as CD on SD.SMS_Unique_Identifier0 = CD.smsID
join [SMS_TLA].dbo.v_RA_System_SMSAssignedSites as SAS on SD.ResourceID = SAS.ResourceID
WHERE (VStat.MessageId = '5447') AND
(stat.ModuleName='SMS Server ') AND
(ins.InsStrValue like 'GUID:%') AND
DATEADD(dd, 0, DATEDIFF(dd, 0, stat.Time)) >= '2013-10-17'
--DONT FORGET TO UPDATE THE DATE!

order by Name0 Desc

Please don’t forget to change the SMS_TLA from three letter acronym to the appropriate database name in SCCM!

Advertisements

9 thoughts on “Get System Names from SCCM Status Messages, the easy way

  1. dec1slh November 4, 2013 / 11:40 pm

    This query times out every time I run it, even when the date is changed to today’s date, any suggestions?

  2. FoxDeploy November 8, 2013 / 7:00 pm

    Make sure you’re using the right date format, for one. Did you change the Database name to SMS_YourSiteCode?

    • dec1slh November 11, 2013 / 9:09 pm

      Yes everything was in the correct format, I ended up using management studio to run the query. Everything else I used made it time out.

  3. Jim February 19, 2014 / 9:51 am

    Worked great for me

  4. Tomas February 24, 2015 / 4:42 pm

    How run this query? Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘SMS_TLA.dbo.StatusMessages’.

    • FoxDeploy February 24, 2015 / 5:36 pm

      Oh, you need to replace TLA with the ‘Three Letter Acronym’ of your site code. I may have forgotten to mention this in the article.

      So if your site code / database is FOX you’d run sms_fox.dbo.StatusMessages

  5. tomas24880 February 24, 2015 / 4:42 pm

    No work for me… had this error:

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘SMS_TLA.dbo.StatusMessages’.

  6. Neil K July 14, 2015 / 11:45 am

    What does the line beginning with DATEADD do for the query? Do I need to set the “update” the date to the date that I run the query on? If I change the date to today’s date and run the query, then change the date to January 1st of the same year, I get different results. I’m trying to use this query to resolve 5447 issues. Thanks for the help!

    • FoxDeploy July 16, 2015 / 8:23 am

      DATEADD is used along with DATEDIFF to give us only the date portion of a standard SQL DateTime property. We use it here to restrict the amount of status messages that are displayed. When you say you’re getting different results from January 1, vs today, that makes sense. The bottom line is where you set the maximum age of events you’ll see. If you only look at events that happened today, versus the whole year, I wouldn’t be surpirsed if there were many, many less 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s