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!
This query times out every time I run it, even when the date is changed to today’s date, any suggestions?
Make sure you’re using the right date format, for one. Did you change the Database name to SMS_YourSiteCode?
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.
Worked great for me
How run this query? Msg 208, Level 16, State 1, Line 1
Invalid object name ‘SMS_TLA.dbo.StatusMessages’.
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
No work for me… had this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘SMS_TLA.dbo.StatusMessages’.
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!
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 🙂