how can i add customproperty in this query

Question

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)

stuartd · Answer

Hi Sadiq, 
 Trust me - as a complete novice on SWQL is to do two things, well 3 actually. 
 
 Always post your code using the "Insert > Code" option 
 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 
 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

how can i add customproperty in this query

Top Replies