Capacity Report for Servers

WinterSoldier

I am working on a report to get the Avg and Peak CPU load, Memory for my monitored servers. The requirement is that I want to see the following information:

Hostname (Managed Node), IP Address, MachineType, Vendor, Site (Custom Property), Avg CPU Load (In Percentage), Peak CPU Load (In Percentage),  Avg Memory (In Percentage), Peak Memory (In Percentage), Timestamp

,Host (From the virtualization table)

,Cluster (From the virtualization table).

I tried getting the columns from hostname until timestamp through my web based report but if I want to also get the details of the Vm that is associated with hosts & cluster it requires SQL or SWQL. Can anyone help here?

One more filter to add here is, I am looking for the historical data for Last 7 days and last 30 days.

Thanks in advance.

Lofstrand

Hi, something like this maybe

Change the Days filter to 30 where you want that. 

Also, you asked for Timestamp. What timestamp do you want as we are summarizing for 7 days.

SELECT
    N.DisplayName AS Hostname
    ,N.IP_Address
    ,N.Vendor
    ,N.MachineType
    ,N.CustomProperties.Site
    ,AVG(N.CPULoadHistory.AvgLoad) AS [AVG CPU Load]
    ,MAX(N.CPULoadHistory.AvgLoad) AS [MAX CPU Load]
    ,AVG(N.CPULoadHistory.AvgPercentMemoryUsed) AS [AVG Mem Usage]
    ,MAX(N.CPULoadHistory.AvgPercentMemoryUsed) AS [MAX Mem Usage]
    ,N.VirtualMachine.host.DisplayName AS [VM host]
    ,N.VirtualMachine.host.Cluster.DisplayName AS [VM Cluster]
FROM Orion.Nodes as N
WHERE
    N.CPULoadHistory.DateTime>ADDDAY(-7,GETUTCDATE())
GROUP BY
    N.DisplayName
    ,N.IP_Address
    ,N.Vendor
    ,N.MachineType
    ,N.CustomProperties.Site
    ,N.VirtualMachine.host.DisplayName
    ,N.VirtualMachine.host.Cluster.DisplayName
ORDER BY 
    N.DisplayName