cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

SWQL and Joining Tables

Jump to solution

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

0 Kudos
1 Solution
Level 12

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

View solution in original post

3 Replies
Level 12

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

View solution in original post

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?

0 Kudos
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.