Splunk Search

How to expand/extract multivalue fields line by line?

timo258
Explorer

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:

timo258_0-1656410219678.png

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

timo258_2-1656410912449.png

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

SkillSkillLevelHours
Hardware-Techniker3 Advanced10
Software-Entwickler Sonderprogramme (C, C++)3 Advanced15


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!

Labels (2)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| 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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| 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
Explorer

Works just perfect! Thank you very much!! 

0 Karma

gcusello
Esteemed Legend

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

0 Karma

timo258
Explorer

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: 

timo258_0-1656415147581.png

 

0 Karma

gcusello
Esteemed Legend

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

0 Karma

timo258
Explorer

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:

SkillSkillLevelHours
Hardware-Techniker3 Advanced10
Software-Entwickler Sonderprogramme (C, C++)3 Advanced15

 

Thank you 🙂

0 Karma

jamie00171
Communicator

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

0 Karma

timo258
Explorer

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: 

timo258_0-1656414887732.png

0 Karma
Get Updates on the Splunk Community!

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...