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

how join to SWQL query

Hi everybody

i am new on SWQL.

i have two SWQL queries. how can i join these queries in one table to use on solarwinds dashboard ?

this query will return 30 node that have less than 70 day to their certificate expiration. this query doe not show node name.

-- query one
SELECT TOP 30 NodeID, ComponentStatisticData
FROM Orion.APM.CurrentStatistics
where ComponentStatisticData < 70;

now more information about node are displayed on another table

SELECT TOP 10 NodeId, IpAddress, Caption, Fqdn
FROM Cortex.Orion.Node

how can i join these two table to see full detail of my query.

Find more posts tagged with

swql query

Accepted answers

KMSigma.SWI

You really should be using [mention:0d6836b12ccc4811bb673b3ebf92ca65:6b577b8c04704e209d29b6772bf67243] . It provides the "JOIN" logic without the "JOIN" syntax (which can get rough).

With navigation properties you can "jump" up or down to other elements.

SELECT [Stats].ComponentStatisticData
    , [Stats].Component.DisplayName
    , [Stats].Component.Application.Node.Caption AS [Node]
    , [Stats].Component.Application.Node.IP AS [IP]
FROM Orion.APM.CurrentStatistics AS [Stats]
WHERE [Stats].ComponentStatisticData < 30

All comments

soheilamiri

node ID is common on both table

soheilamiri

here is the code for joining two table

SELECT top 30 Caption as Node_name, ComponentStatisticData as expire_in, IpAddress as IP_Address
FROM Orion.Nodes AS [nodes]
INNER JOIN Orion.APM.CurrentStatistics AS [cert]
    ON [cert].NodeID = [nodes].NodeID
where [cert].ComponentStatisticData < 30

cnorborg

Probably easier using the JOIN as already shown, but SWQL does have "target relationships", what I've also heard described as nested links, between tables. These are pre-defined joins that you can use anywhere without doing a join. In SWQL Studio, you'll see them at the bottom of the table when you expand it out with what looks like a chain-link icon, they don't show up if you do a "Generate Select Statement". In this case, the Orion.APM.CurrentStatistics table should have one called "Component" that links to "Orion.APM.Component" table. I say should because I don't have APM, so its not in mine, but I'm looking it up in the Schema on GitHub. "Orion.APM.Component" links to "Orion.APM.Application" via the "Application" link. At that point, "Orion.APM.Application" links to "Orion.Nodes" via the "Nodes" link. So, you could drop the JOIN altogether and have something like this:

SELECT top 30 [cert].Component.Application.Nodes.Caption as Node_name, [cert].ComponentStatisticData as expire_in, [cert].Component.Application.Nodes.IpAddress as IP_Address
FROM Orion.APM.CurrentStatistics AS [cert]
where [cert].ComponentStatisticData < 30

Sure, a little ugly, but probably a little faster, and it should look the same as far as output?

KMSigma.SWI

You really should be using [mention:0d6836b12ccc4811bb673b3ebf92ca65:6b577b8c04704e209d29b6772bf67243] . It provides the "JOIN" logic without the "JOIN" syntax (which can get rough).

With navigation properties you can "jump" up or down to other elements.

SELECT [Stats].ComponentStatisticData
    , [Stats].Component.DisplayName
    , [Stats].Component.Application.Node.Caption AS [Node]
    , [Stats].Component.Application.Node.IP AS [IP]
FROM Orion.APM.CurrentStatistics AS [Stats]
WHERE [Stats].ComponentStatisticData < 30