Splunk Search

Extract fields from multi value fields

chiwang
Explorer

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?

Tags (1)
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

chiwang
Explorer

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

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

okrabbe_splunk
Splunk Employee
Splunk Employee

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

chiwang
Explorer

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

CA is not listed
I am using splunk 4.3.1.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

chiwang
Explorer

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?

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...