27 Replies Latest reply on May 11, 2015 12:53 PM by d09h

    modify SWQL query to include node names

    d09h

      I am monitoring NTI environmental devices for humidity (and temperature, but my use case here is the humidity values).  I have a report from ReportWriter that works, but history has taught me that a working SWQL query would future-proof me more if when the database schema changes.

       

      Can anyone modify this SWQL query to include node name?

       

       

      SELECT c.UniqueName AS [Poller Name], s.Status AS [Current Status], ToLocal(s.DateTime) AS [Last Polled]

       

      FROM Orion.NPM.CustomPollerAssignment a

       

      JOIN Orion.NPM.CustomPollers c ON a.CustomPollerID = c.CustomPollerID

       

      JOIN Orion.NPM.CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID

       

      WHERE c.PollerType = 'F' AND a.NodeID = ${NodeID} AND ((c.UniqueName = 'EnvrnHumidity') OR (c.UniqueName = 'Rack1Humidity') OR (c.UniqueName = 'Rack8Humidity')) ORDER BY s.Status DESC

        • Re: modify SWQL query to include node names
          dhanson

          Why not just add it to your select statement and reference a table that includes your device's host name?

           

          This is what your script should look like. I've bolded the differences:

           

          SELECT c.UniqueName AS [Poller Name], s.Status AS [Current Status], ToLocal(s.DateTime) AS [Last Polled], n.Caption AS [Node Name]

          FROM Orion.NPM.CustomPollerAssignment a

          JOIN Orion.NPM.CustomPollers c ON a.CustomPollerID = c.CustomPollerID

          JOIN Orion.NPM.CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID

          JOIN Orion.NPM.Nodes n ON a.NodeID = n.NodeID

          WHERE c.PollerType = 'F' AND a.NodeID = ${NodeID} AND ((c.UniqueName = 'EnvrnHumidity') OR (c.UniqueName = 'Rack1Humidity') OR (c.UniqueName = 'Rack8Humidity')) ORDER BY s.Status DESC

           

          I had to change it a little bit to include some pollers that I actually have in my environment and I had to modify out the ${NodeID} variable, edit out your ToLocal function, and remove your high level database references (orion.NPM.), but ultimately i got it to work in my system as this. Bolded some of my individual edits:

           

          SELECT c.UniqueName AS [Poller Name], s.Status AS [Current Status], s.DateTime AS [Last Polled], n.Caption AS [Node Name]

          FROM CustomPollerAssignment a

          JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID

          JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID

          JOIN Nodes n ON a.NodeID = n.NodeID

          WHERE a.NodeID = '117' AND ((c.UniqueName = 'upsAdvOutputCurrent') OR (c.UniqueName = 'upsAdvInputFrequency') OR (c.UniqueName = 'upsAdvOutputFrequency'))

          ORDER BY s.Status DESC

           

          and returned:

           

          SQL - for thwack ticket.JPG

           

          Is this about what you were looking for?

            • Re: modify SWQL query to include node names
              d09h

              Sorry for the delay in responding.  I was hoping for a summary type of chart in that multiple environmental devices would be listed with their respective results (specifically both nodes that have the pair of pollers assigned).  If I'm not mistaken, your solution is specific to only one node, correct?

                • Re: modify SWQL query to include node names
                  dhanson

                  You can increase the scope to include the results for the other node as well. Figure out the NodeID for both of the nodes (or all of the nodes you want to alert on) and plug them in, separated by commas, in the bolded area below:

                   

                   

                  SELECT c.UniqueName AS [Poller Name], s.Status AS [Current Status], ToLocal(s.DateTime) AS [Last Polled], n.Caption AS [Node Name]

                  FROM Orion.NPM.CustomPollerAssignment a

                  JOIN Orion.NPM.CustomPollers c ON a.CustomPollerID = c.CustomPollerID

                  JOIN Orion.NPM.CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID

                  JOIN Orion.NPM.Nodes n ON a.NodeID = n.NodeID

                  WHERE c.PollerType = 'F' AND a.NodeID IN (#,#,#) AND ((c.UniqueName = 'EnvrnHumidity') OR (c.UniqueName = 'Rack1Humidity') OR (c.UniqueName = 'Rack8Humidity')) ORDER BY s.Status DESC

                   

                  for instance, if you have 2 nodes that you want to include and they are "17" and "23", then replace it with (17,23).

                   

                  The table results will show:

                   

                  Poller Name // Current Status // Last Polled time // Node Name

                   

                  Are you looking for other details? if this isn't what you are looking for, if you could, can you create an example of the output you want?

                  Also, your initial query includes ${NodeName} which narrows your results to a specific node name anyway.

                   

                  Just making a guess, but you're looking for something like:

                   

                  NodeName // Poller1Status // Poller2Status // Poller3Status // etc.

                   

                  is that right?

                   

                  HTH!

                    • Re: modify SWQL query to include node names
                      d09h

                      dhanson, thank you for your help.  Hard-coding a Node ID may set me up for an uncomfortable conversation when/ if I ever remove and add back a device and forget what is hard coded throughout the application.  Think it would be possible to match on the universal device poller that is providing this information?

                       

                      Ideal format:

                       

                      FirstNodeName // Poller1Status // Poller2Status

                      SecondNodeName // Poller1Status // Poller2Status

                       

                      Again, thanks for your assistance.

                        • Re: modify SWQL query to include node names
                          dhanson

                          I saw your first version of the message (filter results based on a similarity between node names) and this is very easy to do. Check out the bold below, as it is the only change (this isn't your ideal output, but the output I described before):

                           

                          SELECT c.UniqueName AS [Poller Name], s.Status AS [Current Status], ToLocal(s.DateTime) AS [Last Polled], n.Caption AS [Node Name]

                          FROM Orion.NPM.CustomPollerAssignment a

                          JOIN Orion.NPM.CustomPollers c ON a.CustomPollerID = c.CustomPollerID

                          JOIN Orion.NPM.CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID

                          JOIN Orion.NPM.Nodes n ON a.NodeID = n.NodeID

                          WHERE c.PollerType = 'F' AND a.NodeID LIKE '%MYDEVICE%' AND ((c.UniqueName = 'EnvrnHumidity') OR (c.UniqueName = 'Rack1Humidity') OR (c.UniqueName = 'Rack8Humidity')) ORDER BY s.Status DESC

                           

                          Now, if we wanted to match on the UnDP, we can exclude the a.NodeID from the WHERE statement completely, as we're already filtering on specific UnDP UniqueNames there. (basically remove what is underlined above)

                           

                          Thank you for specifying what output you want to see. I think I can make this work, just give me a little time and I'll see if I can work out the query to get you your ideal table. =)

                          • Re: modify SWQL query to include node names
                            dhanson

                            Ok, got one for you, and it could potentially be optimized and improved to be shorter and a lot cleaner, but this will get you the results you want. Just replace "POLLER#Name" with the name of the appropriate UnDP's you want to filter on. Don't forget to name your columns properly in the final query (e.g. row 36 below...make sure you edit the poller names there):

                             

                            WITH a AS
                            (
                            SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [Last Polled], n.Caption
                            FROM CustomPollerAssignment a
                            JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID
                            JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID
                            JOIN Nodes n ON a.NodeID = n.NodeID
                            WHERE c.UniqueName = 'POLLER1Name'
                            GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status
                            ORDER BY s.Status DESC
                            )
                            ,
                            b AS
                            (
                            SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [Last Polled], n.Caption
                            FROM CustomPollerAssignment a
                            JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID
                            JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID
                            JOIN Nodes n ON a.NodeID = n.NodeID
                            WHERE c.UniqueName = 'POLLER2Name'
                            GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status
                            ORDER BY s.Status DESC
                            )
                            ,
                            c AS
                            (
                            SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [Last Polled], n.Caption
                            FROM CustomPollerAssignment a
                            JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID
                            JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID
                            JOIN Nodes n ON a.NodeID = n.NodeID
                            WHERE c.UniqueName = 'POLLER3Name'
                            GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status
                            ORDER BY s.Status DESC
                            )
                            SELECT n.Caption AS NodeName, a.Status AS [Poller1Name], b.Status AS [Poller2Name] c.Status AS [Poller3Name]
                            FROM Nodes AS n
                            JOIN a ON n.Caption = a.Caption
                            JOIN b ON n.Caption = b.Caption
                            JOIN c ON n.Caption = c.Caption
                            
                            
                            

                             

                            Let me know if this works out for you!

                              • Re: modify SWQL query to include node names
                                d09h

                                dhanson, surely the issue is PEBKAC.  My modified version doesn't work (Error: A query to the SolarWinds Information Service failed):

                                 

                                 

                                WITH a AS

                                ( 

                                SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [LastPolled], n.Caption

                                FROM CustomPollerAssignment a

                                JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID

                                JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID =s.CustomPollerAssignmentID 

                                JOIN Nodes n ON a.NodeID = n.NodeID 

                                WHERE c.UniqueName = 'Rack1Humidity' 

                                GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status 

                                ORDER BY s.Status DESC

                                b AS 

                                (

                                SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [LastPolled], n.Caption

                                FROM CustomPollerAssignment a 

                                JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID 

                                JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID =s.CustomPollerAssignmentID 

                                JOIN Nodes n ON a.NodeID = n.NodeID 

                                WHERE c.UniqueName = 'Rack8Humidity' 

                                GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status 

                                ORDER BY s.Status DESC

                                ) 

                                SELECT n.Caption AS NodeName, a.Status AS [Rack1Humidity], b.Status AS [Rack8Humidity]  

                                FROM Nodes AS n 

                                JOIN a ON n.Caption = a.Caption 

                                JOIN b ON n.Caption = b.Caption

                                 

                                  • Re: modify SWQL query to include node names
                                    dhanson

                                    You have an extra comma in there after the last parentheses. Remove this and give it another shot!

                                     

                                    (...)

                                    ORDER BY s.Status DESC

                                    )

                                    , <------this can go

                                    SELECT n.Caption AS NodeName

                                    (...)

                                      • Re: modify SWQL query to include node names
                                        d09h

                                        Maybe that's only the first of several errors, as removing that comma didn't make a difference.  (Obviously needed to be removed though.  Thanks for catching that.)

                                          • Re: modify SWQL query to include node names
                                            dhanson

                                            Ahh, so I believe I've figured it out. If you are using SWQL Studio this doesn't work. Let me see if I can find you another way of doing this. My bad.

                                            • Re: modify SWQL query to include node names
                                              dhanson

                                              Ok, back to paying attention to the initial question! I've tested the below query in SWQL Studio, so you should be good to go.

                                               

                                              You want this in SWQL. Sorry. I've went and bolded the flex values for this SWQL Query. EDIT: apparently bolding something in the advanced editor doesn't necessary become visible, but I'm sure you can identify the name of your pollers in a couple places like line 1, 10 and 21. These are where you can pretty much put in whatever Poller Name you want to get it's current status pulled out.

                                              SELECT n.Caption AS NodeName, a.Status AS [Rack1Humidity], b.Status AS [Rack8Humidity]
                                              FROM Orion.Nodes AS n
                                              JOIN
                                              (
                                              SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [Last Polled], n.Caption
                                              FROM Orion.NPM.CustomPollerAssignment a
                                              JOIN Orion.NPM.CustomPollers c ON a.CustomPollerID = c.CustomPollerID
                                              JOIN Orion.NPM.CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID
                                              JOIN Orion.Nodes n ON a.NodeID = n.NodeID
                                              WHERE c.UniqueName = 'Rack1Humidity'
                                              GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status
                                              ORDER BY s.Status DESC
                                              ) AS a ON n.Caption = a.Caption
                                              JOIN
                                              (
                                              SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, s.DateTime AS [Last Polled], n.Caption
                                              FROM Orion.NPM.CustomPollerAssignment a
                                              JOIN Orion.NPM.CustomPollers c ON a.CustomPollerID = c.CustomPollerID
                                              JOIN Orion.NPM.CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID
                                              JOIN Orion.Nodes n ON a.NodeID = n.NodeID
                                              WHERE c.UniqueName = 'Rack8Humidity'
                                              GROUP BY n.Caption, c.UniqueName, s.DateTime, s.Status
                                              ORDER BY s.Status DESC
                                              ) AS b ON n.Caption = b.Caption
                                              
                                              
                                              
                                              
                                                • Re: modify SWQL query to include node names
                                                  dhanson

                                                  Oh, and just in case you were curious, here's a 3rd way to pull the exact same data....This uses pivots and can replace the first SQL query I gave you...it's significantly shorter. This does not work in SWQL Studio (Studio doesn't like PIVOT's) but will work in Report Writer or Database Manager.

                                                   

                                                  SELECT DISTINCT TOP 1000 Caption AS NodeName, Rack1Humidity, Rack8Humidity

                                                  FROM

                                                  (

                                                  SELECT TOP 100 c.UniqueName AS [Poller Name], s.Status, n.Caption

                                                  FROM CustomPollerAssignment a

                                                  JOIN CustomPollers c ON a.CustomPollerID = c.CustomPollerID

                                                  JOIN CustomPollerStatus s ON a.CustomPollerAssignmentID = s.CustomPollerAssignmentID

                                                  JOIN Nodes n ON a.NodeID = n.NodeID

                                                  WHERE c.UniqueName IN ('Rack1Humidity','Rack8Humidity')

                                                  GROUP BY n.Caption, c.UniqueName,  s.Status

                                                  ORDER BY s.Status DESC

                                                  ) AS a

                                                  PIVOT

                                                  (

                                                  MAX(Status) FOR [Poller Name] IN ([Rack1Humidity],[Rack8Humidity])

                                                  ) as pvt

                                                    • Re: modify SWQL query to include node names
                                                      d09h

                                                      dhanson, just curious...does that last query work for you as a Custom Query resource running directly on a web page?  I'm asking since it does not for me.

                                                       

                                                      I sure appreciate all your assistance though.  I rewrote so many Report Writer SQL queries during one upgrade I feel that I need to do everything in SWQL whenever possible.  I'm assuming you would not have pollers named 'Rack1Humidity' and 'Rack 8 Humidity' unless you happened to create them while you were bending over backwards to help.

                                                       

                                                      Thanks again.

                                                        • Re: modify SWQL query to include node names
                                                          dhanson

                                                          I've been testing queries through the Database Manager and SWQL Studio. When I get a successful parse, I'll plug it into Report Writer to verify it will return results. The first query I gave you worked in Database Manager and Report Writer, but not in SWQL. The 2nd one was done solely in SWQL, so it should work there. This third query was similar to the first...works in Database Manager and Report Writer, but not in SWQL.

                                                           

                                                          ...and I've been using 3 UnDP's I have for my UPS units in place of your humidity UnDP's and just swapping out the names when I post them here. =)

                                                          As far as bending over backwards, I'm still learning SQL (for example, PIVOT's..I'd never done one before), so finding these query requests on Thwack is a really good exercise for me, even if I don't get it right. It's a language that is invaluable to a SolarWinds Admin, and I've been studying it since I started this job and began working with SolarWinds. Also, it is a great chance to steal ideas.

                                                           

                                                          So I'm curious now...is the goal for you to be able to display this on a Summary or Node page? I can start working that angle, if you'd like. =) Any descriptions of where you're using this would be appreciated. I'm anticipating that this is going on a Summary/NOC page so you can monitor humidity in your IDF's, so I'm going to be looking at that until you say otherwise.

                                                            • Re: modify SWQL query to include node names
                                                              d09h

                                                              Correct, a summary page.  My current solution is a ReportWriter report (from which I could extract the SQL).  I admire the desire to teach yourself SQL, as I am also self-taught.  However, I have a horrible teacher.  And sometimes when I do something in SWQL or SQL, I find out there really was a more straightforward way built into NPM.

                                                               

                                                              Stealing each others' ideas just makes us look good in front of our management and keeps us employed.

                                                                • Re: modify SWQL query to include node names
                                                                  dhanson

                                                                  Ok, here we go!

                                                                   

                                                                  SELECT n.Caption AS NodeName, a.Status AS OutputFreq, b.Status AS InputFreq, c.Status AS OutputCurrent

                                                                  FROM Orion.Nodes AS n

                                                                  INNER JOIN

                                                                  (

                                                                    SELECT a.NodeID, c.Status

                                                                    FROM Orion.NPM.CustomPollerStatus c

                                                                    INNER JOIN Orion.NPM.CustomPollerAssignment a ON a.CustomPollerAssignmentID = c.CustomPollerAssignmentID

                                                                    WHERE AssignmentName LIKE '%upsAdvOutputFrequency%'

                                                                  ) AS a ON a.NodeID = n.NodeID

                                                                  INNER JOIN

                                                                  (

                                                                    SELECT a.NodeID, c.Status

                                                                    FROM Orion.NPM.CustomPollerStatus c

                                                                    INNER JOIN Orion.NPM.CustomPollerAssignment a ON a.CustomPollerAssignmentID = c.CustomPollerAssignmentID

                                                                    WHERE AssignmentName LIKE '%upsAdvInputFrequency%'

                                                                  ) AS b ON b.NodeID = n.NodeID

                                                                  INNER JOIN

                                                                  (

                                                                    SELECT a.NodeID, c.Status

                                                                    FROM Orion.NPM.CustomPollerStatus c

                                                                    INNER JOIN Orion.NPM.CustomPollerAssignment a ON a.CustomPollerAssignmentID = c.CustomPollerAssignmentID

                                                                    WHERE AssignmentName LIKE '%upsAdvOutputCurrent%'

                                                                  ) AS c ON c.NodeID = n.NodeID

                                                                   

                                                                  This works in the Custom Query Property on a Summary page. Here's the way the output comes out.

                                                                  Custom Query Pic.JPG

                                                                   

                                                                  Here's how yours will look:

                                                                   

                                                                  SELECT n.Caption AS NodeName, a.Status AS Rack1Humidity, b.Status AS Rack8Humidity

                                                                  FROM Orion.Nodes AS n

                                                                  INNER JOIN

                                                                  (

                                                                    SELECT a.NodeID, c.Status

                                                                    FROM Orion.NPM.CustomPollerStatus c

                                                                    INNER JOIN Orion.NPM.CustomPollerAssignment a ON a.CustomPollerAssignmentID = c.CustomPollerAssignmentID

                                                                    WHERE AssignmentName LIKE '%Rack1Humidity%'

                                                                  ) AS a ON a.NodeID = n.NodeID

                                                                  INNER JOIN

                                                                  (

                                                                    SELECT a.NodeID, c.Status

                                                                    FROM Orion.NPM.CustomPollerStatus c

                                                                    INNER JOIN Orion.NPM.CustomPollerAssignment a ON a.CustomPollerAssignmentID = c.CustomPollerAssignmentID

                                                                    WHERE AssignmentName LIKE '%Rack8Humidity%'

                                                                  ) AS b ON b.NodeID = n.NodeID

                                                                   

                                                                  Give this a shot in your Custom Query Resource and let me know if it works. =)

                                                                    • Re: modify SWQL query to include node names
                                                                      d09h

                                                                      Thanks dhanson, will update when I can try the query.  Currently working something hotter.  I sure appreciate the efforts.  With each passing day, I retain a little more SWQL.

                                                                      • Re: modify SWQL query to include node names
                                                                        d09h

                                                                        Thanks dhanson, that seems to have done it.  I guess I'll have to diff the latest version with the previous ones for a lesson learned.  Looks like no PIVOT in this version--not that we started using PIVOT.  I believe the first few attempts were written as SQL and not SWQL, correct?

                                                                         

                                                                        I sure appreciate the assistance.

                                                                        • Re: modify SWQL query to include node names
                                                                          d09h

                                                                          Success!

                                                                           

                                                                          How/ where could one add date/time? My attempts so far are so laughably wrong that I hesitate to share them.  My script kiddie skills are not yet at the level I would like.  There is a DateTime field in the CustomPollerStatus table.  The information is right there, yet I still can't glue that timestamp to the query.

                                                                           

                                                                          ANSWER:  n.LastSync

                                                                          added to the SELECT

                                                                           

                                                                          Thanks for all the heavy lifting dhanson

                                                                           

                                                                          Note:  I seem to recall that SWQL would convert automatically to client's PC time.  Seems like a helpful feature except that our standard is UTC so my solution is now inconsistent.  Perhaps there is a way to convert local to GMT?  Or to keep SWQL from automatically converting?  I can confirm the conversion is taking place and I am seeing Central US time instead of the desired UTC.

                                                                            • Re: modify SWQL query to include node names
                                                                              dhanson

                                                                              Yeah, a couple of the queries I put up were SQL. That's my fault for not re-reading the original question.

                                                                               

                                                                              The final working one I've got below includes the [Last Polled] column that pulls the DateTime from the pollers. n.LastSync can be way off from the last polled time, but if you're lucky they're pretty close. I'd keep with pulling the DateTime from the CustomPollerStatus table.

                                                                               

                                                                              Converting to UTC is a little insane, but I'm thinking that you're saying that the text in the table is correct, but it's adjusting it when it gets displayed in the custom query, right? e.g. It's in UTC in the database, but Orion displays it in MST(or whatever).

                                                                              So my work around for this situation would be to convert the "DateTime" property from the table to a string...in other words, trick Orion into not recognizing this as an actual "DateTime" value and just a string of text. Now you'll get the results exactly as they appear in the database.

                                                                               

                                                                              As usual, edits are bold below.

                                                                               

                                                                              SELECT n.Caption AS NodeName, a.Status AS [Rack1Humidity], b.Status AS [Rack8Humidity], ToString(a.DateTime) AS [Last Polled]

                                                                              FROM Orion.Nodes AS n

                                                                              INNER JOIN

                                                                              (

                                                                                SELECT a.NodeID, c.Status, c.DateTime

                                                                                FROM Orion.NPM.CustomPollerStatus c

                                                                                INNER JOIN Orion.NPM.CustomPollerAssignment a ON a.CustomPollerAssignmentID = c.CustomPollerAssignmentID

                                                                                WHERE AssignmentName LIKE '%Rack1Humidity%'

                                                                              ) AS a ON a.NodeID = n.NodeID

                                                                              INNER JOIN

                                                                              (

                                                                                SELECT a.NodeID, c.Status, c.DateTime

                                                                                FROM Orion.NPM.CustomPollerStatus c

                                                                                INNER JOIN Orion.NPM.CustomPollerAssignment a ON a.CustomPollerAssignmentID = c.CustomPollerAssignmentID

                                                                                WHERE AssignmentName LIKE '%Rack8Humidity%'

                                                                              ) AS b ON b.NodeID = n.NodeID

                                                                               

                                                                              Let me know if this works for you!

                                                                                • Re: modify SWQL query to include node names
                                                                                  d09h

                                                                                  Awesome.  Sure enough, treating the time as a string fools the app into leaving GMT time as GMT and not localizing it.

                                                                                   

                                                                                  It's always been a pet peeve of mine that when polling has failed (even if for months/ years), you can still see stale data on Node Details, so when I have an option to show how current information is, I do so.  At least gives you a chance to detect a polling issue.

                                                                                  • Re: modify SWQL query to include node names
                                                                                    d09h

                                                                                    Don't you love scope creep?  I reinvented this monitoring via SAM due to the need to display color icons (text on a dashboard was missed).  Can a query similar to the last display only results above a certain temperature?  Above temperature X as yellow icon and above higher temperature Y as red icon?

                                                                                     

                                                                                    The SWQL dhanson provided works and gives timestamps.  Does the need to show down/ warning icons necessarily make SWQL non-viable here?

                                                                                      • Re: modify SWQL query to include node names
                                                                                        dhanson

                                                                                        So I don't think I'm good enough to color code this stuff, but I'll look into it.

                                                                                         

                                                                                        As far as limiting it to a specific temperature, you can add a HAVING statement to limit results to values that contain "x <operator> y" like x > y. for instance, if you want this to only display results with values in excess of 30, you can do this:

                                                                                         

                                                                                        SELECT n.Caption AS NodeName, a.Status AS [Rack1Humidity], b.Status AS [Rack8Humidity], ToString(a.DateTime) AS [Last Polled]

                                                                                        FROM Orion.Nodes AS n

                                                                                        INNER JOIN

                                                                                        (

                                                                                          SELECT a.NodeID, c.Status, c.DateTime

                                                                                          FROM Orion.NPM.CustomPollerStatus c

                                                                                          INNER JOIN Orion.NPM.CustomPollerAssignment a ON a.CustomPollerAssignmentID = c.CustomPollerAssignmentID

                                                                                          WHERE AssignmentName LIKE '%Rack1Humidity%'

                                                                                        ) AS a ON a.NodeID = n.NodeID

                                                                                        INNER JOIN

                                                                                        (

                                                                                          SELECT a.NodeID, c.Status, c.DateTime

                                                                                          FROM Orion.NPM.CustomPollerStatus c

                                                                                          INNER JOIN Orion.NPM.CustomPollerAssignment a ON a.CustomPollerAssignmentID = c.CustomPollerAssignmentID

                                                                                          WHERE AssignmentName LIKE '%Rack8Humidity%'

                                                                                        ) AS b ON b.NodeID = n.NodeID

                                                                                        GROUP BY n.Caption, a.Status, b.Status, c.Status, a.DateTime

                                                                                        HAVING a.Status > 30 OR b.Status > 30

                                                                                         

                                                                                        you can tweak the numbers here however you wish. =)

                                                                                        Note that there is now a GROUP BY clause in the statement. This is a requirement to perform the HAVING statement. You can reorder this however you wish. You can also throw in an ORDER BY clause if you want, and make it bring highest values to the top.

                                                                                        For instance you can add:

                                                                                         

                                                                                        ORDER BY a.Status DESC

                                                                                         

                                                                                        To place the top results of Rack1Humidity at the top. If you want to order by 2 factors, just add ", <column name> [ ASC | DESC ]".

                                                                                         

                                                                                        HTH!

                                                                                          • Re: modify SWQL query to include node names
                                                                                            d09h

                                                                                            dhanson, I sure appreciate the help.  As it turns out, visibility (in its most literal sense) is the key for my customer, so the best I've been able to do so far is below.  It gives status icons, which matter more than anything else for my audience.  Can't recall where I lifted this code from...my only modification is the WHERE clause and the ordering, as I am incapable of SWQL of this quality (at least in the two dozen years before I retire):

                                                                                             

                                                                                            SELECT

                                                                                            '' AS n,

                                                                                            n.Caption AS NODE,

                                                                                            '/Orion/images/StatusIcons/small-' + ToString(n.StatusIcon) AS [_IconFor_n], '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.NodeID) AS [_LinkFor_NODE], '' AS a, a.Name AS APP, '/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_a], '/Orion/APM/ApplicationDetails.aspx?NetObject=AA%3a' + ToString(a.ApplicationID) AS [_LinkFor_APP], '' AS c, c.ComponentName AS CMPNT, '/Orion/images/StatusIcons/Small-' + c.StatusDescription + '.gif' AS [_IconFor_c], '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_CMPNT], cs.ComponentStatisticData AS STAT, '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_STAT], cs.ErrorMessage AS MSG, '/Orion/APM/MonitorDetails.aspx?NetObject=AM%3a' + ToString(c.ComponentID) AS [_LinkFor_MSG]

                                                                                            FROM Orion.APM.Component c

                                                                                            JOIN ORION.APM.CurrentStatistics(nolock=true) cs ON c.ComponentID = cs.ComponentID

                                                                                            JOIN Orion.APM.Application(nolock=true) a ON c.ApplicationID = a.ApplicationID JOIN Orion.Nodes(nolock=true) n ON a.NodeID = n.NodeID WHERE a.StatusDescription NOT IN ('Unmanaged') AND c.StatusDescription NOT IN ('Up','Disabled') and ((c.ComponentName LIKE 'something') OR (c.ComponentName LIKE 'something else') OR (c.ComponentName LIKE 'whatever') ORDER BY STAT desc