cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 16

Help with locate missing nodes that end with WAN Edge

Jump to solution

Hi

 

I will like to locate nodes that we don't monitor.

Every site "should" have 3 router IP monitor

XyZ-R1

XyZ-R2

XyZ WAN Edge 

I looking for  sites that missing "XyZ WAN Edge " that are Virtual IP that indicate that we can not reach the site any more.

I know its probably SQL advance one ..?

 

 

 

0 Kudos
1 Solution

Yes, a bit easier as a SWQL query though. Change the custom property name/value as needed.

SELECT Subcaption, COUNT(Subcaption) AS NodeCount
FROM 
	(SELECT SUBSTRING(Caption, 1, 3) AS Subcaption FROM Orion.Nodes n
	WHERE n.CustomProperties.DeviceType = 'Router'
	) t1
GROUP BY Subcaption
--HAVING COUNT(Subcaption) < 3

 

Or if you want to stick with SQL:

SELECT Subcaption, COUNT(Subcaption) AS NodeCount
FROM 
	(SELECT SUBSTRING(Caption, 1, 3) AS Subcaption FROM [dbo].[NodesData] n
	LEFT JOIN NodesCustomProperties ncp ON n.NodeID = ncp.NodeID
	WHERE ncp.DeviceType = 'Router'
	) t1
GROUP BY Subcaption
--HAVING COUNT(Subcaption) < 3

 

View solution in original post

5 Replies
MVP
MVP

Assuming all your devices have a caption prefix of the 3 letter XYZ code, you could try somethiing like the below. I have no idea if it's optimal but it worked in my lab.

A SELECT to generate a list of the first 3 characters of all the nodes, then the outer SELECT generates a list with a count and only shows the subcaptions that have less than 3 nodes.

SELECT Subcaption, COUNT(Subcaption) AS NodeCount
FROM (SELECT SUBSTRING(Caption, 1, 3) AS Subcaption FROM [dbo].[NodesData] ) t1
GROUP BY Subcaption
HAVING COUNT(Subcaption) < 3

 

Quick lab screenshot - 6 nodes starting with 192 and everything else has unique first 3 characters. I commented out the HAVING line so you could see the 6x count.

shuth_0-1600248234818.png

 

Can I fine tune that

with CP ? 

CP=Router 

 

0 Kudos

Yes, a bit easier as a SWQL query though. Change the custom property name/value as needed.

SELECT Subcaption, COUNT(Subcaption) AS NodeCount
FROM 
	(SELECT SUBSTRING(Caption, 1, 3) AS Subcaption FROM Orion.Nodes n
	WHERE n.CustomProperties.DeviceType = 'Router'
	) t1
GROUP BY Subcaption
--HAVING COUNT(Subcaption) < 3

 

Or if you want to stick with SQL:

SELECT Subcaption, COUNT(Subcaption) AS NodeCount
FROM 
	(SELECT SUBSTRING(Caption, 1, 3) AS Subcaption FROM [dbo].[NodesData] n
	LEFT JOIN NodesCustomProperties ncp ON n.NodeID = ncp.NodeID
	WHERE ncp.DeviceType = 'Router'
	) t1
GROUP BY Subcaption
--HAVING COUNT(Subcaption) < 3

 

View solution in original post

Level 10

Hi SJA 

When you say we don't monitor! Do you mean that it is not on your network 

Regards

 

0 Kudos

I need to monitor.

The logic is every site has 2 router +1 virtual IP that is share between.

That mean 3  node/IP per site.

I like to locate site that miss that Virtual IP.    

 

 

0 Kudos