Query to do a KPI Count for two stats

So we've been using modern dashboards and doing some fun stuff that helps give an instant 'feel' for a clients estate. Once such looks at interfaces and looks like this:

I picked up on the fact that the KPI query works just fine for one aspect of the DOWN, but it misses out interfaces that are normally monitored but the entire node is also down. In the widget where we list the actual node and interface names, my query works to include both interfaces that are down on a node that is Up as well as interfaces that are down on a node that is down.

The KPI query is:

SELECT COUNT(1) AS KPI_Numbe 
from Orion.NPM.Interfaces AS a
-- Now we need to JOIN in CPs to allow querying per client
JOIN Orion.NodesCustomProperties as b ON a.NodeID = b.NodeID
WHERE a.Status = '2' 
-- Make sure to change ZZZ_Acme to relevant client prefix
AND ClientPrefix LIKE '%ZZZ_Acme%'

But I can't figure out how to include both in the KPI Count.

I'm almost certainly overlooking the obvious but if someone can assist then that would be fab?

Find more posts tagged with

Accepted answers

christopher.t.jones123

@stuartd to combine both into one where statement you have to do a grouping. I recommend taking the following approach

1. I want node up and interface status is down for specific client

WHERE a.status = '2' and a.Node.Status = '1' AND a.node.CustomProperties.ClientPrefix like '%Zzz_Acme%'

2. I want node down and interface status is down for specific client

WHERE a.status = '2' and a.Node.Status = '2' AND a.node.CustomProperties.ClientPrefix like '%Zzz_Acme%'

combine the two using a grouping

WHERE (a.status = '2' and a.Node.Status = '1' AND a.node.CustomProperties.ClientPrefix like '%Zzz_Acme%') OR (a.status = '2' and a.Node.Status = '2' AND a.node.CustomProperties.ClientPrefix like '%Zzz_Acme%')

If you want to expand to include unreachable interfaces you would just modify the above second group of conditions to include those interfaces that are 'unreachable'.

This would technically fulfill the request but I'm going to throw out a disclaimer that this may not be what the intended outcome is. I say this because of how SolarWinds treats child objects when the parent goes down. For an interface to retain a down status when its parent is down would be considered abnormal behavior in my experience. While I've provided you the technical ability to fulfill the request you might be better off understanding the intent and going from there, I mean lets be honest why would I care about a down interface on a device when the entire device is reporting as down, clearly I have bigger fish to fry at that particular moment. Additionally, if a device is reporting as down why would I trust any of the statuses for its child objects SolarWinds cant communicate to it so SolarWinds doesn't know what the actual current status is.

All comments

christopher.t.jones123

@stuartd.you need to include the status unreachable in your where statement. I don't remember the exact integer value off the top of my head but you could do something like below

WHERE a.status = '2' or a.statusdescription like '%Unreachable%'

stuartd

Thanks for the response @christopher.t.jones123

Unreachable is 12 for the record

I just realised I've messed up my widget that actually lists out the interfaces that are down and the way I fixed that is to create two widgets side by side. One shows down ints on UP nodes, the other down ints on DOWN nodes.

However that doesn't help my KPI widget, as I need to combine both to create one figure. If I use your code 'as is' I now get a figure that is higher, and that is too high.

I guess my expectations of my coding skills are I think they're better than they actually are!!!! Anyway, bottom line is:

I need to pull back all interfaces that are down as a number
AND add in all interfaces that are down on nodes already marked as down
AND that belong to a specific client.

Clearly my efforts of: a.status = '2' AND a.status = '12' AND clientprefix LIKE XYZ doesn't work and I have no idea how to fix that.

Any more thoughts please?

christopher.t.jones123

@stuartd the reason a.status = '2' AND a.status ='12' is not working is because you are creating a condition which is largely not going to exist. You are asking an interface to have two statuses at once. Can you send a screenshot of the condition where a node is down and it is showing that an interface is down? I ask this cause typically if the parent node goes down the system 'should' put all child objects such as interfaces into an unreachable state. In theory if its not doing that then the a.status = '2' would bring those in as part of your down count already.

stuartd

Strictly speaking I** want it to be an either / or on the status, or indeed it could be both.

** I say I, but it's actually a request of the team that directly supports this client - if it can't be done, because as you say the interface is marked as unreachable, then that works for me.

adam.beedell

Agreed with @christopher.t.jones123, but also you don't need to JOIN, just use a.customproperties.clientprefix if you're in SWQL. If you're in SQL, you may as well switch to SWQL as it's not doing anything you'd need SQL functions for

WHERE a.status in ('2','12') probably also works

stuartd

Thanks @adam.beedell this is an old query (in SWQL) written before I knew how to utilise implicit joins - but they are also something I'm very much still getting used to. And where something works I'm leaving well enough alone for now.

Anyway, I've gone with telling them it isn't possible. Thinking about it [and I'm not 100% right now because of a stupid head cold so a tad slow], why would you want to track down interfaces on a node that is down....

I'll give your WHERE statement a whirl as well to see what happens.

Diolch.