Syslog Charts (also alerts, traps, events)

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.

SELECT

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

select

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

,tmp.sum_syslog [Syslog]

,tmp.sum_IEEE_D [ILPOWER-5-IEEE_DISCONNECT]

,tmp.sum_LINK_UPDOWN[LINK_UPDOWN]

,tmp.sum_DUPLEX [DUPLEX_MISMATCH]

,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='1.1.1.1' 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=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.

Enjoy

Amit

Find more posts tagged with

chart

npm

Accepted answers

All comments

i_like_eggs

A-A-A-Awesome

h.hendriks

Yes Awesome. But know is the question "How can we made this?"

Witch manual can i use?

i_like_eggs

The first section of this is using a Custom Query resource - SWQL

The second is using Custom charts SQL as a data series, it would be useful to know the grouping used in the charts i always have problems with grouping or could be a Component monitor using Custom object charts with the SAM module running SQL user experiences monitor?

cscoengineer

I agree. Chart groupings can be pretty confusing. This is how I grouped it.

cscoengineer

The code is pretty much the same for all four, just need to change the table names. Here is the one for alert chart. Does that help?

select

convert(date,ah.timestamp) as [Date]

,sum_email [EmailSent]

,sum_email_f [EmailFailed]

,sum_alert_t [AlertTrigger]

,sum_alert_r [AlertReset]

,'Total' [Total]

from AlertHistoryView ah

join (select convert(date,ah1.timestamp) [Date1]

,sum(case when ah1.ActionTypeID='email' and ah1.EventTypeWord='ActionSucceeded' then 1 else 0 end) sum_email

,sum(case when ah1.ActionTypeID='email' and ah1.EventTypeWord='ActionFailed' then 1 else 0 end) sum_email_f

,sum(case when ah1.EventTypeWord='Triggered' then 1 else 0 end) sum_alert_t

,sum(case when ah1.EventTypeWord='Reset' then 1 else 0 end) sum_alert_r

from AlertHistoryView ah1

where DATEDIFF(day,ah1.timestamp,getdate()) < 30

group by convert(date,ah1.timestamp)) tmp on [Date1]=convert(date,ah.timestamp)

where DATEDIFF(day,ah.timestamp,getdate()) < 30

group by convert(date,ah.timestamp), tmp.sum_alert_t, tmp.sum_alert_r,tmp.sum_email, tmp.sum_email_f

order by 1 desc

i_like_eggs

Hey,

Heres a graph for ncm status. tweeking the code slightly might also be useful for your view

select Convert(date,nc.LastInventory) AS [Date]

,sum(case when nc.LoginStatus like 'Connection Refused by%' then 1 else 0 END) AS [Connections Refrused]

,sum(case when nc.LoginStatus like 'Connectivity issues, %'then 1 else 0 END) AS [Connectivity Issues]

,Sum(case when nc.LoginStatus like '%OK%' then 1 else 0 END) AS [Connections OK]

,'NCM' AS [NCM Status]

from Nodes n

INNER JOIN ncm_nodes nc ON n.nodeid = nc.CoreNodeID

WHERE ObjectSubType = 'SNMP'AND n.Status = 1

Group by Convert(date,nc.LastInventory)

i_like_eggs

lol you could also break the events down into the event types looks cool as an stacked graph

Here is the code:

select convert(date,e.EventTime)

,Sum(case when e.NetObjectType = 'N' then 1 ELSE 0 END) AS [Node]

,Sum(case when e.netobjecttype = 'V' then 1 ELSE 0 END) AS [Volume]

,Sum(case when e.NetObjectType = 'I' then 1 ELSE 0 END) AS [Interface]

,Sum(case when e.NetObjectType LIKE 'HWH%' then 1 ELSE 0 END) AS [Hardware]

,'Events' AS [Events]

from Events e

where e.NetworkNode != '0' AND DATEDIFF(day,e.EventTime,getdate()) <= 30

GROUP BY convert(date,e.EventTime)

order by convert(date,e.EventTime) desc

cscoengineer

Very nice eggs!! Lots of tweaks can be done!

halenmark

I want to create hisroty report of a Synoloygy RS812 NAS harddisk temperature by Splunk.

Is it possible to use Splunk ( SEO Dubai – Professional SEO Services Company in Dubai, UAE ) create a line chart report based on the Syslog?

Thanks!