Understanding the Database layout - help please

In my efforts to better understand my role, and to move forward I need some (a lot?) help in understanding the DB layout / setup / whatever.

Caveat: This is not about me making changes to the DB, it is about helping me understand where to find what I need to make reports, build queries, enable automation checks, etc and maybe (just maybeeee) down the line start making changes.

So, at its base level, I can see an entry e.g. APM_HardwareAlertData (View)

Now, I know that this view is a combination of tables (is that the right word) but my initial question is how do I see what tables this view is pulling its data (records?) from? The column headings are what I am calling tables:

1. So, sticking with this hardware health example, how do I find where the table IsPollingEnabled (for example) held? As for the life of me I can't find it in any of the sub-tables. This is probably my primary issue - trying to identify where the data is stored.

2. Then my next query is, if I am to start using the SDK, then how do the names in the DB correlate to the SDK view?

p.s. if there is any SOLW specific training, or blog/Thwack post you recommend behind this then please do point that out, or alternatively, if there is a web-based resource, or indeed a book then I'd love to know about those as well.

I'm sure I'll have more questions but this is my starter for 10

Find more posts tagged with

npm

ncm

Accepted answers

All comments

stuartd

For the record I should add that I know about, have watched, read or otherwise gibbered over the following content:
.
.
Use SolarWinds Query Language (SWQL) in the Orion Platform - Support Article:
https://support.solarwinds.com/SuccessCenter/s/article/Use-SolarWinds-Query-Language-SWQL?language=en_US

SDK Discussions Home:
https://thwack.solarwinds.com/t5/Orion-SDK-Discussions/bd-p/orion-sdk-discussions

Thwack Posts
https://thwack.solarwinds.com/t5/Product-Blog/Intro-to-API-SDK-SWQL/ba-p/506794 - Intro to API, SDK & SWQL
https://thwack.solarwinds.com/t5/Product-Blog/Intro-to-SWQL-Studio/ba-p/445186 - Intro to SWQL Studio

GitHub Intro Page:
https://github.com/solarwinds/OrionSDK/wiki

EPISODE #86 - ORION SDK 101: INTRO TO POWERSHELL AND ORION API
https://thwack.solarwinds.com/t5/SolarWinds-Lab-Archive/Episode-86-Orion-SDK-101-Intro-to-PowerShell-and-Orion-API/ba-p/591384

EPISODE #91 – CUSTOMIZING THE ORION PLATFORM WITH THE SOLARWINDS API AND SWQL
https://thwack.solarwinds.com/t5/SolarWinds-Lab-Archive/SolarWinds-Lab-Episode-91-Customizing-the-Orion-Platform-With/ba-p/604812

bobmarley

Load up SWQL Studio and almost everything you will need for reporting purposes is under the Orion.Nodes table. If you need some specific queries to get you started post here what you are trying to get and the community is pretty good about providing examples.

stuartd

@bobmarley thanks for that but I feel I need a step back from that and need to understand what I'm looking at better first.

As to providing examples, then I'm all up for that when I have something concrete to ask for, but sadly that has not been my experience to date.

bobmarley

Once you load the SDK up and attach to your database try expanding the items on the left. The Orion.nodes table has most of the information that you would need to use for reports. Most of the items are named pretty well once you expand them out. If you want to see the data inside them the right click on the item and use the create query option. That will show you all of the available fields for that table. From there you can start narrowing your query down to specific items within the table and most tables will have a large number on items you won't need. Just remove those from the query. That should get you started. There are quite a few query examples here on Thwack.

Seashore

The SQL database and the Information service structure is not always the same. So many times they differ alot. So if you are new I recommend you start learning either SQL och SWQL as you will be confused on where to find things.

There are advantages with both SQL and SWQL but I think that if you are using it for reports, custom tables, custom queries etc, you should go with SWQL as that is best supported within orion, it supports the user permissions, limitations etc.

If it's only reports or getting the data externaly, SQL is better... most of the time.

In SQL, a view is "the result of a query". Can be just one part of a table or a complicated combination of queries. A way of "saving" a commonly used query so to speak.

Many of the "tables" you see in SWQL Studio is most likely SQL views or kind of views. SWQL tables are there to stay, SQL tables may change in names and structure as Orion evolves.

APM Hardware health. First we should know that SAM was once called APM, and it kept that name in db and SWQL.
Some hardware polling is made by NPM and in SWQL you find it at Orion.HardwareHealth.....

Hardware inventory that comes with SAM you find in Orion.AssetInventory....

Hope above helps some.

stuartd

@Seashore it most certainly does help some but also adds some confusion

I had high hopes of the pandemic being over sooner than it is, and then I could get my Comp Sci child to visit and he could do me a face to face "walk through" of SQL and get me started. But with him being a crtitical national infrastructure (CNI) engineer at present, and away from home frequently it's been tough to even get facetime sessions with him, let alone quiz him on SQL, etc.

Thus me coming here.

dunky2k

@stuartd Do you have access to the Database via SSMS?

If so open up SSMS and expand your Database to the 'views' section and then find the dbo.APM_HardwareAlertData view.

If you right click on this and select the 'Design' option this will show you what the query does and what tables it pulls its information from.

stuartd

@dunky2k yes we do and that little bit of info is absolute gold.

Thank you.

stuartd

And to prove I've followed this through then I followed the: IsPollingDisabled' table.

This is taken from dbo.APM_HardwareInfo which is itself yet another view, but something I spotted is that the IsPollingDisabled doesn't actually exist as a heading, and is instead a rename of the IsDisabled entry(table/record ??) - something else I didn't know could be done.

Anyway, in the 2nd view (HardwareInfo) I can see that gets its details from: dbo.HWH_HardwareInfo - which if I open is thankfully not a view.

So now I can hopefully follow views in future to track down specific entries and even further into the future I will be able t use those entries to enable changes.

@Seashore Oh, and one thing I forgot to mention in my reply to you. You said: "SAM was once called APM" which is interesting in that we don't have or use SAM so it makes me wonder why this view, which shows all/most of our monitored hardware, is under APM. Just a muse really, as it bothers me not where it is so long as I can track the info down.

Seashore

@stuartd , I can't explain why you have a SAM/APM view even though you don't have SAM. It's strange. Have you tested SAM before and uninstalled?

But it's good you now finds your data.