Passing Multivalue Fields Through Map Command

[dcsteve24]

I have a dashboard used for generating data for reports. Since its initial build, I've been going back and revamping the structures to use tstats commands which have sped up the queries tenfold. Its taken some unique approaches to most of the queries but I have managed to do it to all queries (roughly 13-14) except one.

This particular one is having to work with data that was parsed into an array/multivalue. The final table has multiple of these multivalue fields and each should align correctly. This is important to mention because I've found many of the commands I have used to quicken other searches sort by default or clump together which I don't want. So for an example output, I desire this as my final solution.

Desired Result (note I actually have like 6 of these array fields in my data)

ID 1	ID 2	Data 1	Data 2
1	12	300 400 200 1000	2 3 5 4
1	18	500 100 200 400 150	3 4 5 1 1

For some of these cases I have to perform math in my multivalue to convert it from one type to another (think something like MB to GB) and in other cases I have to perform a lookup via a range which requires a map command via the inputlookup command (reference this ticket here: https://community.splunk.com/t5/Splunk-Search/Lookup-With-Value-Between-Two-Lookup-Fields/m-p/517298).

Through much trial and error I have found I have to handle each multivalue separate to conduct the math or lookup because I cant recombine it without messing up the other multivalues.

 I have a working version with the join command through the basic search but its 3 joins deep to get all the math and lookup stuff. Because its 3 joins with a search command this takes forever to load. I have similar tables with many joins or maps I managed to quicken with tstats, but they don't have the issue of ensuring the data stays aligned (no sorting).

I have found that through a initial search to pull the IDs with tstats, and then using those for map commands to pull my data I can then take the first result with the head command, quickening it greatly. So something like this:

| tstats count where index=blah source=*blah* groupby ID1, ID2, timestamp
| dedup ID1, ID2 sortby -timestamp
| fields - count
| map search="| search index=$index$ source=$source$ ID1=$ID1$ ID2=$ID2$ timestamp=$timestamp$
              | head 1
              | fields Data1
              | mvexpand Data1
              | do some math or something here
              | stats list(Data1) AS Data1
              | eval index=$index$, source=$source$, ID1=$ID1$, ID2=$ID2$, timestamp=$timestamp$"
| map search="| search index=$index$ source=$source$ ID1=$ID1$ ID2=$ID2$ timestamp=$timestamp$
              | head 1
              | fields Data2
              | mvexpand Data2
              | do some math or something here
              | stats list(Data2) AS Data2
              | eval index=$index$, source=$source$, ID1=$ID1$, ID2=$ID2$, timestamp=$timestamp$, Data1=$Data1$"

This works great! until I start handling the multivalues and trying to pass them forward when populating the data. So the minute I try passing on Data1, the map command encapsulates it in a string like "500 100 200 400 150" and it doesn't seem to want to make it back into a multivalue with makemv, split, or anything. It reads it all as one and I cant seem to break it back to the multivalue list. 

ID 1	ID 2	Data 1	Data 2
1	12	300 400 200 1000	2 3 5 4
1	18	500 100 200 400 150	3 4 5 1 1

I've tried the subsearch thing but apparently that only works in the top because trying to do eval Data1=[| makeresults | Data1=$Data1$ | return $Data1 ] fails.

So is there something I'm missing or a command that would solve this? This would be so much quicker for us if I could get this to work and this is showing a lot of promise except that piece.

[dcsteve24 · Accepted solution]

I got it! Go figure I spend a day on this and immediatly after posting I get it...

In my data I didnt need to break apart "Data1" for math but did for the other multivalues. Because of this I was pulling the data straight and simply adding it. I wasn't breaking it apart. It just happened to be the first one I was working with since it was the first in the report table and since it failed to move on to the next map I thought it wasn't working.

Adding a mvexpand and rebuilding it with stats list() inside the map made it work as expected after rebuilding it with makemv after the map command.

[joshlarkins]

Could you post the SPL you used to solve this? I'm struggling with the same issue.

[joshlarkins]

I ended up using

| makemv tokenizer="([\S]+).?" <field_name>