The best place to start for most queries is with the “Generate Select Query” command and then manually editing the query from there. The large text block created when using this can seem overwhelming but remember that you probably do not need all the returned properties.
Base Query from Orion.Nodes
SELECT TOP 1000 NodeID, ObjectSubType, IPAddress, IPAddressType, DynamicIP, Caption, NodeDescription, Description, DNS, SysName, Vendor, SysObjectID, Location, Contact, VendorIcon, Icon, Status, PolledStatus, StatusLED, StatusDescription, NodeStatusRootCause, NodeStatusRootCauseWithLinks, CustomStatus, IOSImage, IOSVersion, GroupStatus, StatusIcon, LastBoot, SystemUpTime, ResponseTime, PercentLoss, AvgResponseTime, MinResponseTime, MaxResponseTime, CPUCount, CPULoad, MemoryUsed, LoadAverage1, LoadAverage5, LoadAverage15, MemoryAvailable, PercentMemoryUsed, PercentMemoryAvailable, LastSync, LastSystemUpTimePollUtc, MachineType, IsServer, Severity, UiSeverity, ChildStatus, Allow64BitCounters, AgentPort, TotalMemory, CMTS, CustomPollerLastStatisticsPoll, CustomPollerLastStatisticsPollSuccess, SNMPVersion, PollInterval, EngineID, RediscoveryInterval, NextPoll, NextRediscovery, StatCollection, External, Community, RWCommunity, IP, IP_Address, IPAddressGUID, NodeName, BlockUntil, BufferNoMemThisHour, BufferNoMemToday, BufferSmMissThisHour, BufferSmMissToday, BufferMdMissThisHour, BufferMdMissToday, BufferBgMissThisHour, BufferBgMissToday, BufferLgMissThisHour, BufferLgMissToday, BufferHgMissThisHour, BufferHgMissToday, OrionIdPrefix, OrionIdColumn, SkippedPollingCycles, MinutesSinceLastSync, EntityType, DetailsUrl, DisplayName, Category, IsOrionServer FROM Orion.Nodes
The above is the default generated query from the Orion.Nodes table for a 2022.1 Hybrid Could Observability installation.
If you are following our query best practices, you’ll notice that no aliases are used – either on the properties or the entity itself. Additionally, this is a humongous block of text that is nearly incomprehensible to the layperson.
Readability Fix (optional)
For a quick fix (for readability), do a Find & Replace replacing the commas in the above query with a better option.
Find & Replace Settings:
- Find: "
," (comma, and a space) - Replace: "
\r\n ," (backslash r, backslash n, five spaces, comma, and a space) - Search Type:
Extended
Click Replace All, and you’ll be presented with an easier to comprehend result.
SELECT TOP 1000 NodeID
, ObjectSubType
, IPAddress
, IPAddressType
, DynamicIP
, Caption
, NodeDescription
-- Additional properties omitted for brevity
, EntityType
, DetailsUrl
, DisplayName
, Category
-- , IsOrionServer
FROM Orion.Nodes
This format is much easier to understand and makes it easy to omit unnecessary properties by adding the double-dash (--) to the beginning of the line(s).
There are other methods to accomplish the same formatting, but the above can be done completely within SWQL Studio.
Aliasing Fix
Using the Find and Replace function again, you can easily add your aliases. First: add an alias to the FROM clause (we’ll use ‘AS [Nodes]’).
Find & Replace Settings:
- Find: "
," (comma, and a space) - Replace: "
, [Nodes]." (comma, space, hard open bracket, alias name, hard close bracket, period) - Search Type:
Standard
Once you replace all, that’ll update the formatting to:
SELECT TOP 1000 NodeID
, [Nodes].ObjectSubType
, [Nodes].IPAddress
, [Nodes].IPAddressType
, [Nodes].DynamicIP
, [Nodes].Caption
, [Nodes].NodeDescription
-- Additional properties omitted for brevity
, [Nodes].EntityType
, [Nodes].DetailsUrl
, [Nodes].DisplayName
, [Nodes].Category
-- , [Nodes].IsOrionServer
FROM Orion.Nodes AS [Nodes]
The last thing you’ll need to do it add your entity alias manually to the first line.
SELECT TOP 1000 [Nodes].NodeID
, [Nodes].ObjectSubType
, [Nodes].IPAddress
, [Nodes].IPAddressType
, [Nodes].DynamicIP
, [Nodes].Caption
, [Nodes].NodeDescription
-- Additional properties omitted for brevity
, [Nodes].EntityType
, [Nodes].DetailsUrl
, [Nodes].DisplayName
, [Nodes].Category
-- , [Nodes].IsOrionServer
FROM Orion.Nodes AS [Nodes]
Why bother at all?
Again, we’re going to go back to the basics here and remind everyone that SWQL Studio is a “data” tool – it is not a human readability tool. Going back to the basics repeatedly may seem trite, but it’s significantly easier to comprehend what you are requesting when the query itself is laid out cleanly and is easy to comprehend. This format also makes it easier to reorder your fields (since they are each stored on a single line and are far easier to cut and paste).
Additionally, when working with entity aliases, SWQL Studio can help you understand the navigation properties and properties as you type your queries.
Aliases Inform Navigation Properties
The primary reason to reformat with entity aliases is to gain easier access to navigation properties and their respective child properties. If we wanted to get information about the volumes assigned to these nodes, we can just start typing and the Intellisense will kick in to assist.
Volumes is the name of the navigation property bound to Orion.Nodes.
Caption is a property on the Orion.Volumes connected navigation property.
We’ve aliased the Volume’s caption to a better name, and our results are easily comprehendible.
With aliases, we can continue to go further. For your own experimentation, connect to volume performance history and pull in average disk writes and observation timestamp.
You now have more than the 1000 rows that you are limited to by the SELECT clause.
If you wanted the maximum of the average disk writes broken down by the disk, but including the node, and only for the last 24 hours, you can start building that using the examples from earlier in this chapter.
SELECT TOP 1000 [Nodes].Caption
-- , [Nodes].IPAddress -- Not needed in results
-- , [Nodes].NodeDescription -- Not needed in results
, [Nodes].Volumes.Caption AS [Volume Caption]
, MAX([Nodes].Volumes.VolumePerformanceHistory.AvgDiskWrites) AS [MaxAvgDiskWrites]
-- , [Nodes].Volumes.VolumePerformanceHistory.ObservationTimestamp -- Not needed in results
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Volumes.VolumePerformanceHistory.ObservationTimestamp >= GETDATE() - 1
GROUP BY [Nodes].Volumes.Caption
, [Nodes].Caption
You can see that although SWQL Studio is a powerful tool, knowing the basics of query construction and some best practices can help you take this tool even further.
Entity Mutability
Each entity has included documentation which will provide information on the actions available on the entity in question. If we continue to work with the Orion.Nodes entity, we can see that this entity supports Read, Create, Update, and Delete. This is further indicated by the icon for the entity which includes a ‘pencil’ overlay indicating that at least some edits are possible.
Additionally, this entity shows actions beyond standard SELECT query on the right-click menu.
To delete an entry, you’ll need the SwisUri value.
For updates you’ll need the SwisUri and the field you wish to alter.
Uniform Resource Indicators
A Uniform Resource Indicator (URI) is a string which contains a unique identifier to a specific element within the SolarWinds platform. Unlike other identifiers (like NodeID, InterfaceID, VolumeID, etc.) this is a unique string containing all the information pertaining to a single element within your monitoring. They follow a standard “format,” and can be constructed based on the element type and known property information, but this is generally not recommended. Simply add “Uri” to many of your queries to get the URI for a matching element.
The URI is not part of the standard output for a query but can be exposed by either adding the “Uri” property manually to an existing query, or by right-clicking and selecting “Generate Select Statement (with Inherited Properties).”
This secondary query builder option will generate a new query will all the properties and inherited properties. In typical uses, this is unnecessary since most inherited properties are aliases of other properties (for naming consistency across the platform), except for exposure of the URI.
In the event that the URI is not returned, that particular entity does not contain said field – and this is by design.
Entity Verbs
As stated earlier, verbs are one of the ways you ask the platform to do things on your behalf outside the standard create, read, update, and delete (CRUD) actions. There are hundreds of verbs available, and each contains their own set of parameters.
Working from a simple example, we can use the API to request that a node be polled.
The parameter list here includes only one parameter. This parameter is expected to be a string and is called netObjectId. This is one of two commonly seen parameters for unique identification of an element (with the other being the URI). Each verb will normally require either the netObjectId or the URI to identify the target of the action of the verb. URI is more commonly utilized for newer verbs and netObjectId is more common for legacy verbs. Refer to the parameter list of the verb in question as to which should be used.
If we wanted to use this verb to immediately poll all Windows nodes in our environment, we would need the netObjectId for these nodes. Although not explicitly documented within the API, the netObjectId will already be familiar to users of the system, as it is what is typically added as a URL parameter to an element details web page. Network object IDs all follow the format of an alphabetic prefix, a colon, and a number.
This can be retrieved using a simple query (where we will also be including the Uri for completeness).
In the previous query, we are using a string function (CONCAT), which concatenates the properties listed. The full list of functions and their uses can be referenced on the Orion SDK Wiki page for SWQL Functions.
If we wanted to immediately send a poll request for the “Home Desktop,” we can invoke the PollNow verb and pass ‘N:12’ as the value for the parameter.
When we click the “Invoke” button this information is sent to the API and the result is returned and displayed in the lower pane.
You can see that the result here is “true” meaning the request was sent successfully. Other verbs may return other information – it will always be displayed in an XML format when using SWQL Studio.
Reporting SWQL Studio Problems
Since SWQL Studio is an open-source, community developed tool, the SolarWinds Support team doesn’t support it as a product. If you find a problem or would like a new feature added to the tool, the best way to provide feedback is to create an Issue on the Orion SDK GitHub project. Please provide as much detail as possible to assist the developers.