SWQL Query Assistance Please

Quizang

Hi All,

Hope someone can help please with writing a SWQL query.

Am seeking to compare 2 values from the same table together - in the Nodes table, am looking to return a device - A if it is the type 'Access Switch' and it's status is down, only if device B is up, and only if both device A and B belong to the same Site (site being another custom property).

Am able to do the easy query of 1, but how to compare it to the other please?

SELECT E0.[Uri], E0.[DisplayName]

FROM Orion.Nodes AS E0 WHERE ( ( E0.[CustomProperties].[DeviceType] = 'Access Switch' ) AND (E0.[Status] = '2') )

Many thanks in advance.

jm_sysadmin

I don't have a way to test at the moment, but I am thinking something like this will get close

SELECT 
    A.[DisplayName] AS DeviceA_Name, 
    A.[CustomProperties].[DeviceType] AS DeviceA_Type, 
    B.[DisplayName] AS DeviceB_Name, 
    B.[CustomProperties].[DeviceType] AS DeviceB_Type, 
    A.[CustomProperties].[Site]
FROM Orion.Nodes A 
JOIN Orion.Nodes B ON A.Site = B.Site 
WHERE 1=1 
    and A.[CustomProperties].[DeviceType] = 'Access Switch' 
    AND A.StatusDescription like '%Down%' 
    AND B.StatusDescription like '%Up%''