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

Intro to SWQL Studio

Level 11

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 1.png

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.

SWQL Studio Entities.png

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.

SWQL Autocompletion.png

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:

  SWQL Objects.png

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).

SWQL Object Properties.png

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.

SWQL Generate Statement.png

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.

  SWQL Field Types.png

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.

SWQL Keys.png

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

SWQL Standard Fields.png

Whereas properties that are inherited are display as green cubes:

  Inherited fields.png

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.

Linked Fields.png

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.

  SWQL Verbs.png

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

31 Comments
Level 12

Great article series. Looking forward to more.

Level 10

I can't wait to see more.

MVP
MVP

Nice, I really want to learn SWQL so that I can further customize my installations of the various modules.

Level 8

Thank you for the awesome Post!

Level 9

Great info to pass along to my Clients!

Level 7

where can I find the endpoint to connect to SolarWinds VMAN server.

I have installed SWQL studio on the same server on which VMAN is installed.

Using this "https://xx.xx.xx.xx:17778/SolarWinds/InformationService/v3/Json/Query" I am able to connect the server from outside and get the data from solarwinds database.

But, if I use same endpoint and credentials to connect with solarwinds from SWQL Studio. I can't.

Can some one help me in making use of this SWQL Studio.Screen Shot 2018-05-24 at 5.05.00 PM.png

In your box, you have "https://" entered once, then "https (server name):  That can't be correct, can it?

Even worse, the second "https//(name):" is missing a colon after the https.

I suggest you remove the first "https://", then insert the missing colon between "https" and "//" and see if it works.

pastedImage_0.png

Level 8

Hi Team,

I have installed Orion SDK. Now I want to add server. Can you guide me please and what types of access require during adding server.

Looking your quicker response.

Regards

Sandeep

Level 11
Level 16

Thanks for the great posting. The retirement of Report Writer and the need for reports that I couldn't get out of the box sent me over to the SWQL Studio to get the job done. Now it's my go to tool.

Today I needed to create a report of what nodes were assigned to each team (via custom properties) and SWQL Studio had the answer for me in minutes.

Level 8

Hi cmazko,

Thanks for your response.

Now can I able to connect my server using SWQL and can write the query & getting the result too. Till here no any problem.

Now problem is, I am getting very few records which is not correct. We have aprox more 400 nodes but I am getting result for only 43 nodes. What could be the reason? Please help. Its really import for me. Query and result as below:

pastedImage_0.png

Regards

Sandeep

Level 8

Intro to SWQL Studio

Hi Team,

I am waiting for your response. Eagerly looking solution.

Need to submit report to our senior managers and struggling till now about getting all data from sever. I have explain my problem in my previous post.

Regards

Sandeep

Level 16

I used your query in my environment and it returns the correct value. Are you sure you are connection to your production environment and not your test environment?

Level 13

Sandeep, the query that you've written should give an accurate count of all the nodes that are managed by Orion.  When you say that you have 400 nodes, are you sure that all 400 nodes are currently managed (monitored) by Orion?  You can run a SWQL query like this to get details about the nodes that Orion knows about.  Do you see particular nodes in the Orion GUI that are not returned by this query?

SELECT

    n.NodeID,

    n.IPAddress,

    n.Caption,

    n.Description

FROM Orion.Nodes n

Level 8

Hi bobmarley

Thanks for your response.

Yes, Connected to our production environment only. I checked in solarwinds gui with the same IP, data is completely different from SWQL. Only 10% data getting from SWQL query or we can say, only last 2 month data getting through query. Is there any limitation set in orion database while configuring user?

Please help. Manually capturing data takes huge time aprx 2 days.

Regards

Sandeep

Level 8

Hi dan jagnow

Thanks for your response.

I checked in solarwinds gui with the same IP, data is completely different from SWQL. Only 10% data getting from SWQL query or we can say, only last 2 month data getting through query. Is there any limitation set in orion database while configuring user?

Yes, all nodes are currently managed by Orion and available in database. and same situation with almost all tables.

Please help. Manually capturing data takes huge time aprx 2 days.

Regards

Sandeep

Level 13

It is definitely possible to set up account limitations that would affect what data your account has access to:

https://support.solarwinds.com/SuccessCenter/s/article/Account-Permissions-and-Limitations-Video

Are you using the same account to connect to SWIS that you are using to log into the GUI?  You should see the same data through both if you are using the same credentials.  In other words, if account limitations are in place, they should take effect in the GUI and when running SWQL queries.

Do you have multiple Orion installations on your network?  Is it possible that you are connecting to an additional poller or a test installation instead of the same server that you're connecting to via the GUI?

Level 8

Hi dan jagnow

Looks issue has been fixed now and getting all the data through query.

Not sure what was the problem, I removed all the connections, clear all memory/cache & again setup/connect account. This works.

Thanks for your all support/help. Will come to you if any challenges in future.

Regards

Sandeep

Level 13

I'm glad to hear it's working now, Sandeep, though it would be more satisfying to understand what was causing the problem earlier.  Good luck with your project!

Level 8

Thank you so much dan jagnow

Level 8

Hi Team,

Need to your help.

How can i get data through query, how many Alert configured (Active+All) on a particular Node? I am referring Orion.Nodes (IPAddress field) and Orion.AlertConfigurations table but couldnt find any relationship between both tables. Is there any table/database for Alert record? I can see here, data is available in SolarWinds GUI but not able to get through query.

Below snapshot for your reference. Could you please provide right query on this regards?

pastedImage_0.png

Regards

Sandeep

Level 8

Eagerly looking your response.

Level 13

See this thread: Quick way to look through all alert trigger definitions for a specific custom property? .  As far as I know, it's not possible to gather this information with a SWQL query, but you should be able to use a SQL query on AlertDefinitionsView.

Level 8

Thanks for the reply dan jagnow​ .

Will check this thread & will let you know if any difficulty.

Level 8

Hi dan jagnow​,

Again come to you with small query

What is the exact meaning about the following Nodes status?

  • Unknown
  • Down
  • Unmanaged
  • External

Little confused about the Node status.

Regards
Sandeep
Level 11
Level 8

Its not much useful and still didnt clear my doubt. Just wanted to one liner definition for clear my doubt.

Level 13

There is a very useful discussion of how node status is calculated for Orion Nodes here:

Orion Platform 2019.2 - Enhanced Node Status

There is also useful information here:

Troubleshoot nodes and interfaces that are Unknown

Unmanaged vs External

I'll try to summarize for the particular statuses you listed:

  • Unknown - The node cannot be polled for response time and status.  This is also the initial status before the first poll has been completed.
  • Down - The node appears to be down.  After a single poll fails, Orion will enter a fast-polling mode and mark the node as down after repeated failures.
  • Unmanaged - Orion knows about this node, but it is not polling the node.  It won't trigger alerts.  Unmanaging nodes during maintenance windows could be useful to avoid unhelpful alerts.
  • External - This is a node you don't own.  This is similar to Unmanaged, since the node is not polled.  However, you can assign applications to external nodes.  An example would be using an HTTP monitor for google.com.
Level 8

awesome dan jagnow​.

This is exactly i want. Thanks for your prompt response with valuable answer.

Level 7

Great summary of the SWQL Studio!  thanks for the overview

Level 9

Hi,

we have a problem with SWQL Studio in v2.6.0.227-beta and v2.6.0.238-beta.

After connecting to our Orion production instance we get this error message box and the entity tree does not load:

        "Don't know how to handle element Properties"
But the connection was established and queries are working. Only the Object explorer (entity tree) on the left is empty. Used this versions some days ago and had no issues.

The version v2.5.0.214 does not have this problem!

Have also no problem with all 3 versions when connection to our Orion test instance. Seems to be a problem with the beta versions in combination of a specific Orion installation.

Regards,

Hermann