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

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.

• SolarWinds Server & Application Monitor (SAM) installed
• Servers monitored with the ‘Asset Inventory’ under list resources selected

• Account with Customise View rights for creating the Orion widget
• Optional: SWQL Studio installed – https://github.com/solarwinds/OrionSDK/releases
  • Orion Administrator level account

Process:
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

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

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.

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.

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.

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.

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

[code]SELECT
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.Name,
s.InstallDate as [Install Date],
s.Publisher,
s.Version
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.

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:

[code]SELECT
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.Name,
s.InstallDate as [Install Date],
s.Publisher,
s.Version
FROM Orion.AssetInventory.Software s
WHERE s.Polling.Node.Caption LIKE ‘%${SEARCH_STRING}%’ OR s.Name LIKE ‘%${SEARCH_STRING}%'[/code]