5 Replies Latest reply on Jun 13, 2018 3:21 PM by jeilers

    SWQL Query Help needed

    212

      This is what I'm trying to accomplish:

      Create a custom query resource where I have a list of nodes that display the current value from a Custom Poller (Sounds easy to me, but I'm NOT a SQL guy)

       

      After much trial and error, I'm off and on getting closer, but it's still no good. Here is what I currently have:

       

      SELECT n.displayname, p.CurrentValue

      FROM Orion.NPM.CustomPollerAssignmentOnNode AS p, Orion.Nodes AS n, Orion.NodesCustomProperties AS c

      WHERE c.UPS_TEMP_PROBE = 'TRUE' AND p.CustomPollerName='RoomTempCelsius' AND n.displayname LIKE '%UPS%';

       

       

      Instead of a list of about 50 (The total of Nodes where c.UPS_TEMP_PROBE = 'TRUE' is a true statement), I get 270k results with each node having hundreds of different results. Before I added AND n.displayname LIKE '%UPS% I was getting 6+ million results.

       

       

      I have had no success trying to create a useful JOIN statement (I spent over a full day trying that). I thought I could somehow find the name of the node in a table and join that data to requested data from the other tables I would be good to go, but with each table having it's own 'displayname' column with different data that was unusable, and for some reason I was getting multiple results for each node when I tried to sort by NodeID, and It seems DISTINCT is not supported.

       

       

      Anyone help a newbie out?

        • Re: SWQL Query Help needed
          deverts

          212,

           

          Good luck figuring the SWQL out, I'm learning as well, and let us know the result (looks like something I could use). That said, this post might have some code you can use. Particularly the piece around "TOP 1". It sounds like your code is displaying all the collected values in the table. Reality is, you just want the most current value.

           

          https://thwack.solarwinds.com/docs/DOC-202202

           

          Additionally, have you upgraded to NPM 12.3? You may not need to write this SWQL at all.

           

          Orion Platform 2018.2 Improvements - Chapter One

           

          Hope these options help.

          D

          • Re: SWQL Query Help needed
            212

            So, I'm trying to create another similar query, and I think I have a more clear understanding of the problem, but still no answer (Thanks for the link deverts ) Unfortunately upgrading is out of my control.

             

            My other query is very similar:

            SELECT n.displayname, p.CustomPollerName, p.CurrentValue

            FROM Orion.NPM.CustomPollerAssignmentOnNode AS p, Orion.Nodes AS n

            WHERE p.CustomPollerName='NumberOfBadBattPacks' AND 'NumberOfBadBattPacks' IS NOT NULL AND n.displayname LIKE '%UPS%'

            ORDER BY CurrentValue DESC;

             

            So - with this query I get a return of every "UPS" node with each valid value in the table. Basically I get all 120+ UPSs all saying they have 8 bad batteries, and they all have 4 bad batteries and 2 bad batteries etc.

             

            UPS0001 NumberOfBadBattPacks 8

            UPS0002 NumberOfBadBattPacks 8

            UPS0003 NumberOfBadBattPacks 8

            ...

            UPS0123 NumberOfBadBattPacks 8

            UPS0001 NumberOfBadBattPacks 4

            UPS0002 NumberOfBadBattPacks 4

            UPS0003 NumberOfBadBattPacks 4

            ...

            UPS0123 NumberOfBadBattPacks 4

            UPS0001 NumberOfBadBattPacks 2

            UPS0002 NumberOfBadBattPacks 2

             

             

            What am I missing that I'm not getting the actual value that should be associated with each particular node?

            As I said before I had no success trying to create a JOIN at all.

            • Re: SWQL Query Help needed
              jeilers

              So I'm looking at the query here:

              SELECT n.displayname, p.CurrentValue

              FROM Orion.NPM.CustomPollerAssignmentOnNode AS p, Orion.Nodes AS n, Orion.NodesCustomProperties AS c

              WHERE c.UPS_TEMP_PROBE = 'TRUE' AND p.CustomPollerName='RoomTempCelsius' AND n.displayname LIKE '%UPS%';

              I'm not seeing a join there. So I generated a query from each table by right clicking and clicking generate a select statement and it gave me:


              SELECT TOP 1000 NodeID, CustomPollerAssignmentID, Description, DetailsUrl, AssignmentName, CustomPollerID, InterfaceID, UnManaged, UnManageFrom, UnManageUntil, CustomPollerDescription, CustomPollerOid, CurrentValue, ID, CustomPollerMIB, CustomPollerName, StatusDescription, Status, StatusLED, Image, AncestorDisplayNames, AncestorDetailsUrls, StatusIconHint, DisplayName, InstanceType, Uri, InstanceSiteId

              FROM Orion.NPM.CustomPollerAssignmentOnNode

               

              SELECT TOP 1000 NodeID, ObjectSubType, IPAddress, IPAddressType, DynamicIP, Caption, NodeDescription, Description, DNS, SysName, Vendor, SysObjectID, Location, Contact, VendorIcon, Icon, Status, StatusLED, StatusDescription, CustomStatus, IOSImage, IOSVersion, GroupStatus, StatusIcon, LastBoot, SystemUpTime, ResponseTime, PercentLoss, AvgResponseTime, MinResponseTime, MaxResponseTime, CPULoad, MemoryUsed, MemoryAvailable, PercentMemoryUsed, PercentMemoryAvailable, LastSync, LastSystemUpTimePollUtc, MachineType, IsServer, Severity, UiSeverity, ChildStatus, Allow64BitCounters, AgentPort, TotalMemory, CMTS, CustomPollerLastStatisticsPoll, CustomPollerLastStatisticsPollSuccess, SNMPVersion, PollInterval, EngineID, RediscoveryInterval, NextPoll, NextRediscovery, StatCollection, External, Community, RWCommunity, IP, IP_Address, IPAddressGUID, NodeName, BlockUntil, BufferNoMemThisHour, BufferNoMemToday, BufferSmMissThisHour, BufferSmMissToday, BufferMdMissThisHour, BufferMdMissToday, BufferBgMissThisHour, BufferBgMissToday, BufferLgMissThisHour, BufferLgMissToday, BufferHgMissThisHour, BufferHgMissToday, OrionIdPrefix, OrionIdColumn, SkippedPollingCycles, MinutesSinceLastSync, EntityType, DetailsUrl, DisplayName, Category, IsOrionServer

              FROM Orion.Nodes

               

              SELECT TOP 1000 NodeID, AlarmLevel, Alerts, AssetTag, C1_Device_Type, C2_OS, CableCompany, City, Comments, ConnectionType, Country, Create_Ticket, Create_TReason, CustMontiorUntil, Customer, CustomerAlerts, Department, Device_Type, Disposition, InServiceDate, LAT, Latitude, LNG, Longitude, Mapname, n_CPUCount, n_mute_reason, NCM_Reason, Node_Type, OS, Polyline, PONumber, PostalCode, Priority, PurchaseDate, PurchasePrice, State, Stedsnavn, StreetAddress, Surveillance_Accounts, Surveillance_Alerts, Switch_StackGrp

              FROM Orion.NodesCustomProperties

              All three of these have NodeID so I can use that to thread these together.

              SELECT n.displayname, p.CurrentValue

              FROM Orion.NPM.CustomPollerAssignmentOnNode as p

              inner join Orion.Nodes as n ON p.nodeid = n.NodeID

              Inner join Orion.NodesCustomProperties as c on p.nodeid = c.nodeid

              WHERE c.UPS_TEMP_PROBE = 'TRUE' AND p.CustomPollerName='RoomTempCelsius' AND n.displayname LIKE '%UPS%';



              EDIT:
              Felt like the message was a bit short. Not 100% certain that will work for you but I tried it with a similar WHERE statement for my environment and it only listed the pollers that I have. I think you original query may be grabbing everything from each table, but I'm not sure. Honestly I'm surprised your environment stood up. Mine blows up(Solarwinds Information Service V3) if I let a swql query run wild and tries to run a query with that many results although I too have an older version. Looks like the issue I experience is addressed in the patch notes.... anywho let me know if it works out for ya. Also this image helps with joins a bit, though it is sql they are very similar for swql.






              1 of 1 people found this helpful