6 Replies Latest reply on Dec 12, 2017 12:36 PM by mesverrum

    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!

        • Re: Inserting custom node property in custom query (SWQL)
          I LIKE EGGS
          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]
          --custom property here:
          ,np.n_country
          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 the nodes custom property table on 
          left join orion.NodesCustomProperties as np on np.NodeID = p.NodeID
          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
          
          
          2 of 2 people found this helpful
              • Re: Inserting custom node property in custom query (SWQL)
                alex777

                That's perfect!

                 

                What would be the format for alerts custom properties?

                  • Re: Inserting custom node property in custom query (SWQL)
                    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