Alert Chart

I'd like to see the count of triggered alerts over time in a bar chart, ideally grouped by alert severity.

There might be something for this out of the box, but I have not come across it.

I don't have experience with the custom charts, but I thought I was close with this query. I appreciate the help!

SELECT AlertHistory.AlertObjects.AlertConfigurations.Severity as Severity, COUNT(*) AS NumberOfAlerts, DATETRUNC('Hour',TimeStamp) as Hour
FROM Orion.AlertHistory
Where EventType = 0
GROUP BY AlertHistory.AlertObjects.AlertConfigurations.Severity, DATETRUNC('Hour',TimeStamp)

Find more posts tagged with

chart

alert_report

Accepted answers

m_roberts

Yes, here you go - the query to support my screenshots above:

DECLARE @TopXX INT

SET @TopXX = (
        SELECT CurrentValue
        FROM Settings
        WHERE SettingID LIKE 'Web-MaximalNumberOfSeriesInChart'
        )

SELECT AHV.RelatedNodeId
    ,N.Caption
    ,cast(floor(cast(AHV.TIMESTAMP AS FLOAT)) AS DATETIME) AS RoundedTime
    ,count(AHV.RelatedNodeID) AS [Alerts Per Day]
FROM AlertHistoryView AHV
INNER JOIN Nodes N ON RelatedNodeId = N.NodeID
WHERE EventTypeWord = 'Triggered'
    AND TIMESTAMP >= ${FromTime}
    AND TIMESTAMP < ${ToTime}
    AND AHV.RelatedNodeID IN (
        SELECT TOP (@TopXX) RelatedNodeID
        FROM AlertHistoryView
        WHERE TIMESTAMP >= ${FromTime}
            AND TIMESTAMP < ${ToTime}
        GROUP BY RelatedNodeID
        ORDER BY COUNT(RelatedNodeID) DESC
        )
GROUP BY cast(floor(cast(AHV.TIMESTAMP AS FLOAT)) AS DATETIME)
    ,AHV.RelatedNodeID
    ,N.Caption
ORDER BY 4 DESC

All comments

stuartd

I'm sure someone with more experience / knowledge than myself will be along shortly, but as far as I know SolarWinds can't do bar charts. I believe they only do: line charts, stacked area charts and big number charts.

m_roberts

I do not have time immediately to deal with the query, but the Custom Chart widget has the ability to support column tables. For a function like this I would use the Stacked Column, so you can clearly see within the same time period the number of Alerts that have been triggered, with a visual breakdown of those by the number in each severity.

https://documentation.solarwinds.com/en/success_center/orionplatform/content/core-customizing-charts-in-the-orion-web-console-sw1349.htm

Here is a screenshot of an example of a chart showing the count of alerts by Node

and the config for it

stuartd

That's cool to know - hopefully it helps the Op.

adam.beedell

Cool chart idea, might implement that myself. What problem are you having with it?

I've got some non-stacked elements a bit like this. There's a cool calendar-type one on thwack using a customhtml element, and you can also add a SAM Perfmon counter against your solarwinds box to track alerts over time. Could do it as an API poller too.

madscomer

I was working towards this, but I kept getting errors. Any chance you could share the query for your Alerts Per Node report? Thanks!

KMSigma.SWI

I don't want to speak to @m_roberts, but the one that I had in my back pocket is:

SELECT DATETRUNC('day', [AH].TimeStamp) AS [Date]
      , COUNT([AH].AlertHistoryID) AS [Alerts Per Day]
      , [AH].AlertObjects.EntityCaption
FROM Orion.AlertHistory AS [AH]
WHERE [AH].EventType = 0
      AND [AH].AlertObjects.EntityType = 'Orion.Nodes'
GROUP BY DATETRUNC('day', [AH].TimeStamp)
      , [AH].AlertObjects.EntityCaption

I used this for a different request here on THWACK a while ago, so it might not be a 1:1 match up with what he used.

m_roberts

Yes, here you go - the query to support my screenshots above:

DECLARE @TopXX INT

SET @TopXX = (
        SELECT CurrentValue
        FROM Settings
        WHERE SettingID LIKE 'Web-MaximalNumberOfSeriesInChart'
        )

SELECT AHV.RelatedNodeId
    ,N.Caption
    ,cast(floor(cast(AHV.TIMESTAMP AS FLOAT)) AS DATETIME) AS RoundedTime
    ,count(AHV.RelatedNodeID) AS [Alerts Per Day]
FROM AlertHistoryView AHV
INNER JOIN Nodes N ON RelatedNodeId = N.NodeID
WHERE EventTypeWord = 'Triggered'
    AND TIMESTAMP >= ${FromTime}
    AND TIMESTAMP < ${ToTime}
    AND AHV.RelatedNodeID IN (
        SELECT TOP (@TopXX) RelatedNodeID
        FROM AlertHistoryView
        WHERE TIMESTAMP >= ${FromTime}
            AND TIMESTAMP < ${ToTime}
        GROUP BY RelatedNodeID
        ORDER BY COUNT(RelatedNodeID) DESC
        )
GROUP BY cast(floor(cast(AHV.TIMESTAMP AS FLOAT)) AS DATETIME)
    ,AHV.RelatedNodeID
    ,N.Caption
ORDER BY 4 DESC

madscomer

I am getting close here, but I would like to return 0 for each hour there is no alert...any ideas?

SELECT top 8000
CASE
when AlertHistory.AlertObjects.AlertConfigurations.Severity = 0 THEN 'Informational'
when AlertHistory.AlertObjects.AlertConfigurations.Severity = 1 THEN 'Warning'
when AlertHistory.AlertObjects.AlertConfigurations.Severity = 2 THEN 'Critical'
when AlertHistory.AlertObjects.AlertConfigurations.Severity = 3 THEN 'Serious'
when AlertHistory.AlertObjects.AlertConfigurations.Severity = 4 THEN 'Notice'
end as Severity
,COUNT(*) AS AlertCount, DATETRUNC('HOUR',TimeStamp) as Time
FROM Orion.AlertHistory
Where EventType = 0
and TimeStamp >= DATETRUNC('Hour',(ADDHOUR(-25,GETUTCDATE())))
GROUP BY AlertHistory.AlertObjects.AlertConfigurations.Severity, DATETRUNC('HOUR',TimeStamp)
ORDER BY Time DESC