Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

SDK SWQL to Database Manager Conversion and JOINS.

I have a fairly simple query I've created that I now want to use in an air-gapped environment and has no access to SSMS or the SDK. As a consequence I am really struggling to convert it to a format that the Database Manager understands.

The query is:

SELECT nd.Client, ncp.NodeID, ncp.Name, ncp.Type, ncp.TypeName, ncp.TypeDescription, ncp.Caption, ncp.FullName, ncp.IfName, ncp.InterfaceName

FROM Orion.NPM.Interfaces AS ncp JOIN
     Orion.NodesCustomProperties AS nd ON ncp.NodeID = nd.NodeID

And whilst I've found the equivalent tables in (I think) in the DB Mgr (Interfaces - and - NodesCustomProperties) I can't seem to covert the query code to something the DB understands.

Whilst I appreciate you fine folks will probably get me up and running with the relevant answer, longer term, is there anywhere I can go and read/watch a video/listen to a podcast/etc that will show me the correlations and the how to or gotchas in converting from one format to the other?

Secondly: JOINS

I think I have basic joins worked out - see above, but when I try to add data from a third table in I seem to screw it all up. Are there any real simple 101 type lessons on JOINS that are specifically aimed at SolarWinds rather than generic SQL?

Find more posts tagged with

Accepted answers

All comments

wluther

When you say you don't have access to SSMS or the SDK (Orion, yes?), do you also mean you cannot access your Orion webpage or DB remotely?

The Orion SDK can actually be installed on any system that can access your Orion webpage, as it connects to the same address, and uses your same credentials, which also applies your same permissions.

The DB manager (SolarWinds version) is installed on primary server, and each additional server. SSMS can be installed on any computer which has access to the DB server, but might require different credentials than your Orion login, specifically for the database.

Now, are you needing/wanting to use SSMS or DB manager for SQL, and work with tables directly in the DB, or use the Orion SDK to work with SWQL? While manipulating the DB directly certainly offers more options, using the Orion SDK to run SWQL is the safest and most "future-proof", and can usually accomplish most tasks.

When it comes to JOINS, if you are using SWQL, you can take advantage of the implied joins, which should be described in the "SWQL Walkthrough" link below (I think).

In general, these have been very helpful links for me, and I still reference them from time to time when I get stuck on something.

Orion Platform SWQL, Thwack! Now I got it

Intro to API, SDK & SWQL

Intro to SWQL Studio

SWQL Walkthrough

wluther

And here is a link to an older post I used as an example for someone else. While it's not exactly what you're asking for, I think it can be used to help understand the basics of joining tables in SWQL.

I'm pretty sure some of my screenshots in this post have been lost, as I posted this at least 2 THWACK versions ago.
thwack.solarwinds.com/.../146125

wluther

To use your query as the example, instead of using an actual JOIN, you can do the following:

(I've made a few changes to your aliases to help me follow it a little easier.)

Using the Orion SDK to follow along, we are going to start with our SELECT'ing of the Orion.NPM.Interfaces table.

Nothing fancy here. This just returns the captions for the first 10 results (TOP 10) from your interfaces table. (I like to use TOP 10 while I'm testing so I'm not always pulling back tens of thousands of results every time.)

SELECT TOP 10
i.Caption
FROM Orion.NPM.Interfaces AS i

If you scroll down a bit further in the Orion SDK, you will see rows with an icon that looks like a link. These are the other tables in the DB to which you can "jump" directly to from the table you are currently on.

All you need to do is start with the alias you used for your current table (i), then add a period and type the alias of the linked table (Node).

SELECT TOP 10
i.Caption
,i.Node.Caption
FROM Orion.NPM.Interfaces AS i

Now you should have the interface caption, as well as the node caption each interface is from. And you've done this without the need for a traditional JOIN.

From there, if you scroll up to the Orion.Nodes table in the SDK, you can find the various tables linked under that table.

From here, let's jump to the NodesCustomProperties table.

SELECT TOP 10
i.Caption
,i.Node.Caption
,i.Node.CustomProperties.Client
FROM Orion.NPM.Interfaces AS i

You can even use this method to filter results too.

SELECT TOP 10
i.Caption
,i.Node.Caption
,i.Node.CustomProperties.Client
FROM Orion.NPM.Interfaces AS i
WHERE i.Node.CustomProperties.Client = 'Customer1'

In regards to simple 101 type lessons for JOINs in SWQL, this would be my vote for most valuable lesson.

mesverrum

I'll mention when converting between SWQL and SQL one thing that trips people up are there are occasionally columns in the DB with slightly different names than their SWQL counterpart, or that exist in SWQL but don't even exist in SQL. I don't see any of those in your query but it does jam people up sometimes when you run into those. When in doubt just select * and see what you get and remove what's not needed afterward.

As for your third table, what are you trying to join, and how are you doing it, and in what way do you feel it is screwing up?

There's nothing especially unique about Orion SQL versus any other database work, we have mostly normalized tables with primary keys that we join to other related tables. What can jam someone up is a join that is mixing many different types of objects together, that can get messy. For example you are working with the interfaces table and a nodes table, if you tried to bring a volumes table into the same query without some sql wizardry you are going to end up with a situation where you get a bunch of strange seeming duplication in the data. You can't easily combine dissimilar data types in SQL without more complex stuff like unions and subqueries. You'll notice that in all the GUI driven parts of the UI they lock you down to an object type and once you pick the type of object you are working with it will essentially lock you out from adding certain other types of objects and data, that's the devs trying to prevent you from joining some crazy combinations of tables that would create a report that makes no sense.

stuartd

As ever Will @wluther my thanks for the replies. And, more reading for me

In answer to the why I want/need o use the DB Manager instead of other option, it is simply because this is a secure, air-gapped environment that requires multiple hoops to be jumped whilst doing a back-flip and landing on one finger before they even consider looking at the change request to install them.

Considering the size of the environment that is more effort than it is worth.

So, all I really want is to replicate what I'm doing with the SDK/SWQL with a few basic queries. I have no intention of editing, deleting or otherwise changing the DB. We (now) have a Lab for that purpose.

Off to read - thank you.

stuartd

I shall attempt this

stuartd

Thank you Marc @mesverrum ...

If I knew how I was screwing up, I wouldn't be here.

But, as far as I know, I wasn't trying to do anything too fancy, just wanted to pull in the names of the nodes as well. I don't recall the error now, but can re-try later and see how I get on.

But first I'm going to go and do some (re)reading.

[quote userid="245123" url="~/product-forums/the-orion-platform/f/report-lab/89732/sdk-swql-to-database-manager-conversion-and-joins/286224#286224"]There's nothing especially unique about Orion SQL versus any other database work[/quote]

Other than the operator at this end not being a database person in any way shape or form.

If there was a course someone could recommend, I may even be able to get my employer to pay for it - or we may already have one available. I shall have to go searching.

stuartd

@wluther

[quote userid="99062" url="~/product-forums/the-orion-platform/f/report-lab/89732/sdk-swql-to-database-manager-conversion-and-joins/286220#286220"]In regards to simple 101 type lessons for JOINs in SWQL, [/quote]

I am sort of following this, and have re-created my above query using this approach and that is ok insofar as it does what my other query does. I say sort of because it will likely take me many efforts of repeating this to get it to stick - which is not to say I understand, just it will be there and will be useful but I couldn't tell someone why

But it doesn't get me any closer to running this from the DB, which in this one off environment is the only thing we have for managing the DB, queries, etc...

mesverrum

I don't teach this class myself anymore now that I'm not a Loop1 employee, but I'd still recommend this as a decent place to begin:

https://loop1.com/solarwinds-public-training/

The 301 is SQL/SWQL/Powershell and can REALLY step up your game as an Orion admin, but also applies to any software that uses SQL and/or an API.

stuartd

Thanks Marc - that looks useful. I have done the 101 or its equivalent via Kenson in the UK - which I believe is a part of Loop1 anyway. That was very useful at the time as we knew so little about the product that had been dropped on us from a great height.

I'd say I'm fairly comfortable with most front of house aspects, day to day admin, etc and so the 301 looks perfect to take that next step. Will definitely look into it more.