Splunk Search

Can splunk do this? search command -> mvexpand -> with more than one multi value field

lpolo
Motivator

I have a data set that could have more than one multi-value field "MV-Field". Each value of each MV-field corresponds to each other. Therefore, I need to expand so every value is an event. Example:

_time         MV-field_1  MV-field_2
timestamp1        1             2
                  2             3
                  5             2

by expanding I would have:

_time        field_1      field_2
timestamp1     1             2
timestamp1     2             3
timestamp1     5             2

I tried mvexpand but it only works with one mv_field.

The query in question is the following:

sourcetype="test" | spath output=id "schedule{}.id" | spath output=recurrence "schedule{}.recurrence" |mvexpand id|fields _time id recurrence

Any ideas.

Thanks,
Lp

Tags (1)

lydiapal_splunk
Splunk Employee
Splunk Employee

Really late to the party but this still shows in Google search results so here you go:

 

| eval mv_combined_field=mvzip(mvfield1,mvfield2,"|")
| mvexpand mv_combined_field
| eval mv_combined_field_expanded=split(mv_combined_field,"|")
| eval mvfield1=mvindex(mv_combined_field_expanded,0), mvfield2=mvindex(mv_combined_field_expanded,1)

 

The delimiter "|" was chosen assuming your data does not contain pipes. It is just to separate the events for later extraction. So with mvzip we take first value of mvfield1 and mvfield2 and combine them together (separated by "|"), same with second with second and so on. Now we've created a single mv field.Now expand with mvexpand and split the values, taking first value with mvindex and second value with mvindex.

For more than 2 fields you can nest it (unlimited times):

 

| eval mv_combined_field=mvzip(mvfield1,mvzip(mvfield2,mvfield3,"|"),"|")
| mvexpand mv_combined_field
| eval mv_combined_field_expanded=split(mv_combined_field,"|")
| eval mvfield1=mvindex(mv_combined_field_expanded,0), mvfield2=mvindex(mv_combined_field_expanded,1), mvfield3=mvindex(mv_combined_field_expanded,2)

 

Hope this helps!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Assuming that the mv fields MUST have the same number of items...

sourcetype="test" 
| spath output=id "schedule{}.id" 
| spath output=recurrence "schedule{}.recurrence"

| rename COMMENT as "determine how many values (n) there are and split into that many events myFan=(zero to n-1)"
| eval myFan=mvrange(0,mvcount(id))
| mvexpand myFan

| rename COMMENT as "select the nth value for each field based on myFan"
| eval id=mvindex(id,myFan)
| eval recurrence=mvindex(recurrence,myFan)
0 Karma

mzorzi
Splunk Employee
Splunk Employee


You should use the mvzip function of eval:

| eval fields = mvzip(field_1,field_2) | mvexpand fields | rex field=fields "(?\d+),(?\d+)" | table _time alpha beta'

sbsbb
Builder

mvzip is only for two fields...
What if you have an large number of fields you want to mvexpand ?
Sometimes you don't even know how many fields it is, perhaps a multi_mv_expand is needed, like muli_mv_expand(field1,field2,field3) or even multi_mv_expand(field*)

0 Karma

dart
Splunk Employee
Splunk Employee

For fields a and b as multivalued fields that are both numeric:

sourcetype=test | eval fields = mvzip(a,b) | mvexpand fields | rex field=fields "(?<alpha>\d+),(?<beta>\d+)" | table _time alpha beta

lpolo
Motivator

Just reading it makes total sense. I did not know that mvzip was part of aval functions. I should review the eval functions one more time.

It should work.

Thanks,
Lp

0 Karma

lpolo
Motivator

I cannot find concatenate function you mention in this url:

http://docs.splunk.com/Documentation/Splunk/4.3.4/SearchReference/CommonEvalFunctions

Therefore, I used the eval concatenation method I showed you.
What I was able to find is the command “strcat”. I can join both fields but the result is not a multivalue field. Therefore, I cannot expand using mvexpand.

This is the query:

sourcetype="test"|head 1 | spath output=id "schedule{}.id" | spath output=recurrence "schedule{}.recurrence" |fields _time host id recurrence|strcat id "--" recurrence join

The result of join is a string with the following format:

list of id fields – list of recurrence fields. 

My goal is to go from this data set:

_time         MV-field_1  MV-field_2
timestamp1        1             2
                  2             3
                  5             2

To this data set:

_time        field_1      field_2
timestamp1     1             2
timestamp1     2             3
timestamp1     5             2

with stcat I can get:

_time        join
Timestamp1      1 2 5 – 2 3 2

Is strcat the command that you recommended?

Regards,
Luciano

0 Karma

emotz
Splunk Employee
Splunk Employee

I would recommend using eval and the eval-concat-exp command to put the 2 strings together. This is not an OR operation. I meant you should combine the 2 fields with a seperator or even just a space and then do the mvexpand.

0 Karma

lpolo
Motivator

Erik,

I tried your recommendation as well and it does not work. It returns zero result. I think splunk cannot do it.

sourcetype=test host="FQDN" | head 1 | spath output=id "schedule{}.id" | spath output=recurrence "schedule{}.recurrence" |fields _time host id recurrence|eval Result=id."-".recurrence|table Result

0 Karma

emotz
Splunk Employee
Splunk Employee

I would create a field3 = mv-field_1 | mv-field_2 and then use mvexpand on field3.

You could then extract it as field_1 and field_2 from the resulting events.

Without knowing what you want to do with the results, it is hard to determine the best solution, but hopefully this works for your situation.

lpolo
Motivator

It did not work. I will edit the question.

0 Karma

lpolo
Motivator

Erik,

Thanks for the trick. I will test it later on.

Regards,
lp

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...