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.

Meraki SDWAN Link Status Monitoring

Just recently, a client of mine sent me a challenge to improve their monitoring for their new Meraki SDWAN deployment around Australia.

They had 152 sites, and each site had a private link, an Internet link, and backup 4G link, and a VPN tunnel which ran over the Internet link, and failover to the 4G link if there were issues.

Currently they only had a view to determine if a site was experiencing an issue which would be easily visible to users, i.e. The Internet and private links both failed, causing a failover to 4G.

They wanted a more granular view than that, a view which would show them all of their sites in one window, with an indicator image for MPLS, Internet, 4G, and VPN, so they could see whenever they had any issues on any of their links.

After trawling through the Thwack forums and Google searches, I realized quite quickly that I was going to have to design a solution myself.

We decided to simply utilize NPM to monitor each of the links, with the client being able to get static addresses for each of the connections (including the 4G link!).

So that allowed us to get all the data we needed into Orion so we knew what was going on around their network. Now I just needed to work out how to display that the way they wanted.

I'll add in at this point that I'm not a developer or DBA or anything in the application space. My focus is Network Engineering, so what I put together, detailed below, is based off a lot of googling and trial and error.

I first off needed a way to easily identify what node belonged to which site, and which link it was (i.e. MPLS, Internet, 4G, or VPN).

I went over to my pet favorite for this and created 3 new Custom Properties, ClientEnvironment, ClientSiteID & ClientLinkID.

This allowed me to record the extra data I needed for each of the nodes so I could identify them in queries.

Now I needed to create the view.

I found a limitation with the built in resources for views pretty quickly. I could get the status data for each node pretty easily, and sorting them by the ClientSiteID and ClientLinkID fields allowed me to group them all together, but this wasn't what my client wanted.

This resulted in 4 rows per site, one for each link. My client wanted a single row for each site with 5 columns, one for the site name, and one for each of the link types.

This seems like a silly point to push, only wanting one row per site instead of 4, but when you think about it, they have 152 sites with 4 links each. That's 608 links to monitor!

Looking at 152 rows instead 608 rows to see the status of all your sites does sound a lot more practical.

Researching methods to manipulate the returned data with SWQL found me a bit stuck as I couldn't work out a way to make a returned value into a column.

I'd just like to clarify here that this doesn't mean there isn't a way to do this, I just couldn't find one. If anybody does know how to do this, please reply with some details as I'd love to know!

I moved outside of SolarWinds and opened up Microsoft SQL Server Management Studio on our SQL server to try and find a way to achieve this with SQL, and with a bit of persistence and way too many Chrome tabs opened, I came up with this.

SELECT ROW_NUMBER() OVER (ORDER BY ClientSiteID) AS RowNum, * FROM (
   SELECT C.ClientSiteID, C.ClientLinkID, '<img src="/Orion/images/StatusIcons/small-' + N.StatusLED + '">' AS Status
   FROM NodesCustomProperties AS C
   LEFT JOIN NodesData AS N ON C.NodeID=N.NodeID
   WHERE C.ClientEnvironment='Network - Meraki WAN'
) AS SDWANSourceData
PIVOT(
   MAX(Status)
   FOR ClientLinkID IN([MPLS], [Internet], [4G], [VPN])
) AS LinkStatus

That's what I was after. A simple view that would give me a row per site, and the status of each link.

Because I had 152 sites to list, I needed to enhance it a little bit to include a row number for each site. This way I could have multiple columns of results, and limit which row numbers were in each.

I wanted to add in a hyperlink to each of the nodes on to its respective status indicators, and that idea sounded easy enough now I was at this point.

I've never regretted my words so quickly in my life!.

I found the best solution to this was to write another version of the above query, but for this one to return the NodeID instead of StatusLED column.

Then I could join those two datasources together, so I now had 9 columns per row, the Site Name, Status Icon for each link, and URL to the Node for each link.

SELECT * FROM (

   SELECT ROW_NUMBER() OVER (ORDER BY Site1) AS RowNum, * FROM (

      SELECT C.ClientSiteID AS Site1, C.ClientLinkID, '<img src="/Orion/images/StatusIcons/small-' + N.StatusLED + '">' AS Status

      FROM NodesCustomProperties AS C

      LEFT JOIN NodesData AS N ON C.NodeID=N.NodeID

      WHERE C.ClientEnvironment='Network - Meraki WAN'

   ) AS SDWANSourceData

   PIVOT(

      MAX(Status)

      FOR ClientLinkID IN([MPLS], [Internet], [4G], [VPN])

   ) AS LinkStatus

AS Status

LEFT JOIN (

   SELECT

      Site2,

      '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [MPLS]) AS [MPLSLink],

      '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [Internet]) AS [InternetLink],

      '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [4G]) AS [4GLink],

      '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [VPN]) AS [VPNLink]

   FROM (

      SELECT C.ClientSiteID AS Site2, N.NodeID AS NodeID, C.ClientLinkID

      FROM NodesCustomProperties AS C

      LEFT JOIN NodesData AS N ON C.NodeID=N.NodeID

      WHERE C.ClientEnvironment='Network - Meraki WAN'

   ) AS SDWANSourceData

   PIVOT(

      MAX(NodeID)

      FOR ClientLinkID IN([MPLS], [Internet], [4G], [VPN])

   ) AS LinkURL

) AS Link ON Status.Site1 = Link.Site2

Looking good now, but I wanted to merge the Node URL and Node Status values together.

Luckily, this query was primed for that, and all I had to do is add in a few lines into the very top SELECT statement to merge them together in HTML.

SELECT

   RowNum,

   Site1 AS Site,

   '<a href="' + [MPLSLink] + '">' + [MPLS] + '</a>' AS [MPLS],

   '<a href="' + [InternetLink] + '">' + [Internet] + '</a>' AS [Internet],

   '<a href="' + [4GLink] + '">' + [4G] + '</a>' AS [4G],

   '<a href="' + [VPNLink] + '">' + [VPN] + '</a>' AS [VPN]

FROM (

   SELECT ROW_NUMBER() OVER (ORDER BY Site1) AS RowNum, * FROM (

      SELECT C.ClientSiteID AS Site1, C.ClientLinkID, '<img src="/Orion/images/StatusIcons/small-' + N.StatusLED + '">' AS Status

      FROM NodesCustomProperties AS C

      LEFT JOIN NodesData AS N ON C.NodeID=N.NodeID

      WHERE C.ClientEnvironment='Network - Meraki WAN'

   ) AS SDWANSourceData

   PIVOT(

      MAX(Status)

      FOR ClientLinkID IN([MPLS], [Internet], [4G], [VPN])

   ) AS LinkStatus

AS Status

LEFT JOIN (

   SELECT

      Site2,

      '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [MPLS]) AS [MPLSLink],

      '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [Internet]) AS [InternetLink],

      '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [4G]) AS [4GLink],

      '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [VPN]) AS [VPNLink]

   FROM (

      SELECT C.ClientSiteID AS Site2, N.NodeID AS NodeID, C.ClientLinkID

      FROM NodesCustomProperties AS C

      LEFT JOIN NodesData AS N ON C.NodeID=N.NodeID

      WHERE C.ClientEnvironment='Network - Meraki WAN'

   ) AS SDWANSourceData

   PIVOT(

      MAX(NodeID)

      FOR ClientLinkID IN([MPLS], [Internet], [4G], [VPN])

   ) AS LinkURL

) AS Link ON Status.Site1 = Link.Site2

WHERE RowNum BETWEEN 1 AND 40

Now I had the SQL query I needed to get this data, and on line 38 of the query, I also have the ability to limit which row numbers are returned.

All that was left is to get this data into Orion, and a huge shout out to Dean, one of the SolarWinds Principal Sales Engineers for helping me with this.

I had a bit of trouble working out how to get this data from this SQL query into SolarWinds to display it in a view, until Dean pointed me in the direction of Custom Table resources.

With a Custom Table, I could create my own datasource with the Advanced Database Query option.

From there, I could tell SolarWinds that I was using the SQL Query Type, and throw in the query I had worked out.

pastedImage_41.png

Perfect! I hid the RowNum column from the overall view, set Site to be Left Aligned, and then most importantly set the MPLS, Internet, 4G, and VPN columns to all be Center Aligned and Allow HTML tags.

With a bit of custom column changes, I ended up with the view below using some of the clients test data. (Site Names blurred out for privacy reasons)

pastedImage_43.png

All that was left was to create 4 more columns, copy this resource into each one, and modify each of the queries to only list the row numbers I wanted (I went for 4 columns of 40 rows).

This was exactly what the client was after, and the management overhead of this view is incredibly small.

In order to get a node to appear on this list, we simply needed to define the SiteID, and the LinkID. The query would then add it to the list.

The things we need to be careful about are:

  • Making sure the ClientEnvironment for each of the links is defined as 'Network - Meraki WAN';
  • Making sure the SiteID for each of the links at the same site all match;
  • Making sure the LinkID is defined as 'MPLS', 'Internet', '4G', or 'VPN'. If these aren't defined correctly, they won't show on the list;
  • Making sure we don't have any duplicate LinkID's for a specific SiteID.

As long as we keep to those rules, adding to and managing this view will be seamless and simple.

This was the deepest I've gone so far with customizing SolarWinds Orion, which is why I wanted to share my experience and findings.

I'm very keen to find out however if there is an easier way to achieve this, so please let me know what could be done differently if you know.

I hope some of you get some value out of this for your own environments.

  • That looks incredibly useful, thank you very much.  I'm not sure I understand how the LINKID's get associated with the appropriate interface on the Meraki devices, though.  I'm not a SQL developer by a long shot.  Are you monitor the Meraki using SNMP or the Meraki API?

  • Using SNMP. The Meraki API is great, but adds delay in getting data returned. We went for SNMP to get the most accurate data as fast as possible.

    The LinkID's get associated together using the Microsoft SQL PIVOT function. This allows us to take the returned data and summarize all of it by site.

  • I've just come across this post as I'm trying to do something similar at the moment. Very impressive work and result!

    I'm interested in how you are retrieving the VPN link info via SNMP - which MIB are you using (and what modle Meraki firewalls are monitoring)? I've run up MX-67 and MX-100s in our lab environment and can see the Ethernet interfaces. I am wondering how you were able to determine the status of the VPN? I am not seeing this as an interface on the MX100.

    Kind Regards,

    Felix

  • Thanks, much.  Is this the correct understanding of where to place the custom properties?  I used City instead of ClientSiteID as I already had the defined and it's easier not to keep adding on...

    Are you adding each of the links(MPLS, Internet, 4G, and VPN) as a node using it's static IP and then adding the custom properties to those nodes?

    I've done what I've mentioned above but the query will not run on my SQL server nor within the Custom Table resource, Advanced Database query.  I used your code, exactly as is (replacing ClientSiteID with City) and get the following error:

    Msg 156, Level 15, State 1, Line 19

    Incorrect syntax near the keyword 'AS'.

    Msg 156, Level 15, State 1, Line 37

    Incorrect syntax near the keyword 'AS'.

  • Hi everyone,

    first of all davidspruce​ this little Dashboard is an amazing work! Great job!

    captgrumpypants​ i had the same issue, i guess you missed a closeing parenthesis in those lines. If you are still having issues, just paste your query here, so we can help.

    However i changed the SQL-Query to meet the requirements for my customer. It is grouped by the customproperty "sitetag" (the sitecode in the customers environment) and it will show every interface with the interfacename "sp_wan" and "sp_lan" with their status and links.

    SELECT 

      

       Site1 AS Site,

       NodeCaption AS Node,

       '<a href="' + [WANLink] + '">' + [sp_wan] + '</a>' AS [WAN], 

       '<a href="' + [LANLink] + '">' + [sp_lan] + '</a>' AS [LAN]

    FROM ( 

       SELECT ROW_NUMBER() OVER (ORDER BY Site1) AS RowNum, * FROM ( 

          SELECT C.Sitetag AS Site1, N.Caption AS NodeCaption, I.Caption AS Caption, '<img src="/Orion/images/StatusIcons/small-' + I.OperStatusLED + '">' AS Status 

          FROM NodesCustomProperties AS C 

    LEFT JOIN NodesData AS N ON C.NodeID=N.NodeID 

    LEFT Join Interfaces AS I ON I.NodeID=N.NodeID

          WHERE I.Caption LIKE 'sp_wan' OR I.Caption LIKE 'sp_lan'

       ) AS SDWANSourceData  

       PIVOT( 

          MAX(Status) 

          FOR Caption IN([sp_wan], [sp_lan]

       )) AS LinkStatus )

    AS Status 

    LEFT JOIN ( 

       SELECT  

          Site2,  

          '/Orion/Interfaces/InterfaceDetails.aspx?NetObject=I:' + CONVERT(varchar, [sp_wan]) AS [WANLink],  

          '/Orion/Interfaces/InterfaceDetails.aspx?NetObject=I:' + CONVERT(varchar, [sp_lan]) AS [LANLink]

       FROM ( 

          SELECT C.Sitetag AS Site2, I.InterfaceID AS InterfaceID, I.Caption AS Caption

          FROM NodesCustomProperties AS C 

          LEFT JOIN NodesData AS N ON C.NodeID=N.NodeID 

          LEFT Join Interfaces AS I ON I.NodeID=N.NodeID

          WHERE I.Caption LIKE 'sp_wan' OR I.Caption LIKE 'sp_lan'

       ) AS SDWANSourceData  

       PIVOT( 

          MAX(InterfaceID) 

          FOR Caption IN([sp_wan], [sp_lan]) 

       ) AS LinkURL 

    ) AS Link ON Status.Site1 = Link.Site2 

    WHERE RowNum BETWEEN 1 AND 40

    Best Regards

    Rene

  • OK, I added a ')' at line 18 after ) AS LinkStatus and it seems to be working

    SELECT  
       RowNum,  
       Site1 AS Site,  
       '<a href="' + [MPLSLink] + '">' + [MPLS] + '</a>' AS [MPLS], 
       '<a href="' + [InternetLink] + '">' + [Internet] + '</a>' AS [Internet], 
       '<a href="' + [4GLink] + '">' + [4G] + '</a>' AS [4G], 
       '<a href="' + [VPNLink] + '">' + [VPN] + '</a>' AS [VPN] 
    FROM ( 
       SELECT ROW_NUMBER() OVER (ORDER BY Site1) AS RowNum, * FROM ( 
          SELECT C.City AS Site1, C.ClientLinkID, '<img src="/Orion/images/StatusIcons/small-' + N.StatusLED + '">' AS Status 
          FROM NodesCustomProperties AS C 
          LEFT JOIN NodesData AS N ON C.NodeID=N.NodeID 
          WHERE C.ClientEnvironment='Network - Meraki WAN' 
       ) AS SDWANSourceData  
       PIVOT( 
          MAX(Status) 
          FOR ClientLinkID IN([MPLS], [Internet], [4G], [VPN]) 
       ) AS LinkStatus) 
    AS Status 
    LEFT JOIN ( 
       SELECT  
          Site2,  
          '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [MPLS]) AS [MPLSLink],  
          '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [Internet]) AS [InternetLink],  
          '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [4G]) AS [4GLink],  
          '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + CONVERT(varchar, [VPN]) AS [VPNLink] 
       FROM ( 
          SELECT C.City AS Site2, N.NodeID AS NodeID, C.ClientLinkID 
          FROM NodesCustomProperties AS C 
          LEFT JOIN NodesData AS N ON C.NodeID=N.NodeID 
          WHERE C.ClientEnvironment='Network - Meraki WAN' 
       ) AS SDWANSourceData  
       PIVOT( 
          MAX(NodeID) 
          FOR ClientLinkID IN([MPLS], [Internet], [4G], [VPN]) 
       ) AS LinkURL 
    ) AS Link ON Status.Site1 = Link.Site2 
    WHERE RowNum BETWEEN 1 AND 40 

    Thank you to everyone for your help, and especially to the creator of this pretty awesome SQL query!!!