Product Blog

2 Posts authored by: Michael Halpin Employee

Greetings! And welcome to the second in our series of primers on customising the Orion® Platform. Today’s post will focus on first installing and then navigating through SWQL Studio. This free utility is simple to use, but can drastically lower the barrier to creating and editing SWQL queries.

Help Resources

Before we go any further, it’s worth highlighting two key resources for using the SDK.

  • The GitHub®
  • And the THWACK®

 

The GitHub site is the main resource, where you can download the installer, browse code samples, and review the schema and wiki for the SDK itself. It’s also where issues are tracked, so if you do find a bug you can flag it as an issue there.

The other main resource is the Orion SDK forum here on THWACK. SolarWinds does not provide pre- or post-sales support on any customisations, including code using the Orion SDK. However, that does not mean you are alone. You can often find code samples that may address your use case by simply searching the forum. And the site is also frequented by not just SolarWinds staff and THWACK MVPs, but other users who can also provide feedback and guidance on code that you may be working on.

Installing SWQL Studio

SWQL Studio is a Windows®-based utility, and is included with the Orion SDK. The installation is simple. Just navigate to the “releases” area of the GitHub site (https://github.com/solarwinds/OrionSDK/releases) and download and install “OrionSDK.msi” on your Windows workstation.

Once installed, connect it to your Orion server (of course, you will be working on your special dev server).

 

   SWQL Studio

 

Navigating through SWQL Studio

The connection itself is made to SWIS on the server (the same Information Service covered in the previous post). By accessing via SWIS, instead of SWQL itself:

  • Separate logins to the actual SQL database itself are not needed
  • Account limitations are still applied
  • Any changes to the database schema are abstracted from the user

Once connected, you will see the server you connected to, as well the credentials you used, and if you expand on this connection icon, you will see the various data sources represented. Note, the list of objects will vary depending on the modules installed and the versions of those modules.

 

 

To the right of the object list you will see an editor screen. From here, you can type in SWQL, and execute the query to see the results. Running the query is very simple:

  • You can click Query-> Execute in the menu
  • Or simply hit F5 or Ctrl-E

In the below example, I typed out a simple query. We will talk more in the next post about how to write queries. I just want to draw your attention to the fact that in this latest version of SWQL Studio (2.3.0.123), autocompletion is also possible.

 

A lot of the power of SWQL Studio however, lies in the fact that the necessary data can be viewed graphically.

Let’s expand on the Orion item in the list:

 

 

Then scroll down to Orion.Nodes, where we will see the resource referenced in the hand-written SWQL statement I used previously. If I expand this resources I will then see all of the properties of this Orion.Nodes object, two of which I had referenced in the aforementioned SWQL statement (caption, and IP_Address).

 

 

A key difference between SQL and SWQL is that there is no equivalent to select* in SWQL. However, just by right-clicking on the entity within SWQL studio, you can “Generate Select Statement,” which will populate the editor window, with all the fields populated.

 

 

Another useful aspect is the ability to identify the property type by its icon. To explain a bit further, let’s refer to Orion.Syslog as an example.

 

 

 

First up, we have the keys, which are indicated by the golden key icon. These are essentially the unique IDs used to reference each individual record in the data source.

 

 

The most common properties are the standard fields, indicated as blue rectangular prisms:

 

 

 

Whereas properties that are inherited are display as green cubes:

 

 

 

A very interesting aspect here is that related entities (depicted with the chain icon below) are also shown here. That means you can see which of these can be used to implicitly join data, without having to write an explicit join.

 

 

And as a final point, verbs (represented as pink prisms) that can be used in scripts are listed here, as well the parameters needed for each verb. We will cover this very cool feature in a future post.

 

 

 

Wrap Up

  Now that we have taken a solid look at SWQL studio, you should be in an excellent position to dive in and look at the data available within Orion, for use in reports and scripts. Not only does it allow you to easily create SWQL that you can use for various purposes, it also allows you to parse the entities within the database, including related objects.  With the next post, we will actually work with SWQL queries, and some of the constructs that differentiate SWQL from SQL. And in follow-up posts, we will see how the verbs can be used to provide management capabilities from

Greetings All!

 

On the SolarWinds Sales Engineering team, my colleagues and I often get requests from customers regarding how to do something custom, whether it is simply viewing certain data about a node on its node details page, or perhaps it is something more complex, such as automating putting devices into maintenance mode, as part of a workflow, or used to create runbooks.

 

Over the coming weeks we will have a series of “primers”, to equip you with the skills needed to create and adapt scripts & queries, within the Orion® Platform.  If you need to address any of these use cases, or similar, then this is the series for you!

  1. When you need to include information that’s not covered in an out of the box Orion report
  2. If you need to automate the addition of node to Orion for monitoring, as part of an onboarding process for new VMs
  3. If you require usage metrics for particular devices, so you can chargeback to other departments or customers

 

To begin with, we will introduce some of the terms and concepts involved, starting with some architecture basics, and building through to more hands on examples, looking at custom reports & scripts.

Topics will include:

  1. Intro to API, SDK, & SWQL
  2. SWQL studio
  3. SWQL Walkthrough
  4. Examples of SWQL in reports/alerts/web/ etc
  5. Automating Orion using PowerShell®
  6. Automating Orion from Linux® & some bonus tips ‘n tricks

 

The overall goal here is enable you to work through, and find solutions for your particular use cases. What this series will not be is:

  • An introduction to SQL
  • An introduction to scripting/programming
  • Pre-built solutions for every custom use case

 

We want to help you help yourself.

 

Read First!

Before we look at a single piece of code or query, let’s just take a moment to cover some important housekeeping. As with any customisation, especially when scripting there is always a possibility that things may go wrong. While automating manual processes is a most excellent endeavour, accidently deleting all your nodes is not! So before you begin working on any customisation, let’s just take a moment to cover a few simple best practices.

  • Set up a dev instance of the Orion Platform for experimentation
  • Don’t try untested scripts on production systems
  • Make a backup of your Orion database

 

So, with that, let’s get on with the show.

 

Terms and Concepts

First up we will introduce a few terms. If you are an advanced user you can probably skip ahead at this stage, but if new to writing queries and scripts, having a strong understanding of these at the very beginning can save a lot of hardship further on down the line.

 

SolarWinds Query Language (SWQL). SWQL (pronounced “swick-le”) is essentially a read-only subset of SQL with some SolarWinds conveniences added, and will be core to many of the topics that will be covered in the following posts. The third post in our series in particular will dive into SWQL in more detail, but at this stage we will look at some high-level points.

Application Programming Interface (API). In software development terms, an API is can be thought of as the access point for one piece of software to access another. In an N-tier application it allows different parts of an application to be developed independently. Orion, for example is N-tier, and web, polling, reporting, and coordination components communicate via service layers.

In the context of Orion, the API is what allows to read data using SWQL, as well as adding, deleting and updating data “invoking” commands (which we will examine in more detail in our 5th and 6th posts.)

SolarWinds Information Service (SWIS). The actual implementation of the API within the Orion Platform is embodied as SWIS, which manifests a Windows® service, the SolarWinds® Information Service.  It is via SWIS that other Orion Platform products (such as Network Atlas, Enterprise Operations Console (EOC) and Additional Web Servers) communicate. It is also via SWIS that various scripting and programming technologies can be used to access Orion.  From a technical perspective, it can be accessed over two ports:

  • 17777 – net.tcp: high performance but Microsoft® only-
  • 17778 – JSON or SOAP  over HTTPS - interoperability with other programming languages

 

Software Development Kit (SDK). An SDK is a set of tools and libraries, provided by a vendor, to allow others to more easily consume their API. In relation to Orion, the Orion SDK can be installed on Windows, and provides not only the files needed to use PowerShell scripts, but also includes SWQL Studio, which can be used to build custom SWQL queries and visually browse the available data. It is worth noting that since it’s possible to access the API using REST, you don’t need to have the Orion SDK deployed. Our next post will cover installing the SDK, and some tips for its use.

 

Intro to SWQL

SWQL can be hand-written, or more commonly, the SWQL studio can be used to generate queries. For simplicity, at this early stage, it’s worth noting that constructs from standard SQL such as

  • Select x from y
  • Where
  • Group by
  • Order by
  • Join
  • Union

 

All exist in SWQL, along with functions such as

  • SUM
  • Max
  • Min
  • Avg
  • Count
  • Isnull
  • Abs

 

A key point to note here however, is that update, insert and delete are not supported via SWQL itself. Those use cases are supported outside of SWQL and will be covered at a later point.

A major differentiator however is that SWQL automatically links many related objects without joins. This makes writing queries much simpler and more efficient.

 

For example, if we want to select the caption of the nodes in an Orion instance, and also list the interface names for each interface on those devices, using traditional SQL we would end up with something similar to

 

SELECT TOP (5)

    N.[caption]     

      ,[InterfaceName]

       FROM [Interfaces] I

       left join [Nodes] N on N.NodeID = I.NodeID

 

Running this would output

Caption                InterfaceName

ORION11            vmxnet3 Ethernet Adapter

mysql01              eno16777984

mysql01              lo

mysql01              eno16777984

bas-2851.local   VoIP-Null0

 

With SWQL, this simply becomes

SELECT TOP 5 Caption ,N.Interfaces.Name

       FROM Orion.Nodes N I

 

Gives the same results! Moreover, because it’s read-only, you cannot really break anything.

 

Wrap Up

With today’s post we’ve laid the foundations of the customizing the Orion Platform. We’ve identified some use cases where the API can be used to both read information from, or make changes to your Orion Platform  And to make the series “real”, we’ve seen a short SWQL example, that gives a good introduction to the power of using SWQL over SQL within the Orion Platform.  In the next post we will begin to get hands-on, by installing and navigating through, the Orion SDK. But in the meantime, you can discover more about the topics covered in the SolarWinds Lab episode SWIS API PROGRAMMING CLASS.

Filter Blog

By date: By tag:

SolarWinds uses cookies on its websites to make your online experience easier and better. By using our website, you consent to our use of cookies. For more information on cookies, see our cookie policy.