This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SDK SWQL to Database Manager Conversion and JOINS.

stuartd over 3 years ago

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?

Top Replies

Parents

0 mesverrum over 3 years ago

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.
Cancel
Vote Up +1 Vote Down

Cancel
0 stuartd over 3 years ago in reply to mesverrum

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.

mesverrum said:
There's nothing especially unique about Orion SQL versus any other database work

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.
Cancel
Vote Up 0 Vote Down

Cancel
0 mesverrum over 3 years ago in reply to stuartd

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.
Cancel
Vote Up +1 Vote Down

Cancel
0 stuartd over 3 years ago in reply to mesverrum

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.
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 stuartd over 3 years ago in reply to mesverrum

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.
Cancel
Vote Up 0 Vote Down

Cancel

Children

No Data