Pulling in Information from a SQL Query to Another Database on a Different Server in an Email Alert Action - How To Do?

Hello,

Hoping someone has a sample Alert Action that they can share or information in general, if this is possible...

In an alert action (email), I'm looking for a way using SQL to query a different database on a different server, using variables for the current alert triggering to pull in that information to the email message. I've seen mention of doing a Linked SQL Server Instance, presumably bringing that server's database & table(s) effectively into Orion to query on. Not sure this is still a viable approach or not, or if there is another way / better way.

As an example, lets say a node (server) goes down which triggers the "node down" alert. I'd like to query a database on another server that contains ticketing information, specifically to see if this node has an open change ticket or not, thereby correlating the "down" event with the work in progress and at least call out in the alert text that this may be the cause of the down message.

While it'd be great if this could all be pre-processed or more of an IF/THEN so the text can vary depending on result, I don't believe the alert engine is that sophisticated for that. However, perhaps it's possible on the alert trigger?... Potentially if the aforementioned Linked SQL Server method holds or similar method & is then able to be utilized there as well??

Any input is appreciated. Thanks!

Find more posts tagged with

Alert

query

linked

sql

different database

Alert Action

Accepted answers

All comments

mesverrum

So I do a lot of work with linked databases in my instance. One way you could make this work is to run a sql query as an agent job every so often reaching to the other DB getting a list of all the nodes that are entering or exiting a change window and use that output to set a custom property in Orion. Then just add that property to the alert logic, something like ChangeActive = False.

Marek

Thanks for the response! This is an interesting take on how to solve the goal... I'll have to think about how that may look. I'm interested in adding some of the details to the message so that may require several custom properties. e.g. Including the related ticket number that applies to the change or perhaps a link to the ticket as well / instead and the change window timeframe. Hence why I was thinking a dynamic, real-time query may be more applicable.

As an agent job, you'd also have to clear all the related custom properties on all objects every time too to ensure "ChangeActive" & any other properties weren't still set when the change window / ticket closes, right?...