Site Summarizer Query

 This query will mimic the heath of SMS site status within the console.

SELECT

case Summarizer_SiteSystem.Status

            When ‘0’ then ‘Green/OK’

            When ‘1’ Then ‘Yellow/Warning’

            When ‘2’ then ‘Red/Error’

            else ‘Red/No Status’

end as ‘Status’,

Summarizer_SiteSystem.SiteCode as ‘Site Code’,

Summarizer_SiteSystem.SiteObject,

Summarizer_SiteSystem.Role as ‘Role’,

Summarizer_SiteSystem.DownSince as ‘Down Since’,

Summarizer_SiteSystem.TimeReported as ‘Date’

FROM

            Summarizer_SiteSystem Summarizer_SiteSystem

Where

            left(Summarizer_SiteSystem.TimeReported,11) <> left(Getdate(),11) or Summarizer_SiteSystem.DownSince <> NULL

ORDER BY

            Summarizer_SiteSystem.SiteCode,

            Summarizer_SiteSystem.Role