Splunk Search

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

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)

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

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'

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

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

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

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

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

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

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.

Motivator

It did not work. I will edit the question.

0 Karma

Motivator

Erik,

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

Regards,
lp

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!