Quarterly Usage Reports - Monthly breakdown

Question

Hi All,

Odd one here - One of the asks from our capacity team is that on top of the basic capacity planning they get from SolarWinds they would like to run historic usage reports on things like CPU, Memory & Interface Traffic for anything up to 6 Months.

They gave me an example format they used many years ago with an older tool and asked if I could get as close as possible.

As you can see from the image they want the Monthly AVG Values shown for the last month + 3 months prior to that (4 in total) but they want them in there own fields rather than all 4 months rolled into one avg value which is all you get right now from the OOB Reports.

I'm sure this is possible, but I haven't really had a chance to try and attempt the SQL/SWQL Fu that would be required to get the Month-1, Month-2, Month-3 etc.

If anyone has any ideas or even something that is close to what I'm looking for then I would love to discuss further.

Thanks

mesverrum · Answer

This query gets the data you are asking for, runs reasonably quickly in my environment (16-20 seconds) but yours may vary. select n.caption, n.CPULoad as CurrentLoad , m0.AvgCpu, m0.MaxCPU , m1.AvgCpu, m1.MaxCPU , m2.AvgCpu, m2.MaxCPU , m3.AvgCpu, m3.MaxCPU from orion.nodes n left join ( select NodeID, max(MaxLoad) as MaxCPU, avg(AvgLoad) as AvgCPU from orion.CPULoad where DateTime > DATETRUNC('month',GETDATE()) group by NodeID ) m0 on m0.nodeid=n.NodeID left join ( select NodeID, max(MaxLoad) as MaxCPU, avg(AvgLoad) as AvgCPU from orion.CPULoad where DateTime < DATETRUNC('month',GETDATE()) and datetime > ADDMONTH(-1,DATETRUNC('month',GETDATE())) group by NodeID ) m1 on m1.nodeid=n.NodeID left join ( select NodeID, max(MaxLoad) as MaxCPU, avg(AvgLoad) as AvgCPU from orion.CPULoad where DateTime < ADDMONTH(-1,DATETRUNC('month',GETDATE())) and datetime > ADDMONTH(-2,DATETRUNC('month',GETDATE())) group by NodeID ) m2 on m2.nodeid=n.NodeID left join ( select NodeID, max(MaxLoad) as MaxCPU, avg(AvgLoad) as AvgCPU from orion.CPULoad where DateTime < ADDMONTH(-2,DATETRUNC('month',GETDATE())) and datetime > ADDMONTH(-3,DATETRUNC('month',GETDATE())) group by NodeID ) m3 on m3.nodeid=n.NodeID where n.cpuload >= 0

m_roberts · Answer

Hi Dave

What you need is the Pivot function, which is a feature of SQL, but sadly not supported within the API currently.

I suggest you add your voice here - https://thwack.solarwinds.com/t5/Discussions/SWQL-Pivot-Tables-or-Count-Return-number/td-p/260325

Therefore, I would suspect the following options exist:

* Use of CASE or sub queries will need to be used
* Look to either create a custom page within the Orion app which performs this pivot function in code 
* Create this report in external app against the Orion SQL database

Mark