topic Re: How to automatically change a field value from lookup table? in Splunk Search

How to automatically change a field value from lookup table?

MScottFoley — Mon, 18 Sep 2023 22:23:01 GMT

I have logs with a Customer field where the name of the customer is not consistent.
customer=Bobs Pizza
customer=Bob's Pizza
customer=Bobs Pizzeria

I want to use an automatic lookup to change all to a standard name without needing to changing existing searches.

customer_lookup.csv
customer_name,standard_customer_name
Bobs Pizza,Bob's Pizza
Bobs Pizzeria,Bob's Pizza

I am trying to do this with a lookup table in the search before I try to make it an automatic lookup.

| lookup customer_lookup customer_name as Customer output standard_customer_name AS Customer

This lookup only works if the Customer returned in the search is actually in the lookup table. So Customer="Bobs Pizza" is in the result, but Customer="Frank's Artichokes" is not. I can't add all customers to the table. I have tried many forms of the lookup. I can get a list with the original Customer name and the standard customer name in one exists, but that won't work for current searches.

Can this be done? I would think it could cause problems since someone could add an automatic lookup to hide certain things if needed.

Re: Automatically change a field value from lookup table

PickleRick — Mon, 18 Sep 2023 18:56:04 GMT

You can create a lookup with a WILDCARD match type.

Re: How to automatically change a field value from lookup table?

gcusello — Tue, 19 Sep 2023 06:31:52 GMT

Hi @MScottFoley ,

only to complete the solution from @PickleRick that's perfect, you have to:

go in [Settings > Lookups > Lookup definitions]
choose the lookup
flag Advanced Options
insert "WILDCARD" in Match Type
Save

Ciao.

Giuseppe

Re: How to automatically change a field value from lookup table?

bowesmana — Tue, 19 Sep 2023 07:36:04 GMT

You are overwriting Customer so if your lookup is not found, it will overwrite Customer

Do it like this

| lookup customer_lookup customer_name as Customer output standard_customer_name | eval Customer=coalesce(standard_customer_name, Customer)

so, if your Customer does not exist in the lookup, it will return a null standard_customer_name and then the coalesce will just use the original Customer

Re: How to automatically change a field value from lookup table?

MScottFoley — Tue, 19 Sep 2023 13:17:54 GMT

That is perfect. I see now why it was not working before.

Re: How to automatically change a field value from lookup table?

gcusello — Tue, 19 Sep 2023 13:22:20 GMT

Hi @MScottFoley ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉