SCCM Web Status Message Viewer

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
Advertisements

3 thoughts on “SCCM Web Status Message Viewer

  1. Felix November 13, 2014 / 8:03 pm

    Nice work. It even works in 2012 r2. I created a rdl dashboard using this statement in my dataset.

    • Amit Desai March 28, 2016 / 12:58 am

      Hi Felix,

      Can you share me RDL files.

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