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

Building Simple PerfStack Templates With SWQL

As the title states, I will attempt to show you how to build quick, and simple, PerfStack templates using SWQL queries. My intent is to keep it simple here, so I will NOT be going into anything that requires you to be a world class rocket surgeon.

Here is an easy way to build a list of saved PerfStack projects: Building Simple PerfStack Project Lists With SWQL

Basically, I just need a quick and easy way to build PerfStack views in bulk. When manually building a PerfStack dashboard, you are actually building the URL as you are adding the various metrics. I am simply taking that URL, and stamping it out across all nodes at once. I want to be able to build a handful of templates, to allow my team to quickly troubleshoot issues as they are occurring. I do NOT want to have someone building a PerfStack dashboard during the outage. I do, however, want them to have access to this super awesome tool, but it needs to be useful in the heat of battle. I think we will all get there, in time, but maybe this will work for now...?

Okay, in the spirit of keeping it simple, let's just start things out with a few metrics per node. More specifically, I am going to build a template that shows me the basic metrics for CPU, memory, response time, alerts, and events. Now, let's break this down...

Example NodeID: 1234

CPU

Orion.CPULoad.MinLoad

Orion.CPULoad.MaxLoad

Orion.CPULoad.AvgLoad

If you go through the steps to manually create your PerfStack dashboard, you would need to do the following:

Select the node you want.

Select the type of metrics you want.

Drag each node metric over to the graph section you want.

Using the min, max, avg cpu load metrics, on our example node (nodeid=1234), and having each on its own graph box, your URL would look something similar to this:

http://{orionserver}/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_1234-Orion.CPULoad.MinLoad;0_Orion.Nodes_1234-Orion.CPULoad.MaxLoad;0_Orion.Nodes_1234-Orion.CPULoad.AvgLoad;

And your PerfStack dashboard would look something like this:

Performance_Analysis_01_Basic_CPU_Load-001.png

All in all, pretty simple and easy to do.

Now, would you want to do that for each of the 1500, 5000, or 10000+ nodes you are monitoring? If so, I reckon there is no need for you to keep reading. In fact, you best get back to mass producing those graphs on your one person assembly line. For the rest of us, let's look at a simple solution.

Add a "Custom Query" resource to a summary page, and then add the following query into the resource.

SELECT
Caption
,'PerfStack_Template_A' AS [PerfStack-A]
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinLoad;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxLoad;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgLoad;' AS [_LinkFor_PerfStack-A]
FROM Orion.Nodes

And, to make things a bit easier to navigate, enable the search option, and drop in the following query: (This is certainly not required, but will surely assist in tracking down the node you want to investigate.)

SELECT
Caption
,'PerfStack_Template_A' AS [PerfStack-A]
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinLoad;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxLoad;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgLoad;' AS [_LinkFor_PerfStack-A]
FROM Orion.Nodes
WHERE
Caption LIKE '%${SEARCH_STRING}%'

After saving/submitting your resource, you should see something that looks like this:

Performance_Analysis_01_Basic_CPU_Load-002.png

See, nothing fancy... Just a simple little node search box, with a link to something.

Now check out what in one of those links...

Performance_Analysis_01_Basic_CPU_Load-003.png

Hmm... That looks awfully familiar... Only... You didn't have to build anything... And, whats more, it's already built for EVERY node you are monitoring.

Okay, I admit, that graph template is not all that impressive, and probably not worth much of anything... However, (yeah, there's always a however...) we can easily make a couple of changes, which should make this more helpful.

Instead of having each different CPU metric on its own graph box, let's group all 3 of them together in the same box.

This is easily accomplished by changing the inner semicolons to commas.

SELECT
Caption
,'PerfStack_Template_A' AS [PerfStack-A]
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgLoad;' AS [_LinkFor_PerfStack-A]
FROM Orion.Nodes

After changing those 2 semicolons to commas, this is what our graph looks like: (since the min, max, & avg are all the same, our graph looks pretty well hidden, but as you can see on the right side, all 3 metrics are still there.)

Performance_Analysis_01_Basic_CPU_Load-004.png

Now, let's add some more stuff, and finish this thing up...

We are going to add the remaining items from our initial list (memory, response time, alerts, & events)

To show our progress, we are going to add new lines to our query, keeping the first draft the same. This will give us the ability to build different templates, and access them from a central place.

We are going to keep similar metrics together in the same graph box, as we did with the CPU Load metrics. We will end up with a single graph box which contains all of the CPU Load metrics, another with memory, another with response time, etc..

You are simply taking each of the individual metrics groups, and combining them together, into a single URL.

Use a comma to keep metrics in the same graph box, and use a semicolon to end the selection of each graph box.

Using the example below, first remove all of the lines that start with "--", as well as all blank/empty lines.

--CPU Load:
,'/ui/perfstack/?presetTime=last12Hours&charts=
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinLoad,
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxLoad,
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgLoad;

--Memory:
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.TotalMemory,
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinMemoryUsed,
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxMemoryUsed,
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgMemoryUsed;

--ResponseTime:
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.AvgResponseTime,
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.MinResponseTime,
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.MaxResponseTime,
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.Availability;

--Alerts & Events:
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.PerfStack.Alerts,
0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.PerfStack.Events;'

Then, just backspace the lines together, starting from the last row, and going up, until they look like this:

,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgLoad;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.TotalMemory,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinMemoryUsed,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxMemoryUsed,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgMemoryUsed;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.AvgResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.MinResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.MaxResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.Availability;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.PerfStack.Alerts,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.PerfStack.Events;'

Finally, just add that newly combined row to your query, which will then look like this: (Don't forget to add your SWQL _LinkFor_ alias to the end of the line, so it matches the alias name)

SELECT
Caption
,'PerfStack_Template_A' AS [PerfStack-A]
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgLoad;' AS [_LinkFor_PerfStack-A]

,'PerfStack_Template_Z' AS [PerfStack-Z]
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgLoad;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.TotalMemory,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinMemoryUsed,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxMemoryUsed,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgMemoryUsed;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.AvgResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.MinResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.MaxResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.Availability;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.PerfStack.Alerts,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.PerfStack.Events;' AS [_LinkFor_PerfStack-Z]

FROM Orion.Nodes

The End Result:

Here is my simple little SWQL query that will give you examples for each of the different basic metric types we used in the many, many, many words above.

PerfStack-A = CPU Load

PerfStack-B = Memory Used

PerfStack-C = Response Time

PerfStack-D = Alerts & Events

PerfStack-Z = All of the above

SELECT
Caption
,'PerfStack_Template_A' AS [PerfStack-A]
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgLoad;' AS [_LinkFor_PerfStack-A]

,'PerfStack_Template_B' AS [PerfStack-B]
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.TotalMemory,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinMemoryUsed,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxMemoryUsed,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgMemoryUsed;' AS [_LinkFor_PerfStack-B]

,'PerfStack_Template_C' AS [PerfStack-C]
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.AvgResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.MinResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.MaxResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.Availability;' AS [_LinkFor_PerfStack-C]

,'PerfStack_Template_D' AS [PerfStack-D]
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.PerfStack.Alerts,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.PerfStack.Events;' AS [_LinkFor_PerfStack-D]

,'PerfStack_Template_Z' AS [PerfStack-Z]
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxLoad,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgLoad;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.TotalMemory,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MinMemoryUsed,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.MaxMemoryUsed,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.CPULoad.AvgMemoryUsed;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.AvgResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.MinResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.MaxResponseTime,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.ResponseTime.Availability;0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.PerfStack.Alerts,0_Orion.Nodes_' + TOSTRING(NodeID) + '-Orion.PerfStack.Events;' AS [_LinkFor_PerfStack-Z]

FROM Orion.Nodes

The SWQL query above should show you a resource similar to:

Performance_Analysis_01_Basic_CPU_Load-006.png

And the "PerfStack-Z" template should build out to something like this:

Performance_Analysis_01_Basic_CPU_Load-005.png

**There is really no need to save this dashboard (that I know of, anyway), as the link will just open up the same exact thing, every time. However, if you wanted to use this as a jumping off point, adding other metrics for other nodes/interfaces/etc., it would probably be best to save it.**

While this is really only building single node "templates", you can add multiple nodes to a single graph with a quick copy paste, or some decent SWQL skills (which I am obviously lacking, as I don't have any a multi-node query ready yet...).

For example, the following would show the Average CPU Load for NodeIDs 1234 & 5678, on the same graph box.

0_Orion.Nodes_1234-Orion.CPULoad.AvgLoad,0_Orion.Nodes_5678-Orion.CPULoad.AvgLoad;

There is a browser/URL limitation, so, eventually, once the URL gets too long, it will not work. I believe this is why they convert the URL into a stored name, to circumvent the issue. Having said that, if you do build a super long URL, just update the database directly. (insert buyer beware, backup early, backup often, etc... )

This is, by no means, the best way to accomplish bulk "pre-built" templates for troubleshooting. However, maybe it will hold you over until the next release of PerfStack...?

For more ways to customize your SolarWinds environment, make sure to check out this link, by CourtesyIT

How to do various customizations with your Solarwinds

Thank you,

-Will

Labels (2)
Comments

It looks like sstark85​ has been digging into PerfStack on a more advanced level, for those interested:

SolarWinds PerfStack API

PerfStack API PowerShell Module

This type of article is highly beneficial.  I am getting ready to work in the lab with PerfStack.  Thanks for this information.

Fantastic stuff wluther!  Our biggest gripe with Perfstack has been the inability to access it from anywhere other than in the analysis dashboard.  This fixes that.  I'm also getting ready to dig in to sstark85's stuff you linked as I love anything to do with Powershell.  Thanks so much for this!

xtraspecialj​ Please, by all means, come back and share your findings here, so we can all gain some knowledge of this new tool together... You too, jkump

Will we be able to create a Perfstack containing interface Tx/Rx % Utilisation using interface custom property?. For example, All Interface containing Custom property Wan_link = yes should be on a single dashboard reporting its TX/RX

Utilisation or Peek Utilisation.

.interface.PNG

reubenjack​ Sure, you simply need to adjust the SWQL query. You will need to add a JOIN to connect the NodeCustomProperties table, as well as a WHERE ncp.Wan_link = 'Yes' filter to narrow it down. Additionally, you will need to adjust the "Template" section of the query, to correct for different metrics. Initially, I just manually built out a PerfStack graph, for a single node, with the basic metrics, and then copied that URL and pieced it out.

Here are some PerfStack metrics you can use for interface stats. I'm not 100% certain they are all correctly formatted, however, if it doesn't work, just build it out manually, and substitute the pieces of the URL for what is in the query above.

Orion.NPM.InterfaceAvailability.Availability

Orion.NPM.InterfaceErrors.InErrors

Orion.NPM.InterfaceErrors.InDiscards

Orion.NPM.InterfaceErrors.OutErrors

Orion.NPM.InterfaceErrors.OutDiscards

Orion.NPM.InterfaceErrors.Errors

Orion.NPM.InterfaceErrors.Discards

Orion.NPM.InterfaceErrors.PercentDiscards

Orion.NPM.InterfaceErrors.PercentErrors

Orion.NPM.InterfaceErrors.ReceivePercentErrors

Orion.NPM.InterfaceErrors.TransmitPercentErrors

Orion.NPM.InterfaceErrors.CRCAlignErrors

Orion.NPM.InterfaceErrors.LateCollisions

Orion.NPM.InterfaceTraffic.InAveragebps

Orion.NPM.InterfaceTraffic.InMinbps

Orion.NPM.InterfaceTraffic.InMaxbps

Orion.NPM.InterfaceTraffic.InTotalBytes

Orion.NPM.InterfaceTraffic.InTotalPkts

Orion.NPM.InterfaceTraffic.InAvgUniCastPkts

Orion.NPM.InterfaceTraffic.InMinUniCastPkts

Orion.NPM.InterfaceTraffic.InMaxUniCastPkts

Orion.NPM.InterfaceTraffic.InAvgMultiCastPkts

Orion.NPM.InterfaceTraffic.InMinMultiCastPkts

Orion.NPM.InterfaceTraffic.InMaxMultiCastPkts

Orion.NPM.InterfaceTraffic.OutAveragebps

Orion.NPM.InterfaceTraffic.OutMinbps

Orion.NPM.InterfaceTraffic.OutMaxbps

Orion.NPM.InterfaceTraffic.OutTotalBytes

Orion.NPM.InterfaceTraffic.OutTotalPkts

Orion.NPM.InterfaceTraffic.OutAvgUniCastPkts

Orion.NPM.InterfaceTraffic.OutMaxUniCastPkts

Orion.NPM.InterfaceTraffic.OutMinUniCastPkts

Orion.NPM.InterfaceTraffic.OutAvgMultiCastPkts

Orion.NPM.InterfaceTraffic.OutMinMultiCastPkts

Orion.NPM.InterfaceTraffic.OutMaxMultiCastPkts

Orion.NPM.InterfaceTraffic.TotalBytes

Orion.NPM.InterfaceTraffic.TotalPackets

Orion.NPM.InterfaceTraffic.Averagebps

Orion.NPM.InterfaceTraffic.OutPercentUtil

Orion.NPM.InterfaceTraffic.InPercentUtil

Orion.NPM.InterfaceTraffic.PercentUtil

Thank you,

-Will

reubenjack

I think I may have misunderstood you, so I am going to ask, for clarification.

Are you wanting to make a template that shows EVERY interface, from a single node, all on the same graph?

Or, are you wanting to be able to look at a list of interfaces, from ALL nodes, having a pre-set template for each interface, showing the same metric types?

Something like this should give you a list of all interfaces, regardless of node, with the interface custom property value "Wan_link" equal to "Yes". Each interface should have a link to a pre-built PerfStack graph, showing TX% and RX% for the interface.

SELECT

n.NodeID

,n.Caption AS [NodeName]

,i.InterfaceID

,i.Caption AS [IntName]

,'PerfStack Template-A' AS [PSTemplate-A]

,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.NPM.Interfaces_' + TOSTRING(InterfaceID) + '-Orion.NPM.InterfaceTraffic.OutPercentUtil,0_Orion.NPM.Interfaces_' + TOSTRING(InterfaceID) + '-Orion.NPM.InterfaceTraffic.InPercentUtil;' AS [_LinkFor_PSTemplate-A]

FROM Orion.Nodes AS n

JOIN Orion.NPM.Interfaces AS i ON n.NodeID=i.NodeID

JOIN Orion.NPM.InterfacesCustomProperties AS icp ON i.InterfaceID=icp.InterfaceID

WHERE

icp.Wan_link='Yes'

If you are wanting it to graph EVERY qualified interface on the same graph, using the same link, then you would probably need to use a more advanced method, with which I lack the knowledge to share.

I would guess this to be done with some sort of stored procedure in SQL, or using the SDK, or asking a magic crystal ball...? I'm really not sure on that. But, on the bright side, there are a bunch of smart people on this site, so maybe we will see an answer from one of them soon.

Thank you,

-Will

reubenjack

Also, there is only so much that will fit in the URL, before it breaks itself.

When the URL is too long, I do the following:

I build the URL.

I manually create a "dummy" PerfStack project, and save it. (It doesn't matter what is on the graph, it only matters that the project is saved.)

I take the main part of the URL, and update the "PerfStackProjects" table, in the database.

This allows me to bypass the character limit of the URL, by just dumping the data directly into the database.

Thank you,

-Will

Thank you so much. This really helped a lot. Yes, if all matched interface would have shown in a single dashboard it would be cool. However this is good. Saved me a lot of work

reubenjack​ Great, I'm glad you found some use for this, even if not the most efficient way. Perhaps KMSigma​, mesverrum​, mrxinu​, alexslv​, aLTeReGo​, or anyone else browsing through Thwack, can come up with a better solution...

Updated the main post with a link to a SWQL query to generate a simple list of PerfStack projects.

Building Simple PerfStack Project Lists With SWQL

Thanks.  Very helpful!!!!

With this being my first foray into Perfstack, I found the information extremely valuable.

With a lot of trial an error, I was able to use the same principles to create views for a specific set of application monitors to be able to review over 1200 nodes.

@wluther hacking Orion since 2000!

LOL... probably more breaking, than hacking...

pastedImage_1.png

pastedImage_0.png

Thanks for pointing this trick out out, I co-opted it and started using this method to replace all the links in my existing collection of custom SWQL as soon as I saw it.  Figured I should post back an example so people can see an additional use case for this idea.

So I think the default cpu/mem and latency/loss gauges take up wayyyyy too much screen real estate so a while back I wrote a swql that gets me all the info i need in a much more compact form.

Instead of this thing taking up 1/4 of my screen

pastedImage_2.png

I used SWQL to make this that only takes a couple lines

pastedImage_0.png

And in the past I had set up the links so that if you clicked on a metric it showed the old style charts like this

pastedImage_1.png

Obviously these charts are a little lacking and old school but it got the job done.

Using wluther​'s method I reworked my script to load a relevant perfstack template for each metric that turns that clunky old chart into this one thay shows the key relevant stats and any active alerts on the same timeline

pastedImage_3.png

Here is the code for the custom SWQL resource:

select distinct --n.caption as [Node], n.detailsurl as [_Linkfor_Node],

case when n.cpuload < 0 then 'Not Polled'

when n.host.nodeid is not null and n.host.cpucorecount is not null then concat(round(n.host.cpuload,0),'% of ',n.host.CpuCoreCount,' CPU')

when cpu.[cpu count] is not null then concat(cpuload,'% of ',cpu.[cpu count],' CPU')

else 'Polling Error'

end as [CPU Load]

,'/ui/perfstack/?presetTime=last30Days&charts=0_Orion.Nodes_'+tostring(nodeid)+'-Orion.CPULoad.AvgLoad,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.CPULoad.MaxLoad,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Alerts;' as [_linkfor_CPU Load]

,CASE

WHEN cpuload >= htc.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN cpuload >= htc.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN cpuload < htc.Level1Value  THEN '/Orion/images/StatusIcons/Small-Up.gif'

WHEN cpuload >= n.CpuLoadThreshold.Level2Value and htc.Name is null THEN '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN cpuload >= n.CpuLoadThreshold.Level1Value and htc.Name is null THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN cpuload <  n.CpuLoadThreshold.Level1Value and htc.Name is null THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_CPU Load]

,case when percentmemoryused < 0 then 'Not Polled'

else concat(percentmemoryused,'% of ',(round(n.totalmemory/1073741824,0)),' GB')

end as [Memory Used]

,'/ui/perfstack/?presetTime=last30Days&charts=0_Orion.Nodes_'+tostring(nodeid)+'-Orion.CPULoad.AvgPercentMemoryUsed,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.CPULoad.MaxMemoryUsed,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.CPULoad.TotalMemory,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Alerts;' as [_linkfor_Memory Used]

,CASE

WHEN percentmemoryused >= htm.Level2Value and htm.Name = 'VIM.Hosts.Stats.MemUsage' THEN '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN percentmemoryused >= htm.Level1Value and htm.Name = 'VIM.Hosts.Stats.MemUsage' THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN percentmemoryused < htm.Level1Value and htm.Name = 'VIM.Hosts.Stats.MemUsage' THEN '/Orion/images/StatusIcons/Small-Up.gif'

WHEN percentmemoryused >= n.percentmemoryusedThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN percentmemoryused >= n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN percentmemoryused <  n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_Memory Used]

,CASE

WHEN responsetime<0 then 'No Response'

ELSE concat(responsetime,' ms')

END AS [Latency]

,'/ui/perfstack/?presetTime=last30Days&charts=0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Status,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.ResponseTime.MaxResponseTime,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Alerts,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.ResponseTime.AvgResponseTime;' as [_linkfor_Latency]

,CASE

WHEN responsetime >= n.responsetimeThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN responsetime >= n.responsetimeThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN responsetime <  n.responsetimeThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_Latency]

,concat(percentloss,'%') as [Packet Loss]

,'/ui/perfstack/?presetTime=last30Days&charts=0_Orion.Nodes_'+tostring(nodeid)+'-Orion.ResponseTime.Availability,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Status,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.PerfStack.Alerts,0_Orion.Nodes_'+tostring(nodeid)+'-Orion.ResponseTime.PercentLoss;' as [_linkfor_Packet Loss]

,CASE

WHEN percentloss >= n.percentlossThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN percentloss >= n.percentlossThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN percentloss <  n.percentlossThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_Packet Loss]

--,n.percentmemoryusedThreshold.Level1Value

from orion.nodes n

left join (SELECT count(NodeID) as [CPU Count], nodeid

FROM Orion.CPUMultiLoadCurrent

group by nodeid) cpu on cpu.nodeid=n.nodeid

left join Orion.VIM.HostThresholds htc on htc.host.NodeID=n.NodeID and htc.Name = 'VIM.Hosts.Stats.CPULoad'

left join Orion.VIM.HostThresholds htm on htm.host.NodeID=n.NodeID and htm.Name = 'VIM.Hosts.Stats.MemUsage'

where nodeid=${nodeid}

order by n.cpuload

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

wluther

Hi wluther,

is there any way to bring the Highest percent used for a Volume for the related Node? I am trying to add the link to the Volume I have listed in my SWQL Query, however I dont get any output on it.

Query:

++++++++++++++++++++++++++++++++++

select n.caption as [Node], n.detailsurl as [_Linkfor_Node], n.ip_address as [IP], n.MachineType as  [Machine],

,case

when vol.high is null then 'N/A'

when voldown.down is not null then (tostring(voldown.down)+' Down')

else (tostring(vol.high) + ' %')

end as [Fullest Disk]

,'/ui/perfstack/?presetTime=last30Days&charts=0_Orion.NodeID_'+tostring(NodeID)+'-Orion.Nodes.Vol.High;' as [_linkfor_Fullest Disk]

,CASE

WHEN voldown.down is not null then '/Orion/images/StatusIcons/Small-Down.gif'

WHEN vol.[High] >= 95 then '/Orion/images/StatusIcons/Small-Critical.gif'

WHEN vol.[High] >= 90 THEN '/Orion/images/StatusIcons/Small-Warning.gif'

WHEN vol.[High] >=  0 THEN '/Orion/images/StatusIcons/Small-Up.gif'

END AS [_IconFor_Fullest Disk]

from orion.nodes n

+++++++++++++++++++++++++++++++

fulldisk.PNG

fulldisk2.PNG

So If I click on the numbers like all those 99%, I land to the perfstack but without any data in it.

Could you please guide me through this? I'm desperately looking forward to any help you or anyone else can provide.

Thanks in advance

This is good stuff wluther​ I always get some of this kind of thing from KMSigma​ too!  I like to see the SWQL.

If you're not yet participating in the NPM 12.3 beta, I encourage you to do so

How do you get the Average Disk Reads and Writes? It seems to use a different node ID than the node.

I did a manual perfstack to see what the variable to use for the metrics that I wanted. It is using a different node ID for the volume metrics.

ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_1803-Orion.CPULoad.MinLoad,0_Orion.Nodes_1803-Orion.CPULoad.MaxLoad,0_Orion.Nodes_1803-Orion.CPULoad.AvgLoad;0_Orion.Volumes_772-Orion.VolumePerformanceHistory.AvgDiskWrites,0_Orion.Volumes_772-Orion.VolumePerformanceHistory.AvgDiskReads;

that is the volumeid for the drive you selected

I changed it to this and it doesn't load. What am I doing wrong?

SELECT 

Caption 

,'PerfStack_Template_A' AS [PerfStack-A] 

,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.Nodes_' + TOSTRING(VolumeID) + '-Orion.VolumePerformanceHistory.AvgDiskWrites,0_Orion.Nodes_' + TOSTRING(VolumeID) + '-Orion.VolumePerformanceHistory.AvgDiskReads;' AS [_LinkFor_PerfStack-A]

FROM Orion.Nodes 

gundamunit1​ The SWQL query does not know how to process the "VolumeID" variable, as it does not exist in the "Orion.Nodes" table. Having said that, you can easily link to the "Orion.Volumes" table via an implied join, by simply preceding it with the current location/table (Nodes), then the connection to the Volumes table, which is Volumes.

So, all you need to do is add a little bit before the (VolumeID), so it looks like this: (Nodes.Volumes.VolumeID)

gundamunit1​ Here is a link with a few more examples on how to jump around in a SWQL query, pulling values from various other tables, using that nice and easy implicit join method.

Re: SQL Query to return Node Caption and SN?

Let us know how it goes, and if you get it all working.

Thank you,

-Will

Dang Will you're getting to be the SWQL man!

ecklerwr1​ Well, I'm not sure I'd go that far, but thanks, I appreciate the encouragement. Some day I hope to actually know what I'm doing... or at least half-way know...

I find myself trying to do a better job of limiting data in PerfStack when I want to simply display data throughput for multiple interfaces on one or more devices.  For example, I have a pair of Cisco Nexus 7009 core routers, and I'd like to show only throughput in bits per second (or Mb/s or Kb/s) for every active interface.

When I select an interface from a router and drag it to the PerfStack section, I get far more than I want--I get EVERYTHING about that interface, when all I may want is current throughput.

Is it possible to just get Mb/s for an interface to show up in PerfStack?  Once I had only that info, I'd do the same for ALL active interfaces on these two core routers.  And I'd have a nice option to display historic or real time throughput instead of going through the manual process of building a new page, adding many custom html sources, pasting in the html display info, and modifying the node/interface ID for every window, and manually providing names for those custom windows.  That process worked well for me in the old days, but with PerfStack it seems I should be able to do it on the fly and generate the data and display much more easily than I'm used to (See my steps here:  Rick's Favorite Report #2: Creating Simple Custom Views Showing Multiple Interfaces' Bandwidth Util...

What can I do to only display throughput for multiple interfaces on two or more nodes in PerfStack, while filtering out other information about those interfaces?

wluther, I know this is an old thread, but I have recently found the need to create a template for a particular combination of node and interface data. As an MSP, we have numerous customers for which we must report on certain performance metrics on a monthly basis. For each customer, we will have a particular node and interface combination with particular metrics that need to be charted in a PerfStack project. Do you think your approach could be utilized possibly in conjunction with node/interface custom properties to make it easy to create a link to a customer-specific project for which the designated metrics are dynamically defined?

not wluther, but I can say I've done queries similar to what you are describing before to build out lots of perstack views quickly.  Tagged a property on a bunch of routers, then tagged the primary and secondary network interfaces appropriately, left joined each category of interfaces to the nodes, turned the whole mess into one long concatenated string of the metrics I wanted to show.

Then I was able to just click the links and hit Save for each location and add them to the dashboards.  All told it took maybe half an hour for me to create the query and then make 50 saved perfstack views. Saved a lot of time compared to doing them by hand.

That sounds like another good approach. I'm really looking for the option that requires the least amount of effort since we have more than 150 "customers" and I have to generate these projects and send/share them frequently with little or no warning.

Would you care to share an example of the queries you employ? I am typically interested in metrics like node availability, interface traffic and errors/discards over a 7 day, one month and 3 month history.

Thanks for your input.

Thing I like about perfstack is you don't have to build one for every time slice, you just build one and then if people want a different slice they can just click the time selection at the top of the screen.

The example in this post is basically exactly what I did.  Build out a single example with the charts you want and then copy the string from it, so this one shows responsetime and rx/tx for an interface on my router

https://myserver/ui/perfstack/?presetTime=last24Hours&charts=0_Orion.Nodes_28-Orion.ResponseTime.Avg..." title="https://orion.loop1.com/ui/perfstack/?presetTime=last24Hours&charts=0_Orion.Nodes_28-Orion.ResponseT...

Once I have that I just need to find where the node and interface id's go and write a query to swap those around for all the other cities I have based on some properties to identify routers and which interfaces are WAN ports

something along the lines of

select n.customproperties.City,

concat('https://myserver/ui/perfstack/?presetTime=last24Hours&charts=0_Orion.Nodes_', n.nodeid, '-Orion.ResponseTime.AvgResponseTime,0_Orion.Nodes_', n.nodeid, '-Orion.ResponseTime.PercentLoss;0_Orion.NPM.Interfaces_', n.interfaces.interfaceid, '-Orion.NPM.InterfaceTraffic.InAveragebps,0_Orion.NPM.Interfaces_', n.interfaces.interfaceid, '-Orion.NPM.InterfaceTraffic.OutAveragebps;') as [_linkfor_City]

from orion.nodes n

where

n.customproperties.DeviceType = 'Router'

and n.interfaces.customproperties.ConnectionType = 'WAN'

I guess I'm a little dense (That's why I have to ask), but it looks like on still has to "build" a custom project for each customer, city, etc, by changing the nodeID and/or interfaceID in the URL to match what belongs to that customer. What I am trying to do if possible, is have a link on a customer view that goes directly to their custom PerfStack project. There, they could change the time parameters, export the project, etc. to their hearts content. I just need a procedure for getting to this point. Sorry that I am not completely following your line of thought.

No you only have to build it once, the SWQL example would create a link for every distinct city in my example, but you would use whatever properties you have in your environment.

Does the client view have any kind of account or view limitations on it?  Those would work automatically to cut this widget down to only display nodes inside their view space.  Just load the same widget to everyone's dashboards and it will automatically filter to only things they have on the view.

Yes, each customer has their version of a Summary page that is account limited. I'll look at it some more and let you know if I have any issues. Thanks again for your help.

I would like to adapt this example for Node data to work with three pieces of data from interfaces: Total Bytes (Transmit + Receive), Discards (Transmit + Receive), and Errors (Transmit + Receive). However, I am having a little difficulty determining the exact variable format to place in the SWQL query to get this data. Can anyone tell me the best way to find this information? Thanks.

???

rluther, how would the Node query be adapted to query Interface dat such as Total Bytes (Transmit + Receive), Discards (Transmit + Receive), and Errors (Transmit + Receive)?

patriot​ The SWQL query itself is simply pulling just enough information to populate the URL, which is the most important part of this process.

If you go and manually build a perfstack chart, dragging and dropping your various metrics for an interface, then you will actually be building the URL. Just take that URL, and replace the SWQL query with the parts from the URL.

perfstack_custom_SWQL_query.png

For example:

This is the part that adds Interface Total Bytes.

0_Orion.NPM.Interfaces_22987-Orion.NPM.InterfaceTraffic.TotalBytes;

But, if you just added that, it would always show you the graph for that specific interface ID, 22987.

So, you need to swap that static entry out for the dynamic value, which will be populated by your SWQL query.

0_Orion.NPM.Interfaces_'+TOSTRING(interfaceID)+'-Orion.NPM.InterfaceTraffic.TotalBytes;

The line in the SWQL query would be something like:
,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.NPM.Interfaces_'+TOSTRING(interfaceID)+'-Orion.NPM.InterfaceTraffic.TotalBytes;'

As for the SWQL query itself, after updating the URLs you are building, you really shouldn't need to do much, other than point it from Orion.Nodes to Orion.NPM.Interfaces.

That got it. Thanks so much wluther!

I spoke too soon it appears. The query was accepted, but when I click on a project link, all I get is the chart for the Total Bytes. The other two data types are not displayed. i also get an error pop up on the chart page:

error 1.jpg

When I click for details, I get:

error 2.jpg

I suspect that somewhere in there are the details of what went wrong.

What next?

Thanks so much again.

patriot​​ I'm not sure what happened. What is the SWQL query you are using? In regards to only having the TotalBytes metric, and not the other two, you would simply need to add them. They seem to start with "0_", and end with ";".

Here is the query. Am I missinga JOIN statement?

SELECT 

FullName

,'Interface Traffic Stats' AS [Interface Traffic Stats]

,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.NPM.Interfaces_'+TOSTRING(InterfaceID)+'-Orion.NPM.InterfaceTraffic.TotalBytes;0_Orion.NPM.Interfaces_TOSTRING(InterfaceID)-Orion.NPM.InterfaceErrors.Discards;0_Orion.NPM.Interfaces_TOSTRING(InterfaceID)-Orion.NPM.InterfaceErrors.Errors;' AS [_LinkFor_Interface Traffic Stats]

FROM Orion.NPM.Interfaces

patriot​ It looks like the query is missing the "+" before and after the last 2 interfaceID variables. (and also the ' mark before/after them too)

Added those and still getting the error.

Here is the query now:

SELECT 

FullName

,'Interface Traffic Stats' AS [Interface Traffic Stats]

,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.NPM.Interfaces_'+TOSTRING(InterfaceID)+'-Orion.NPM.InterfaceTraffic.TotalBytes;0_Orion.NPM.Interfaces_+TOSTRING(InterfaceID)+-Orion.NPM.InterfaceErrors.Discards;0_Orion.NPM.Interfaces_+TOSTRING(InterfaceID)+-Orion.NPM.InterfaceErrors.Errors;' AS [_LinkFor_Interface Traffic Stats]

FROM Orion.NPM.Interfaces

patriot​ Still looks like you're missing the single quote for those 2 variables.

'+TOSTRING(InterfaceID)+'


Otherwise, it's not going to be able to properly close the string.

That did it finally. Thanks for your patience.

No worries. I'm glad you got it all working.

Let us know if you need anything else. And feel free to re-post your final query to share with others.

Thank you,

-Will

My pleasure. So here is a custom SWQL query to provide direct access to a custom PerfStack project for all interfaces. It includes Total Bytes (Received+Transmitted), Total Errors and Total Discards:

SELECT 

FullName

,'Interface Traffic Stats' AS [Interface Traffic Stats]

,'/ui/perfstack/?presetTime=last12Hours&charts=0_Orion.NPM.Interfaces_'+TOSTRING(InterfaceID)+'-Orion.NPM.InterfaceTraffic.TotalBytes;0_Orion.NPM.Interfaces_'+TOSTRING(InterfaceID)+'-Orion.NPM.InterfaceErrors.Discards;0_Orion.NPM.Interfaces_'+TOSTRING(InterfaceID)+'-Orion.NPM.InterfaceErrors.Errors;' AS [_LinkFor_Interface Traffic Stats]

FROM Orion.NPM.Interfaces

Version history
Revision #:
1 of 1
Last update:
‎05-19-2017 02:06 PM
Updated by: