This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL query variable in alert trigger condition does not work

I have created a custom property called "Alerting" on my Group objects, and I want to know when a user changes the value of the "Alerting" custom property to "No." I've set up an Auditing Event alert that will trigger if the event's Action Type is equal to "Group changed." So far so good.

I'm running into problems because the Alert Manager doesn't provide an easy way to get from the Audit Event ID to the custom properties of the object of the audit. I wrote a SQL query to find the value of the custom property for the group that was changed:

SELECT Alerting from ContainerCustomProperties where ContainerID = (SELECT NetobjectID from AuditingEvents where AuditEventID = ${N=SwisEntity;M=AuditEventID})

Looking at the Alert Manager, it seems as though I should be able take that query and add it to the conditions as a custom variable:

pastedImage_12.png

However, when I do this, the alert doesn't trigger when a group's "Alerting" property is changed to False. I've tried replacing "False" with 0 (zero), but that doesn't work either.

When I view the generated SWQL for the trigger condition, this is what I see:

SUBSCRIBE CHANGES TO Orion.AuditingEvents

INCLUDE [Uri] AS [Uri], [DisplayName] AS [DisplayName]

WHEN ( ( ( AuditingActionType.[ActionType] = 'Orion.GroupChanged' ) AND ( '${SQL: SELECT Alerting from ContainerCustomProperties where ContainerID = (SELECT NetobjectID from AuditingEvents where AuditEventID = ${N=SwisEntity;M=AuditEventID})}' = 'False' ) ) AND ( (  ADDED ) ) )

This looks like it ought to work. Should it? Does Solarwinds actually support this, or is it something unsupported that the app just happens to let me set up that way?

  • If this is the only custom property on your groups you could try something like I have below for your alert.

    pastedImage_1.png

    You can also add another line like the bottom one that would read like this to further narrow the search:

    pastedImage_2.png

  • Thanks. I set this up, but it looks like the "Custom Property edited" action refers to editing the definition of a custom property, not to changing the value of a custom property. If I change the value of "Alerting" for a group from Yes to No, the alert isn't triggered. If I edit the definition of the custom property  (e.g. change the description from "Should Solarwinds generate alerts for this group" to "Boo!") the alert is triggered and Solarwinds logs a "Custom Property edited" audit event. If I change the value of the custom property, Solarwinds logs a "User (username) has changed Group (group name)" audit event.

  • For clarification is this a True/False property?

    If so you may have write the alert trigger as a SQL Query that combines the audit events table with the container table that records the custom property changes.

    In the alert definition I do not believe that it will translate the SQL macro that you are trying to use. (Anyone is welcome to prove me wrong, but I have not been able to get it to work)

    Let me know if this idea may work.

  • Yes, in my example, "Alerting" is a True/False property (represented in the database as 0/1, and in the GUI as Yes/No). I did end up writing a trigger query that looks at the AuditEvents table and the ContainerCustomProperties table:

    SELECT AuditingEvents.AuditEventMessage, AuditingEvents.AuditEventID FROM AuditingEvents

    WHERE

    ActionTypeID = 20 and

    NetObjectType = 'C' and

    DateDiff(mi,TimeLoggedUTC,GetUTCDate()) < 2 and

    ( SELECT Alerting from ContainerCustomProperties

    where ContainerID = AuditingEvents.NetObjectID

    ) = 0

    This works, but a custom SWQL alert for Audit Events doesn't subscribe to the Audit Events queue. Instead, I have to run it every N minutes and look for newer audit events that match. Not a huge deal, but I was hoping to take advantage having the alert tested every time an audit event is logged.

  • You still can.

    You can have the SQL query that you have and have that as the trigger for the alert. The alert will run that query every time you want it to (5 mins is what I usually use for SQL alert triggers) and will alert if any results are returned.

    If you are not comfortable with this I can try to assist with finding another way.

  • Yes, I think I'll stick with my query and just have it run frequently. My end users like to see their changes take effect in real time. Five minutes might be too long for them.emoticons_wink.png