5 Replies Latest reply on Feb 22, 2017 9:17 AM by p.darila

    VMan / Orion report for CPU Ready [%]

    squinsey

      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

        • Re: VMan / Orion report for CPU Ready [%]
          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:

          cpuready_cluster.png

           

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

            • Re: VMan / Orion report for CPU Ready [%]
              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

                • Re: VMan / Orion report for CPU Ready [%]
                  p.darila

                  Hi again,

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

                   

                  ReportFinal.png

                   

                  ReportFinal2.png

                   

                   

                  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)

                  )

                  ReportSettings.png

                  (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

                    • Re: VMan / Orion report for CPU Ready [%]
                      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 ?

                        • Re: VMan / Orion report for CPU Ready [%]
                          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)