Splunk Search

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

Dosambela1
New Member

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.

0 Karma

sundareshr
Legend

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
0 Karma

Dosambela1
New Member

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.

0 Karma

sundareshr
Legend

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?

0 Karma

Dosambela1
New Member

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...