Email Alert for Routing Neighbors - SQL code to pull Custom Properties

Hi guys,

We have email alerts that get sent to our clients automatically when their devices go down. I have done this by utilizing Custom Properties ${AffectedSites} etc. in the email alert.

We have 3 sites that are satellite sites that we monitor via reporting on the status of OSPF in Routing Neighbors.

 

My question is if there is a way to use SQL codes to pull the custom properties of the node that goes down when the routing neighbor ospf status goes down and the alert triggers. 

I cant put custom properties on the routing neighbor ip address we are monitoring so it makes it a bit difficult since I am no SQL master.

I currently use this code I found online to pull the node name of the device that goes down:

${SQL:SELECT ISNULL((SELECT TOP 1 Caption FROM NodesData LEFT JOIN NodeIPAddresses ON NodesData.NodeID=NodeIPAddresses.NodeID WHERE (NodeIPAddresses.IPAddress='${N=SwisEntity;M=NeighborIP}' OR NodesData.IP_Address='${N=SwisEntity;M=NeighborIP}')),'Unknown Neighbor')}

We have to employ this method because solarwinds reports a lot of false downs, but OSPF doesn't go down during these false events. 

Thanks,

James Rich

Parents
  • That query looks mighty familiar Slight smile

    Assuming that the name of your property you want to display is AffectedSites you can do this

    ${SQL:SELECT ISNULL((SELECT TOP 1 AffectedSites FROM Nodes n LEFT JOIN NodeIPAddresses ON n.NodeID=NodeIPAddresses.NodeID WHERE (NodeIPAddresses.IPAddress='${N=SwisEntity;M=NeighborIP}' OR n.IP_Address='${N=SwisEntity;M=NeighborIP}')),'Affected Site Unknown')}

  • Oh my god. That worked flawlessly. I'm going through and fixing up the alert now and its looking fantastic.
    I am just changing the "AffectedSites" portion after SELECT TOP 1 to our other custom properties such as AffectedServices, Company, Email etc. 

    Seems to be working fantastic. I can't thank you enough. 

Reply
  • Oh my god. That worked flawlessly. I'm going through and fixing up the alert now and its looking fantastic.
    I am just changing the "AffectedSites" portion after SELECT TOP 1 to our other custom properties such as AffectedServices, Company, Email etc. 

    Seems to be working fantastic. I can't thank you enough. 

Children
No Data