sql_response alert on value not generating a separate alarm for each matching row

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

• Using the Value tab, I created an entry in Alarm List with a regex value of .* for one of the columns (doesn't matter which since it is .*)
• For the most part out of about 10 rows returned it seems to always generate an alarm for the last row.  Sometimes for a random row in between.
• I used OC to look at active and historical alarms but no evidence shows that it ever created an alarm for each row other than the ones I mentioned above.
• My customer wants it to generate an alarm for each row so that each respective alarm contains the row data which in the end results in a unique service desk ticket for each row.  It is my understanding that it is supposed to create an alarm for each matching row.

Environment

• Release: 20.4

Cause

• Guidance, configuration

Resolution

Due to testing results, we requested that DBA add an Id type column to the table for the select query to run against and this enabled us to generate alarms that were unique based on the ID.

Then the alarms would not be suppressed nor acted upon by any nas AO profile or preprocessing rule - since we suspected some initial 'interference' due to the broadness of some of the regexes in the nas rule base and the fact that one of the alarm messages was being suppressed and had a high count. The total number of alarms was correct (36) but one alarm was being suppressed 33 times. We also noticed alarms being generated but then being cleared/suppressed very quickly.

To enable a single alarm for every distinct row in the query result, we used a regex of:

^[a-zA-Z0-9 ]*$/

and operator set to "in"

with Row key set to the "Id" column

Then with a low monitoring interval we temporarily set for testing, e.g., 2 mins, we restarted the sql_response probe and the probe generated a single alarm for each row in the table (122 alarms).

Note that when you run the Test query in the probe the results are limited (by design) to 50 rows, but that had no affect on the final result.