Is there a way to create a tabular view of Netpath measurements

mthangav​,

I talked to a netpath dev a few months ago and we specifically discussed the existing lack of reporting in Netpath and it is something he is working on.  With that said, in the mean time I have built myself one SWQL based report showing the most recent polling results from my netpaths.  It is still a bit of a work in progress because few of my clients have really been using netpath enough to want to spend time working on it but it should be helpful to get you started if you decide to build your own report.

SELECT --esa.ProbeID
case
when esa.laststatus =0 then 'Unknown'
when esa.laststatus =1 then 'Up'
when esa.laststatus =2 then 'Down'
when esa.laststatus =3 then 'Warning'
when esa.laststatus =4 then 'Critical'
end as [Status]
,sa.ProbeName as [Source]
--, esa.EndpointServiceID
, es.DisplayName as [Destination]
, sa.detailsurl as [_linkfor_DisplayName]
, es.HostName
, es.Port
--, esa.LastStatus
--, esa.LastProbeTime
, round(t.Rtt,0) as [Latency]
,round(t.PacketLoss,0) as [Loss]
,case
when esa.laststatus =0 then '/Orion/images/StatusIcons/Small-Unknown.gif'
when esa.laststatus =1 then '/Orion/images/StatusIcons/Small-up.gif'
when esa.laststatus =2 then '/Orion/images/StatusIcons/Small-down.gif'
when esa.laststatus =3 then '/Orion/images/StatusIcons/Small-warning.gif'
when esa.laststatus =4 then '/Orion/images/StatusIcons/Small-critical.gif'
end as [_iconfor_Status]
FROM Orion.NetPath.EndpointServices es
join Orion.NetPath.EndpointServiceAssignments esa on esa.EndpointServiceID=es.EndpointServiceID
join Orion.NetPath.ServiceAssignments sa on sa.EndpointServiceID=esa.EndpointServiceID
join (SELECT distinct max(executedat)as [ExecutedAt], EndpointServiceID, ProbeID
FROM Orion.NetPath.tests t
group by EndpointServiceID, ProbeID) tops on tops.probeid=esa.probeid and tops.endpointserviceid=esa.endpointserviceid
join Orion.NetPath.Tests t on t.EndpointServiceID=es.EndpointServiceID and t.probeid=esa.probeid and tops.executedat=t.executedat
where esa.enabled='true'
order by esa.laststatus desc, esa.displayname asc

Hope that helps

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

I'd ask cobrien​ about this because from previous discussion at the SWUG I recall there was more involved with this than simple packetloss/latency/port. Not sure how much can be said, tagging anyway.

Your input on how you would like to see this working would be especially valuable right now.  tulsi​, could you assist in setting up a discussion time?

For some reason the icons are not showing for me instead it shows:

/Orion/images/StatusIcons/Small-Unknown.gif

Could you tell me what I am doing wrong?

Check the inetpub folder of the server that hosts your website and confirm that there are icons at the default path

/Orion/images/StatusIcons/Small-Unknown.gif

Maybe some kind of proxy/content filter/iis trickery you guys have set up that would be stopping you from loading them.

I know this is an older thread, but is there a way to add the "PATH HISTORY" area for each, onto this useful SWQL page?

Hi,

This is a great way to view current stats. Do you know how you would be able to add a history column for the Latency data? so you could summarise and see the hourly average for example for a given month etc. I have several Netpaths set up from different locations and need to be able to compare the latency between the different Paths and report on a monthly basis. Any help or advice would be greatly appreciated.

Regards

So, we were having the same issue of needing history from NetPath.  Our Use case was to show "uptime" for each application in use from each client network: 24 Hour Uptime, 7 Day Uptime, and 30 Day Uptime as a % of uptime - mostly high level dashboard stuff for a NOC view shared to users and executives.

We wanted to ideally run SAM from two networks instead of NetPath to do more robust checks in case each network had application problems to report, but SAM can't be run from a probe so we had to use NetPath only (had a 503 error today on an application that reported an issue in SAM but not NetPath - so uptime here is misleading).

I took the above SWQL code and manipulated it for the high level history we wanted:

SELECT sa.DisplayName AS [Application Name],
case  
when esa.laststatus =0 then 'Unknown'  
when esa.laststatus =1 then 'Up'  
when esa.laststatus =2 then 'Down'  
when esa.laststatus =3 then 'Warning'  
when esa.laststatus =4 then 'Critical'  
end as [CurrentStatus],
case  
when esa.laststatus =0 then '/Orion/images/StatusIcons/Small-Unknown.gif'  
when esa.laststatus =1 then '/Orion/images/StatusIcons/Small-up.gif'  
when esa.laststatus =2 then '/Orion/images/StatusIcons/Small-down.gif'  
when esa.laststatus =3 then '/Orion/images/StatusIcons/Small-warning.gif'  
when esa.laststatus =4 then '/Orion/images/StatusIcons/Small-critical.gif'  
end as [_iconfor_CurrentStatus],  
ROUND((SUM (CASE WHEN t.Status = 1 AND t.ExecutedAt > AddDay(-1, GetDate()) THEN 1 ELSE 0 END) * 100.0 / SUM (CASE WHEN t.ExecutedAt > AddDay(-1, GetDate()) THEN 1 ELSE 0 END)),2) AS [24 Hour Uptime],
ROUND((SUM (CASE WHEN t.Status = 1 AND t.ExecutedAt > AddDay(-7, GetDate()) THEN 1 ELSE 0 END) * 100.0 / SUM (CASE WHEN t.ExecutedAt > AddDay(-7, GetDate()) THEN 1 ELSE 0 END)),2) AS [7 Day Uptime],
ROUND((SUM (CASE WHEN t.Status = 1 AND t.ExecutedAt > AddDay(-30, GetDate()) THEN 1 ELSE 0 END) * 100.0 / SUM (CASE WHEN t.ExecutedAt > AddDay(-30, GetDate()) THEN 1 ELSE 0 END)),2) AS [30 Day Uptime]

FROM Orion.NetPath.Tests t
join Orion.NetPath.ServiceAssignments sa on t.EndpointServiceID=sa.EndpointServiceID
join Orion.NetPath.EndpointServiceAssignments esa on esa.EndpointServiceID=sa.EndpointServiceID  

WHERE t.ExecutedAt > AddDay(-30, GetDate())
GROUP BY EndpointServiceID, ServiceName, ProbeName, LastStatus

The end result is a usable chart showing "uptime history" of NetPath.  Feel free to tweak to improve it (as there are better people in SWQL than myself) until Solarwinds Development team provides a more robust real interface to NetPath.  Please post any updates/comments here.

Please note, that there are some limitations here:

If your NetPath probe itself is down, there is nothing written to the DB for those scheduled attempts, so it will show as UP with this current code I've got above. So keep your probe(s) up 100% or suggest improvements to the code.

Also as mentioned beware of what you are communicating as "success" here to the consumer of this table - NetPath is not SAM.

Regards,

Jared

Hi,

Im afraid this just returns a Query is not Valid message, but it sounds like a great starting point for what i need.

Regards

I was concerned that my paste into my post missed something, so I just copied my post above into the SWQL studio (latest version as well as latest NPM) and it worked.  Good luck to you on it.

Made a last tweak yesterday once I noticed that in some cases there was a status of 100 which is not well defined, so I defaulted that to down.  During that state the netpath display showed 'no data' and if you looked at the icon for Netpath directly it showed red.

SELECT sa.DisplayName AS [Application Name],
case  
when esa.laststatus =0 then 'Unknown'  
when esa.laststatus =1 then 'Up'  
when esa.laststatus =2 then 'Down'  
when esa.laststatus =3 then 'Warning'  
when esa.laststatus =4 then 'Critical' 
else 'Down'
end as [Current Status],
case  
when esa.laststatus =0 then '/Orion/images/StatusIcons/Small-Unknown.gif'  
when esa.laststatus =1 then '/Orion/images/StatusIcons/Small-up.gif'  
when esa.laststatus =2 then '/Orion/images/StatusIcons/Small-down.gif'  
when esa.laststatus =3 then '/Orion/images/StatusIcons/Small-warning.gif'  
when esa.laststatus =4 then '/Orion/images/StatusIcons/Small-critical.gif'
else '/Orion/images/StatusIcons/Small-down.gif'
end as [_iconfor_Current Status],  
ROUND((SUM (CASE WHEN t.Status = 1 AND t.ExecutedAt > AddDay(-1, GetDate()) THEN 1 ELSE 0 END) * 100.0 / SUM (CASE WHEN t.ExecutedAt > AddDay(-1, GetDate()) THEN 1 ELSE 0 END)),2) AS [24 Hour Uptime],
ROUND((SUM (CASE WHEN t.Status = 1 AND t.ExecutedAt > AddDay(-7, GetDate()) THEN 1 ELSE 0 END) * 100.0 / SUM (CASE WHEN t.ExecutedAt > AddDay(-7, GetDate()) THEN 1 ELSE 0 END)),2) AS [7 Day Uptime],
ROUND((SUM (CASE WHEN t.Status = 1 AND t.ExecutedAt > AddDay(-30, GetDate()) THEN 1 ELSE 0 END) * 100.0 / SUM (CASE WHEN t.ExecutedAt > AddDay(-30, GetDate()) THEN 1 ELSE 0 END)),2) AS [30 Day Uptime]
FROM Orion.NetPath.Tests t
join Orion.NetPath.ServiceAssignments sa on t.EndpointServiceID=sa.EndpointServiceID
join Orion.NetPath.EndpointServiceAssignments esa on esa.EndpointServiceID=sa.EndpointServiceID  
WHERE t.ExecutedAt > AddDay(-30, GetDate())
GROUP BY EndpointServiceID, ServiceName, ProbeName, LastStatus

Again, hopefully Solarwinds will allow us to better use NetPath history.