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!
- When you need to include information that’s not covered in an out of the box Orion report
- If you need to automate the addition of node to Orion for monitoring, as part of an onboarding process for new VMs
- 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:
- Intro to API, SDK, & SWQL (This page)
- SWQL studio
- SWQL Walkthrough
- Examples of SWQL in reports/alerts/web/ etc
- Automating Orion using PowerShell
- Automating Orion from Linux & some bonus tips ‘n tricks
The overall goal here is ri 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.
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 endeavor, 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.
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.
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
- Group by
- Order by
All exist in SWQL, along with functions such as
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)
FROM [Interfaces] I
left join [Nodes] N on N.NodeID = I.NodeID
Running this would output
ORION11 vmxnet3 Ethernet Adapter
With SWQL, this simply becomes
SELECT TOP 5 Caption ,N.Interfaces.Name
FROM Orion.Nodes N
Gives the same results! Moreover, because it’s read-only, you cannot really break anything.
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 these topics in the following resources: