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

Top Replies

  • 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 emoticons_happy.png

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