Lookup email address for Alert

Question

I want to configure an Alert Action which will send an email when, for example, a node goes down.  Each node has a Custom Property that lists the department responsible for the node.  I want to configure a single Alert Action that will send an email alert to the appropriate email address for that department.  I've seen that I can use a variable for an email address.  However I don't want to add *another* Custom Property for my nodes that will contain the email address; the email address is tightly coupled to the department.  What I want is some way to use the "department" Custom Property and resolve it into the appropriate email address for that department.

I've thought of one or two possibilities but they all seem rather complex.  I've considered creating a custom table with one field for departments and one for the email addresses.  I'd use it in a SQL Variable, joining the custom table to the NodesCustomProperties table through the department Custom Property to get the email address.  Maintaining that custom table would be difficult, though.  I've considered using a temporary table, built in the SQL Variable itself, however that would be awkwardly complex as well, and result in a temporary table being built for every execution of the Alert, which seems like a sledgehammer approach.  Last option, of course, would be to create that additional Custom Property for every node.

Has anyone done this before?  Does anyone have any alternate suggestions?

elhannaford · Answer

Hmm, maybe I didn't explain well.

I'm not looking for a way to construct the Trigger Condition(s).  I'm looking for a way to construct the email address for the Email Alert Action.

I have a dozen or so different locations, each with a separate team that's responsible for their own nodes.  I can construct, and, up to now, I have constructed, a dozen different Alerts, each with their own Trigger Conditions and each with their own Email Alert Actions - I have 4 separate Email Alert Actions for each Alert: one to email an alert, and 3 in escalation stages to send pages (via email) to appropriate people.  However, any time I want to modify the Alerts I have to touch all 12 of those Alerts and all 48 Email Alert Actions.  That's a lousy design and should be unnecessary; each of those Email Alert Actions are virtual copies of each other, the only significant difference is the email address.  I should be able to consolidate those Email Alert Actions down to just *4* total, assigning the same Actions to each Alert, using variables as needed to customize the Alert, such as the email address.  If I get really fancy I might be able to consolidate things down to a single Alert, but I'll save that for later.

However, to achieve this, I have to somehow take the "department" Custom Property and change it to its respective email address.  I can then use that variable in the Recipents field of the Email Alert Action.  Here's a pic of what I'm kind-of trying to accomplish, with some hand-wavium in the SQL statement (I'm very much a novice with SQL):

This example assumes the first solution I mentioned above, with a custom table named, "emails", inner-joined to NodesCustomProperties.  Anyway, I hope you get the idea.  However, this is a poor solution and I'm hoping that someone can suggest something different.