How to split and filter transaction events?

[kevin_telford]

We have denormalized some JSON events into CSV. The events themselves have simple fields (in the example data, id), and two arrays of objects (in the example data, foos and bars), and so the CSV equivalent will have all top-level fields on each row, and then either the first object type or second populated, with the other left empty. Below is 5 sample events in both CSV and JSON (sample events are highly truncated for brevity).
JSON:

{ "id":1, "foos":[ { "fooval":"a", "footype":"red" }, { "fooval":"b", "footype":"red" }, { "fooval":"a", "footype":"red" }, { "fooval":"a", "footype":"green" } ], "bars":[ { "barval":"x" }, { "barval":"y" } ] }
{ "id":2, "foos":[ { "fooval":"a", "footype":"red" }, { "fooval":"c", "footype":"red" } ], "bars":[ { "barval":"z" } ] }
{ "id":3, "foos":[ { "fooval":"c", "footype":"green" }, { "fooval":"d", "footype":"green" } ], "bars":[ { "barval":"y" } ] }
{ "id":4, "foos":[ { "fooval":"a", "footype":"red" }, { "fooval":"c", "footype":"red" } ], "bars":[ { "barval":"y" } ] }
{ "id":5, "foos":[ { "fooval":"d", "footype":"red" }, { "fooval":"d", "footype":"red" }, { "fooval":"d", "footype":"red" }, { "fooval":"d", "footype":"green" } ], "bars":[ { "barval":"y" } ] }

CSV:

id,fooval,footype,barval
1,a,red,
1,b,red,
1,a,red,
1,a,green,
1,,,x
1,,,y
2,a,red,
2,c,red,
2,,,z
3,c,green,
3,d,green,
3,,,y
4,a,red,
4,c,red,
4,,,y
5,d,red,
5,d,red,
5,d,red,
5,d,green,
5,,,y

The type of questions that we're struggling with are ones where we need to filter by a value contained in one object array, and then do something else like count the values of the second object array. So using this data, a question we would like to answer is: get the count of unique fooval's for all events where there is a barval of 'y', and where the foo object has a footype of 'red'
For our sample data the expected answer would be

fooval  count
   a      3
   b      1
   c      1
   d      3

(a would match for id 1 twice and id 4 once, b once for id 1, c once for id 4, and d three times for id 5)

Since we need to filter the search by barval, which is never present in any rows that have a foo object, we have attempted the solution using the transaction command. A simple attempt to do so looks like this

search | transaction id | search barval=y footype=red | top fooval

However, the result here (a:2, b:1, c:1, d:1) is only a count of the transacted events where these fields exist, not a unique filterer count.
What gets us much closer is when we use the transaction option mvlist=true

index=search_trouble sourcetype=search_trouble_csv | transaction id mvlist=true | search barval=y footype=red | top fooval | where fooval!="NULL"

However this now gives us (a:4, b:1, c:1, d:4), which is because we're still counting the foo objects where footype=red.
Ideally I think we'd want to split the transaction back into events and apply another filter on footype, although attempts at this have failed (we tried using mvraw=true as well).

Any insights would be appreciated, thanks!

[javiergn · Accepted solution]

Hi,

This is what I've done (simply replace my inputcsv with your "index=search_trouble sourcetype=search_trouble_csv")

| inputcsv mycsv.csv
| eval foovaltype = fooval . "::" . footype
| stats list(foovaltype) as foovaltype, list(barval) as barval by id
| mvexpand barval
| mvexpand foovaltype
| eval temp = split(foovaltype,"::") | eval fooval = mvindex(temp,0) | eval footype = mvindex(temp,1)
| fields - temp, foovaltype
| stats count by fooval
| search barval=y footype=red

And the output is:

fooval  count
a   3
b   1
c   1
d   3 

[kevin_telford]

Very cool, thank you. I always forget about stats list. I had to change the order of the last two commands and search then stats count, but otherwise works like a charm. Thanks again.