This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom query help

miked_nme over 6 years ago

Greetings Everyone,

Need help constructing a complex SWQL query to build a historical BW utilization table going back 180 days IN/OUT and broken up by month. Goal is to have it be 1 line, multiple columns per node/interface within a custom table as part of a larger situational awareness dashboard for our director. We would also like the values to conditionally format based on what percentage is returned for that given month. An example being Interface X off of Node Y was at 90% utilization per the 95th percentile IN for the month of March. We want that 90% value to be Red. If it dropped to 36% in May we want that value to be Green. The conditional format color just going off the already out of the box Solarwinds default thresholds.

I've already tried the syntax suggested in this thread: https://thwack.solarwinds.com/thread/113097 But I just get "Query is not valid" when attampting to use

DateTrunc(‘day’, T.ObservationTimestamp) AS [Day],

AVG(T.Traffic.InAveragebps) AS InAvgBps, AVG(T.Traffic.OutAveragebps) AS OutAvgBps,

MIN(T.Traffic.InMinbps) AS InMinBps, AVG(T.Traffic.OutMinbps) AS OutMinBps

MAX(T.Traffic.InMaxbps) AS InMaxBps, AVG(T.Traffic.OutMinbps) AS OutMaxBps

Or the subquery further down the thread.

We are on the latest version of NPM here.

This is what I have so far trying different things to also add in some fields to another custom table we're working on for the same dashboard. We have tabs capturing assets from different regions which is why we are using the CarrierName to filter out the other nodes/interfaces.

SELECT N.Caption AS [Node Name],

C.CarrierName AS PACE,

I.Name,

T.ObservationTimestamp,

T.InTotalPkts AS [Total Pkts IN],

T.OutTotalPkts AS [Total Pkts OUT],

'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a'+ToString(N.NodeID) AS [_LinkFor_Node Name],

'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=I:'+ToString(I.InterfaceID)+'&view=InterfaceDetails' AS [_LinkFor_Name],

I.DetailsUrl,

N.DetailsUrl

FROM Orion.Nodes AS N

INNER JOIN Orion.NPM.Interfaces AS I on N.NodeID=I.NodeID

INNER JOIN Orion.NPM.InterfaceTraffic AS T on N.NodeID=T.NodeID

INNER JOIN Orion.NPM.InterfacesCustomProperties AS C on I.InterfaceID=C.InterfaceID

WHERE C.CarrierName Like 'Primary_b%'

OR C.CarrierName Like 'Alternate_b%'

ORDER BY N.Caption Asc, C.CarrierName Desc

0 tdanner over 6 years ago

"T.Traffic.InAveragebps" - in your query, you have used an explicit JOIN to bring in Orion.NPM.InterfaceTraffic with the alias "T". So you don't need to say "T.Traffic.InAveragebps". You can just say "T.InAveragebps". Or "I.Traffic.InAveragebps".
Also - you are joining in InterfaceTraffic only on the NodeID. You should join on the InterfaceID instead.
I recommend you work out this query in SWQL Studio before transferring it to the website. You will get autocomplete for the property names and better error messages than "Query is not valid". You can download it from https://github.com/solarwinds/OrionSDK/releases/latest
Cancel
Vote Up 0 Vote Down

Cancel
0 miked_nme over 6 years ago in reply to tdanner

Thank you for the Tip, I took out the .Traffic portion of the query. Also realized shortly after trying the modified join that trying to join Orion.Nodes to NPM.InterfaceTraffic wasn't going to work on InterfaceID. There's no InterfaceID column in Orion.Nodes, at least not listed here: SolarWinds Information Service v3.0 Schema Documentation
So I changed that Join statement to be between NPM.Interfaces and NPM.InterfaceTraffic which worked. Was able to obtain SWQL Studio by way of a ticket as we cannot reach github from here due to certificate issues with the site. Just working through some local hbss issues now to get it working fully and then will test queries in there.
Any suggestions on a specific syntax that will accomplish what we are trying to do as described above?
Cancel
Vote Up 0 Vote Down

Cancel
0 tdanner over 6 years ago in reply to miked_nme

Here's a thread that describes how to do conditional formatting of data using the Custom Table resource. Note that the Custom Table resource is different than the Custom Query resource and does not recognize the _IconFor_XX and _LinkFor_XX conventions - it has its own way to handle links.
Text colour to display alerts using custom query
Cancel
Vote Up 0 Vote Down

Cancel