8 Replies Latest reply: May 23, 2012 12:46 AM by netlogix RSS

Sending Orion Alerts using database query

solarnewbie

I wanted to see if it is possible to perform some sort of a database query to bring in some more information right before sending an Orion alert. This database query would collect some more pertinent information regarding the node and send it as part of the Email alert. Pertinent information might be additional IP address, user names, etc that is retrieved from the database. So, a sample email alert might look like this:

 

Node: 10.100.2.x is down.

 

Information retrieved via database query (prior to sending the alert)

Rack No: 2W

Management console IP: 10.200.2.x

username: admin

Contact Name: Joe Help

Address: 123 any street, Any city, US.

Avocent: xxxx

 

Affected servers: 10.100.x.x

etc etc

 

The database information would be entered into a separate relational database perhaps using MS Access forms or Sharepoint forms.

 

Thanks in advance

 
  • Re: Sending Orion Alerts using database query
    netlogix

    Why not use Custom Properties?

    If you can't do that, you could ${SQL: Select Blah from [OTHERSQLSOURCE].[OTHERDATABASE].dbo.[Table] where [OTHERDATABASEKEY] = ${Nodes.OTHERDATABASEKEY}} (if you are using HTML email, replace the inner {} with @)

    But you need to do all the linking in the SQL server and test it through Visual Studio.

    • Re: Sending Orion Alerts using database query
      mikeprigge


      You can also just embed ${CustomPropertyName} in the Alert. i.e. IP Address: ${IPAddress}

      I usually use a descriptor of the Custom Property- you can also, build them using the Build Varible button on the bottom of the Alert email page in Advanced Alert Manager...

    • Re: Sending Orion Alerts using database query
      solarnewbie

      Netlogix & mikeprigge,

      Thanks for your quick responses. I will probably readup on netlogix's response on using a secondary DB to host all this information. As mentioned, there will be a separate application (MS Access or sharepoint) that will build & maintain this relational database. Also, the user maintaining this database might not have access to Orion configuration and hence the need for segregating the platforms.

      Thanks.

      • Re: Sending Orion Alerts using database query
        netlogix

        I don't know if this helps get you on track, but here is an MS article on linked servers: sp_addlinkedsrvlogin (Transact-SQL)

        • Re: Sending Orion Alerts using database query
          solarnewbie

          Thanks. I will check it out. BTW, I couldn't find any documentation on Orion website for how to use database queries in Alert Manager. Do you have any links or docs? I was looking for some pointers.

           

          On a separate note, while using Custom Properties, is the custom information stored in a separate DB table in the Orion database? Also, will I be able to have a one-to-many relationship between 1 node and the custom information I provide?

           

          Thanks in advance.

          • Re: Sending Orion Alerts using database query
            netlogix

            Custom Properties are stored in the Nodes, Volumes, or Interface table and they are one to one.  The ${SQL: Select blah} will only the first Column and first row, but if you do a little trickery, you can get all the data to show up int it (i.e. ${SQL:select distinct Caption+',' as [data()] from nodes where  not Caption in (SELECT distinct [FullName] FROM [NetPerfMon].[dbo].[ContainerMemberSnapshots] where [EntityDisplayName] = 'Node') union select '.' as a FOR XML PATH('')} - that puts a list of all servers not in a Group in the email)

             

            I don't know of any documentation, but basically you change the drop down to Custom SQL, then put the "where" conditions in.  I usually use Visual Studio to test what results I would get back and try the "not" to make sure that those are reported to, because they are the good ones.

      • Re: Sending Orion Alerts using database query
        HemiTruck

        actually most of the fields you need are already in the NPM database.

        any that are not you could add to it.

        no real reason to store the data in a separate database. just makes more issues with compatibility and maintenance

        you can pull all the fields into the alert when you are creating it as the guy above said with the button at the bottom that lets you build the variables.

         

        be careful especially if you use access that is like driving a Dodge Viper with a 3 cylinder Yugo in front of you.

        • Re: Sending Orion Alerts using database query
          netlogix

          Good Point Hemi - I actually run a powershell script in a windows task scheduler that goes out and grab data from other source (AD, Excel Spreadsheet, other database, etc) and writes it into custom properties, and formats it so I just have to use ${NotificationInfo} or $PAD_OU} and it's all there.  You could even have it run every hour so the data isn't stale.