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