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

# Need to calculate percentages in a report

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

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

• ###### Re: Need to calculate percentages in a report

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

• ###### Re: Need to calculate percentages in a report

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
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

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]

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

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

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

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]

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

Brain fart!! 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

Yes, that is correct.

• ###### Re: Need to calculate percentages in a report

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

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

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]

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

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.