topic Re: Extract fields from multi value fields in Splunk Search

Extract fields from multi value fields

chiwang — Mon, 28 Sep 2020 13:25:25 GMT

I am trying to create a new fields from a multi value fields. Here's an example:

group_id, user_id  user_address         user_phone_number
a         123      cityA, NY, USA       123-234-345
          234      cityBC, NJ, USA      234-345-345
b         567      cityDEF, NY, USA     234-345-456

stats list(user_id), list(user_address), list(user_phone_number) by group_id

user_id, user_address and user_phone_number are multi value fields.
I want to be able to extract state information from user_address to generate a table like this

group_id, user_id  state         user_phone_number
a         123      NY            123-234-345
          234      NJ            234-345-345
b         567      NY            234-345-456

How can I achieve this?

Re: Extract fields from multi value fields

martin_mueller — Thu, 28 Feb 2013 22:17:04 GMT

The same way as extracting it from a single value field:

| stats count | fields - count | eval foo = "New York City, NY, USA;Los Angeles, CA, USA" | makemv foo delim=";" | rex field=foo ", (?<state>[A-Z][A-Z]),"

Re: Extract fields from multi value fields

chiwang — Mon, 28 Sep 2020 13:25:47 GMT

With this query, if I run

stats list(user_id), list(state), list(user_phone_number) by group_id

I will get this:

group_id, user_id  state         user_phone_number

a         123      NY            123-234-345

          234                    234-345-345

b         567      NY            234-345-456

Only the first state in a group is displayed

Any idea how to get a full list of states?

Re: Extract fields from multi value fields

martin_mueller — Fri, 01 Mar 2013 17:04:46 GMT

What result are you getting when you enter my example query into splunk?

Re: Extract fields from multi value fields

chiwang — Fri, 01 Mar 2013 21:25:15 GMT


foo                                 state
New York City, NY, USA              NY
Los Angees, CA, USA

CA is not listed
I am using splunk 4.3.1.

Re: Extract fields from multi value fields

martin_mueller — Fri, 01 Mar 2013 21:34:01 GMT

I see. Over here in 5 it works like a charm.

Re: Extract fields from multi value fields

okrabbe_splunk — Fri, 01 Mar 2013 22:18:05 GMT

Maybe try a different regex?
(?[^,]+,[A-Z]+)

Re: Extract fields from multi value fields

chiwang — Mon, 04 Mar 2013 15:53:55 GMT

Updated with (?[^,]+,[A-Z]+), no states are returned.

foo                                 state
New York City, NY, USA              
Los Angees, CA, USA

Re: Extract fields from multi value fields

martin_mueller — Mon, 04 Mar 2013 15:55:53 GMT

The issue is not the expression, that worked fine. Apparently your Splunk version treats multi-valued fields differently.