SWQL Query Help needed

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

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.

Can you try this:

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

I have a similar query working with this.

D

If I add DISTINCT into my queries they fail.  -  Error: A query to the SolarWinds Information Service failed.

Same error as any typo or unsupported language I guess.

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.