Enabled Alerts report for prior week for PowerBI

routermonkey

Been digging around on this, wasn't quite sure where to post it, but I'm hoping that someone has invented this wheel already.  I haven't been able to dig up quite what I want, and am hoping someone else has done something like this before I start from scratch.

I want a report on how many times ALL enabled alerts have fired over the past week.  I don't need breakdown by node, just alert X fired Y times over that past week.  The goal is to build a PowerBI dashboard for reporting this, and I'm fine with getting the data into PowerBI manually to start, but would like to work toward this being pulled by PowerBI automatically.  I'm hoping that someone has the SWQL code that would report this (or at least have something close I can get to work) so that I can work toward having PowerBI pull the data itself in the long run.

I'm pretty new to PowerBI and I know enough SWQL to be dangerous.  I can usually find something close enough to what I want to make it work.  Has anyone already done something like this?

bobmarley

I got most of this somewhere off Thwack. It may get you started.

SELECT --TOP 25 
  COUNT( AlertHistoryID ) AS [Alert Count]
, oah.AlertObjects.AlertConfigurations.DisplayName AS [Alert Name]
, oah.AlertObjects.RelatedNodeCaption AS [Node Name]
, oah.AlertObjects.EntityCaption AS [Alert Entity Name]
, oah.AlertObjects.EntityType AS [Alert Classification]
, oah.AlertObjects.AlertConfigurations.Frequency AS [Alert Evaluation Frequency (seconds)]
, CASE --check if alert trigger condition contains setting for 'Condition Must Exist For'
    WHEN oah.AlertObjects.AlertConfigurations.Trigger LIKE '%%' THEN 'No'
    ELSE 'Yes' 
    END AS [Condition Must Exist For Delay]
, CASE --check if alert trigger condition contains setting for 'Condition Must Exist For'
    WHEN oah.AlertObjects.AlertConfigurations.Trigger LIKE '%%' THEN ''
    ELSE REPLACE(SUBSTRING( 
            --substring s
            oah.AlertObjects.AlertConfigurations.Trigger, 
            --substring start
            (CHARINDEX(
                'P', 
                oah.AlertObjects.AlertConfigurations.Trigger, 
                0)+14),
            --substring length
            (CHARINDEX(
                '', 
                oah.AlertObjects.AlertConfigurations.Trigger, 
                0) - CHARINDEX(
                        'P', 
                        oah.AlertObjects.AlertConfigurations.Trigger, 
                        0) - 14)
            ), --end substring
        --replace pattern
        'T',
        --replace replacement
        ''
        ) --end replace
        END AS [Delay Setting]
FROM Orion.AlertHistory oah
--WHERE EventType = 0 --https://support.solarwinds.com/SuccessCenter/s/article/Meaning-of-EventTypes-value-in-the-AlertHistory-table?language=en_US
Where [OAH].TimeStamp >= GETUTCDATE() - 14
GROUP BY oah.AlertObjects.RelatedNodeCaption, oah.AlertObjects.AlertConfigurations.DisplayName, oah.AlertObjects.EntityCaption, oah.AlertObjects.RelatedNodeCaption, oah.AlertObjects.EntityType, oah.AlertObjects.AlertConfigurations.Frequency
ORDER BY [Alert Count] DESC