Help with query to create PDU status table

breakerofchains

I have ~150 PDUs in the data center and I wish to produce an output that will list each PDU by name in one column and in subsequent columns I need MIN, MAX, AVG for Temperature, Humidity, Amps (x3 by phase), Voltage, (x3 by phase) Watts (x3 by phase) over the previous 7 days with two final columns providing kWh and the date/time of the report so 35 columns total with only one row per PDU.  My SQL skills are very dated so any help someone wants to provide would need to be pretty specific.  The output, when it is all said and done, needs to be .CSV so that numbers will be seen in Excel as numbers.

This is for a project I am working to track all aspects of compute racks to provide overall an overall view of capacity and health.  The spreadsheet visually depicts rack space, network port availability, patch panel availability, fiber availability and, with help from the Thwack community, it will show temperature and humidity values as well.  In all there are 13 aspects depicted allowing a quick analysis on remaining data center resources and areas of concern.

mesverrum

I don't have an example of the code for this kind of table handy, but basically you are going to need to start of with your base query showing the nodes, then for each column you join in the undp value columns for each custom poller metric you are collecting.  As long as all the UNDP's are single rows this is pretty easy, if they have multiple rows it could become kind of messy looking and makes the sql a bit more complex to clean that up but in general it works the same.

Something like

select n.caption, p1.[Poller1 Min],  p1.[Poller1 Avg], p1.[Poller1 Max], p2.[Poller2 Min], p2.Poller2 Avg], p2.[Poller2 Max], ...etc

from orion.nodes n,

left join (select nodeid, min(currentvalue) as [Poller1 Min], avg(currentvalue) as [Poller1 Avg], max(currentvalue) as [Poller1 Max]

from orion.npm.custompollerassignment cpa

where cpa.custompollername = 'first undp poller'

group by nodeid) p1 on p1.nodeid=n.nodeid

left join (select nodeid, min(currentvalue) as [Poller2 Min], avg(currentvalue) as [Poller2 Avg], max(currentvalue) as [Poller2 Max]

from orion.npm.custompollerassignment cpa

where cpa.custompollername = 'second undp poller'

group by nodeid) p2 on p2.nodeid=n.nodeid

etc etc for each of the next few dozen pollers.  I'm not sure how many joins it takes before performance would be an issue but that's a bridge you would have to cross if you get there.  Worst case you split the query up into a couple reports rather than one giant one.