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

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

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...