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

[amir_bnp]

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

[to4kawa]

sample:

| makeresults
| eval calling="+491345256 +32245245134 +35162456235 +216456724356 +444562345 +339324561"
| makemv calling 
| mvexpand calling
| append [ | makeresults
| eval _raw="num,country
+33,France
+32,Belgium
+44,England
+351,Portugal
+216,Tunisia"
| multikv forceheader=1 
| table num country
| eval nums=num."%:".country
| stats list(nums) as nums]
| eventstats list(nums) as nums
| mvexpand nums
| eval num=mvindex(split(nums,":"),0), country=mvindex(split(nums,":"),1)
| where like(calling,num)

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/Usefieldlookupstoaddinformationtoyourev...

modify CSV and try match type WILDCARD

[amir_bnp]

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

[rnowitzki · Accepted solution]

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.

[amir_bnp]

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