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

[dan_tembe]

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.

Thanks!

Dan

Message was edited by: Dan Tembe

[sergiu_panaite]

Hi Dan,

What about a before insert business rule on em_alert where you do a GlideRecord call in the external table and try to see if the values matches?

[dan_tembe]

Hello Sergiu,

Thanks for the response. "Before Insert" business rule is the best place now that I read through. I am trying to structure a Glide Record query but for some reason I cannot seem to structure the function to field in lookup table to match the field in alert table part. Just being a noob, but I will update with details in a bit.

Thanks!

Dan

[sergiu_panaite · Accepted solution]

I guess you're looking for something like (where BR is before insert in em_alert table and current.resource refers to a field on current record on em_alert):

var gr = new GlideRecord('customer_data_lookup');
gr.addQuery('customer_uuid', current.resource);
gr.query();


if (gr.next()){
   current.u_customer = gr.customer_name;
}

Of course, need adjusted to the fields you want.

[dan_tembe]

Hello Sergiu,

Thanks! That worked. I made the changes to my exact table & field names.

I am able to get the correct data in the customer field.

Much appreciate your response and help.

Best Regards
Dan