Select Page

Custom Query: Software Inventory Using A Custom Query On A SolarWinds Dashboard

by 1, Jul, 2022Blog Posts, Dashboards & Custom Views, Systems Management

SolarWinds® Orion® has a cool feature that allows us to create an interactive searchable table, which we are using here as we need one place to go to see all software installed across multiple machines that are within SolarWinds. This can be completed by using the Custom Query widget and creating a SWQL script to pull the exact data we want from different tables and presenting it in a way that is pleasing to the eye and clear to understand.

Requirements: To be able to successfully complete this guide, below is required to be able to replicate this view in your own environment.

Select Inventory (Insight Image) - Prosperon Networks

We are using SWQL Studio here as it allows us to build the query efficiently, view the data we require, so we can take this in to the web UI widget configuration. The data can be pulled from multiple tables within the database using an easy to understand query language based on SQL, called SWQL (SolarWinds Query Language).

Open SWQL Studio and connect to the SolarWinds Application Server with your account that has admin level permissions.

Find the table down the left hand side that has most if not all the data you require to present your view. The table we will be using to complete this task is ‘Orion.AssetInventory.Software’. Right-click and click ‘Generate Select Statement’, which will immediately give us a query that shows all available columns in this Orion SWQL Entity and is a great starting place to build our final query.

On the top bar hit the play button this will show you the top 1000 records in this table and what detail is under which column

Select Top (Insight Image) - Prosperon Networks

Now we need to decide what columns of data on this view we want to show and what we are missing. The only column here which isn’t useful is the NodeID and what we really need is the Name of the Node so it is clear for the person seeing this table which node it is referring to.

We are going to go with a best practice approach to SWQL/SQL queries, by utilising the ‘alias’ syntax, which also means we don’t have to type Orion.AssetInventory.Software everytime. In the query after Orion.AssetInventory.Software give it an alias using a letter/s which here I am keeping it simple and use ‘S’. I have also reorganised the columns to show in the order I prefer and removed the ‘NodeID’ column.

(Tip: make sure there is a comma after every column except the last column otherwise you will receive an error).

Query 2 (Insight Image) - Prosperon Networks

Another benefit of using aliases is that to add another column, start typing s. after a comma and SWQL Studio will display columns not listed in the original select statement are displayed for automatic completion. This also includes linked SWQL entities  (related tables) which we can use to present the data we want to see in a clean efficient manner.

Query 2 Description (Insight Image) - Prosperon Networks
These connected entities can be seen on the left hand side of the SWQL Studio UI, under the current table and are displayed in the list with a chain link icon to indicate that this is a linked entity meaning we can use the inherent navigation properties available in SWQL without the need to create what can be complex JOIN statements.
Orion Asset Inventory (Insight Image) - Prosperon Networks

Within the Orion.AssetInventory.Polling entity there is a column named Caption so we can use this by typing S.Polling.Node.Caption then hit execute query.

Query 2 Node (Insight Image) - Prosperon Networks
Query 2 Caption (Insight Image) - Prosperon Networks

We should also add the abbreviation for the entity to all columns used and I have also reordered putting the caption as the first column.

Query 2 PROS (Insight Image) - Prosperon Networks
(TIP: To prevent a large data set being returned and generating unnecessary load on the database, always use TOP #of records when using the SWQL studio to control the number or records which will be returned before executing the query. This will speed things up and we will remove this once moved to the web console as we can tell it how many to show on the page).

Lets Move to the Orion Web Interface

We could end there by copying the text into a custom query on the web console and show it in its raw form and it would look ok. But we really want to improve the look and feel of the widget to fit in with the rest of SolarWinds Web Console.

We can make it more interactive by showing if the node is up and being able to go to that node from the query.

Firstly we need to name the column we want to make it easier to reference, to do this after the column we add “AS [Node Name]”. We need to use square brackets when using a word that is used as a column header or if we want to add spaces into the column header like [Node Name] or [Server ID] for example, I have also done this for the INSTALL DATE column.

Query 2 PROS AD 03 (Insight Image) - Prosperon Networks
There are a couple of ways to add the status icon to the node but here we use the following to create a new data column in the query output:

[code]’/Orion/images/StatusIcons /small-‘ + ToString(s.Polling.Node.StatusIcon) AS [_IconFor_Node Name][/code]

[code]’/Orion/images/StatusIcons /small-‘[/code] Is the URL location on the server of the images folder. This will be found in the Inetpub folder where your website is stored, with the + used to join two parts together. The second section after the +

[code]ToString (s.Polling.Node.StatusIcon)[/code] Is a dynamic value taking the output of s.Polling.Node.StatusIcon and forcing it to be text so it can be used to complete the URL string.

The AS is naming the column but [_IconFor_Node Name] is advising the custom query to use this as a web link for a specific column as before the square brackets is due to the column name having a space within it. This will show as a column within SWQL Studio with the correct URL in it the fields but when copied to the web console the column is used as a reference and not shown in the table.

Query 2 PROS 2 (Insight Image) - Prosperon Networks
To make the Node Name a clickable link we use the ‘LinkFor_Node Name’ where LinkFor_ tells Orion how it should be used.

[code]s.Polling.Node.DetailsUrl AS [_LinkFor_Node Name][/code]

Select Top 1000 (Insight Image) - Prosperon Networks
Here is the full query to use in the Custom Query Widget:

s.Polling.Node.Caption as [Node Name],
‘/Orion/images/StatusIcons/small-‘ + ToString(s.Polling.Node.StatusIcon) AS [_IconFor_Node Name],
s.Polling.Node.DetailsUrl AS [_LinkFor_Node Name],
s.InstallDate as [Install Date],
FROM Orion.AssetInventory.Software s[/code]

Notice how this not only provides a link to the Node detail page, but also includes the hover over function, which means we may not even need to click into the detail page to get what information we needed.

Installed Software (Insight Image) - Prosperon Networks
Extending the Widget to make it Searchable and Interactive!

We can also enable the search function of this widget to give the ability of filtering and decide what columns to search through by adding the where clause on the end. This is simply a case of using the variable for the text input ${SEARCH_STRING} and including in the WHERE clause:

s.Polling.Node.Caption as [Node Name],
‘/Orion/images/StatusIcons/small-‘ + ToString(s.Polling.Node.StatusIcon) AS [_IconFor_Node Name],
s.Polling.Node.DetailsUrl AS [_LinkFor_Node Name],
s.InstallDate as [Install Date],
FROM Orion.AssetInventory.Software s
WHERE s.Polling.Node.Caption LIKE ‘%${SEARCH_STRING}%’ OR s.Name LIKE ‘%${SEARCH_STRING}%'[/code]

Edit Resource Installed Software (Insight Image) - Prosperon Networks
We can give it a title, subtitle and select the number of rows to be shown on the first load.
Installed Software Search By Device Name (Insight Image) - Prosperon Networks
We can quickly create a view using these steps for any data within the SolarWinds Database and show it on any dashboard or NOC as it is required. Other examples would be to show windows patches with Patch Manager, show firmware versions to find out which devices are compliant and which are not. Show MAC addresses in one place rather than visiting each node the opportunities are endless if it’s in SolarWinds you can present it in a way that is useful for the user.

Consultancy: SolarWinds Customisation Services

Chris Dodds

Chris Dodds

Senior SolarWinds Engineer

Chris Dodds is a Senior SolarWinds Engineer at Prosperon Networks, and a SolarWinds THWACK MVP. As a SolarWinds Engineer, Chris helps customers meet their IT monitoring requirements with SolarWinds.

Consultancy: SolarWinds Customisation Services

Related Insights From The Prosperon Blog
Share This