How to expand/extract multivalue fields line by line?

[timo258]

Hi!

I have 3 multivalue fields (max. 3 values per field) and I want to expand/extract them to single values. Data looks like this:

When I use | mvexpand Splunk extracts to all skills, all skillLevels with all skill hours:

How can I tell splunk to extract only line by line? 
Result should look like:

Skill	SkillLevel	Hours
Hardware-Techniker	3 Advanced	10
Software-Entwickler Sonderprogramme (C, C++)	3 Advanced	15

Query: (without | mvexpand)

| eval Skills = mvappend(customfield_26202_child_value, customfield_26204_child_value, customfield_26205_child_value)
| eval SkillLevel = mvappend(customfield_26206_value, customfield_26207_value, customfield_26208_value)
| eval Hours = mvappend(customfield_26300, customfield_26301, customfield_26302)
| table Skills,SkillLevel,Hours

Thank you very much!

[ITWhisperer · Accepted solution]

| eval range=mvrange(0,mvcount(Skills))
| mvexpand range
| eval Skills=mvindex(Skills,range)
| eval SkillLevel=mvindex(SkillLevel,range)
| eval Hours=mvindex(Hours,range)
| fields - range

[timo258]

Works just perfect! Thank you very much!! 

[gcusello]

Hi @timo258,

you should see the mvexpand command (https://docs.splunk.com/Documentation/Splunk/9.0.0/SearchReference/Mvexpand) and try something like this:

index=your_index
| mvexpand Skill
| stats sum(Hours) AS Hours values(skillLevel) AS skillLevels BY Skill

Ciao.

Giuseppe

[timo258]

Hi Giuseppe,

Thank you for your answer!

The problem with your solution is, if I do a: 

| stats sum(Hours)

Splunk will sum up all hours in that field: 

[gcusello]

Hi @timo258,

the problem is that multivales field are ordered in alphabetical order on the single field, in other words, the first value of the first multivale isn't sure that it's corresponding to the first value of the second field.

How do you arrived to this multivalues? are they the result of a stats command or are they in the row events?

in the fist case ,please share the code, in the second, please share some sample of your events.

Ciao.

Giuseppe

[timo258]

Hi Giuseppe,

raw data is a  huge json file with events like this: (separated by "key" field)

{
  "customfield_26300" : 10.0,
  "customfield_26302" : null,
  "customfield_26301" : 15.0,
  "customfield_26202" : {
    "child" : {
      "value" : "Hardware-Techniker"
    }
  },
  "customfield_26204" : {
    "child" : {
      "value" : "Software-Entwickler Sonderprogramme (C, C++)"
    }
  },
  "key" : "PBWP-4881",
  "customfield_26207" : {
    "value" : "3 Advanced"
  },
  "customfield_26206" : {
    "value" : "3 Advanced"
  }
}

customfield_26202 and customfield_26204 has the same content and I have to merge/append them together.  Same for customfield_26027 and 26206 etc.
That is why I did this: 

| eval Skills = mvappend(customfield_26202_child_value, customfield_26204_child_value, customfield_26205_child_value)
...

I did some tests, Splunk is taking the order from mvappend() function. I think it is not alphabetical. 

Any ideas how I can achieve that:

Skill	SkillLevel	Hours
Hardware-Techniker	3 Advanced	10
Software-Entwickler Sonderprogramme (C, C++)	3 Advanced	15

Thank you 🙂

[jamie00171]

Hi @timo258 ,

After the mvexpand you could try:

| stats sum(Hours) as Hours_total by Skill SkillLevel
| stats list(Skill) list(SkillLevel) by Hours_total

Then use table and/or rename as you need to get the correct order and name of columns.

Thanks,

Jamie

[timo258]

Hi Jamie,

Thank you for your answer!

The problem with your is, if I do a: 

| stats sum(Hours)

Splunk will sum up all hours in that field: