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.

Help with locate missing nodes that end with WAN Edge

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 ..?

  • Hi SJA 

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

    Regards

  • 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.    

  • 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 

  • 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