Total Resources combined together by Custom Property Entry.

jakegevans

I have two custom properties that are attached to every VM. Datacenter and Product. One of my managers are wanting a "simple" report that shows the total amount of memory, the total amount of memory used that is grouped by Datacenter and another grouped by Product for the last 30 days.

Meaning instead of seeing each VM individually that shows their CPU and Memory info he just wants to see ALL the CPU count for Datacenter A, B, C and ALL CPU combined for Datacenter A, B, C.

I hope I explained this well, I probably didn't so if anyone has questions please let me know.

yaquaholic

Hi Jake,

Here's a little SWQL to get you started....


SELECT N.NodeID, N.IPAddress, N.Caption, CL.AvgLoad, CL.AvgMemoryUsed
FROM Orion.Nodes N
INNER JOIN Orion.CPULoad CL ON N.NodeID = CL.NodeID
WHERE N.CustomProperties.<CP Name> LIKE '<CP value>'
AND CL.DateTime >=AddDay(-30,getdate()))

The above SWQL code, once you add your Custom Property Name to <CP Name> and the appropriate value (Datacentre or Product in your case), will retrieve the current averages for each device meeting the WHERE Clause. 

To work out the overall averages, just average the results:

SELECT AVG(CL.AvgLoad) AS AVG_CPU,

               AVG(CL.AvgMemoryUsed) as AVG_Mem

FROM Orion.Nodes N

INNER JOIN Orion.CPULoad CL ON N.NodeID = CL.NodeID

WHERE N.CustomProperties.<CP Name> LIKE '<CP value>'

AND CL.DateTime >=AddDay(-30,getdate()))

You can use this SWQL logic on any table with a NodeID in it, within the JOIN.

That should get you started