Reducing the relation of a report from stats

[brettcave]

I'm sure this is a really simple one, but for some reason I can't get my head around how to format a result table in a specific way.

I have a search that I run through stats. The stats command is like:

... | stats sum(started) as started sum(finished) as finished by D1 D2
D1     D2     started     finished
did1   did2   3            4
did1   no2    4            8
no1    did2   5            9
no1    no2    6            5

But I want to reduce it down, without using appends (e.g. with only eval, chart and/or stats) to a format like:

Dimension    started    finished
did1         7          12
no1          11         14
did2         8          13
no2          10         13

It's sort of like a pivot I guess...

thanks in advance.

[jonuwz · Accepted solution]

Anyhow, assuming that you can't simplify the original search into something more suitable for processing into the final result.

Here's how to transform your current output into the output you want.

... | eval Dimension=D1." ".D2 | fields - D1 D2 | makemv Dimension | mvexpand Dimension | stats sum(finished) as finished sum(started) as started by Dimension

What we're doing is duplicating the values for started and finished for the values of D1 and D2 per line.

[brettcave]

thanks, it works great. d1 and d2 are determined from an MV field after a transaction:

... | transaction visId | makemv someField | eval d1=mvfind(someField, "a_value") | eval d2=mvfind(someField, "b_value") | eval did_d1=if(isnotnull(d1), 1, 0) | eval did_d2=if(isnotnull(d2), 1, 0) | ....

[jonuwz]

whats the raw data look like ? i.e. do D1 and D2 exist in the same event, or are they always in seperate events ?