topic Re: use information from a lookup to extract values from a field with a rex in Splunk Enterprise

use information from a lookup to extract values from a field with a rex

amir_bnp — Tue, 28 Jul 2020 12:05:37 GMT

hello everyone,

i need some help for a request.

I have a lookup which contains area country code (phone) associated to country and i want to use these area code( +33, +32,..) to extract from the "calling" field the code which is at the beginning of the number.

EX:

lookup.csv

num country

+33 France

+32 Belgium

+44 England

+351 Portugal

+216 Tunisia

Field "calling":

calling

+33xxxxxxxxx

+351xxxxxxxxx

+216xxxxxxxx

+32xxxxxxxx

I want to use a rex which points to the "num" field in the lookup file and which will automatically extract the area code in the "calling" field thanks to the lookup.

can we use the "num" field of the lookup file as a variable and put this variable in a rex?

I think that making a rex that identifies the first 2 or 3 is difficult because you will have to do it one by one

Thx

Re: use information from a lookup to extract values from a field with a rex

rnowitzki — Wed, 29 Jul 2020 08:47:59 GMT

Hi @amir_bnp ,

I came up with a solution. Maybe not the smartest/shortest way, but it works with some sample data.

| makeresults | eval calling="+491345256 +32245245134 +35162456235 +216456724356 +444562345 +339324561" | makemv calling | mvexpand calling | eval sub_calling_3=substr(calling,1,4) | eval sub_calling_2=substr(calling,1,3) | lookup countrycode.csv num as sub_calling_3 OUTPUT country as country_3 | lookup countrycode.csv num as sub_calling_2 OUTPUT country as country_2 | eval country=if(country_2!="",country_2, country_3) | fields - sub_calling_2, sub_calling_3, country_2, country_3

You only need the SPL starting from the first eval. The lines above it are just to create some sample data.

It tries to match the country with either 2 or 3 digit country code (the 2 lookup lines) and then creates the final country field from either one of them with the eval if(....).

It would fail (or produce random output) if there are countries that share the same first 2 digits.
So, if country A has +12 and Country B has +123, not sure if this combination exists.

Hope it helps.
BR
Ralph

--
Karma and/or Solution Tagging appreciated.

Re: use information from a lookup to extract values from a field with a rex

to4kawa — Wed, 29 Jul 2020 10:21:13 GMT

sample:

recommend:

index=yours
| inputlookup append=t lookup.csv
| eval nums=num."%:".country
| evenstats list(nums) as nums
| mvexpand nums
| eval num=mvindex(split(nums,":"),0), country=mvindex(split(nums,":"),1)
| where like(calling,num)

OR

https://docs.splunk.com/Documentation/Splunk/8.0.5/Knowledge/Usefieldlookupstoaddinformationtoyourevents

modify CSV and try match type WILDCARD

Re: use information from a lookup to extract values from a field with a rex

amir_bnp — Wed, 29 Jul 2020 11:27:41 GMT

hi @rnowitzki ,

Thx for your answer and it works for me.

It taught me a lot and concerning your concern about similar code, I checked and it should not be a problem

amir

Re: use information from a lookup to extract values from a field with a rex

amir_bnp — Wed, 29 Jul 2020 11:31:43 GMT

hi @to4kawa

thx for your answer but when i run your request, i have a pb due to excessive memory usage.

When i run step by step your request there are 1 million event at a time and splunk truncate event so at the end i dont have all my event.

thx for your help

amir

topic Re: use information from a lookup to extract values ​​from a field with a rex in Splunk Enterprise

use information from a lookup to extract values ​​from a field with a rex

Re: use information from a lookup to extract values ​​from a field with a rex

Re: use information from a lookup to extract values ​​from a field with a rex

Re: use information from a lookup to extract values ​​from a field with a rex

Re: use information from a lookup to extract values ​​from a field with a rex

topic Re: use information from a lookup to extract values from a field with a rex in Splunk Enterprise

use information from a lookup to extract values from a field with a rex

Re: use information from a lookup to extract values from a field with a rex

Re: use information from a lookup to extract values from a field with a rex

Re: use information from a lookup to extract values from a field with a rex

Re: use information from a lookup to extract values from a field with a rex