4 Replies Latest reply on Jun 19, 2018 10:31 AM by mesverrum

    Help With SQL Reports

    mark.grogan

      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="/Orion/View.aspx?View=NodeDetails&NetObject=N:' + 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.

        • Re: Help With SQL Reports
          mesverrum

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

           

          add:

          ,Device_Type

          ,DSS_Site

           



            • Re: Help With SQL Reports
              mark.grogan

              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.

            • Re: Help With SQL Reports
              cscoengineer

              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)

              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

               

              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'

               

              Thanks

              Amit

              1 of 1 people found this helpful