Extract fields from multi value fields

[chiwang]

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?

[martin_mueller]

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]),"

[martin_mueller]

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

[chiwang]

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

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

[okrabbe_splunk]

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

[martin_mueller]

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

[chiwang]

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

CA is not listed
I am using splunk 4.3.1.

[martin_mueller]

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

[chiwang]

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?