Version 2

    Creating dependencies using groups is a long and tedious process.  The problem with groups is that it already uses dynamic queries to define the members using CP.  So why even use the groups - just use CPs.  Group structures are not changeable, other than deleting and creating it.  Plus some other issues with groups, such as groups not being updated properly because of subscription errors.  And limitation on number of groups.  If someone has 2000 sites, then creating those 2000x2 groups (parent, and child) is not practical.

     

    It's a lot easier to use properly structured custom properties.  This is the process I came up with, along with the SQL code (I'm sure SQL gurus out there can optimize this).

    1) Create CP called SiteID.  Tag it with unique Site identifier (001, 002, etc).

    2) Create CP called Layer.  Tag it with WAN for all WAN routers (for other devices you can use LAN, Firewall, Switch, etc)

         a) The alert will look for the CP "WAN" withina SiteID and get the satus of the WAN node.  This will serve to define a dependency.

    3) The key to the code is the row_number (very similar to the rank) which returns the row of the returned dataset. The full custom SQL alert is:

    Select Nodes.caption, nodes.nodeid From nodes   (Base code that SolarWinds Alert gives you)

    Where  (nodes.status=2) and

    (

       (nodes.layer not like 'WAN') and

       (

        ((select status from (select row_number()  over (order by nodeid) as rownumber, status from nodes nn where (nodes.siteid=nn.siteid) and nn.layer = 'WAN') as foo where foo.rownumber=1) is null) or

         ((select status from (select row_number()  over (order by nodeid) as rownumber, status from nodes nn where (nodes.siteid=nn.siteid) and nn.layer = 'WAN') as foo where foo.rownumber=1) =1 )

        )

      or

      (

      ((select status from (select row_number()  over (order by nodeid) as rownumber, status from nodes nn where (nodes.siteid=nn.siteid) and nn.layer = 'WAN') as foo where foo.rownumber=2) is null) or

         ((select status from (select row_number()  over (order by nodeid) as rownumber, status from nodes nn where (nodes.siteid=nn.siteid) and nn.layer = 'WAN') as foo where foo.rownumber=2) =1 )

      )

       or

       (nodes.layer like 'WAN' or nodes.layer is null)

    )

    4) For testing.  I created 4 nodes.  Node1=8.8.8.8(WAN)  Node2=8.8.8.1(WAN')  Node3=8.8.8.2(non-WAN1)  Node4=8.8.8.3 (Non-WAN2).  Then change the state of the nodes (8.8.8.8 is always up, anything else will be down).

     

     

    The script can be expanded using OR to check for 3 more more WAN routers.

     

    Thanks

    Amit