Inserting custom node property in custom query (SWQL)

alex777

Hey,

I'm using a custom query (mesverrum) that poll alerts that were reset but not acknowledge, but I want to insert 2 things:

select ac.Name

,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Name]

,EntityCaption as [Trigger Object]

,EntityDetailsUrl as [_linkfor_Trigger Object]

,case

WHEN RelatedNodeCaption=EntityCaption THEN 'Self'

When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption

End as [Parent Node]

,RelatedNodeDetailsUrl as [_linkfor_Parent Node]

,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_Parent Node]

,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]

,case when ack.timestamp is null then 'N/A'

else tostring(minutediff(ah.TimeStamp,ack.timestamp))

end as [Minutes Until Acknowledged]

,case when reset.timestamp is null then 'N/A'

else tostring(minutediff(ah.TimeStamp,reset.timestamp))

end as [Minutes Until Reset]

,ack.Message as [Note]

FROM Orion.AlertHistory ah

left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid

left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid

left join Orion.Actions a on a.actionid=ah.actionid

left join Orion.Nodes p on p.nodeid=RelatedNodeID

left join (select timestamp, AlertActiveID, AlertObjectID,message from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID

left join (select timestamp, AlertActiveID, AlertObjectID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID

WHERE

daydiff(ah.timestamp,GETUTCDATE())<7

and ah.eventtype=0

and ac.category='MON'

and ack.timestamp is null

and reset.timestamp is not null

order by ah.timestamp desc

• Custom node property
• Custom alert property

Can you guys help me out?

Thank you!

mesverrum

select ac.Name
,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Name]
,ac.CustomProperties.alertproperty -- this is my example property, change alertproperty to whatever you need
,EntityCaption as [Trigger Object]
,EntityDetailsUrl as [_linkfor_Trigger Object]
,case
WHEN RelatedNodeCaption=EntityCaption THEN 'Self'
When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
End as [Parent Node]
,RelatedNodeDetailsUrl as [_linkfor_Parent Node]
,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_Parent Node]
,p.CustomProperties.Environment -- this is my example property, change environment to whatever you need
,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]
,case when ack.timestamp is null then 'N/A'
else tostring(minutediff(ah.TimeStamp,ack.timestamp))
end as [Minutes Until Acknowledged]
,case when reset.timestamp is null then 'N/A'
else tostring(minutediff(ah.TimeStamp,reset.timestamp))
end as [Minutes Until Reset]
,ack.Message as [Note]


FROM Orion.AlertHistory ah
left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
left join Orion.Actions a on a.actionid=ah.actionid
left join Orion.Nodes p on p.nodeid=RelatedNodeID
left join (select timestamp, AlertActiveID, AlertObjectID,message from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID
left join (select timestamp, AlertActiveID, AlertObjectID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID


WHERE
daydiff(ah.timestamp,GETUTCDATE())<7
and ah.eventtype=0
and ac.category='MON'
and ack.timestamp is null
and reset.timestamp is not null


order by ah.timestamp desc

See lines 3 and 12, no need to manually join those tables since SWQL has them built in as native joins already.

Have you downloaded SWQL Studio from the Github?  It will make your life much better if you are going to be working with queries like these much.  It almost makes them self explanatory... almost.

Releases · solarwinds/OrionSDK · GitHub