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

Basic Actions in SWQL / Database Manager, etc

So via a combination of reading here, brute force try X, - that didn't work so try Y, etc; I have hit upon a few basic queries to help me search through the database.

I know I need to take this much further and so turn to the assembled brains here. As my company has helpfully blocked access to github I can't go and do any searches etc over there, so please if you need to point me at anything then do so here or other places on t'webs.

So, for arguments sake, as a very basic search I can do the following:

SELECT TOP 1000 * FROM [dbo].[Interfaces]
WHERE InterfaceName like '%unrouted%'

I also know that I can do an

AND FullName like '%abc_xyz%'

to pull back unrouted interfaces where the name is like abc_xyz. So far, so good.

Now, let's suppose I want to take all those unrouted vlan interfaces (which is what my serach gives me) and change them from being monitored to not monitored. Is there a simple way forward for this? Or at the very least, is there a search I can do to discover which interfaces are unrouted and being monitored?

Next step would be to consider doing delete and tidy-up operations (if necessary).

As an aside, are these types of opertions the sort of thing that should be run in SWQL Studio or as a script from say, Powershell? Which would then be my next hurdle.

Find more posts tagged with

Accepted answers

All comments

stuartd

As a further query, how do I do a search query across multiple tables...

Say

dbo.Nodes and
dbo.Interafces

Seashore

First, against the SQL-database, with Database manager, you ask T-SQL queries.
Against Solarwinds Information Service you ask SWQL queries, Solarwinds Query language.

They are similar but not the same.

With SWQL you can't change things, only read.I suggest you look at the SWQL studio, download it for free, and try that. In some parts of the gui you can only use SWQL.

Here is an example of SWQL vs SQL, giving the same result:

SWQL:

SELECT TOP 10
N.Caption AS [NodeName]
,I.Caption AS [InterfaceName]

FROM Orion.Nodes N
INNER JOIN Orion.NPM.Interfaces I On N.Nodeid=I.NodeID

SQL:

SELECT TOP 10
N.Caption AS [NodeName]
,I.Caption AS [InterfaceName]

FROM NodesData N
INNER JOIN Interfaces I On N.Nodeid=I.NodeID

mesverrum

So when you search interfaces it is only including monitored interfaces. Every result you see in your scenario is potentially something to be deleted from the interfaces table but you will want to get your SQL comfort levels way up before you start deleting (and make sure you have very backups when you try).
Back when I first decided I needed to learn SQL to make SW easier I started here:
https://www.w3schools.com/sql/sql_intro.asp
And if you are going to make changes and inevitably screw something up learning how to use this stuff can save you a lot of headaches
https://www.mssqltips.com/sqlservertutorial/3305/what-does-begin-tran-rollback-tran-and-commit-tran-mean/

stuartd

@Seashorethank you. That makes sense as to why the structure / layout is different. Fortunately we have the downloaded SDK from just before my company locked down Github so we do have that to use and play with. I have put in a request to open it back up but those wheels turn incredibly slowly.

Here is an example of SWQL vs SQL, giving the same result

I hadn't heard of T-SQL before, and also wasn't aware that Studio is read only, not that I'm planning on changes just yet as need to imrove my knowledge first.

stuartd

As ever, my thanks Marc.

>> but you will want to get your SQL comfort levels way up before you start deleting

Understood - my request was more about trying to understand the baby steps so as to understand what is possible down the line. I will definitely try and find some time to look at both those links you supplied. Trouble is, and is one of the reasons I'm asking so many questions, is that this is not my main job, though I can see it could (should?) be. So I need to work out how to justify the time to my team lead - but learning is good whatever so that should work.

stuartd

Coming back to my original query though, and changing it slightly....

We get a lot of engineers that just mass import and tick monitor on absolutely everything. As a default we don't monitor VLans and I could see a potential usefulness for running a script against certain sections (tables?) of the database to undo this.

But this brings me back to the main point I was trying to get to - and that is in knowing what each of the columns do.

Some of them are clearly obvious: InterfaceName, InterfaceID, etc but out of the 100 or so which one tells me it is being monitored and that could eventually, potentially be manipulated via a script to un-monitor it.

I guess, what I'm asking, is there a resource somewhere (probably github) that will detail what the tables / columns are?

p.s. decided to make a start over at w3schools and have also found Codeacademy where I can type stuff in, which is more in aligned with how I learn.

Seashore

I don't think there are any guide to what tables and columns do what. Running SWQL in SWQL-Studio you get some help but not that much. Check on thwack to get more input and dig around. Not always easy to find the answer.

If you want to delete alot of interfaces, use "Manage nodes" and "show: interfaces". There you can search for some type of interfaces and delete them.

Otherwise you can delete elements via the API, you should not delete interfaces or nodes in SQL.

mesverrum

If you see it on the interface table it is a monitored interface. Npm doesn't keep entries for interfaces that aren't being monitored. UDT does potentially but that's a different table (if you even have that module)