In Part 1 of this article series we discussed basics of the SolarWinds Orion API & SDK, why you would use it, and how to get it. We also looked at some general concepts regrading APIs, REST and JSON. By the end of the first article, you should have either installed the pre-compiled MSI, or downloaded/cloned the repo from GitHub.

For this article we will be covering some basic usage of the SolarWinds Query Language (SWQL) Studio. Next, we’ll be querying our Orion poller with cURL and a REST client, showcasing the interaction with SolarWind’s API.

I’ll be bouncing back and forth between Mac OS X and Windows, just to illustrate some concepts. This isn’t to say you need OS X for anything, I just happen to be using this as my primary operating system. Most of what we’ll cover will actually be within Windows.

What is SWQL and SWQL Studio?

Although it originally stood for Semantic Web Query Language, I believe most refer to it as SolarWinds Query Language. After all, it’s a proprietary subset of SQL, only used within the SolarWinds universe. Similar to SQL, you can use SWQL to query the SolarWinds database for specific information spanning a wealth of data.

SWQL supports the following constructs from SQL:

SELECT … FROM …
WHERE clauses
GROUP BY clauses and HAVING clauses
ORDER BY clauses
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
UNION and UNION ALL
SELECT TOP n
SELECT DISTINCT
Subqueries in the SELECT clause
Subqueries in the FROM/JOIN clauses
SWQL supports the following aggregate functions:
SUM
MAX
MIN
AVG
COUNT
SWQL supports the following regular functions:
ISNULL
ABS

This may seem like a lot to digest if you’ve never used SQL queries before, but no need to stress. This is why SWQL Studio was created, to make query building much easier. Something to note: you must use CRUD operations to create, read, update, or delete entities.

Using SWQL Studio

The first thing you’ll want to do is open the SWQL Studio application, which should either been installed with the pre-compiled MSI, or via the downloaded repo from GitHub. You should see a window like this:

Simply enter the hostname of your main Orion poller, and your login credentials. If you’re using AD/LDAP, enter the domain name first just like you would when logging into the web portion of SolarWinds Orion NPM (e.g. domain\username).

Once logged in, you’ll be presented with a three areas. The area on the left side is your SolarWinds Information Service (SWIS) schema. The two boxes on the right are your query window (top), and the results window (bottom). Take some time to browse through the extensive list on the left, realizing just how much there is to choose from. Virtually endless options.

I’m going to start off with a very basic example. Say we simply want to get a list of our monitored nodes. You can de-collapse “Orion” and scroll down until you find “Orion.Nodes”. Right-click this and choose “Generate Select Statement”

This will automatically populate the query window based upon our selection, which encompasses everything below that Orion.Nodes parent we selected. Pay close attention to the SELECT and FROM commands; this is our SWQL lingo.

To make things a bit easier, I’m going to reduce my query down to just a few items in particular, let’s say: NodeID, ObjectSubType, NodeDescription, Description, Vendor, MachineType. After I’ve got my query narrowed down, I’ll press F5 (or go to Query>Execute) and retrieve the results in the bottom box.

And we have data! Actually, in my case, I have too much data. So, possibly I want to narrow this down to just a particular node. We can do that. If you scroll back up and take a look at the available constructs, you can probably get a hint at which is needed for this task. If you guessed WHERE, you’re right. Let’s add this to the query, and only find results where my NodeID is 6. Press F5 to query and you should see something like this:

We’ve narrowed our results down to this single node. Some entities can be connected, allowing you to traverse these relationships to pull data in the same query. Notice items with the icon resembling a link or chain:

This means you can reference these in relationship queries. For example, say I want to look for available IP addresses in a particular subnet. I can easily do that by querying the IPAM.IPNode entity. However, this particular table does not include the information I’m looking for, such as the Owner of the IP address, or the name assigned to that IP. These custom properties are actually stored in IPAM.IPNodeAttr. With relationship queries, we could write something like this below, where “I” is representing the relationship.

SELECT I.IPAddress, I.Custom.IPNodeId, I.Custom.Owner, I.Custom.Name_Assignment
FROM IPAM.IPNode I
WHERE I.IPAddress Like '10.150.111%'

Notice I’m specifying “Custom” in the Selection, signifying this query will be navigating that linked entity to retrieve the property. Note: I’m using a % symbol to represent a wildcard. Search to see our results.

You can dive deep into SWQL. If you’re looking for really advanced queries and a wealth community-driven examples, take a trip over to thwack. I stumbled upon an especially helpful article which also includes and downloadable XLS file with examples here. 

Remember, you can also browse SWIS using the schema reference link in the wiki.

Accessing SWIS via API

You’re playing around with queries, and ready to see what this is like when interacting via the API. Access to the SWIS API requires you attach to the Orion poller over HTTPS using port 17778. For example: https://orion.yourdomain.com:17778

If you look through SolarWinds Port Requirements document, you’ll notice that many of the modules utilize this port for communications with the Orion server(s). By building their applications on top of the SWIS API, SolarWinds as an organization and as a comprehensive suite of applications, extends this accessibility to the end-user. In other words, they themselves are using the API. You should too.

Arms day. Let’s do some cURLs.

The simplest way to demonstrate the API functionality is to perform some cURL requests. cURL is a command-line tool for transferring data to or from a server. In this case, we’ll be sending an HTTPS request to the SWIS API, and receiving JSON data in response. cURL is preloaded on Mac OSX, but it’s also available on Windows here, or download Cygwin and load it there.

Using cURL to send request to the API

In this example I’ll be doing a simple query. I’m looking for the IP address of a node. In SWQL it would look like this:

SELECT IPAddress
FROM Orion.Nodes
WHERE NodeID=7500

In cURL, it looks like this:

~$ curl https://orion:17778/SolarWinds/InformationService/v3/Json/Query?query=SELECT+IPAddress+FROM+Orion.Nodes+WHERE+NodeID=7500
~$ 
~$ 

Looks like the command was accepted, but I didn’t get any response. To determine what’s going on I’ll enable verbose mode and try again.

~$ curl -v https://orion:17778/SolarWinds/InformationService/v3/Json/Query?query=SELECT+IPAddress+FROM+Orion.Nodes+WHERE+NodeID=7500
* Trying 10.10.12.36...
* Connected to orion (10.10.12.36) port 17778 (#0)
* TLS 1.0 connection using TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA
* Server certificate: SolarWinds-Orion
> GET /SolarWinds/InformationService/v3/Json/Query?query=SELECT+IPAddress+FROM+Orion.Nodes+WHERE+NodeID=7500 HTTP/1.1
> Host: orion:17778
> User-Agent: curl/7.43.0
> Accept: */*
> 
< HTTP/1.1 401 Unauthorized
< Content-Length: 0
< Server: Microsoft-HTTPAPI/2.0
< WWW-Authenticate: Basic realm=""
< Date: Thu, 01 Sep 2016 19:34:01 GMT
< 
* Connection #0 to host orion left intact
~$

Ah, we’re getting an HTTP/1.1 401 Unauthorized. I need to authenticate to Orion. To do so I’ll pass my username in the syntax. Note that I’m using a domain, and must include this. Use a double backslash to escape it from the shell.

~$ curl -v -u lab\\dvarnum https://orion:17778/SolarWinds/InformationService/v3/Json/Query?query=SELECT+IPAddress+FROM+Orion.Nodes+WHERE+NodeID=7500
Enter host password for user 'lab\dvarnum':
* Trying 10.10.12.36...
* Connected to orion (10.10.12.36) port 17778 (#0)
* TLS 1.0 connection using TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA
* Server certificate: SolarWinds-Orion
* Server auth using Basic with user 'lab\dvarnum'
> GET /SolarWinds/InformationService/v3/Json/Query?query=SELECT+IPAddress+FROM+Orion.Nodes+WHERE+NodeID=7500 HTTP/1.1
> Host: orion:17778
> Authorization: Basic TE9MWWVhUmlnaHQh
> User-Agent: curl/7.43.0
> Accept: */*
> 
< HTTP/1.1 200 OK
< Content-Length: 40
< Content-Type: application/json
< Server: Microsoft-HTTPAPI/2.0
< Date: Thu, 01 Sep 2016 19:35:19 GMT
< 
* Connection #0 to host orion left intact
{"results":[{"IPAddress":"10.10.0.42"}]}~$ 
~$

And look at that: we have our results! We sent a request to the Orion SWIS REST API and received a response in JSON. Righteous.

Troubleshooting Certificate Errors

If you receive certificate validation errors, try throwing the -k flag in the curl command.

Using a REST client

While I enjoying doing curls haxor-style on the command-line, it’s often more valuable to use a robust REST client. These clients often provide easy-to-use interfaces, archiving, debugging, and “pretty” printed responses. You can get real clever and send advanced headers, attach scripts, and turn all the knobs with the click of button, rather than memorize commands and flags. I’m using a REST client called Postman. There are dozens of client and browser plug-in options. Try a few out and find the one that works best for you.

In my REST client, I’m going to do a similar request, but instead ask for some more information. I want the location, street address and city of a particular node in my environment. These are custom properties that have been user-added to the system. In SWQL it looks like this:

SELECT I.CustomProperties._Location, I.CustomProperties.Address_1, I.CustomProperties.City
FROM Orion.Nodes I
WHERE I.Caption = 'DVARNUM_2960'

So my query looks something like below.  Notice how I concatenate the commands with “+” symbols.

https://orion:17778/SolarWinds/InformationService/v3/Json/Query?query=SELECT+I.CustomProperties._Location,I.CustomProperties.Address_1,I.CustomProperties.Address_City+FROM+Orion.Nodes+I+WHERE+I.Caption='DVARNUM_2960'

First thing to do is start up your REST client.

Next we’ll want to add authentication. In Postman, go to Authorization. Choose your authentication type, then enter your credentials. Click Update Request to submit.

You should notice the Headers tab is highlighted. Click it to see the Authorization request hashed as a parameter in the headers to be sent.

Similar to the cURL request, we’re going to query the SWIS API, but via the REST client. Leave GET as our query method, and enter your query in the bar next to it. Press send. As long as your query is accurate, you should receive the results back as JSON data.

Using a self-signed certificate, or having SSL problems?

If you’re using a self-signed certificate, or you have some other SSL certificate-related issues, you can disable validation. Go to Settings, and turn off SSL certificate validation. I’m not condoning this as a best practice; I’m simply pointing out the option in case your environment requires yet. A valid SSL certificate for SolarWinds Orion is always preferred.

Update a custom property field via the REST API

So far all we’ve done is essentially query the database via the API. We haven’t actually made any field changes to the database. Although our options are slightly more limited than the web interface, custom property values is something we can certainly change via the API. This is helpful for bulk changes, automation scripts, and click-less interaction.

To start, I’m going to do a slightly modified query. This time I want a full output of all my custom property values for a particular node. No specifics, just give me the full list. My query looks like this:

https://orion:17778/SolarWinds/InformationService/v3/Json/swis://orion/Orion/Orion.Nodes/NodeID=7500/CustomProperties

Notice above that the “Address_2” field is empty. My goal here is to update that with the suite ID of the apartment complex. My suite number is “API”. In order to make changes, I’ll need to POST data rather than plainly GET data. Additionally, I’ll need to tell the remote end what I intend to post. Since our data exchanges are JSON-formatted, my post statement should look like this:

{ "Address_2": "Suite API" }

Go to the Body tab, select raw, and then choose JSON as the data format. Paste the string above in, and click Send.

In the response field below you’ll receive an HTTP 200 OK response and a “null” in the body text. This is normal. To see if this actually executed as planned, let’s switch back GET and execute the same query.

The “Address_2” field is now populated with our suite number “Suite API”. To reiterate, we made a written change to the Orion database via the REST API. If I pull up the familiar SolarWinds Orion web interface and look at the properties of this node, I’ll see, as expected, the changes reflected here as well.

Up Next

In the next article of this series we’ll be looking at Python and PowerShell scripting, and how this can be utilized to automate tasks, enhance workflows, add value and open doors to multitudes of possibilities.  

References

How to use SolarWinds Query Language (SWQL – SWIS)

SWQL Syntax