Show min/max of one column with top/count

[atamido]

The data I have looks like this:

Time    Shape   Color
12:00   square  green
12:01   circle  blue
12:02   square  blue
12:03   circle  green
12:04   square  green
12:05   circle  blue
12:06   square  green
12:07   circle  blue
12:08   square  green
12:09   circle  green

I would like something similar to the TOP view that shows Shape/Color/Count/Percent. But I would also like to see the min/max of the times for each of those also, so I can see the first and last times of each of those combinations. So the outgoing chart might look like:

Shape   Color  Count  Percent  Begin  End
square  green      4      40%  12:00  12:08
circle  blue       3      30%  12:01  12:07
circle  green      2      20%  12:03  12:09
square  blue       1      10%  12:02  12:02

[martin_mueller · Accepted solution]

Try this:

base search | stats count as Count earliest(_time) as Begin latest(_time) as End by Shape Color 
| eventstats sum(Count) as Total | eval Percent = round(Count/Total*100,2)."%"
| table Shape Color Count Percent Begin End

[atamido]

Amazing, that's exactly what I wanted. I added some time converts and a sort to act just like TOP, and I'm done.

base search | stats count as Count earliest(_time) as Begin latest(_time) as End by Shape Color
| eventstats sum(Count) as Total | eval Percent = round(Count/Total*100,2)."%"
| convert ctime(Begin) as Begin | convert ctime(End) as End | sort Count DESC
| table Shape Color Count Percent Begin End