how can i add customproperty in this query

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)

Parents
  • Hi Sadiq,

    Trust me - as a complete novice on SWQL is to do two things, well 3 actually.

    1. Always post your code using the "Insert > Code" option
    2. Always format your code - even if copied from elsewhere.
            I find it easiest to use Notepad++ and then add the 'Poor Man's T-SQL Formatter' as a starting point.
            Doing this makes it so much easier to troubleshoot, edit, add to, etc
    3. Comment on the code so when you come back to it you know why you did something or added something - (you'll see I commented but feel free to remove that) and commenting, when dropped into SWQL Studio appears in green, and looks like this:

    So, all that said I added your code to Notepad++; formatted it and then added the line you need which gives me:

    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
        -- Added following line to include Device_Type CP
        ,AlertObjects.Node.CustomProperties.Device_Type
    	,AlertObjects.Node.IPAddress AS IPAddress
    	,AlertObjects.Node.Vendor AS Vendor
    	,AlertObjects.Node.MachineType AS MachineType
    FROM Orion.AlertObjects(NOLOCK = true) AS 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

    N.B - I don't have your custom property, but once I'd formatted your code it was easy to see you already had a Nodes CP, so I just duplicated that line and then removed the .Locations AS Locations on the second line, replacing it with Devcie_Type and so those two lines look like:
     

    ,AlertObjects.Node.CustomProperties.Locations AS Location ,AlertObjects.Node.CustomProperties.Hardware_Type
     

Reply
  • Hi Sadiq,

    Trust me - as a complete novice on SWQL is to do two things, well 3 actually.

    1. Always post your code using the "Insert > Code" option
    2. Always format your code - even if copied from elsewhere.
            I find it easiest to use Notepad++ and then add the 'Poor Man's T-SQL Formatter' as a starting point.
            Doing this makes it so much easier to troubleshoot, edit, add to, etc
    3. Comment on the code so when you come back to it you know why you did something or added something - (you'll see I commented but feel free to remove that) and commenting, when dropped into SWQL Studio appears in green, and looks like this:

    So, all that said I added your code to Notepad++; formatted it and then added the line you need which gives me:

    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
        -- Added following line to include Device_Type CP
        ,AlertObjects.Node.CustomProperties.Device_Type
    	,AlertObjects.Node.IPAddress AS IPAddress
    	,AlertObjects.Node.Vendor AS Vendor
    	,AlertObjects.Node.MachineType AS MachineType
    FROM Orion.AlertObjects(NOLOCK = true) AS 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

    N.B - I don't have your custom property, but once I'd formatted your code it was easy to see you already had a Nodes CP, so I just duplicated that line and then removed the .Locations AS Locations on the second line, replacing it with Devcie_Type and so those two lines look like:
     

    ,AlertObjects.Node.CustomProperties.Locations AS Location ,AlertObjects.Node.CustomProperties.Hardware_Type
     

Children