Splunk Enterprise

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

amir_bnp
Explorer

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

 

 

Labels (2)
0 Karma
1 Solution

rnowitzki
Builder

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.

--
Karma and/or Solution tagging appreciated.

View solution in original post

0 Karma

to4kawa
SplunkTrust
SplunkTrust

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

0 Karma

amir_bnp
Explorer

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.

amir_bnp_0-1596022281031.png

 

thx for your help

 

amir

 

0 Karma

rnowitzki
Builder

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.

--
Karma and/or Solution tagging appreciated.

View solution in original post

0 Karma

amir_bnp
Explorer

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

 

 

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!