3 Replies Latest reply on Jan 9, 2018 9:13 AM by mesverrum

    Help? - Application Monitor Reporting - % as green, % as warning, % as critical, % as down

    jlhartsock

      I've been given a new requirement for reporting on applications in which my user would like to see the percentages for each state of an application's monitoring passes (monitoring using an HTTPS component in SAM) in the past month. The availability percentage available through custom tables only calculates based on being up or down with passes in the warning or critical state counting as being available. I need something that would look something like this:

       

      Green      95.5%

      Warning    2.0%

      Critical      1.0%

      Down        1.5%

       

      Can anyone help me understand how to go about this? I apologize if this is easy as this is my first time diving into reports that aren't default.

        • Re: Help? - Application Monitor Reporting - % as green, % as warning, % as critical, % as down
          jlhartsock

          Replying in case this helps someone else out. I was able to get some time for one of my DBA's inhouse and created the following query that can be run using the database manager utility. I've not found a way yet to build this into a report so I execute this monthly and then update the HTML widget on my report. By changing the dates, the availability state, and componentID based on the desired monitor, I'm able to build out the "color" percentages that I needed for my reporting.

           

          DECLARE @percentage FLOAT

           

          select @percentage =  round((detail_cnt.cnt*100.0)/(detail_total_cnt.cnt),3)

          from  (select sum(recordcount) cnt from [APM_ComponentStatus] where componentid = 58551 and Availability = 1 and timestamp between '10/01/17' and '10/31/17') detail_cnt,

                   --- totals

                (select sum(recordcount) cnt from [APM_ComponentStatus] where componentid = 58551 and timestamp between '10/01/17' and '10/31/17') detail_total_cnt

           

          select percentage = @percentage

            • Re: Help? - Application Monitor Reporting - % as green, % as warning, % as critical, % as down
              mesverrum

              You can add this to a custom query resource to get a similar result for applications

               

              SELECT s.Application.node.Caption as [Node], s.Application.Name as [Application]
              , (sum(case when s.Availability=1 then 1
              else 0 end)*100)/(count(s.ApplicationID)) as [Up %]
              , (sum(case when s.Availability=3 then 1
              else 0 end)*100)/(count(s.ApplicationID)) as [Warning %]
              , (sum(case when s.Availability=14 then 1
              else 0 end)*100)/(count(s.ApplicationID)) as [Critical %]
              , (sum(case when s.Availability=2 then 1
              else 0 end)*100)/(count(s.ApplicationID)) as [Down %]
              , (sum(case when s.Availability not in (1,2,3,14) then 1
              else 0 end)*100)/(count(s.ApplicationID)) as [Other %]
              
              ,s.Application.node.DetailsUrl as [_linkfor_Node]
              ,s.Application.DetailsUrl as [_linkfor_Application]
              , '/Orion/images/StatusIcons/Small-Up.gif' as [_iconfor_Up %]
              , '/Orion/images/StatusIcons/Small-Warning.gif' as [_iconfor_Warning %]
              , '/Orion/images/StatusIcons/Small-Critical.gif' as [_iconfor_Critical %]
              , '/Orion/images/StatusIcons/Small-Down.gif' as [_iconfor_Down %]
              , '/Orion/images/StatusIcons/Small-Unknown.gif' as [_iconfor_Other %]
              
              FROM Orion.APM.ApplicationStatus s
              
              where daydiff(s.timestamp,getdate())<30
              --and s.ApplicationID=${applicationid}
              
              group by s.Application.node.Caption, s.Application.Name, s.Application.node.DetailsUrl, s.Application.DetailsUrl

               

              If you remove the comments from line 24 then you can add it to an application details page to get just that app, or leave it how it is for a summary page showing the percents for all apps

               

               

              And the below gives the same effect for individual components

               

              SELECT s.component.FullyQualifiedName as [Component]
              , (sum(case when s.Availability=1 then 1
              else 0 end)*100)/(count(s.ApplicationID)) as [Up %]
              , (sum(case when s.Availability=3 then 1
              else 0 end)*100)/(count(s.ApplicationID)) as [Warning %]
              , (sum(case when s.Availability=14 then 1
              else 0 end)*100)/(count(s.ApplicationID)) as [Critical %]
              , (sum(case when s.Availability=2 then 1
              else 0 end)*100)/(count(s.ApplicationID)) as [Down %]
              , (sum(case when s.Availability not in (1,2,3,14) then 1
              else 0 end)*100)/(count(s.ApplicationID)) as [Other %]
              
              ,s.component.DetailsUrl as [_linkfor_Component]
              , '/Orion/images/StatusIcons/Small-Up.gif' as [_iconfor_Up %]
              , '/Orion/images/StatusIcons/Small-Warning.gif' as [_iconfor_Warning %]
              , '/Orion/images/StatusIcons/Small-Critical.gif' as [_iconfor_Critical %]
              , '/Orion/images/StatusIcons/Small-Down.gif' as [_iconfor_Down %]
              , '/Orion/images/StatusIcons/Small-Unknown.gif' as [_iconfor_Other %]
              
              FROM Orion.APM.ComponentStatus s
              
              where daydiff(s.timestamp,getdate())<30
              --and s.ComponentID =${componentid}
              
              group by s.component.FullyQualifiedName, s.component.DetailsUrl

               

               

              -Marc Netterfield

                  Loop1 Systems: SolarWinds Training and Professional Services