Down Interface Resource SQL Filter Help

Good Morning NPM Community

We have a 'Main Dashboard' view for our network and one of the resources we use is the Down Interface Resource. We have a few projects in some remote locations taking place and while work is being done occasionally a port on a switch will go up and down. I don't want to stop all monitoring or alerting for these nodes or the interfaces but I do want to stop the interfaces from displaying in the down interface resource while the work is taking place. If I am working with one technician in one location on one switch I can use the following SQL filter to stop the nodes from displaying in the 'Down interfaces Resource'

Community='{Community-String}' AND interfaces.NodeID <> 11111

The above SQL works well as long as I am just woking with one node.

However if I start working with a second or third engineer and they are working on 2 different nodes and I edit the filter above to look like this

Community='{Community-String}' AND interfaces.NodeID <> 11111 OR interfaces.NodeID <> 22222 OR interfaces.NodeID <> 33333

I basically stop the 'Down Interface Resource' view from even working correctly. I will try to write out what I am trying to accomplish with the SQL statement above:...

Show all down interfaces on nodes with a Community='{Community-String} And the node ID is not equal to 11111 OR node ID is not equal to 22222 OR Node ID is not equal to 33333

Is this not possible or do I have the SQL statement setup incorrectly?

Find more posts tagged with

down interfaces sql

sql filter

sql

interface down resource

Accepted answers

mesverrum

It's just a problem with your use of and/or

By OR'ing multiple <> nodeid then you effectively make it so all devices match the condition

all nodes would match the logic of (not node A OR not node

you need AND's when you are trying to use negative matches

Community='{Community-String}' AND interfaces.NodeID <> 11111 AND interfaces.NodeID <> 22222 AND interfaces.NodeID <> 33333

All comments

natetech

How about Community='{Community-String}' AND (interfaces.NodeID <> 11111 OR interfaces.NodeID <> 22222 OR interfaces.NodeID <> 33333)

or Community='{Community-String}' AND (interfaces.NodeID != 11111 OR interfaces.NodeID != 22222 OR interfaces.NodeID != 33333)

k1gaudineer

Thank you for the suggestion. I have tried the query both ways (using the <> and then using the !=) unfortunately the result is not correct. The down interface resource still displays all interfaces that are down for the 3 nodes. I am beginning to wonder if I have the correct understanding of the Down Interface resource...

For example: If I have the following SQL statement entered Community={'Community-String'} I would expect to see all interfaces that are down on nodes that match {'Community-String'} That seems to work. I have about 28 nodes that have that R/O Community string configured and 3 of those nodes have some interfaces that are down because of project work and those interfaces are showing in the down interfaces resource. We know there is a project in progress so I would like to remove those interfaces for those nodes from this view.

If I use the SQL statement Community={'Community-String'} AND interfaces.NodeID <> 11111 I can successfully remove the interfaces for one nodes from appearing in the Down Interface resource. But when I try to add an OR for the second or 3rd node I seem to break the logic and all interfaces on nodes that match {'Community-String'} are once again displayed. Do I need to use {'Community-String'} in every OR Statement?

Like this possibly?? (Community={'Community-String'} AND interfaces.NodeID <> 11111) OR (Community={'Community-String'} AND interfaces.NodeID <> 22222) OR (Community={'Community-String'} AND interfaces.NodeID <> 33333)

mesverrum

It's just a problem with your use of and/or

By OR'ing multiple <> nodeid then you effectively make it so all devices match the condition

all nodes would match the logic of (not node A OR not node

you need AND's when you are trying to use negative matches

Community='{Community-String}' AND interfaces.NodeID <> 11111 AND interfaces.NodeID <> 22222 AND interfaces.NodeID <> 33333

mesverrum

I also just want to point out this kind of scheme only makes sense for small environments where a person can babysit little one-off changes like this. For something more scalable you would probably just want to implement a workflow such as having the engineers who are working on a node mute it for the duration of the expected maintenance window, then use a custom query to replace the down interface resource with one that only displays down interfaces on nodes that are not muted.

This is the way I would start something like that,

select i.FullName as Interface, i.DetailsUrl as [_linkfor_Interface], '/Orion/images/StatusIcons/Small-' + StatusIcon as [_iconfor_Interface]

from orion.npm.Interfaces i

left join orion.AlertSuppression sup on (sup.entityuri = i.node.Uri and sup.SuppressFrom <= GETUTCDATE() and sup.SuppressUntil > GETUTCDATE()) or (sup.entityuri = i.Uri and sup.SuppressFrom <= GETUTCDATE() and sup.SuppressUntil > GETUTCDATE())

where i.status=2

and sup.EntityUri is null

The end result is that now nobody has to edit the sql filter on the resource every time they want to work on a node, and nobody will forget to edit it back when they are done. There are a lot of ways you could tweak that idea but its a starting point.