topic Re: Extracting country codes from phone numbers in Splunk Search

Extracting country codes from phone numbers

himynamesdave — Sun, 20 Apr 2014 09:22:40 GMT

Lets see how many of you are up early this Easter Sunday - bonus point on offer 🙂

I have a lookup with a list of phone country codes - copypasta from http://countrycode.org/ - here's an example:

Code, Country
+1, USA
+44, UK
...

My logs are delivered in a nice format, with the phone number parsed as:

Number
+44789xxxxxxx

The problem I'm having is that country codes are of varying length making a "country code" field extraction difficult.

Is there anyway, preferably without regex, I can use the intelligence from the CSV to pull out the country code into a separate field?

Thanks guys!

Re: Extracting country codes from phone numbers

MuS — Sun, 20 Apr 2014 09:58:45 GMT

Hi himynamesdave,

This is completly untested, but try something like this:

 YourBaseSearch | eval Code="" | eval Country="" | inputcsv YourCSVfile append=true | eval myCountry=if(match(Number, Code), Country, "no match") ...

This is still untested 😉

UPDATE:

now this is tested and it is working 😉

YourBaseSearchHere | inputcsv YourcsvHere append=t | stats values(Code) AS Code values(Country) AS Country values(Number) AS Number | mvexpand Code | eval Code=substr(Code,2) | eval CountryName=if(match(Number, Code), Country, "No match") | table Number Code CountryName

What is happening here?
First you search your events and append some information from the csv, pass the values of the three fields Number Code and Country to stats, expand the multivalued in Code and remove the + from the Code. At last check if the Code matches any number and return a table of Number, International dialing code and Country name.

Hope this helps to get you started and thx @martin_mueller for pushing me in the right direction...

Cheers, MuS

Re: Extracting country codes from phone numbers

martin_mueller — Sun, 20 Apr 2014 10:07:28 GMT

I don't think that'll work - how does the Code and Country end up in the event with the Number after the appended inputcsv?

Re: Extracting country codes from phone numbers

krish3 — Sun, 20 Apr 2014 10:56:54 GMT

try this...And this is using regex 🙂

.... your search|rex field=Number "+(?<code>(1|44|91|....)"

For this to work you should have a Number field holding all the phone numbers as you have mentioned example in question.

Re: Extracting country codes from phone numbers

MuS — Sun, 20 Apr 2014 11:26:57 GMT

your right, as I said it is untested 😉
but see my update .... this could work, maybe ... still untested ...

Re: Extracting country codes from phone numbers

MuS — Sun, 20 Apr 2014 12:07:45 GMT

update ping

Re: Extracting country codes from phone numbers

martin_mueller — Sun, 20 Apr 2014 22:13:00 GMT

That should leave you with one row per country code...?

Re: Extracting country codes from phone numbers

martin_mueller — Sun, 20 Apr 2014 22:23:19 GMT

How about this:

search yielding Number field
| rex field=Number "^(?<Code_4>(?<Code_3>(?<Code_2(?<Code_1>+\d)\d)\d)\d)"
| lookup country_codes Code as code_4 OUTPUT Country as Country_4
| lookup country_codes Code as code_3 OUTPUT Country as Country_3
| lookup country_codes Code as code_2 OUTPUT Country as Country_2
| lookup country_codes Code as code_1 OUTPUT Country as Country_1
| eval Country = coalesce(Country_4, Country_3, Country_2, Country_1)

I've assumed that in case of ambiguous starts to a code (e.g. US +1 vs American Samoa +1684) the longer match should win. I didn't resolve US vs Canada, that's a long list in and of itself.

Re: Extracting country codes from phone numbers

adityapavan18 — Mon, 21 Apr 2014 07:17:45 GMT

Hi himynamesdave,

If your end goal is to list the country name fora respective phone number, have you tried lookup with country code fields with "*"

Have lookups like

PhoneNumber Country
+91* India
+1* USA 
.. ..
.. ..

And try , i guess you dont need to extract country code anymore

Re: Extracting country codes from phone numbers

himynamesdave — Mon, 21 Apr 2014 10:59:06 GMT

Unfortunately this will not work due to the ambiguous starts to a code (e.g. US +1 vs American Samoa +1684) 😞

Re: Extracting country codes from phone numbers

lakromani — Wed, 17 Jun 2015 08:40:48 GMT

I hope I can hang on too this thread, since my problem is equal:

phone.csv

Code, Country
45, Denmark
46, Sweden
47, Norway
48, Poland

Example line list

Jun 17 10:27:34 172.30.112.1 AAA: 172.30.34.58 logged in with username 0045696744444

client_ip = 172.30.34.58 
 client_site = House_of_fun 
 eventtype = Portal_User_logged_in Information  
 module = AAA 
 sourcetype = udp:514 
 tag = Information 
 username = 0045696744444

So then I tried to modify your line and got this:

 host="172.30.112.1" username="00*" | inputcsv phone.csv append=t | stats values(Code) AS Code values(Country) AS Country values(username) AS username | mvexpand Code | eval Code=substr(Code,3) | eval CountryName=if(match(username, Code), Country, "No match") | table username Code CountryName

or this:

host="172.30.112.1" username="00*" | inputcsv C:\Program Files\Splunk\etc\apps\search\lookups\phone.csv append=t | stats values(Code) AS Code values(Country) AS Country values(username) AS username | mvexpand Code | eval Code=substr(Code,3) | eval CountryName=if(match(username, Code), Country, "No match") | table username Code CountryName

Can you see what is wrong? Why it just give list with all numbers, and on first line I get under Country name "No match"?

PS, This: "eval Code=substr(Code,3)" should remove the two "00"? (starting from third character)
Also tried do add "00" to the csv file, and also with and without the "substr" code

Re: Extracting country codes from phone numbers

MuS — Thu, 18 Jun 2015 19:22:57 GMT

Try this:

host="172.30.112.1" username="00*"
| inputcsv phone.csv append=t
| stats values(Code) AS Code values(Country) AS Country values(username) AS username
| eval username=substr(username,3)
| eval CountryName=if(match(username, Code), Country, "No match")
| table username Code CountryName

in your use case the username is the phone number so you have to use this in the eval and compare it with the Code

Re: Extracting country codes from phone numbers

lakromani — Tue, 30 Jun 2015 18:56:56 GMT

It does work. When I got some time I will post how.

Re: Extracting country codes from phone numbers

mjegannathan — Tue, 29 Sep 2020 07:46:45 GMT