SWQL filter by custom property

richmondg

Hi,

Good day!

I'm new to SWQL and have no prior knowledge of SQL queries. I'm trying to have the script below to be filtered by a custom property, when I add the filtering code [Nodes.CustomProperties.AssetTag = 'Backend'] I get a SWQL build error. How do I correctly filter the custom table report script below?

SELECT
Nodes.Caption as NodeName,
Nodes.IP_Address as IP,
Max(Events.EventTime) as EventTime,
Nodes.NodeID as ID,
Nodes.CustomProperties.AssetTag,

(
ToString(DayDiff(0,GETUTCDATE() - MAX(Events.EventTime))) + ' Days ' +
ToString(Ceiling((HourDiff(0, GETUTCDATE() - MAX(Events.EventTime)) / 24.0 - Floor(HourDiff(0,GETUTCDATE() - MAX(Events.EventTime)) / 24.0)) * 24 )) + ' Hours ' +
ToString(Ceiling((MinuteDiff(0, GETUTCDATE() - MAX(Events.EventTime)) / 60.0 - Floor(MinuteDiff(0,GETUTCDATE() - MAX(Events.EventTime)) / 60.0) ) * 60 )) + ' Minutes ')
as Down_Time

FROM orion.Nodes as Nodes
INNER JOIN orion.Events as Events ON (Nodes.NodeID = Events.NetworkNode)
INNER JOIN Orion.EventTypes as EventTypes ON (Events.EventType = EventTypes.EventType)

Where (
(Events.NetworkNode = Nodes.NodeID) AND
(Events.EventType = 1) AND
(Nodes.Status = '2') AND
(Nodes.CustomProperties.AssetTag = 'Backend')
)
Group By Nodes.Caption,Events.NetworkNode, Events.EventType, Nodes.NodeID,Nodes.IP_Address
Order By EventTime ASC

MathieuJM

Hello

you need to add your custom property to your group by: 

SELECT Nodes.Caption AS NodeName
     , Nodes.IP_Address AS IP
     , Max(Events.EventTime) AS EventTime
     , Nodes.NodeID AS ID
     , Nodes.CustomProperties.AssetTag
     , (ToString(DayDiff(0, GETUTCDATE() - MAX(Events.EventTime))) + ' Days ' + ToString(Ceiling((HourDiff(0, GETUTCDATE() - MAX(Events.EventTime)) / 24.0 - Floor(HourDiff(0, GETUTCDATE() - MAX(Events.EventTime)) / 24.0)) * 24)) + ' Hours ' + ToString(Ceiling((MinuteDiff(0, GETUTCDATE() - MAX(Events.EventTime)) / 60.0 - Floor(MinuteDiff(0, GETUTCDATE() - MAX(Events.EventTime)) / 60.0)) * 60)) + ' Minutes ') AS Down_Time
FROM orion.Nodes AS Nodes
INNER JOIN orion.Events AS Events
     ON (Nodes.NodeID = Events.NetworkNode)
INNER JOIN Orion.EventTypes AS EventTypes
     ON (Events.EventType = EventTypes.EventType)
WHERE (
          (Events.NetworkNode = Nodes.NodeID)
          AND (Events.EventType = 1)
          AND (Nodes.Status = '2')
          AND (Nodes.CustomProperties.AssetTag = 'Backend')
          )
GROUP BY Nodes.Caption
     , Events.NetworkNode
     , Events.EventType
     , Nodes.NodeID
     , Nodes.IP_Address
     , Nodes.CustomProperties.AssetTag -- This is where we call out the custom property
ORDER BY EventTime ASC