SQL Graph Help for AlertHistoryView

Question

Trying to create a custom SQL Graph using the AlertHistoryView Table.  I want to show a graph that gives a total count of triggered alerts per the time frame I specify.

For example, A graph that shows the last hour, showing how many Alerts were triggered every minute...... or a graph for the last 24 hours that shows how many alerts were triggered every hour.... and so on.

I'm not great with SQL.... all i got so far is:

Select *

From AlertHistoryView

Where EventTypeWord = 'Triggered'

Not sure how to refine the Select statement or add a time element in there.

Any assistance would be appreciated.

Thanks

magicjuanhunglo · Answer

Thanks so much.. .I will test this out.  I really appreciate your help.

mesverrum · Answer

So this turned out to be pretty tricky to get it working and looking how I wanted it to, but this is what I came up with

So this is a 24 hour chart with the numbers broken down per hour, set it to display using columns because lines/area get ugly gaps for any periods where you didn't have any alerts.  I didn't have many acknowledges in my lab so I added in resets to have some more to show.  You can skip adding the resets part if you don't need it on your chart.

SELECT distinct datetrunc('hour',eventtime) as [date], case when a.Alerts is null then 0 else a.Alerts end as [Alerts], case when r.Resets is null then 0 else r.Resets end as [Resets], case when ack.ack is null then 0 else ack.ack end as [Ack], 'Alerts' as [Legend]

FROM Orion.Events

full join (select count(eventtype) as [Alerts]

,datetrunc('hour',timestamp) as [date]

,'Total' as caption

,case when EventType=0 then 'Triggered'

when EventType=1 then 'Alert Reset'

when EventType=2 then 'Acknowledged'

end as [Message]

FROM Orion.AlertHistory ah

where eventtype in (0)

and hourdiff(timestamp,getdate())<25

group by datetrunc('hour',timestamp), eventtype

order by date desc) a on a.date=datetrunc('hour',eventtime)

full join (select count(eventtype) as [Resets]

,datetrunc('hour',timestamp) as [date]

,'Total' as caption

,case when EventType=0 then 'Triggered'

when EventType=1 then 'Alert Reset'

when EventType=2 then 'Acknowledged'

end as [Message]

FROM Orion.AlertHistory ah

where eventtype in (1)

and hourdiff(timestamp,getdate())<25

group by datetrunc('hour',timestamp), eventtype

order by date desc) r on r.date=datetrunc('hour',eventtime)

full join (select count(eventtype) as [Ack]

,datetrunc('hour',timestamp) as [date]

,'Total' as caption

,case when EventType=0 then 'Triggered'

when EventType=1 then 'Alert Reset'

when EventType=2 then 'Acknowledged'

end as [Message]

FROM Orion.AlertHistory ah

where eventtype in (2)

and hourdiff(timestamp,getdate())<25

group by datetrunc('hour',timestamp), eventtype

order by date desc) ack on ack.date=datetrunc('hour',eventtime)

where hourdiff(eventtime,getdate())<25

order by datetrunc('hour',eventtime)

magicjuanhunglo · Answer

That's awesome.. thank you.  I know a little SQL ad even less SWQL.

How would I change this up to show Alerts that have been acknowledged.  So I would use your recommendation above for how many alerts were triggered per hour for my first chart... then on my second chart, I would want to show how many alerts were acknowledged per hour as well.