Hi everybody, I have a Code and I want to show a value from custom property such as "City" but I have a problem when adding "Nodes.customproperty.city as [City]" to the below Code:
SELECT
NodeName AS [Node Name],
'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],
DetailsUrl AS [_LinkFor_Node Name],
concat(SUBSTRING(tostring(MAX(e.EVENTTIME)),1,4),SUBSTRING(tostring(MAX(e.EVENTTIME)),5,2),
SUBSTRING(tostring(tolocal(MAX(e.EVENTTIME))),12,8)) as Downtime,
CONCAT(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24,' Day(s) ',
MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60%24,'h ',
MINUTEDIFF(tolocal(max(e.eventtime)),getdate()) - (MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60)*60,'m') AS Duration -- ,Nodes.customproperty.city as [City]
FROM ORION.NODES NODES
INNER JOIN ORION.EVENTS e ON NODES.NODEID = e.NETWORKNODE
WHERE STATUS = 2 and E.Eventtype = 1 and nodes.customproperties.Country Like '%Arab Emirates%'
GROUP BY NodeName, StatusIcon, DetailsUrl -- ,City
ORDER BY MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate()) ASC