This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Help With SQL Reports

Hello

I am very new to writing SQL queries, I have been tasked with creating a problem report to provide,  Detail of the Triggered Alert (Alert Name), When the Alert was Triggered and count of times with in that period, I also would like to include columns for the Site and Device Type to which the device belongs.

I found a report written by another community member that I have been trying to tweak to give me what I need.

select

'<img src="/NetPerfMon/images/Small-' + n.StatusLED + '"/> ' + '<ahref="thwack.solarwinds.com/.../View.aspx + CAST(N.NodeID  AS varchar(256)) + '"style="font-size:100%">' + N.Caption + '</a>' AS NODE

,'<ahref="' + '/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' +CAST(ahv.AlertObjectID AS varchar(256)) + '"style="font-size:100%">' + ahv.Name + '</a>' AS 'AlertName'

,COUNT(*) AS 'Total_Alerts'

,DAY(timestamp)AS 'Day'

,MONTH(TimeStamp)AS'Month'

,YEAR(timestamp)AS 'Year'

FROM AlertHistoryView ahv with(nolock)

INNER JOIN Nodes n ON n.NodeID = ahv.RelatedNodeId

WHERE

Category =‘Default'

AND Device_Type = 'switch'

AND EventTypeWord = 'Triggered'

AND DATEDIFF(DAY,TimeStamp, GETDATE()) < 30

group by
DAY(timestamp),MONTH(TimeStamp) ,YEAR(timestamp)

,n.StatusLED,n.Caption,n.NodeID,ahv.AlertObjectID,ahv.Name

order by total_alerts desc, month(timestamp)desc, day(timestamp)desc

The above seems to work well,  However I am currently unable to work out how to include columns from the Nodes (View) with in the report.

The follow columns present in the nodes View Table need to be visible with in the report.

Device_Type

DSS_Site

any help or pointers would be much appreciated.

  • After ",YEAR(timestamp)AS 'Year'" and before "FROM AlertHistoryView ahv with(nolock)"

    add:

    ,Device_Type

    ,DSS_Site



  • Thanks for the reply Mesverrum, how ever doing so does not work....

    I have had a play with the SQL statement and removing the following lines allows the columns to be added.

    COUNT(*) AS 'Total_Alerts'

    group by DAY(timestamp),MONTH(TimeStamp) ,YEAR(timestamp),n.StatusLED,n.Caption,n.NodeID,ahv.AlertObjectID,ahv.Name

    How ever I would still like the total number alerts per day grouped to give me the individual day total alert count.

  • I looked at your report and it does give you the information, but with some small tweaks you get a lot more.  For example, it's import to know how many emails were sent out.  Did the emails fail to be sent?  How many resets were triggered?  All this in a nice little box - to have control over the view (swql)

    pastedImage_0.png

    SELECT

    ah.AlertObjects.AlertConfigurations.Name as [Alert Name]

    ,count(case when ah.eventtype=0 then 1 else null end) as [Triggered]

    ,count(case when ah.EventType=1 then 1 else null end) as [Reset]

    --,count(case when ah.EventType=5 then 1 else null end) as [Action Fail]

    ,count(case when ah.EventType=6 then 1 else null end) as [Action Success]

    ,count(case when ah.EventType=6 and act.ActionTypeID like '%email%' and act.Enabled = TRUE then 1 else null end) as [Email Success]

    ,count(case when ah.EventType=5 and act.ActionTypeID like '%email%' and act.Enabled = TRUE then 1 else null end) as [Email Failed]

    FROM Orion.AlertHistory ah

    left join Orion.Actions act on act.ActionID=ah.ActionID

    where DAYDIFF(tolocal(ah.TimeStamp),GETDATE()) <=30 and ah.AlertObjects.AlertConfigurations.Name is not null

    --and ah.AlertObjects.AlertConfigurations.Name like '%${SEARCH_STRING}%'

    group by ah.AlertObjects.AlertConfigurations.Name

    order by [Triggered] DESC

    -- EventType 0 then 'Triggered'

    -- EventType 1 then 'Reset'

    -- EventType 2 then 'Acknowledged'

    -- EventType 3 then 'Note'

    -- EventType 4 then 'AddedToIncident'

    -- EventType 5 then 'ActionFailed'

    -- EventType 6 then 'ActionSucceeded'

    -- EventType 7 then 'Unacknowledge'

    -- EventType 8 then 'Cleared'

    To get a visual representation of the top 10 triggers(sql):

    select

    convert(datetime,tmp.date1) as [Date]

    ,count( tmp.mess) as [Total]

    , tmp.mess as [Message]

    from (

          select ah.TimeStamp [DateTIme]

                 ,convert(date,ah.TimeStamp) as [Date1]

                 ,ah.name as [mess]

          from AlertHistoryView ah

      where DATEDIFF(day,ah.TimeStamp,getdate()) <= 30

      and ah.name in (select top 10 ah.name

          from AlertHistoryView ah

      where DATEDIFF(day,ah.TimeStamp,getdate()) <= 30

      group by ah.name

      order by count(*) desc

      )

      ) tmp

    group by tmp.date1, tmp.mess

    pastedImage_1.png

    To find the offending node which is generating excessive alerts.  A simple tweak to the original query will do it:

    SELECT

    ah.AlertObjects.Node.Caption as [Node]

    --ah.AlertObjects.AlertConfigurations.Name as [Alert Name]

    ,count(case when ah.eventtype=0 then 1 else null end) as [Triggered]

    ,count(case when ah.EventType=1 then 1 else null end) as [Reset]

    --,count(case when ah.EventType=5 then 1 else null end) as [Action Fail]

    ,count(case when ah.EventType=6 then 1 else null end) as [Action Success]

    ,count(case when ah.EventType=6 and act.ActionTypeID like '%email%' and act.Enabled = TRUE then 1 else null end) as [Email Success]

    ,count(case when ah.EventType=5 and act.ActionTypeID like '%email%' and act.Enabled = TRUE then 1 else null end) as [Email Failed]

    FROM Orion.AlertHistory ah

    left join Orion.Actions act on act.ActionID=ah.ActionID

    where DAYDIFF(tolocal(ah.TimeStamp),GETDATE()) <=30 and ah.AlertObjects.node.caption is not null

    --and ah.AlertObjects.AlertConfigurations.Name like '%${SEARCH_STRING}%'

    group by ah.AlertObjects.Node.Caption

    order by [Triggered] DESC

    -- EventType 0 then 'Triggered'

    -- EventType 1 then 'Reset'

    -- EventType 2 then 'Acknowledged'

    -- EventType 3 then 'Note'

    -- EventType 4 then 'AddedToIncident'

    -- EventType 5 then 'ActionFailed'

    -- EventType 6 then 'ActionSucceeded'

    -- EventType 7 then 'Unacknowledge'

    -- EventType 8 then 'Cleared'

    pastedImage_2.png

    Thanks

    Amit

  • oh yeah, it slipped my mind that you have to add them to the 'group by' lines as well, but the suggested SWQL queries above are pretty solid as well if you want to give the a go.