How do I separate multi-value field in individual fields?

[edrivera3]

Hi
I have the following syntax that extract multiple values for the same fields in an event.

This is the query:

index=.... | rex max_match=100 "(?<connBlock>\d+)\s+(?<connector>[\d\w]+)\s+Located\s+at\s+STA:\s*(?<sta_coord>[\d\w[^,]+)

For this query I get the following fields for each event:

connBlock = [500 600 700 800 ...] and stat_coord [A345 A3422 B2434 ...]

Those events also have a field called testNum which looks like "AFKE-232322".

I want to create a table like this:

testNUM            connBlock         stat_coord
AFKE-232322        500               A345
AFKE-232322        600               A3422
AFKE-232322        700               B2434
AFKE-232322        800               C745
... I also have different tests
AFBE-228322        500               A345
AFBE-228322        600               D3422
AFCE-005322        700               B2434

When I try to do a table, I get the table below, and the stat_coord, connBlock appear in the same row than the testNum.

testNUM            connBlock         stat_coord
AFKE-232322        500               A345
                   600               A3422
                   700               B2434
                   800               C745
AFBE-228322        500               A345
                   600               D3422
AFCE-005322        500               B2434

The reason for separating the fields is that I want to do a query like the one below and get the sta_coord or the connector based on a testNum and connBlock of an event.

index=.... | search testNum=AFKE-232322 connBlock=600

[fz · Accepted solution]

Hi edrivera3,

You can use mvexpand with eval to do this.

Here is the sample query for you.

Your base query
| eval tagged=mvzip(testNUM, connBlock)
| mvexpand tagged
| makemv tagged delim=","
| eval testNUM=mvindex(tagged,0)
| eval connBlock=mvindex(tagged,1)
| table *

Hopefully this will get you what your expecting to do.

Cheers!

[clehw]

Thanks so much for this! Also fixed my similar issues! 🙂 

[mihai_T]

Thanks a lot 🙂 !

[damiensurat]

Hi edrivera3. I've experienced these types of scenarios before and man. What a doozie. You may want to try to use the mvexpand on those fields if they are already considered multivalue. In some scenarios you may need to make the field a mv field first using the makemv command and then piping out to mvexpand. Try your search| mvexpand connBlock |mvexpand stat_coord. Here a link to a similar mv solution which may help as well: https://answers.splunk.com/answers/25653/mvexpand-multiple-multi-value-fields.html

If this doesn't work let me know and id be happy to further assist. Happy splunking!

[edrivera3]

damientsurat: Your solution is correct and it's the same as the one provided by fz. Do you want me accept your answer? If so, please write an answer with the solution. If you are not interested I'm going to accept fz's answer.

[damiensurat]

Ahhbone last thing. Check example 3 in the splunk mvexpand command doc

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Mvexpand

[edrivera3]

Thanks. I think this is the solution, but the results gets truncated. I actually have 5 different multi-value fields.

[strive]

Did you use limit=0 argument.

limit
Syntax: limit=
Description: Specify the number of values of  to use for each input event.
Default: 0, or no limit