How to replace URLs in multivalue fields in my logs with values from a CSV lookup file?

[Dosambela1]

Hi.

I have a lookup which contains a list of URLs and 3 more fields loaded from a CSV file:

Example:
URL, value1, value2, value3
/til/showCode.html,Personal,,Update code,
/soc/showUpdateData.html,Personal,ShowUpdate,Update profile,
/rel/changeCert.html,Admin ,Users,Change Certif,

And I have 4 multivalue fields in my logs which could contains the URLs of the list:

Example:

Field1

Value1:

/til/showCode.html (mcount n=25)
/soc/showUpdateData.html

Value2:

/soc/showUpdateData.html (count m=100)
GET/back/return.html (mcount n=200)

Field2

Value1:

/soc/showUpdateData.html | 8 3 NA
/soc/showUpdateData.html | 7 17 NA
GET/til/sd/security | 2 5 NA

Value 2:

POST/authent.html | 2 4 NA
/rel/changeCert.htm | 8 23 NA

I need to replace in the fields which could contain URLs with values that exist in the lookup. So, I need to replace the URLs with the values value1+value2+value3 in the lookup:

Field1

Value1:

Personal Update code (mcount n=25)
Personal ShowUpdate Update profile

Value2:

Personal ShowUpdate Update profile (count m=100)
GET/back/return.html (mcount n=200)

Field2

Value1:

Personal ShowUpdate Update | 8 3 NA
Personal ShowUpdate Update | 7 17 NA
GET/til/sd/security | 2 5 NA

Value2:

POST/authent.html | 2 4 NA
Admin Users Change Certif | 8 23 NA

Thanks very much for helping me.

[sundareshr]

Try this

your base search | table f1 f2 | rex max_match=0 field=f1 "(?<url1>[^\(]+)(?<rest1>.*)" | rex max_match=0 field=f2 "(?<url2>[^\|]+)(?<rest2>.*)" | lookup urllookup.csv url AS url1 OUTPUT value1 AS val1_f1 value2 AS val2_f1 value3 AS val3_f1  | lookup urllookup.csv url AS url2 OUTPUT value1 AS val1_f2 value2 AS val2_f2 value3 AS val3_f2 | eval f1_url=url1." ".val1_f1." ".val1_f2." ".val1_f3

[Dosambela1]

Thanks very much.

It has worked very well. I have only one probleme. If the value or url1 does not exist in urllookup.csv i need to keep them in the result.

I tried that:

eval f1_url=if((isnull(val1_f1 ) AND isnull(val2_f1 ) and isnull(val1_f3)), url1, val1_f1 ." ".val2_f1 ." ".val1_f3)

But only works ok of none of the urls in the multivalue fields exist in the lookup. If one or more exists, it returns the match of the urls which exists but not the others who does not exists in the lookup.

Thanks very much for helping me.

[sundareshr]

Which are the 4 multi-value fields in the logs? I see field1 & field2, how are values 1 & 2 related to field 1 & 2. Does field 1 have both values 1 & 2?

[Dosambela1]

Good morning.
In fact I have given the example with 2 multi-values fields (field1 and field2). And I have given examples with possible values (value1, value2...). My original problem is with 4 multi-values fields (field1, field2, field3, field4) and the possibles values are the given values (value1, value2...).

Thanks very much.