13 Replies Latest reply on Oct 31, 2016 12:45 PM by patriot

    Need to calculate percentages in a report

    patriot

      I have a somewhat unusual report (at least i think it is) that I need to do for a customer.

      I need to be able to calculate the percentage of CPU utilization polls that were BELOW 75 for a customers servers and then also calculate an overall percentage for that group of servers. In other words, I want to be able to show what percentage of polls for CPU utilization were below 75% for each server belonging to a customer (custom property is used to filter the report) over the last month, and then to also show a single average value for the group of servers.

       

      Can that be done?

        • Re: Need to calculate percentages in a report
          patriot

          Oh, and I also need to do the same for memory utilization. Thanks so much.

            • Re: Need to calculate percentages in a report
              patriot

              Anyone? zackm, I know you know this stuff!!

                • Re: Need to calculate percentages in a report
                  I LIKE EGGS

                  Hasn't got an answer so why not 

                   

                  select n.Caption,
                  case when cpu.AvgPercentMemoryUsed < 75 then cpu.AvgPercentMemoryUsed end '< 75 %',
                  case when cpu.AvgPercentMemoryUsed > 75 then cpu.AvgPercentMemoryUsed end '> 75%',
                  cpu.DateTime
                  FROM CPULoad cpu
                  INNER JOIN Nodes n ON n.NodeID = cpu.NodeID 
                  WHERE (datetime > Dateadd(minute,-150 ,Getdate()))-- check the last 15 entries 
                  --and n.NodeID = 49 -- select a distinct node 
                  --order by '< 75 %' --order by higest cpu reading
                  

                   

                  This should give you what you are looking for, it checks the previous 15 records and the case statement is pretty much checking for two values you can also comment the nodeid if you are looking for a specific node and un-comment the order by to get the higest cpu reading

              • Re: Need to calculate percentages in a report
                blsanner

                The problem you are going to have with this is that, unless you have increased your detail record retention, you won't know what the individual polling values are out past 7 days.  By default, Orion summarizes data after 7 days so that you only have an hourly average at that point which makes it impossible to determine the number of polls above 75%.  However, if you have increased your detail statistic retention to 30 days, then the following SWQL query should work for you:

                 

                SELECT n.Caption,

                SUM(CASE WHEN AvgLoad > 75 THEN 1 ELSE 0 END) * 100.0 / COUNT(AvgLoad) AS [Percentage over 75]

                FROM Orion.CPULoad c

                JOIN Orion.Nodes n ON c.NodeID = n.NodeID

                WHERE DAYDIFF(DateTime, GETUTCDATE()) < 30

                GROUP BY Caption

                 

                You would just need to add in the condition for your customer custom property in the WHERE line.

                 

                - blsanner

                Loop1 Systems: SolarWinds Training and Professional Services

                  • Re: Need to calculate percentages in a report
                    patriot

                    Thanks, but when I try this query I get an error saying that DAYDIFF is "not a recognized function name". What is that about?

                      • Re: Need to calculate percentages in a report
                        patriot

                        Actually, there is more than one issue with this query. Orion.CPULoad and Orion.Nodes are also not recognized.

                          • Re: Need to calculate percentages in a report
                            blsanner

                            This is an SWQL query and cannot be used as just a SQL query.  You either have to put it in a Custom Query resource on an Orion view or you can put it in a report using the Advanced SQL/SWQL Query option and selecting SWQL as the query type.  I edited it to add in the use of a custom property:

                             

                            SELECT n.Caption,

                            SUM(CASE WHEN c.AvgLoad > 75 THEN 1 ELSE 0 END) * 100.0 / COUNT(c.AvgLoad) AS [Percentage over 75]

                            FROM Orion.CPULoad c

                            JOIN Orion.Nodes n ON c.NodeID = n.NodeID

                            JOIN Orion.NodesCustomProperties ncp ON c.NodeID = ncp.NodeID

                            WHERE DAYDIFF(DateTime, GETUTCDATE()) < 30 AND ncp.Site_Code = 'ABC'

                            GROUP BY n.Caption

                             

                            You would just have to change the Site_Code to whatever your custom property name is and the value, of course.  Here is what it looks like in a Custom Query resource:

                            And the result:

                              • Re: Need to calculate percentages in a report
                                patriot

                                Brain ****!! Thank you. So, if I understand the query correctly, I will get a result for the percentage of polled data (or summarized data since it covers 30 days) that the CPU Load was greater than 75%. Is that correct?

                                • Re: Need to calculate percentages in a report
                                  patriot

                                  Also, how would I get the data for the last calendar month (not the last 30 days)? Thanks so much again for your help.

                                    • Re: Need to calculate percentages in a report
                                      patriot

                                      Last question. I need to add one other calculation step in the report. I need to show only a single average CPU and memory used value for all of the nodes in the same department. In other words, if the Sale department has 5 servers, I need to show a single result for the average number of polls in the last 24 hours and the last calendar month that the CPU was less than 75% and the memory util was less than 75%. Does that make sense?

                                      • Re: Need to calculate percentages in a report
                                        blsanner

                                        One way to get last month would be to replace:

                                         

                                        DAYDIFF(DateTime, GETUTCDATE()) < 30

                                         

                                        with:

                                         

                                        (MONTH(GETUTCDATE()) - MONTH(DateTime)) = 1

                                         

                                        So, the entire query would be:

                                         

                                        SELECT n.Caption,

                                        SUM(CASE WHEN c.AvgLoad > 75 THEN 1 ELSE 0 END) * 100.0 / COUNT(c.AvgLoad) AS [Percentage over 75]

                                        FROM Orion.CPULoad c

                                        JOIN Orion.Nodes n ON c.NodeID = n.NodeID

                                        JOIN Orion.NodesCustomProperties ncp ON c.NodeID = ncp.NodeID

                                        WHERE (MONTH(GETUTCDATE()) - MONTH(DateTime)) = 1

                                        GROUP BY n.Caption

                                         

                                        As for the department average, I would not suggest or even try to put that in the same query.  I could probably come up with something in a separate query.  But, for clarification, are you looking for the percentage above 75% or less than 75% for the department averages?

                                          • Re: Need to calculate percentages in a report
                                            patriot

                                            I'm looking for below 75% for both CPU and memory utilization (in separate reports). I just used CPU as an example thinking that I could modify for the memory myself. So, I need to be able to show a single value for the average CPU Load for all devices for the last month for each customer filtered to show only that customers devices. Same then for memory utilization. Thanks so much.