shank098 has asked for the wisdom of the Perl Monks concerning the following question:

Hey all

Hoping that someone can give me a hand with this one...

A while back now I created a script which queries a MySQL database with the goal being to send email notifications to the proper tech support staff. It's pretty basic stuff,

If SlotA = Value AND SlotB = Value AND SlotN = value then notify GroupA

So if all the Slots and values in the event match what we have in the DB, then we know who or what group to send the event to. The issue is that it's based completely on AND statements.

The problem that I have found with this method is that I have to create/maintain a ton of filters since all the slots/values have to match.

What I would like to do is something like the following,

If SlotA = Value and SlotB = Value and (SlotC = value OR SlotC = value) then notify GroupA

Anyone have any tips on how to tackle this?

Without getting into too much detail about the DB structure I have one table which holds all my filter information. Here's it structure,

id int(3)
fgid int(3)
slot varchar(20)
value varchar(55)

So an example of a filter might look something like this,

1 - 1 - slotA - value
2 - 1 - slotB - value
3 - 1 - slotC - value
4 - 1 - slotC - value

I am hung up on how to construct the logic in situations where a single slot can have multiple values...the original method with to simply dump all the values from the DB into a Hash and then check that slot/value combos against what was being received from the monitoring system...I am hung up on how to do the "OR" statements...

Any suggestions would be appreciated...

  • Comment on Building a notification system based on MySQL

Replies are listed 'Best First'.
Re: Building a notification system based on MySQL
by Illuminatus (Curate) on Nov 17, 2008 at 23:48 UTC
    Can't you simply store the "OR" statements as actual 'or' regex strings? For each set of rows with matching fgid's, create a hash keyed by slot values. First check if the slot value is defined in the hash. If not, just add it, otherwise append |value to the existing value for that slot key. Then, when an event comes in, use =~ to match slots instead of eq

    Does that make sense?

Re: Building a notification system based on MySQL
by jfroebe (Parson) on Nov 17, 2008 at 21:32 UTC

    Is your pseudo code if statement in the MySQL query or in your Perl code?

    Jason L. Froebe

    Blog, Tech Blog

Re: Building a notification system based on MySQL
by ig (Vicar) on Nov 17, 2008 at 23:30 UTC

    It might help if you showed relevant bits of your existing code, at least the parts that do not do what you want.

    How are your events received? Are they strings? Are they hashes with slot names as keys? Are they database records?

    In your existing code, with all the filter values from the DB in a hash, what is your code for checking the slot/value combos against the received events?

      here's the main chunk of code from the existing script, which is doing the job just fine

      while (my($gid,$gname) = each %groups) { if ($debug >= 3) { writeLog("I","Check notification group $gna +me");} ##Create an ARRAY with each filter for each group my @gArray = getFilterGroups($gid); foreach my $fGroup (@gArray) { my %fHash = getFilters($fGroup); my $fullMatch = getEntries($fGroup); if ($debug >=3) { writeLog("I","The filter group ($fGroup) + has $fullMatch entries");} my $count = 0; while (my($slot,$value) = each %fHash) { if ($debug >=3) { writeLog("I","Checking event against + FILTER:$slot=>$value");} my $result = checkEvent($slot,$value); if ($result == 1) { if ($debug >=3) {writeLog("I","Tivoli slot and fil +ter slot information match");} $count++; if ($debug >=4) {writeLog("I","Count is at $count +--> fullMatch is $fullMatch")}; if ($count == $fullMatch) { my @memberList = getMembers($gid); foreach my $member(@memberList) { if ($debug >=2) {writeLog("I","Generating +automated notification to $member");} #notifyLog($member); notify($member); } if ($debug >= 1) { writeLog("I","inserting int +o db log");} dblog($gid); $count = 0; } } } } }

      It simply loops through all the groups present in the DB and grabs the filters. It considers a match to be when all the slot/value combination's in the filter DB match with those received from the event.

      Events are received from a BMC product called Impact. It has a built-in rules engine and when certain criteria are satisfied we call the PERL script.

      Details of the events are passed to the script as environment variables, here's a code snippet,

      my $thost = $ENV{mc_host}; my $source = $ENV{source}; my $server_hndl = $ENV{server_handle};
Re: Building a notification system based on MySQL
by duckyd (Hermit) on Nov 18, 2008 at 00:14 UTC
    It sounds like when you've got multiple values, you might want to use an in statement, I.E.
    If SlotA = Value and SlotB = Value and SlotC in (value1, value2) then +notify GroupA
    I'm curious if you have another table that you're getting the Group to notify from - it seems likely to me that there's a way to determine the person to notify in a single sql query (without requiring building a query up dynamically in perl), but without knowing more about the specifics of the tables in question, it's impossible to say.

      There are actually a bunch of other tables,

      • users: this table contains email address, and user names
      • groups: this table defines all the groups - group id's and names
      • membership: this table relates users to groups
      • notify: this table relates filters to groups
      • filter: this table actually contains the filters

      Thanks for the tip! Here's the new code to handle a situation when the key already exists

      while (my($slot,$value) = $filterSth->fetchrow_array()) { if ($filerHash{$slot}) { //slot already exists, just append to it my $existingValue = $filterHash{$slot}; my $newValue = $existingValue . " | " . $value; $filterHash{$slot} = $newValue; } else { //slot does not exist, just add it $filterHash{$slot} = $value; } }