how can i add customproperty in this query

sadiqsul

hi all

i would like to add one of customproperty in this query . how can i add 

SELECT DISTINCT AlertActive.AlertActiveID
     , AlertObjects.AlertObjectID
     , AlertConfigurations.Name
     , AlertConfigurations.Severity
     , AlertConfigurations.ObjectType
     , AlertObjects.EntityUri
     , AlertObjects.EntityType
     , AlertObjects.EntityCaption
     , ToLocal(AlertActive.TriggeredDateTime) AS TriggeredDateTime
     , AlertObjects.LastTriggeredDateTime
     , AlertActive.TriggeredMessage AS Message
     , AlertActive.AcknowledgedDateTime
     , AlertActive.Acknowledged AS Acknowledged
     , AlertActive.AcknowledgedBy
     , AlertActive.AcknowledgedNote
     , CASE 
          WHEN Floor((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 86400) > 0
               THEN ToString(ToString(Floor((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 86400)) + 'd ' + ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) - 86400 * (Floor((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 86400))) + 0.0) / 3600)) + 'h ' + ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) - 3600 * (Floor((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 3600))) + 0.0) / 60)) + 'm ')
          WHEN Floor(((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) - 86400 * (Floor((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 86400))) + 0.0) / 3600) > 0
               THEN ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) - 86400 * (Floor((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 86400))) + 0.0) / 3600)) + 'h ' + ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) - 3600 * (Floor((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 3600))) + 0.0) / 60)) + 'm ')
          WHEN Floor(((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) - 3600 * (Floor((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 3600))) + 0.0) / 60) > 0
               THEN ToString(ToString(Floor(((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) - 3600 * (Floor((SecondDiff(AlertActive.TriggeredDateTime, GetUtcDate()) + 0.0) / 3600))) + 0.0) / 60)) + 'm ')
          ELSE ''
          END AS ActiveTime
     , AlertObjects.Node.caption AS Node
     , AlertObjects.Node.CustomProperties.Locations AS Location
     , AlertObjects.Node.IPAddress AS IPAddress
     , AlertObjects.Node.Vendor AS Vendor
     , AlertObjects.Node.MachineType AS MachineType
FROM Orion.AlertObjects(NOLOCK = true) AlertObjects
INNER JOIN Orion.AlertActive(NOLOCK = true) AlertActive
     ON AlertObjects.AlertObjectID = AlertActive.AlertObjectID
INNER JOIN Orion.AlertConfigurations(NOLOCK = true) AlertConfigurations
     ON AlertConfigurations.AlertID = AlertObjects.AlertID
ORDER BY AlertConfigurations.Name
     , AlertObjects.EntityCaption

i want to add this (device_type)

LeBeauUK

I see that you have got a Custom Property in the select area

[quote userid="295797" url="~/products/the-orion-platform/f/report-lab/101177/how-can-i-add-customproperty-in-this-query"]AlertObjects.Node.CustomProperties.Locations as Location[/quote]

So to add the device_type custom property, simply add:

AlertObjects.Node.CustomProperties.device_type as [Device Type]

Have the as in square brakets you can have it display without the under score.