Dig Deep with Data Mining in SolarWinds

So you’ve installed your SolarWinds software and it’s been happily collecting status and statistics data for weeks, months, or years. You’ve used the out-of-the-box reports, but what if you want to roll up your sleeves and get into the raw data?

Plan A: Dive into the Database

There are a couple of problems with this.

First, it’s uncharted territory. While the database is organized it’s also normalized, so unless you know where to look and how the tables are related, you’re going to have a tough time finding what you need. Second, if there are changes, and let’s face it, there are always going to be changes, your reports may break or, worse, cause unpredictable performance issues.

Plan B: Use the SolarWinds Query Language (SWQL)

SolarWinds had the foresight to create a proprietary SQL-like language called SolarWinds Query Language and, along with it, a utility called SWQL Studio (below):

This application is laid out like your typical SQL client: things to query on the left (entities), a query window in the upper right, and the results in the bottom right. Since there are so many entities to choose from, they’re even organized by namespace (think of them as folders).

So in a world that already includes SQL and SQL Studio, why use SWQL?

SWQL Studio

It’s Resistant to Change

There’s nothing worse than upgrading your system then to have the reports and integrations you’ve written fail because something you’re depending on isn’t backward compatible.

If you use SWQL to write your reports, you can be sure that SolarWinds is going to do their best to make sure that any underlying changes to the database schema don’t affect you. For example, Network Configuration Manager hasn’t been called Cirrus in a very long time, but that namespace remains even though it’s been duplicated and expanded in its own NCM namespace.

Finding Related Information is Much Easier

In addition to having access to all of the data by way of the entities you can find related information using linking properties. You can start with Orion.Nodes and from there you can access a slew of data without having to learn how the entities are related:

SWQL Studio

You can certainly use joins if you want (inner & left are supported) but if you only need a few fields from another table, it’s a convenient way to go. If you wanted to include a column for the engine (poller) that a node is assigned to, the query below would do the trick:

SWQL Studio

Finally, Automation

If you have any aspirations for automating some of the things you have to do in SolarWinds they’ve got you covered with 495 functions they call “verbs” ranging from adding nodes to building groups and dependencies.

SWQL Studio

Some of the things you can easily automate:

  • Adding Devices to Your Inventory
  • Adding Interfaces to Nodes
  • Assigning UnDP Pollers to Nodes & Interfaces
  • Assigning Application Templates to Nodes
  • Creating Groups & Dependencies
  • Deploying Agents
  • Discovering Interfaces on Nodes
  • Running NCM Scripts Against Nodes

For examples on using a few of these, you can check out:

SolarWinds’s repository on GitHub and of course we’re eager to help you find your way in the OrionSDK forums on Thwack.

Steven Klassen
Programmer Analyst