I was playing with koshyk's query and came up with this one.
Make sure you use fast mode and event sampling 1:100,000 or else it might take forever.
index=whatever
|rename _bkt AS bucketId, _cd AS cd
|stats earliest(_time) as first_event latest(_time) as last_event
by bucketId
|join type=left bucketId
[|dbinspect index=whatever |fields bucketId, state]
|stats first(first_event) AS first_event, last(last_event) AS last_event dc(bucketId) AS buckets by state
|eval last_event=strftime(last_event,"%m/%d/%Y %H:%M")
|eval first_event=strftime(first_event,"%m/%d/%Y %H:%M")
|table first_event, last_event, state buckets |sort - last_event
And if you want to see 'em all:
index=*
|rename _bkt AS bucketId, _cd AS cd
|stats earliest(_time) as first_event latest(_time) as last_event
by bucketId, index
|join type=left bucketId
[|dbinspect index=* |fields bucketId, state index]
|stats first(first_event) AS first_event, last(last_event) AS last_event dc(bucketId) AS buckets by state index
|eval last_event=strftime(last_event,"%m/%d/%Y %H:%M")
|eval first_event=strftime(first_event,"%m/%d/%Y %H:%M")
|table index first_event, last_event, state buckets |sort - last_event
... View more