Below you’ll find the code used to recreate some of the functionality of the SCCM Status Message viewer, for SCCM SQL Web Reports. You will need to Create the Second Report first. Then, go to the first report and link it to the second. Column 1 should link to Site Code, while Column 2 should link to Computer Name.
select 'Warnings as of' as 'Alert View', DATEADD(ss,-240-(24*3600),GetDate()) as 'Time' select com.SiteCode, com.MachineName, stat.MessageID, com.ComponentName, count(*) as 'Warning Count' from v_StatusMessage stat join v_ServerComponents com on stat.SiteCode=com.SiteCode and stat.MachineName=com.MachineName and stat.Component=com.ComponentName where Time > DATEADD(ss,-240-(24*3600),GetDate()) and Severity='-2147483648' group by com.SiteCode,com.MachineName,com.ComponentName,stat.MessageID order by count(*) desc select 'Errors as of' as 'Alert View', DATEADD(ss,-240-(24*3600),GetDate()) as 'Time' select com.SiteCode, com.MachineName, stat.MessageID, com.ComponentName, count(*) as 'Error Count' from v_StatusMessage stat join v_ServerComponents com on stat.SiteCode=com.SiteCode and stat.MachineName=com.MachineName and stat.Component=com.ComponentName where Time > DATEADD(ss,-240-(24*3600),GetDate()) and Severity='-1073741824' group by com.SiteCode, com.MachineName, com.ComponentName,stat.MessageID order by count(*) desc
–Page 2 – SCCM Component Status Messages for a Particular
site in the last 24 hours
select SiteCode, MachineName, Component, Convert(VARCHAR(10),RecordID) as 'Record', MessageID, DATEADD(ss,240,Time) as 'Time', CASE Severity WHEN -1073741824 THEN '*' ELSE ' ' END as 'Error', CASE Severity WHEN -2147483648 THEN '*' ELSE ' ' END as 'Warning', CASE Severity WHEN 1073741824 THEN '*' ELSE ' ' END as 'Informational' from v_StatusMessage where Time > DATEADD(ss,-240-(24*3600),GetDate()) and SiteCode = @sitecode and MachineName = @machname --and Component = @component and(Severity in(-2147483648,-1073741824)) order by MachineName, Time DESC
Nice work. It even works in 2012 r2. I created a rdl dashboard using this statement in my dataset.
LikeLike
Hi Felix,
Can you share me RDL files.
LikeLike