Mimicing groupby & join behavior to get group aggregates

[leecaf]

Referring to table below, If it started with only Col1 and Col2. In a relational DB I would do a groupby followed by a join on 'col1' to get the derived col 'MaxOfCol1' . is there a better to do it in splunk since joins are inefficient?

+------+------+-----------+
| Col1 | Col2 | MaxOfCol1 |
+------+------+-----------+
|    1 |    1 |         3 |
|    1 |    2 |         3 |
|    1 |    3 |         3 |
|    2 |    4 |        55 |
|    2 |    2 |        55 |
|    2 |   55 |        55 |
|    3 |   12 |        12 |
|    3 |    3 |        12 |
|    3 |    1 |        12 |
+------+------+-----------+

[leecaf]

every event has a field called Col1 and Col2 as per the table above. I want to do an 'eval' like operation which will add the 3rd field MaxOfCol1 to each event.

[brettcave · Accepted solution]

1 function: stats.

Provide some sample data for more....
Keep this open for reference: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commonstatsfunctions

[brettcave]

you could use transaction command too, and then use eval with some MV functions. You can also propogate 1 field to multiple events using stats values(col2) as col2_distinct by col1 | mvexpand col2_distinct. stats + values = a distinct list. stats + list = a full list.

[Ayn]

... | eventstats max(col2) as maxofcol1 by col1 | table col1 col2 maxofcol1

[leecaf]

I am aware of stats but 'stats max(col2) by col1' would return only the aggregates. I can join this with my original events but is that really the best way? or is there a cleverer more efficient way of doing this?

[Ayn]

Out of what? Splunk doesn't have columns in that sense, to begin with.

[leecaf]

I want to produce a table with Col1, Col2, Col2-MaxOfCol1. that's the end goal.

[Ayn]

I think you need to submit sample events and what you want to do. Trying to apply SQL terms right away in Splunk often leads to confusion.