topic Re: How to expand/extract multivalue fields line by line? in Splunk Search

How to expand/extract multivalue fields line by line?

timo258 — Tue, 28 Jun 2022 10:17:45 GMT

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!

Re: How to expand/extract multivalue fields line by line?

jamie00171 — Tue, 28 Jun 2022 10:28:36 GMT

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

Re: How to expand/extract multivalue fields line by line?

gcusello — Tue, 28 Jun 2022 11:00:56 GMT

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

Re: How to expand/extract multivalue fields line by line?

timo258 — Tue, 28 Jun 2022 11:16:43 GMT

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:

Re: How to expand/extract multivalue fields line by line?

timo258 — Tue, 28 Jun 2022 11:19:41 GMT

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:

Re: How to expand/extract multivalue fields line by line?

gcusello — Tue, 28 Jun 2022 11:27:37 GMT

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

Re: How to expand/extract multivalue fields line by line?

timo258 — Tue, 28 Jun 2022 14:38:43 GMT

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 🙂

Re: How to expand/extract multivalue fields line by line?

ITWhisperer — Fri, 01 Jul 2022 12:24:30 GMT

Re: How to expand/extract multivalue fields line by line?

timo258 — Fri, 01 Jul 2022 13:40:11 GMT

Works just perfect! Thank you very much!!