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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...