Histogram of transaction durations

[dbcase]

Hi,

I have this query that finds the duration of the transaction times.

index=wholesale_app buildTarget=* product=* analyticType=sessionStart OR (analyticType=AppStateEvent AND Properties.index=3)|transaction clientSessionId startswith="sessionStart" endswith="AppStateEvent"|eval duration=if(duration<=50000,duration,0)|stats values(duration)

All well and good but what I'd like to do is have a count of how many transactions were in a given time period (range)

Something like this
0-10 seconds 4 transactions
11-30 seconds 2 transactions
31-60 seconds 1 transaction
1-3 minutes 8 transactions
3-10 minutes 21 transactions
etc etc

Something that looks like the below. Any thoughts?

[cmerriman · Accepted solution]

you're going to have to do an eval. if the spans were the same, i'd say you could use bucket and do |bucket duration as bucket span=30s|stats count by bucket for instance, but that isn't the case.

something like this should work, just finish the case statement:
|eval bucket=case(duration<11,"0-10 seconds",duration<31,"11-30 seconds",duration<61,"31-60 seconds",duration<181,"1-3 minutes",duration<601,"3-10 minutes",.....,1=1,"Other")|stats count by bucket

you will also need to do an extra eval for each 0-10 seconds, 11-30 seconds.... to give them numerical values in order to sort them in the right order.
|stats count by bucket|eval sort=case(bucket="0-10 seconds",1,bucket="11-30 seconds",2,bucket="31-60 seconds",duration<181,"1-3 minutes",3,bucket="3-10 minutes",4,.....bucket="Other",x)|sort + sort|fields - sort

[dragan_dragan]

The sort can be simplified as

| eval bucket=case(
duration < 5,"0-4 seconds",
duration < 10,"5-9 seconds",
duration < 30,"11-30 seconds",
duration < 60,"30-59 seconds",
duration < 120,"1-2 minutes",
duration < 300,"2-5 minutes",
duration < 600,"5-10 minutes",
duration < 1200,"10-20 minutes",
1=1,"Other"
)
| stats max(duration) as maxDur, count by bucket
| sort +maxDur


[dbcase]

Hi Cmerriman,

Many thanks! This is what I ended up with

index=wholesale_app buildTarget=* product=* analyticType=sessionStart OR (analyticType=AppStateEvent AND Properties.index=3)|transaction clientSessionId startswith="sessionStart" endswith="AppStateEvent"|eval duration=if(duration<=50000,duration,0)|eval bucket=case(duration<11,"0-10 seconds",duration<31,"11-30 seconds",duration<61,"31-60 seconds",duration<181,"1-3 minutes",duration<601,"4-10 minutes",duration<1801,"11-30 minutes",duration<3601,"31-60 minutes",duration>3600,"Greater than 1 hr",1=1,"Other")|stats count by bucket

[dbcase]

Updated further

index=wholesale_app buildTarget=* product=* analyticType=sessionStart OR (analyticType=AppStateEvent AND Properties.index=3)|transaction clientSessionId startswith="sessionStart" endswith="AppStateEvent"|eval duration=if(duration<=50000,duration,0)|eval bucket=case(duration<11,"0-10 seconds",duration<31,"11-30 seconds",duration<61,"31-60 seconds",duration<181,"1-3 minutes",duration<601,"4-10 minutes",duration<1801,"11-30 minutes",duration<3601,"31-60 minutes",duration>3600,"Greater than 1 hr",1=1,"Other")|stats count by bucket|eval sorta=case(bucket=="0-10 seconds",1,bucket=="11-30 seconds",2,bucket=="31-60 seconds",3,bucket=="1-3 minutes",4,bucket=="4-10 minutes",5,bucket=="11-30 minutes",6,bucket=="31-60 minutes",7,bucket=="Greater than 1 hr",8,bucket=="Other",x)|sort + sorta|fields - sorta