topic Re: How to extract area codes from phone number field? in Splunk Search

How to extract area codes from phone number field?

jhillenburg — Mon, 02 Feb 2015 20:42:14 GMT

Hi. I have a series of systems (contact center, fax, Cisco CUCM, etc) where phone numbers are returned in the data. The phone numbers can be represented by:

5-digit extension (ie. x22101)
10-digit NANP number (i.e. 3125551212)
11-digit NANP number (ie. 13125551212)
E.164 number (+13125551212)

For the moment, I am not concerned about #1 or #4, but I would like to do two things: Match only callers that have 10 or 11-digit phone numbers, strip the country code "1" when it shows up, and then leave only the area code for area code mapping.

Has anyone done this?

Re: How to extract area codes from phone number field?

jrmurray — Mon, 28 Sep 2020 18:49:22 GMT

Try a calculated field.

Settings -> Fields -> Calculated Fields

if(len(phone_number)==10 or len(phone_number)==11,replace(phone_number, "^1?(\d{3})\d{7}$","\1"))

You will have to associate it with your target source, sourcetype, or host, as well as the target app. Might have to mess with the regular expression and matched field length to include the + sign.

--
J.R. Murray
MetaNet IVS

Re: How to extract area codes from phone number field?

KindaWorking — Mon, 02 Feb 2015 22:24:09 GMT

If I am understanding what you are after. This is the regex you can use for the field extractor.

 1?(?<NANP>\d{10}\d?)

Re: How to extract area codes from phone number field?

jayannah — Mon, 02 Feb 2015 22:56:46 GMT

You know the country code is always 1 (if present)

                            1?(?<ph_num>\d{10})  ==> This should return 10 digit irrespective whether country code 1 is prefix or not.
                            \d*(?<ph_num>\d{10})  ==> This return last 10 digits from phone number irrespective of whether the country code is 1 or 22 or 100 or not prefixed.

Re: How to extract area codes from phone number field?

masonmorales — Tue, 03 Feb 2015 00:43:52 GMT

If you are only after the area code for those two scenarios, try this:

rex "1?(?<area_code>\d{3})"

Re: How to extract area codes from phone number field?

jhillenburg — Tue, 03 Feb 2015 20:51:17 GMT

This is great, except that it matches 5-digit extensions beginning with "1".

Re: How to extract area codes from phone number field?

jhillenburg — Tue, 03 Feb 2015 20:53:52 GMT

What I wanted to do was to pull out the area code. I think another responder below has gotten me a lot closer. However, what you have done has solved a related problem. That, I have one system where the logs easily provide me with the telephone number, but another one where I needed to perform field extract, and your regexp might be helpful. Thanks.

Re: How to extract area codes from phone number field?

jhillenburg — Tue, 03 Feb 2015 20:55:43 GMT

Thanks. This seems to be helpful in the same manner as the response from KindaWorking,, above.

Re: How to extract area codes from phone number field?

jhillenburg — Tue, 03 Feb 2015 20:58:26 GMT

I'm trying to determine what this gets me that the other responses don't. Thanks for the response.

Re: How to extract area codes from phone number field?

jrmurray — Tue, 03 Feb 2015 21:09:15 GMT

This basically says if the phone number is 10 or 11 digits, perform the regular expression match and preserve only the digits you are looking for. In your example, a field extractor would probably work just as well, but this gives you a little more flexibility I think. On second glance, it looks like a character was missing from my initial response, so try this instead:

if(len(phone_number)>=10 and len(phone_number)<=12,replace(phone_number, "^\+?1?(\d{3})\d{7}$","\1"))

This will take care of handling the + operator as well. The field extractor version of this regular expression would be something like:

\+?1?(?P<area_code>\d{3})\d{7}

Re: How to extract area codes from phone number field?

masonmorales — Tue, 03 Feb 2015 21:31:21 GMT

What if you changed it to:

 rex "1?(?<area_code>\d{3})\d{6}"