Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Allow functions in SQL in alert email address field

We use a lot of "Send an email/page" actions in alert triggers and resets. I want to use a SQL query to choose the email address for the alert message. Right now, I can't use a SQL statement that contains a function, like this one:

${SQL: SELECT IsNull(NullIf(ACP.AlertEmail,''),N.AlertEmail) FROM APM_ApplicationCustomProperties ACP INNER JOIN APM_Application A on A.ID=ACP.ApplicationID INNER JOIN NodesCustomProperties N on A.NodeID=N.NodeID  where ACP.ApplicationID='${N=SwisEntity;M=ApplicationID}'}

That is, I want my configuration to look like this:

It looks like the address parser won't accept the open and close parentheses characters. Please refine the address validation function to let me put any valid SQL in there.

For bonus thanks, give us the "Insert Variable" button for the email address box so we can search for any custom properties in which we might have stored additional email addresses.

Find more posts tagged with

Status: None

Comments

njoylif

I think this should work. try putting

${SQL:SELECT IsNull(ACP.AlertEmail,N.AlertEmail) AS email FROM APM_ApplicationCustomProperties ACP INNER JOIN APM_Application A on A.ID=ACP.ApplicationID INNER JOIN NodesCustomProperties N on A.NodeID=N.NodeID where ACP.ApplicationID=${N=SwisEntity;M=ApplicationID}}

things I changed

IsNull statement (I assume you want if acp alertemail is null, use nodesCP alertemail) maybe try: ISNULL(ISNULL(ACP.AlertEmail,N.AlertEmail),'')

no space btn SQL:Select

AppID is numerical

added "as email" for select item

try mix and match if not what I have.

m-milligan

Thanks, njoylif. I tried your query, but it too was deemed unacceptable by the parser. Like mine, it seems to be choking on the parentheses and on the comma inside IsNull().

aLTeReGo

We are tracking this internally under CORE-5290.

m-milligan

Great! Any idea when this will be included in a patch or upgrade, or is it too soon to say?

m-milligan

Update: I thought I could get around this by using a computed column. Here's what I did:

Created a new custom property, DoNotEdit_EffectiveAlertEmail, for Applications.
Created a user-defined SQL Server function, getEffectiveApplicationAlertEmail(), to get the effective email address.
Using T-SQL, replaced the new column (dbo.APM_ApplicationCustomProperties.DoNotEdit_EffectiveAlertEmail) with a computed column. The computed column's value was getEffectiveApplicationAlertEmail(ApplicationID).

This put the desired email address into the new custom property. However, I was then unable to change other custom properties for Applications and I was unable to unmanage applications. I suspect this is because both of those operations cause Solarwinds to try to update the value of the computed column, which causes an error in SQL Server.

I have another idea, which I will test later. If it works, I'll post it here.

m-milligan

I have found a successful workaround using a computed column. Remember, my original requirement was to get the contents of this query into the email field of an application alert:

SELECT IsNull(NullIf(ACP.AlertEmail,''),N.AlertEmail) FROM APM_ApplicationCustomProperties ACP INNER JOIN APM_Application A on A.ID=ACP.ApplicationID INNER JOIN NodesCustomProperties N on A.NodeID=N.NodeID  where ACP.ApplicationID='${N=SwisEntity;M=ApplicationID}'

Here's how I did it:

In SQL Server, I created a user-defined function, getEffectiveApplicationAlertEmail(), that uses this query to retrieve the value that I want.

Using T-SQL, I added a new column, DoNotEdit_EffectiveAlertEmail, to the APM_ApplicationCustomProperties table. This is a computed column whose value is getEffectiveApplicationAlertEmail(ApplicationID). Here's the SQL:

ALTER TABLE dbo.APM_ApplicationCustomProperties

  ADD DoNotEdit_EffectiveAlertEmail AS (dbo.getEffectiveApplicationAlertEmail(ApplicationID))

GO

Solarwinds doesn't know about this column, so it won't try to update its value when Application custom properties are updated through the GUI or the API.

In the Alert Manager, I used a macro to get the value of the computed column into the address field:

I've tested this and Solarwinds picks up the correct value in the cc: field. This approach has the advantage that the end-user can't see the DoNotEdit_EffectiveAlertEmail column in the GUI, so they won't try to edit it.

aLTeReGo, is there anything of which I should be aware if I use this approach? Obviously, if Solarwinds makes any changes to the database schema, my changes are going to stomped on. Can you think of anything else? Thanks.

aLTeReGo

At best you will likely need to recreate this property after upgrading or the Configuration Wizard is run and schema is updated. At worst, you may encounter erroneous errors when upgrading, requiring you to drop your computed field from the table before proceeding with the upgrade. I'd recommend testing on lab/test machine to see what the actual effect is. If you can re-run the configuration wizard you should be fine (for now), but future upgrades could break due to an unexpected computed field.