Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Replicate Top 10 Nodes By Current Response Time in Modern Dashboard

I like the "Top 10 Nodes by Current Response Time" widget in the classical dashboard, but I can't figure out how to exactly replicate it on the modern dashboard.

I tried the 'hubble' approach to get the SWQL code and it works in SQWL Studio, but not when I copy and paste the exact code in the modern dashboard widget. It also looks different that what I was expecting.

I want to replicate this widget exactly (keeping the red 'No Response' at the top if a node is down, then sort by highest current response time).

This is what Hubble gave me (I made one page with just this one widget on it), but this doesn't look right to me.

SELECT TOP 10 Nodes.NodeID, Caption, GroupStatus, ResponseTime, PercentLoss, IP_Address, DNS, SysName, C.GUID, Nodes.Status, Nodes.InstanceSiteID as InstanceSiteId, os.Name as SiteName
FROM Orion.Nodes (nolock = true) Nodes
JOIN Orion.NodesCustomProperties (nolock=true) CP ON Nodes.NodeID = CP.NodeID and CP.InstanceSiteId = Nodes.InstanceSiteId
LEFT OUTER JOIN Orion.NodesWebCommunityStrings (nolock=true) C ON C.NodeID=Nodes.NodeID and C.InstanceSiteId = Nodes.InstanceSiteId
JOIN Orion.Sites (nolock=true) os ON os.SiteID = Nodes.InstanceSiteID
WHERE    Nodes.Status NOT IN ('11', '9', '12')  
ORDER BY (CASE WHEN ResponseTime<0 THEN 2147483647 ELSE ResponseTime END) DESC, PercentLoss  DESC RETURN XML RAW</pre>I got kinda close when I tried to make it from scratch, but couldn't figure out the 'no response' part at the top or the Icon. SW sets the 'ResponseTime' value to -1 when a device is offline so when I sort by response time it will never be shown because technically it is at the end since -1ms is a lower value than 30ms.Just wondering if anyone else has successfully replicated this to a modern dashboard and wouldn't care to share the SQWL code.Thanks!

Find more posts tagged with

Accepted answers

KMSigma.SWI

For the sorting, that will require SWQL because -1 (the no response number) needs to be higher than all the others.

As far as coloring it, that's not possible given the current implementation of modern dashboards.

This is what I was able to pencil together:

The SWQL I'm using for this is:

SELECT TOP 10 [Nodes].NodeID
     , [Nodes].Caption
     , CASE WHEN [Nodes].ResponseTime < 0 THEN 'No Response' ELSE CONCAT([Nodes].ResponseTime, ' ms') END AS [ResponseTime_Text]
     , CONCAT('/ui/perfstack/?context=', [Nodes].InstanceSiteID, '_Orion.Nodes_', [Nodes].NodeID, '&withRelationships=true&presetTime=Today&charts=', [Nodes].InstanceSiteID, '_Orion.Nodes_', [Nodes].NodeID, '-Orion.ResponseTime.AvgResponseTime' ) AS ResponseTime_Url
     , CONCAT( [Nodes].PercentLoss, ' %' ) AS [PercentLoss_Text]
     , CONCAT('/ui/perfstack/?context=', [Nodes].InstanceSiteID, '_Orion.Nodes_', [Nodes].NodeID, '&withRelationships=true&presetTime=Today&charts=', [Nodes].InstanceSiteID, '_Orion.Nodes_', [Nodes].NodeID, '-Orion.ResponseTime.PercentLoss' ) AS PercentLoss_Url
     , [Nodes].Status
     , [Nodes].DetailsUrl
     , [Nodes].InstanceSiteID as InstanceSiteId
-- The below line is used for the sort, but is "hidden" from the table
     , CASE WHEN [Nodes].ResponseTime<0 THEN 2147483647 ELSE ResponseTime END AS [ResponseSort]
FROM Orion.Nodes AS [Nodes]
JOIN Orion.Sites AS [Sites] ON [Sites].SiteID = [Nodes].InstanceSiteID
WHERE    [Nodes].Status NOT IN ('11', '9', '12')  
ORDER BY (CASE WHEN [Nodes].ResponseTime<0 THEN 2147483647 ELSE ResponseTime END) DESC, PercentLoss DESC</pre>

All comments

wluther

@bunjiega You should be able to get pretty close by just using a few of the main fields without even needing to use a SWQL query.

Is this something, more or less, you are tryin to accomplish?

wluther

Just in case it is what you are looking for, here is a quick and simple run through of how to get there.

Add your new/empty table widget

Select the graphical query builder

Search, find, and add these fields.

You can either submit what you have, or you can scroll down a bit and filter out some of the results if you'd like.

Now you should have your 5 columns.

And you should be able to apply the following settings for each of the different columns, which should get you to the results previous posted above.

Status

Caption

DetailsUrl

ResponseTime

And Percent Loss

If that's not what you are looking for, then let's just keep this here in case it helps someone else, and we can try again next week.

Let us know how it goes.

Thank you,

-Will

bunjiega

Thanks for the detailed answer! What you did was as far as I was able to get myself. I can't figure out how they put the down nodes at the top with 'no response' in red.

KMSigma.SWI

For the sorting, that will require SWQL because -1 (the no response number) needs to be higher than all the others.

As far as coloring it, that's not possible given the current implementation of modern dashboards.

This is what I was able to pencil together:

The SWQL I'm using for this is:

SELECT TOP 10 [Nodes].NodeID
     , [Nodes].Caption
     , CASE WHEN [Nodes].ResponseTime < 0 THEN 'No Response' ELSE CONCAT([Nodes].ResponseTime, ' ms') END AS [ResponseTime_Text]
     , CONCAT('/ui/perfstack/?context=', [Nodes].InstanceSiteID, '_Orion.Nodes_', [Nodes].NodeID, '&withRelationships=true&presetTime=Today&charts=', [Nodes].InstanceSiteID, '_Orion.Nodes_', [Nodes].NodeID, '-Orion.ResponseTime.AvgResponseTime' ) AS ResponseTime_Url
     , CONCAT( [Nodes].PercentLoss, ' %' ) AS [PercentLoss_Text]
     , CONCAT('/ui/perfstack/?context=', [Nodes].InstanceSiteID, '_Orion.Nodes_', [Nodes].NodeID, '&withRelationships=true&presetTime=Today&charts=', [Nodes].InstanceSiteID, '_Orion.Nodes_', [Nodes].NodeID, '-Orion.ResponseTime.PercentLoss' ) AS PercentLoss_Url
     , [Nodes].Status
     , [Nodes].DetailsUrl
     , [Nodes].InstanceSiteID as InstanceSiteId
-- The below line is used for the sort, but is "hidden" from the table
     , CASE WHEN [Nodes].ResponseTime<0 THEN 2147483647 ELSE ResponseTime END AS [ResponseSort]
FROM Orion.Nodes AS [Nodes]
JOIN Orion.Sites AS [Sites] ON [Sites].SiteID = [Nodes].InstanceSiteID
WHERE    [Nodes].Status NOT IN ('11', '9', '12')  
ORDER BY (CASE WHEN [Nodes].ResponseTime<0 THEN 2147483647 ELSE ResponseTime END) DESC, PercentLoss DESC</pre>