cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Intro to API, SDK & SWQL

Level 11

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

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.

27 Comments
MVP
MVP

I don't currently do a lot of this type of work with our installation, but when I do I post a question on Thwack. The results from the community are quick and accurate. However, I really am looking forward to learning more of this so that I can build and/or customize more on my own.

Level 13
  • Set up a dev instance of the Orion Platform for experimentation

Any recommendations on how to do this?  Licensing? one-for-one mirror of prod or...?

Level 12

Love it and love SWQL - have been customizing with it on dashboards and reports for years now.  Also... (pronounced “swick-le”) - who knew?

Level 12

Also as a general suggestion, I would love to see PIVOT functionality written into SWQL.  Its the one major annoying thing that is missing - I work around it with various JOINs, but PIVOT would be so much more to the point and useful for those custom query dashboard resources.

Level 11

Hi josh.haberman​, good question!

For one-off testing you can use a trial install on a test machine. Trial versions will work for 30 days, and are a good option for testing.

For something more permanent, the best option would be to contact your account manager, and they can cover the options for licenses for small test environments.

Level 11

Good idea matt b​.

You can create feature requests Github, by logging an issue on Issues · solarwinds/OrionSDK · GitHub

Level 8

I'm fairly new to SW and SWQL.  I appreciate the detailed explanations and step by step instructions.  Please keep 'em coming. Jim

MVP
MVP

So glad to see this topic getting touched on, it could just be my own experience but I feel like the technical capabilities of the Thwack community have really grown since I first started using the product several years ago and leveraging SWQL and the API have been great help in expanding the capabilities of the tools.

Level 12

@Michael Halpin Does this article need some updating? This query in SWQL Studio 2.3.0.123

SELECT TOP 5 Caption, N.Interfaces.Name

FROM Orion.Nodes N I

Give me this error "mismatched input 'I' expecting 'EOF'

2018-05-17 08:36:35,951 [8] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null)  Exception running query:

SELECT TOP 5 Caption, N.Interfaces.Name

FROM Orion.Nodes N I RETURN XML RAW

2018-05-17 08:36:35,951 [8] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null)  Exception for Operation: <s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">

018-05-17 08:36:35,951 [8] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null)  Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

SolarWinds.Data.SWQL.SWQLSyntaxException: mismatched input 'I' expecting 'EOF' ---> Antlr.Runtime.MismatchedTokenException: A recognition error occurred.

   at SolarWinds.Data.SWQL.Parser.SWQLParser.MismatchIsUnwantedToken(IIntStream input, Int32 ttype)

   at Antlr.Runtime.BaseRecognizer.RecoverFromMismatchedToken(IIntStream input, Int32 ttype, BitSet follow)

   at Antlr.Runtime.BaseRecognizer.Match(IIntStream input, Int32 ttype, BitSet follow)

   at SolarWinds.Data.SWQL.Parser.SWQLParser.statement()

   --- End of inner exception stack trace ---

   at SolarWinds.Data.SWQL.Parser.SWQLParser.statement()

   at SolarWinds.Data.SWQL.SWQLParser.Parse(String query)

   at SolarWinds.Data.Query.Engine.QueryProcessor.ProcessInternal(String query, IQueryExecutionContext context)

   at SolarWinds.InformationService.Core.QueryPlanCache.GetQueryPlan(String query, String username, Boolean federationEnabled, Func`1 buildQueryPlan)

   at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer)

FWIW, this query runs fine

SELECT TOP 5 Caption

FROM Orion.Nodes

MVP
MVP

Nice write up. I'm in the process of learning swql.

MVP
MVP

Yeah that I would appear to be a typo

What are the chances of getting an update to the highlighted link?

pastedImage_0.png

MVP
MVP

Sorry for digging up an old thread, but does anybody know is it possible to add formatting directly into the SWQL Code? For example, I want to centre align some of the output fields like you can when you create a Custom HTML Table.

MVP
MVP

You could use concat() to wrap your rows in html formatting tags, then instead of using the custom query resource you would use the custom table resource, give it a custom SWQL datasource, and check the box to allow it to use html. 

MVP
MVP

This is where I get stuck. If I use a Custom query then I can use _IconFor which is great but when it’s displayed the status icon is very close to the Node name (maybe it’s just my OCD). I’ve managed to use ToString as a replacement for Cast as a semi workaround but it’s not perfect.

if I use a Custom Table then _IconFor doesn’t work properly, now I can use CAST for the StatusLED and align it within the table field which helps my alignment issue but is that the right way to display the Status Icon? I’ve seen some people add the StatusLED as a individual header and just clear the name field within Custom Table and out it just before The Node so it looks like they are together.

I guess just like most things SolarWinds there is more than one way to skin the cat!

Any thoughts on best practice / practical usage? I’m finding that I will always try to build custom query where possible now, but sometimes Custom Table (and even SQL) just work well enough.

MVP
MVP

For me personally I do everything in SWQL with the custom query resource simply because it is MUCH faster for me to copy/paste the query into my client environments from my lab.  On the other hand, you get much more control over how things look if you are using the custom table resource.  You can get fancy with stuff like column sizes, line spacing, fonts and sizes, whatever, but that's a lot of extra clicking around for me week after week so I have been avoiding it.  Since I recently built my view migrator tool I've been thinking about revisiting the issue, since that thing makes it trivially easy to inject an entire view with everything already in place so I don't have to click as much.  In the near future it will probably be worth the extra time for me to redo most my SWQL in SQL to take advantage of that extra control over the presentation since the tool will bundle it all up for me.

Level 11

Sorry folks, missed this one, indeed a typo.

Level 11

Sorry, missed this, fixed now

Level 7

Very Helpful and I like the break down. Thanks.

MVP
MVP

This is an area where i definitely want to learn more. Thanks for the post.

Level 10

Hi Michael Halpin

First, thank you so much for putting this great learning material. It's is very helpful.

I am just curious to know if you are planning to add section 4, 5 and 6?

Specifically 4 is what I would be very interested in.

Level 7

Hi Community,

Great work! I am pretty new to solarwinds.

Question: Is there a way to integrate network devices in solarwinds using the API ? As there are hundreds of devices and its killing my time doing it manually in GUI. Thanks in advance.




MVP
MVP

This would probably help

Success Center

Level 12

Seconded ...

mesverrum​ - any idea when these steps will be cimpleted?

MVP
MVP

No idea, not my blog series.  I am actually presenting on topics close to 4 and 5 today at the NY SWUG in a few hours though if you happen to be on Long Island haha.  Not sure if they will be recording any of it or not, worst case I might be able to just post my slide deck somewhere afterward.

New York, New York: July 10-11, 2019

Level 12

New to SWQL. Was building a query for duplicate nodes like this:

select t1.NodeID,t1.ObjectSubType,t1.SysName,t1.Dns,t1.IP_Address,t1.Caption,t1.DynamicIP,t1.Vendor,t1.IsServer,t1.MachineType,t1.LastSync from Orion.Nodes As t1
INNER JOIN (
SELECT TOP 10000 IP_Address, COUNT(*) AS Total FROM Orion.Nodes
Group By IP_Address
HAVING COUNT(IP_Address) > 1
)
cte on cte.IP_Address = t1.IP_Address

 

Any way to use a variable to defined the column IP_Address to make the query more portable should I wish to change the column on which I search (like T-SQL offers) ?

nsjah