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

SWQL and Joining Tables

I have a report I generate with some SWQL, I want to limit the report to nodes that are in our production environment. I have a custom attribute for nodes that is used to designate the environment. I can't figure out how to path the Orion.NodesCustomProperties table to use it in the where clause. In general I struggle with how to know if tables can be joined, if they are already somehow joined, or how to join them if not. It would be nice if SWQL studio let me drag items to the query.

E0.[NodesCustomProperties].[Environment] = 'PRD'

SELECT E0.[SqlServer].[Node].[Caption],
E0.[SqlApplicationAlert].[InstanceName],
E0.[DisplayName],
CASE E0.[SqlDatabaseAlert].[RecoveryModel]
WHEN '3' THEN 'SIMPLE'
WHEN '1' THEN 'FULL'
END AS RecoveryModel,
IsNull(E0.[SqlDatabaseAlert].[DaysFromLastBackup],10000)as DaysFromLastBackup,
IsNull(E0.[SqlDatabaseAlert].[LastBackup], AddDay(-10000,GetDate()))as LastBackup
FROM Orion.APM.SqlDatabase AS E0
WHERE E0.[Name] NOT LIKE '%temp%'
AND E0.[SqlServer].[SqlApplicationAlert].[Edition] NOT LIKE '%Express%'
AND E0.DatabaseID <> 2731
AND E0.DatabaseID <> 3287
AND E0.DatabaseID <> 3289

Find more posts tagged with

Accepted answers

Seashore

So in this case we have to join a few times to get where we want. First from SQLDatabase to Application and then to NodesCustomProperties. Like this:

SELECT E0.[SqlServer].[Node].[Caption],
E0.[SqlApplicationAlert].[InstanceName],
E0.[DisplayName],
CASE E0.[SqlDatabaseAlert].[RecoveryModel]
WHEN '3' THEN 'SIMPLE'
WHEN '1' THEN 'FULL'
END AS RecoveryModel,
IsNull(E0.[SqlDatabaseAlert].[DaysFromLastBackup],10000)as DaysFromLastBackup,
IsNull(E0.[SqlDatabaseAlert].[LastBackup], AddDay(-10000,GetDate()))as LastBackup
FROM Orion.APM.SqlDatabase AS E0
INNER JOIN Orion.APM.Application A on E0.Applicationid=A.ApplicationID
INNER JOIN Orion.NodesCustomProperties NCP ON A.NodeID=NCP.NodeID
WHERE E0.[Name] NOT LIKE '%temp%'
AND E0.[SqlServer].[SqlApplicationAlert].[Edition] NOT LIKE '%Express%'
AND E0.DatabaseID <> 2731
AND E0.DatabaseID <> 3287
AND E0.DatabaseID <> 3289
AND NCP.[Environment] = 'PRD'

New code in bold above. Hope it works!

/Thomas

All comments

Seashore

So in this case we have to join a few times to get where we want. First from SQLDatabase to Application and then to NodesCustomProperties. Like this:

SELECT E0.[SqlServer].[Node].[Caption],
E0.[SqlApplicationAlert].[InstanceName],
E0.[DisplayName],
CASE E0.[SqlDatabaseAlert].[RecoveryModel]
WHEN '3' THEN 'SIMPLE'
WHEN '1' THEN 'FULL'
END AS RecoveryModel,
IsNull(E0.[SqlDatabaseAlert].[DaysFromLastBackup],10000)as DaysFromLastBackup,
IsNull(E0.[SqlDatabaseAlert].[LastBackup], AddDay(-10000,GetDate()))as LastBackup
FROM Orion.APM.SqlDatabase AS E0
INNER JOIN Orion.APM.Application A on E0.Applicationid=A.ApplicationID
INNER JOIN Orion.NodesCustomProperties NCP ON A.NodeID=NCP.NodeID
WHERE E0.[Name] NOT LIKE '%temp%'
AND E0.[SqlServer].[SqlApplicationAlert].[Edition] NOT LIKE '%Express%'
AND E0.DatabaseID <> 2731
AND E0.DatabaseID <> 3287
AND E0.DatabaseID <> 3289
AND NCP.[Environment] = 'PRD'

New code in bold above. Hope it works!

/Thomas

jameslindsay

Ok, so then that seems to indicate that I am over thinking it. It seems in some cases you don't have to join to get to a field, for example E0.[SqlServer].[Node].[Caption]. How did Orion.APM.SqlDatabase get joined to Nodes to pull the caption?

Seashore

In Swql many "tables" are "pre-joined" so to say. That's why you could take that shortcut.
I prefer though to join myself to have better control and better understanding.