This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Add custom property value with SWQL

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

  • give this ago:

    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    -- 

    ,CP.Country as [City]

    FROM ORION.NODES NODES

    INNER JOIN ORION.EVENTS e ON NODES.NODEID = e.NETWORKNODE

    INNER JOIN ORION.NodesCustomProperties CP ON CP.NodeID = NODES.NodeID

    WHERE STATUS = 2 and E.Eventtype = 1 and CP.Country = 'XXX'

    GROUP BY NodeName, StatusIcon, DetailsUrl   ,City

    ORDER BY MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate()) ASC

  • Thank you for your attention, I can solve this problem by adding Nodes.CustomProperties.City to GROUP BY:

    SELECT

      NodeName AS [Node Name],

      '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],

      DetailsUrl AS [_LinkFor_Node Name],

      Nodes.CustomProperties.City,

      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

    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, Nodes.CustomProperties.City

    ORDER BY MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate()) ASC