8 Replies Latest reply on Feb 11, 2015 6:50 PM by Leon Adato

    Using Custom Properties in Advanced Alert Message

    k1gaudineer

      I am having trouble trying to get the syntax correct for using a custom property for an interface in an Advanced Alert for a node.  We do track circuit numbers with a custom property called 'Circuit-#' and this custom property is assigned to the interface.

       

      I am creating an advanced alert for a node and in the message body  I can pull other important information when the alert is emailed out like Office Name, Address, City, State, Zip and  IP addresses.  Occasionally the node can become unresponsive because of circuit trouble and I would also like to include the circuit number and carrier trouble reporting number in the alert.  Is it possible to reference an interface custom property from a node alert?

        • Re: Using Custom Properties in Advanced Alert Message
          janene16

          I don't think you can do that since the Type of Property to monitor selected is Node.

          Unless you do a SQL query, perhaps its possible.

          • Re: Using Custom Properties in Advanced Alert Message
            HolyGuacamole

            You will need to use a SQL macro to extract the information you need using a SQL query, as you figured out

            SQL Variable in advanced alerts

             

            If you attach the Alert definition, we can get to the bottom of why your SQL macro isn't working

            1 of 1 people found this helpful
            • Re: Using Custom Properties in Advanced Alert Message
              Leon Adato

              HolyGuacamole is right - you CAN do it, but it will take a SQL variable. Assuming your custom property is called "CircuitID" (that hash makes me nervous), your variable would look something like this:

               

              ${SQL: select CircuitID from interfaces join nodes on interfaces.nodeid = ${NodeID}}

               

              Note that this will return ALL of the circuiID's (if the device has more than one).

               

              Hope that helps!

              1 of 1 people found this helpful
                • Re: Using Custom Properties in Advanced Alert Message
                  k1gaudineer

                  Good Morning everyone, and thanks for all of the replies.  HolyGuacamole and adatole are corect I need to use a SQL variable.

                   

                  I tried the one that adatole recomended but that did not work....  So I watched the SolarWinds Lab #7 video and dug through a few more post that were linked from that video site and so far I have settled on an SQL variable that looks like.  The SQL variable below almost works....

                  I tried with 2 curly braces at the end of the statement but that made the result look funny so I have removed on curly brace and the result looks OK...

                   

                  Circuit Info:  ${SQL:Select CircuitID From Interfaces WHERE Interfaces.NodeID = ${NodeID} AND CircuitID IS NOT NULL}

                  If the alert is tested with the above SQL statement the output looks like this....Circuit Number: 34.HCXX.98908..NW

                   

                  Now if the same alert triggers  and this node is a switch with no configured circuit number or carrier info The email body

                  will look like this....:  Circuit Info: ${SQL:Select [CircuitID] From [dbo].[Interfaces] WHERE Interfaces.NodeID = $@NodeID@ AND [CircuitID] IS NOT NULL}...  Not really what I want to happen...  i would prefer that the line just stay blan if there is no value to show....


                  So it looks like i will need to create 2 alerts and add a new custom property for nodes.  The two alerts will look exactly the same except one will have the SQL statements in it and the other will not.  The new custom nodes field property will be a YES/NO on which alert alert to use.  One of the alerts will have a new trigger condition added to test if the value is YES or NO....


                  If anyone knows of a way to stop the SQL statement from being displayed when the field is NULL I would greatly appreciate it...

                    • Re: Using Custom Properties in Advanced Alert Message
                      Leon Adato

                      I think you can use ISNULL() or COALESCE() as follows (please test this - I'm doing this off the top of my head)

                       

                      ${SQL:Select ISNULL(CircuitID, 'no circuit found') From Interfaces WHERE Interfaces.NodeID = ${NodeID} AND CircuitID IS NOT NULL}

                        • Re: Re: Using Custom Properties in Advanced Alert Message
                          k1gaudineer

                          adatole

                          Interesting result......  I tested the SQL statement you suggested and the email body shows this when the CircuitID field is NULL

                           

                          Circuit Number: ${SQL:Select ISNULL(CircuitID, 'no circuit found') From Interfaces WHERE Interfaces.NodeID = $@NodeID@ AND CircuitID IS NOT NULL}

                           

                          In the statement above notice the part of the SQL statement I changed to red and bold type....  In the SQL variable statement that NodeID macro actually has the curly braces around it...  But when the statement is evaluated and the field is NULL the curly braces are replaced with an '@' sign.  But if the field has a value the circuit number prints as expected.....  I'm not sure why the curly braces are being replaced with an '@' symbol....

                            • Re: Re: Using Custom Properties in Advanced Alert Message
                              Leon Adato

                              AHHHHH, Right. You used the "Test" button, didn't you?

                               

                              Don't do that. Just... don't. It doesn't work. It doesn't populate variables. It was a nice idea that didn't really pan out. It's one more really good reason to upgrade to NPM 11.5.

                               

                              But if that's not something you can do in the next 5 minutes, then do this instead:

                               

                              Copy the alert out

                              Change the parameters so ANY node would pass

                              Add a parameter so that ONLY one node will pass (ie: nodeid = 15)

                              let 'er rip

                              use the "reset" optiono to make it fire again if you need to make more changes.

                               

                              Hope that helps.

                              - Leon