6 Replies Latest reply on Aug 23, 2017 12:39 PM by sanketw

    Application availability report based on status

    brandonccr

      I was trying to figure out how to write a report for an application monitor of ours. I found the default availability reports for application monitors will just give 100% availability for any status other than down and don't take into account other statuses such as warning or critical. I am interested in trying to figure out how to write a report that could tell me what percentage of time was the application monitor in UP status for a period of time or what percentage of time it was critical or unreachable.

       

      Looking at the graphs in the Application Availability resource it looks like this is possible. For example the graph is telling me right now between 6 and 7 am this morning my application monitor was 8.33% critical and 91.67% Up. I want to get this information into report form.

       

      I'm guessing it would probably have something to do with the APM_ApplicationStatus_Detail table in the database. It looks like it gives a timestamp for each poll and what the status of the monitor was at that time. I just am not sure how to write a query to take this data and write this report with it.

       

      Has anyone done a report like this before or would anyone be able to give me an example of a query I could use to do this?

       

      Thanks!

        • Re: Application availability report based on status
          bluefunelemental

          What time frame are you looking at? There is the application hourly table right near the detail one. I think I would try out the new web reports for this and see if I could put the graph up top followed by the table details summarized hourly or daily.

            • Re: Application availability report based on status
              brandonccr

              I'm looking at doing weekly reports and our server currently collects 32 days of detailed statistics. To be honest I hadn't tried web reports graphing yet - I will look at it and I'll post if I find anything that works for this.

              • Re: Application availability report based on status
                brandonccr

                Today I took a moment to look over the web reports for this. It doesn't seem I am able to what I want in there either. I can make a graph exactly like the one in the application details, but the issues I find with this are that I seem to have to add every monitor manually (I want to be able to say select all application monitors where node custom property X = Y)  and I can't single out certain statuses to look at. In the end I would like my report to be able to show all the application monitors that fit that criteria and say for example here are the percentages of time they were in critical status or in unreachable status.

                 

                @zackm - I don't know the exact specifics of the hardware (I wasn't involved in that stuff) but I do know it has 24 logical CPU cores, 32GB RAM, and 4 240GB SSDs in RAID 10 for the database data. We currently have over 5000 nodes and 2280 component monitors.

                  • Re: Re: Application availability report based on status
                    zackm

                    1) </drool> @ your server. that's a nice one for sure!

                     

                    2) try this to get you in the ballpark. (ties in the nodes table so you can call your custom property)

                     

                    select
                      n.caption
                      ,a.name
                      ,d.availability
                      ,d.percentavailability
                    from apm_applicationstatus_detail d
                    join apm_application a on d.applicationid = a.id
                    join nodes n on a.nodeid = n.nodeid
                    where d.percentavailability <> 100
                    and n.CUSTOMPROPERTY = 'stuff'
                    

                     

                    Just edit the last line according to your needs. You may have to work with this some more (maybe an AVG or something for your hourly stuff). If you have more questions, please let me know.

                     

                    -ZackM

                    Loop1 Systems: SolarWinds Training and Professional Services

                    1 of 1 people found this helpful
                      • Re: Application availability report based on status
                        sanketw

                        Hi ZackM,

                         

                        Could you please tell how is PercentAvailability calculated?

                        Also could you please explain a bit on the below query which derives PercentAvailability for this month's Application status. I didn't quite understand RecordCount as well.

                         

                        SELECT

                        max(APM_ApplicationStatus.TimeStamp) as Month,

                        Nodes.Caption,

                        APM_Application.Name AS ApplicationName,

                         

                        sum(APM_ApplicationStatus.PercentAvailability * APM_ApplicationStatus.RecordCount) / sum(APM_ApplicationStatus.RecordCount) AS PercentAvailability

                         

                        FROM APM_ApplicationStatus with(nolock)

                        INNER JOIN APM_Application with(nolock) ON APM_Application.ID = APM_ApplicationStatus.ApplicationID

                        INNER JOIN Nodes with(nolock) ON Nodes.NodeID = APM_Application.NodeID

                        WHERE APM_ApplicationStatus.TimeStamp >= dateadd(minute, datediff(minute, getdate(), getutcdate()), dateadd(month, datediff(month, 0, getdate()), 0))

                        GROUP BY APM_Application.ID, APM_Application.Name, Nodes.Caption

                        ORDER BY Nodes.Caption, APM_Application.Name