Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

VMan / Orion report for CPU Ready [%]

Hi all,

Trying to get a report via the Orion portal for CPU ready.

I know it's avail in VMan, but in Orion the value comes up with {0:0.00} ms.

Anyone familiar with this and can confirm it's actually displaying CPU Ready % and just needs the Custom Format changed?

I'd prefer it in Orion obviously, but if VMan Reporting is the only solution, then I need to narrow the calculations down to business hours M-F, 6am - 9pm.

In my below examples, I attempted to set the clustername seperated by OR statements before the AND, however it did not appear to work.
Is it possible to optimise the queries for the the VMs in the various clusters, or is it correct how I have done it.

VM - CPU Utilisation
vm.powerstate:poweredOn AND cluster.name:"METRO-PROD" OR
vm.powerstate:poweredOn AND cluster.name:"DC-PROD" OR
vm.powerstate:poweredOn AND cluster.name:"OL-PROD" OR
vm.powerstate:poweredOn AND cluster.name:"DC-DEV" OR
vm.powerstate:poweredOn AND cluster.name:"OL-DEV" OR
vm.powerstate:poweredOn AND cluster.name:"DC-OPS"

Thanks

Find more posts tagged with

Accepted answers

p.darila

Hmmm, very good point

Here you go:

SELECT VirtualMachine, Cluster,
Case when Week(GetDate())-WeekNumber=0 then CPUReady else Null end AS ThisWeek,
Case when Week(GetDate())-WeekNumber=1 then CPUReady else Null end AS LastWeek,
Case when Week(GetDate())-WeekNumber=2 then CPUReady else Null end AS TwoWeeksAgo,
Case when Week(GetDate())-WeekNumber=3 then CPUReady else Null end AS ThreeWeeksAgo
from
(
    SELECT VM.Name AS VirtualMachine, CS.Name AS Cluster, Week(ToLocal(VMS.DateTime)) AS WeekNumber, AVG(VMS.AvgCPUReady) AS CPUReady
    FROM Orion.VIM.VMStatistics VMS
    join Orion.VIM.VirtualMachines VM on VM.VirtualMachineID = VMS.VirtualmachineID
    join Orion.VIM.Hosts HO on HO.HostID = VM.HostID
    join Orion.VIM.Clusters CS on HO.ClusterID = CS.ClusterID
    where VM.PowerState = 'poweredOn' and CS.Name in ('METRO-PROD','DC-PROD','OL-PROD','DC-DEV','OL-DEV','DC-OPS')
    and Hour(ToLocal(VMS.DateTime)) >= 6 and Hour(ToLocal(VMS.DateTime)) < 21
    and WeekDay(ToLocal(VMS.DateTime)) in (1,2,3,4,5)
    group by VM.Name, CS.Name, Week(ToLocal(VMS.DateTime))
)

- GetDate() is machine local time.

- All 'VMS.DateTime' were changed to 'ToLocal(VMS.DateTime)'.

- Now all dates are in local time (time-zone of your machine - where database is running)

All comments

p.darila

Hello,

you can create report similar to VMAN 'CPU Ready by Cluster' by following these steps:

Create new report > choose Custom Chart > select Advanced Database Query, and choose SWQL
use this query:

SELECT Name, DateTime, CPUReady from
(
SELECT CS.Name, VMS.DateTime, AVG(VMS.AvgCPUReady) AS CPUReady
FROM Orion.VIM.VMStatistics VMS
join Orion.VIM.VirtualMachines VM on VM.VirtualMachineID = VMS.VirtualmachineID
join Orion.VIM.Hosts HO on HO.HostID = VM.HostID
join Orion.VIM.Clusters CS on HO.ClusterID = CS.ClusterID
where VM.PowerState = 'poweredOn' and CS.Name in ('METRO-PROD','DC-PROD','OL-PROD','DC-DEV','OL-DEV','DC-OPS')
group by CS.Name, VMS.DateTime
)
as mytable where Hour(DateTime) >= 6 and Hour(DateTime) < 21
and WeekDay(DateTime) in (1,2,3,4,5)

for each cluster ('METRO-PROD','DC-PROD','OL-PROD','DC-DEV','OL-DEV','DC-OPS') - it calculates average of CPU Ready for all powered On VMs on that cluster
+ there will be only mon-friday days (without saturday and sunday) + only time 6-21
Edit Resource:
- Group chart data by: Name, Legend shows: Name, Add data series: CPUReady, Units: Percent, More > TimeColumn: DateTime
and you can get output like this:

- is this what you need? or you want it in table report?

squinsey

Hi p.darila thanks for taking a look and assisting.

Indeed a table report is preferred. I tried to change it over to table but all I got was rows of %'s oops.

Sorry I didn't mention that

p.darila

Hi again,

so, I prepared query for report that could looks like this:

steps:

- create new report > Custom Table > SWQL query

use this query:

SELECT VirtualMachine, Cluster,
Case when Week(GetDate())-WeekNumber=0 then CPUReady else Null end AS ThisWeek,
Case when Week(GetDate())-WeekNumber=1 then CPUReady else Null end AS LastWeek,
Case when Week(GetDate())-WeekNumber=2 then CPUReady else Null end AS TwoWeeksAgo,
Case when Week(GetDate())-WeekNumber=3 then CPUReady else Null end AS ThreeWeeksAgo
from
(
    SELECT VM.Name AS VirtualMachine, CS.Name AS Cluster, Week(VMS.DateTime) AS WeekNumber, AVG(VMS.AvgCPUReady) AS CPUReady
    FROM Orion.VIM.VMStatistics VMS
    join Orion.VIM.VirtualMachines VM on VM.VirtualMachineID = VMS.VirtualmachineID
    join Orion.VIM.Hosts HO on HO.HostID = VM.HostID
    join Orion.VIM.Clusters CS on HO.ClusterID = CS.ClusterID
    where VM.PowerState = 'poweredOn' and CS.Name in ('METRO-PROD','DC-PROD','OL-PROD','DC-DEV','OL-DEV','DC-OPS')
    and Hour(VMS.DateTime) >= 6 and Hour(VMS.DateTime) < 21
    and WeekDay(VMS.DateTime) in (1,2,3,4,5)
    group by VM.Name, CS.Name, Week(VMS.DateTime)
)

- add all columns > sort it as you want

- Group results by Cluster then by VirtualMachine

- on each 'Week' column use SUM Data aggregation + you can use custom format (i.e. 0.000 > you will get round to 3 decimals)

- Or if you want report only for clusters, you can use modified query (it makes average of CPU Ready from VMs)

SELECT Cluster,
Case when Week(GetDate())-WeekNumber=0 then CPUReady else Null end AS ThisWeek,
Case when Week(GetDate())-WeekNumber=1 then CPUReady else Null end AS LastWeek,
Case when Week(GetDate())-WeekNumber=2 then CPUReady else Null end AS TwoWeeksAgo,
Case when Week(GetDate())-WeekNumber=3 then CPUReady else Null end AS ThreeWeeksAgo
from
(
    SELECT CS.Name AS Cluster, Week(VMS.DateTime) AS WeekNumber, AVG(VMS.AvgCPUReady) AS CPUReady
    FROM Orion.VIM.VMStatistics VMS
    join Orion.VIM.VirtualMachines VM on VM.VirtualMachineID = VMS.VirtualmachineID
    join Orion.VIM.Hosts HO on HO.HostID = VM.HostID
    join Orion.VIM.Clusters CS on HO.ClusterID = CS.ClusterID
    where VM.PowerState = 'poweredOn' and CS.Name in ('METRO-PROD','DC-PROD','OL-PROD','DC-DEV','OL-DEV','DC-OPS')
    and Hour(VMS.DateTime) >= 6 and Hour(VMS.DateTime) < 21
    and WeekDay(VMS.DateTime) in (1,2,3,4,5)
    group by CS.Name, Week(VMS.DateTime)
)

(just screenshot from settings)

- unfortunately, I wasn't able to make column names dynamic - so there are only static names 'this week', 'last... and so on. It will always calculate current week + 3 back

- all values are in %

Hope this help

squinsey

Thanks p.darila will give it a go now.

Is it correct to think these times are in fact UTC ?

Is there an easy way to force it to the locale ?

cfizz34

If this is not a default alert or report in VMAN then it should be. This is very basic stuff that even free monitoring solutions can find and report on.