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

Bump *Any sql devs our there that can help?*

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

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

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems