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

NetPath Custom Query Widget

I use NetPath in a number of ways, but the foremost is to validate that connections to SaaS applications are within tolerances all around my environment.  Having that information in a summary is very helpful.

NetPath_Services.png

NetPath already does that, but having a filter would be nice to only show a specific service.  What would be more awesome would be being able to give me summary information over time instead of just showing the most recent.

With that in mind, I started digging into the API to find if I could get a better summary of this information.  Much to my pleasure, I was able to find the information, but it was strewn across a few tables.  So I had to dust off some old SQL tactics in my brain to get the information.  (I'm terrible at hand-writing join queries and almost as bad when doing GROUP BY for calculating minimums/averages/minimums).

The two "tables" that I need to use are Orion.NetPath.ServiceAssignments and Orion.NetPath.Tests.

What I came up with was a little bit of SQL magic, a little bit of SWQL magic, and exactly what I needed in a resource.  I've added some comments in the script to show what I'm doing.  Without further ado, here's the SWQL script:

SELECT [SA].ProbeName AS [Source]
     , [SA].ServiceName AS [Destination]
     , [SA].DetailsUrl AS [_LinkFor_Source]
     , CONCAT('/Orion/images/StatusIcons/Small-', [SI].IconPostfix, '.gif') AS [_IconFor_Source] -- This is the status for the most recent poll only
     , ROUND([Tests].MinLatency, 2) AS [Min Latency (ms)]
     , ROUND([Tests].AvgLatency, 2) AS [Avg Latency (ms)]
     , ROUND([Tests].MaxLatency, 2) AS [Max Latency (ms)]
     , CONCAT(ROUND([Tests].MinLatency, 2), ' / ', ROUND([Tests].AvgLatency, 2), ' / ', ROUND([Tests].MaxLatency, 2) ) AS [Min/Avg/Max Latency (ms)]
     , ROUND([Tests].MinPacketLoss, 2) AS [Min Packet Loss (%)]
     , ROUND([Tests].AvgPacketLoss, 2) AS [Avg Packet Loss (%)]
     , ROUND([Tests].MaxPacketLoss, 2) AS [Max Packet Loss (%)]
     , CONCAT(ROUND([Tests].MinPacketLoss, 2), ' / ', ROUND([Tests].AvgPacketLoss, 2), ' / ', ROUND([Tests].MaxPacketLoss, 2) ) AS [Min/Avg/Max Packet Loss (%)]
FROM Orion.NetPath.ServiceAssignments AS [SA]
INNER JOIN Orion.StatusInfo AS [SI]
   ON [SA].Status = [SI].StatusID
INNER JOIN (
    SELECT EndpointServiceID
         , ProbeID
         , MIN(Rtt) AS MinLatency
         , AVG(Rtt) AS AvgLatency
         , MAX(Rtt) AS MaxLatency
         , MIN(PacketLoss) AS MinPacketLoss
         , AVG(PacketLoss) AS AvgPacketLoss
         , MAX(PacketLoss) AS MaxPacketLoss
    FROM Orion.NetPath.Tests
    WHERE ExecutedAt >= GETUTCDATE() - 1 -- ExecutedAt is stored in UTC, so we use 'GETUTCDATE() - 1' to get last 24 hours only
    GROUP BY EndpointServiceID, ProbeID
) AS [Tests]
ON  [Tests].ProbeID = [SA].ProbeID
AND [Tests].EndpointServiceID = [SA].EndpointServiceID
WHERE [SA].ServiceName = 'Office 365' -- This is the NetPath Service Name as displayed on your NetPath summary page
  AND [SA].Enabled = 'True'
ORDER BY [SA].ProbeName

Let me break it down a little bit.  First off, we are joining up three tables here.  The one that I didn't mention earlier is Orion.ServiceInfo.  This table has the status "names" based on the numbers.  I use them for pulling in the icons.  This leads to a bit of SWQL magic.

SWQL has the ability to recognize icons and links, but only if they are formatted properly.  This is where lines 3 and 4 from the above script come into play.  If you have an element in a custom query called "Stuff", then you can give it a URL named "_IconFor_Stuff" and it will be displayed to the left of the label.  Similarly, you can use the "_LinkFor_Stuff" to provide a clickable link.  As a note for the future, many tables provide a "DetailsUrl" field that's very useful for this.

I'm also using some specific SQL-like constructs - specifically joining a table to a table and joining a table to a query (which returns table-like content).  In lines 14 and 15, I'm joining in the Orion.StatusInfo table which provides the IconPostfix needed for line 4.

Later in lines 16 through 28 I'm making a query from Orion.NetPath.Tests where I summarize information over the last 24 hours.  Note that in line 26 I'm using a comparison to only pull back minimums, averages, and maximums over the last 24 hours.  You can change this, but be aware that it will take more time to execute the query.  On a 10 minute probe (the default), each probe provides 60 minutes / 10 minutes per test * 24 hours = 144 tests / probe.  Multiple this by the number of probes (4 in my environment) means the query has to summarize 576 entries into six total statistics.

Thankfully SWQL is highly optimized.  As an example me running this full query takes 0.023 seconds.  If I change the number of days on line 26 to 7 (show me the last week), then it runs in 0.59 seconds.  Like I said, the SWQL is highly optimized.

So what's all this look like?  Just edit a page and add a Custom Query widget and paste in the above query (changing line 31 to match one of your own NetPath Services) and you should get something very pretty.

NetPath_CustomQuery.png

I've elected to show Min/Avg/Max in multiple ways, but in a production environment, I probably only care about average.  To get just the average to display, just comment out (or delete) the unneeded elements.  In SQL (and SWQL) the double-dash indicates a comment.

SELECT [SA].ProbeName AS [Source]
     , [SA].ServiceName AS [Destination]
     , [SA].DetailsUrl AS [_LinkFor_Source]
     , CONCAT('/Orion/images/StatusIcons/Small-', [SI].IconPostfix, '.gif') AS [_IconFor_Source] -- This is the status for the most recent poll only
--     , ROUND([Tests].MinLatency, 2) AS [Min Latency (ms)]
--     , ROUND([Tests].AvgLatency, 2) AS [Avg Latency (ms)]
--     , ROUND([Tests].MaxLatency, 2) AS [Max Latency (ms)]
     , CONCAT(ROUND([Tests].MinLatency, 2), ' / ', ROUND([Tests].AvgLatency, 2), ' / ', ROUND([Tests].MaxLatency, 2) ) AS [Min/Avg/Max Latency (ms)]
--     , ROUND([Tests].MinPacketLoss, 2) AS [Min Packet Loss (%)]
--     , ROUND([Tests].AvgPacketLoss, 2) AS [Avg Packet Loss (%)]
--     , ROUND([Tests].MaxPacketLoss, 2) AS [Max Packet Loss (%)]
     , CONCAT(ROUND([Tests].MinPacketLoss, 2), ' / ', ROUND([Tests].AvgPacketLoss, 2), ' / ', ROUND([Tests].MaxPacketLoss, 2) ) AS [Min/Avg/Max Packet Loss (%)]
FROM Orion.NetPath.ServiceAssignments AS [SA]
INNER JOIN Orion.StatusInfo AS [SI]
   ON [SA].Status = [SI].StatusID
INNER JOIN (
    SELECT EndpointServiceID
         , ProbeID
         , MIN(Rtt) AS MinLatency
         , AVG(Rtt) AS AvgLatency
         , MAX(Rtt) AS MaxLatency
         , MIN(PacketLoss) AS MinPacketLoss
         , AVG(PacketLoss) AS AvgPacketLoss
         , MAX(PacketLoss) AS MaxPacketLoss
    FROM Orion.NetPath.Tests
    WHERE ExecutedAt >= GETUTCDATE() - 1 -- ExecutedAt is stored in UTC, so we use 'GETUTCDATE() - 1' to get last 24 hours only
    GROUP BY EndpointServiceID, ProbeID
) AS [Tests]
ON  [Tests].ProbeID = [SA].ProbeID
AND [Tests].EndpointServiceID = [SA].EndpointServiceID
WHERE [SA].ServiceName = 'Office 365' -- This is the NetPath Service Name as displayed on your NetPath summary page
  AND [SA].Enabled = 'True'
ORDER BY [SA].ProbeName

So the above returns:

NetPath_CustomQuery_Smaller.png

Hopefully this has helped a few of you with getting your feet wet within SWQL and the custom query.  Now go forth and script around a bit.

Labels (1)
Tags (1)
Comments

Awesome stuff sir!

This is amazing. Thanks!

Been looking for something like this for a while

I have been using this since you first posted it, and it is SUPER helpful!

Great job, and thank you very much for sharing this wonderful addition.

Hi Guys,

I took this one step further and created a graph showing latency & Packet loss from all probes on a service.

Add a custom Chart to a Dashboard. Enter the following SWQL as the Data Source:

SELECT [SA].ServiceName AS [Destination] 

     ,[SA].ProbeName AS [Source]  

     , [SA].DetailsUrl AS [_LinkFor_Source]

    -- , CONCAT('/Orion/images/StatusIcons/Small-', [SI].IconPostfix, '.gif') AS [_IconFor_Source]

     , [Tests].Time AS [DATETIME]

     , Round([Tests].latency, 2) AS [Latency]

     , ROUND([Tests].PacketLoss, 2) AS [Loss]

FROM Orion.NetPath.ServiceAssignments AS [SA] 

--INNER JOIN Orion.StatusInfo AS [SI] 

  -- ON [SA].Status = [SI].StatusID 

INNER JOIN ( 

     SELECT EndpointServiceID

         , ExecutedAt AS Time

         , ProbeID 

         , Rtt AS Latency

         , PacketLoss 

     FROM Orion.NetPath.Tests

    WHERE ExecutedAt >= GETUTCDATE() - 2) AS [Tests] 

ON  [Tests].ProbeID = [SA].ProbeID 

AND [Tests].EndpointServiceID = [SA].EndpointServiceID 

WHERE [SA].ServiceName = 'SEVICENAME'  -- This is the NetPath Service Name as displayed on your NetPath summary page 

  AND [SA].Enabled = 'True' 

ORDER BY [Tests].Time

Here are the Graph settings:

pastedImage_0.png

Here's the End Result:

pastedImage_2.png

Love all of this!  Thanks for sharing!

cwhall​ I happened to try yours with all settings as above but no data. Hmm.

superb. thanks

KMSigma​ did you realize how magical this is? This works for EVERY netpath. All I had to do was change 1 line. You genius, you.

WHERE [SA].ServiceName IS NOT NULL -- This is the NetPath Service Name as displayed on your NetPath summary page

Awesome - when I asked cobrien​ about this at London SWUG he thought it was a fake widget I’m glad to see it’s not and that I can replicate it

CourtesyIT​ add to list for netpath

I stand corrected.

Very nice.  Help fill a gap for a Business Dashboard we have been putting together.

Quick question.  The Latency and Packet Loss is averaged over the last 24 hours.  I removed the below line to get the data from the last time NetPath was ran.

    WHERE ExecutedAt >= GETUTCDATE() - 1 -- ExecutedAt is stored in UTC, so we use 'GETUTCDATE() - 1' to get last 24

Anything that I'm missing or causing by doing that?

If you remove that line then you aren't getting the "last time," you are getting the average of every time it's ever run that's stored in the database.  What you want is the TOP 1 Grouped by the site and sorted, descending, by the execution time.

I'm out of pocket right now, but can take a crack at the code when I'm back online best week or someone else here can probably help out.

Thanks a bunch….I’m getting everything I was looking for in the query you have provided, just want it to pull the “last time” data instead of averaging over a period of time.

Screen Shot 2019-04-05 at 7.42.29 PM.png

If you really only have the one data point, you can do a "SELECT TOP 1 [blah, blah, blah] ORDER BY ExecutedAt DESC"

KMSigma ​ this is a such a neat routine. It's so useful & I'm using it all over the place now. Thanks for posting.

Excellent share, works brilliant. thank you!

I don't get any data for the first query for some reason?

For me Source status icon not shown, any advise.

SELECT [SA].ProbeName AS [Source] 

     , [SA].ServiceName AS [Destination] 

     , [SA].DetailsUrl AS [_LinkFor_Source] 

     , CONCAT('/Orion/images/StatusIcons/Small-', [SI].IconPostfix, '.gif') AS [_IconFor_Source] -- This is the status for the most recent poll only 

     , ROUND([Tests].MinLatency, 2) AS [Min Latency (ms)] 

     , ROUND([Tests].AvgLatency, 2) AS [Avg Latency (ms)] 

     , ROUND([Tests].MaxLatency, 2) AS [Max Latency (ms)] 

     , CONCAT(ROUND([Tests].MinLatency, 2), ' / ', ROUND([Tests].AvgLatency, 2), ' / ', ROUND([Tests].MaxLatency, 2) ) AS [Min/Avg/Max Latency (ms)] 

     , ROUND([Tests].MinPacketLoss, 2) AS [Min Packet Loss (%)] 

     , ROUND([Tests].AvgPacketLoss, 2) AS [Avg Packet Loss (%)] 

     , ROUND([Tests].MaxPacketLoss, 2) AS [Max Packet Loss (%)] 

     , CONCAT(ROUND([Tests].MinPacketLoss, 2), ' / ', ROUND([Tests].AvgPacketLoss, 2), ' / ', ROUND([Tests].MaxPacketLoss, 2) ) AS [Min/Avg/Max Packet Loss (%)] 

FROM Orion.NetPath.ServiceAssignments AS [SA] 

INNER JOIN Orion.StatusInfo AS [SI] 

   ON [SA].Status = [SI].StatusID 

INNER JOIN ( 

    SELECT EndpointServiceID 

         , ProbeID 

         , MIN(Rtt) AS MinLatency 

         , AVG(Rtt) AS AvgLatency 

         , MAX(Rtt) AS MaxLatency 

         , MIN(PacketLoss) AS MinPacketLoss 

         , AVG(PacketLoss) AS AvgPacketLoss 

         , MAX(PacketLoss) AS MaxPacketLoss 

    FROM Orion.NetPath.Tests 

    --WHERE ExecutedAt >= GETUTCDATE() - 1 -- ExecutedAt is stored in UTC, so we use 'GETUTCDATE() - 1' to get last 24 hours only 

    GROUP BY EndpointServiceID, ProbeID 

) AS [Tests] 

ON  [Tests].ProbeID = [SA].ProbeID 

AND [Tests].EndpointServiceID = [SA].EndpointServiceID 

WHERE [SA].ServiceName = 'bbc' -- This is the NetPath Service Name as displayed on your NetPath summary page 

  AND [SA].Enabled = 'True' 

ORDER BY [SA].ProbeName 

pastedImage_0.png

In SWQL Studio, what is returned for the "_IconFor_Source" field?  In my environment, I'm seeing the "Small-up.gif" path:

pastedImage_0.png

Below is my result.

pastedImage_0.png

That looks to be from the report writer, which uses different syntax.  Your query was built to be a "Custom Query" widget (to be placed directly on an Orion page).

e.jenidy​ - You have added a Custom Table instead of a Custom Query widget to your dashboard. The Custom Table is designed more for SQL (It can work with SWQL but meh)

Thanks for help, Issue solved after changed to Custom Query

Using Orion Maps and Netpath to monitor critical applications on the dashboard

neomatrix1217_0-1586214520870.png

 

This query has stopped working since upgrading to 2020.2. Not had time to solve. But has anyone come across which tables are now used?

 

Thanks

Version history
Revision #:
1 of 1
Last update:
‎07-24-2018 12:08 PM
Updated by: