4 Replies · Latest reply on Sep 14, 2017 7:21 AM by Dan Tembe

    Update a field in em_alert table based on lookup in a different table, match

      I am struggling to overcome this particular issue.


      I have alerts that I am creating from events coming from a monitoring tool. The events that create the alert don't contain any customer name. So I created a custom table called customer_data_Lookup . The customer_data_lookup table has a field that contains the same information as the event or alert field "resource".  I wish to do the following, when an alert is created, I want to fire off a lookup query to custom_data_lookup table. If the value from resource filed in em_alert matches customer_uuid field in customer_data_table, then take the value from customer_name field in the customer_data_table and insert it in the u_customer field in the em_alert table.


      Not sure if I am explaining it correctly.


      Basically I want to do a match on a value in resource field in the em_alert table to a field in an external table, if matched then I want to update a different field in em_alert table with data from another field in customer_data_lookup.


      em_alert table

      resource field     u_customer field

      11111               <blank>

      3333               <blank>

      2222               <blank>

      4444               <blank>


      customer_data_lookup table

      customer_UUID     Customer_Name

      1111                         custA

      2222                         custB

      3333                         custC

      4444                         custD


      on every new alert, I want to do a lookup to customer_data_lookup table, if em_alert.resource == customer_data_lookup.customer_UUID, then update the alert row, em_alert.u_customer with data from customer_data_lookup.Customer_Name


      End result that I am looking for is


      em_alert table

      resource field     u_customer field

      11111                  custA

      3333                   custC   

      2222                   custB 

      4444                   custD



      What I tried so far -

      1) I have tried to create a transform map, but I am not clear on how to create the mapping, and make it fire off when there is a new alert inserted in the em_alert table. I tried different script samples which didn't produce en error but did not update the u_customer field in em_alert table. So I gave that up.


      2) I also tried "Data Lookup Rule" but there I cannot make the rule work because

                for matching I want to use

                     source table =em_alert and matcher table =customer_data_lookup

                but if there is a match then I want to use

                     source table = customer_data_lookup and matcher table = em_alert.


      I would really appreciate any direction here that would help me put in customer name into my alert along with the customer UUID.




      Message was edited by: Dan Tembe