1 Reply Latest reply on Mar 13, 2018 1:00 AM by mesverrum

    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.

        • Re: Help with query to create PDU status table
          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.