Syslog Charts (also alerts, traps, events)

Version 2

    I enjoy creating charts to graphically present information.  In this example, I’ll demonstrate syslog.  The same code (with slight modification) can be used for: alerts, traps; and events.



    1. s.Hostname as [Node]

    ,'/Orion/images/StatusIcons/Small-' + s.node.StatusLED AS [_IconFor_Node]

    ,s.node.DetailsUrl AS [_LinkFor_Node]

    , case s.SysLogSeverity when 0 then 'Emergency'

                            when 1 then 'Alert'

                            when 2 then 'Critical'

                            when 3 then 'Error'

                            when 4 then 'Warning'

                            when 5 then 'Notice'

                            when 6 then 'Informational'

                            when 7 then 'Debug'

      end as [Severity]               

    , case when s.MessageType is null or s.messagetype = '' then '[Node Not in Orion]' else s.MessageType end as [Message]

    ,count(*) as [Total]

    FROM Orion.SysLog s

    --join Orion.SysLogSeverities sd on sd.SeverityCode=s.SysLogSeverity

    where DAYDIFF(s.DateTime,getdate()) =0

    group by s.MessageType, s.Hostname, s.node.StatusLED, s.node.nodeid, s.node.DetailsUrl, s.SysLogSeverity

    HAVING count(*) > 1000

    order by [Total] desc




    convert(date,s.DateTime) as [Date]

    ,tmp.sum_syslog [Syslog]




    ,tmp.sum_authfailure [AuthFailure]

    ,tmp.sum_Host_WEBMAIL [HOST_WEBMAIL]

    ,tmp.sum_Host_1_1_1_1 [Host_1_1_1_1]

    ,'Total' [Total]

    from syslog s

    join (select convert(date,s1.DateTime) [Date1]

                 ,count(case when s1.Hostname='webmail'  then 1 else null end) sum_Host_WEBMAIL

                 ,sum(case when s1.Hostname=''  then 1 else 0 end) sum_Host_1_1_1_1

                 ,sum(case when s1.MessageType='ILPOWER-5-IEEE_DISCONNECT'  then 1 else 0 end) sum_IEEE_D

                 ,sum(case when s1.MessageType='LINK-3-UPDOWN'  then 1 else 0 end) sum_LINK_UPDOWN

                 ,sum(case when s1.MessageType='CDP-4-DUPLEX_MISMATCH'  then 1 else 0 end) sum_DUPLEX

                           ,sum(case when s1.MessageType='SNMPv2-MIB:authenticationFailure'  then 1 else 0 end) sum_authfailure

                ,sum(case when 1=then 1 else 0 end) sum_syslog

    from syslog s1

             where DATEDIFF(day,s1.DateTime,getdate()) <= 30

             group by convert(date,s1.DateTime)) tmp on [Date1]=convert(date,s.DateTime)

    group by convert(date,s.DateTime), tmp.sum_syslog, tmp.sum_authfailure

    ,sum_IEEE_D, sum_LINK_UPDOWN, sum_DUPLEX, sum_Host_WEBMAIL, sum_Host_1_1_1_1

    order by 1 desc



    The final product is lots of easily digestible information in tabular and chart form.