Can you help me combine 2 queries into a timechart?

[cindywee]

Hi all,

I have the following data and I need some help to progress further.

I have fields: _time uniqueId action user host

The events are paired. Both events share the same uniqueId, user and host, and each will have an "action" which is "action connect" or "action disconnect".

I am trying to create a single 5mins span timechart, showing number of "paired events" and the average duration for that 5 mins.

I am able to do that with separate queries but I am unable to join them into one, or find a better query.

index=myindex action=\*action*
| stats min(_time) as Start, max(_time) as End, by uniqueId user host
 | eval Duration = tostring((End - Start), "duration") 
| eval Start_time=strftime(Start,"%F %T") 
| eval End_time=strftime(End,"%F %T") 
| where Duration!="00:00:00"
| table uniqueId user host Start_time End_time Duration

index=myindex action=\*action*
|bin _time span=5m 
| stats count(uniqueId) as Sessions by _time

Thanks

[woodcock]

Try this:

index=myindex action=\*action*
| stats min(_time) AS _time range(_time) AS duration BY uniqueId user host
| search duration>0    
| timechart span=5m dc(uniqueId) AS Sessions avg(duration) as avgDuration

[stuartidelta01]

(1) You need to carry forward _time from your stats command if you plan to use a timechart afterwards - in the example below I use "earliest(_time) as _time" to achieve this - this associates the event with the time it started

(2) Use range(_time) to calculate a duration

index=myindex action=\*action*
|stats earliest(_time) as _time range(_time) as duration by uniqueId user host
|timechart span=5m avg(duration) as avgDuration dc(uniqeId) as Sessions

You will probably then find that the two series on the timechart operate on different scales - use the overlay control in the timechart to allow one of the series to be represented differently. e.g. set the timechart to an area chart and then use overlay controls to set the pairedEvents series to be a line on that chart. Click on the option to allow it to have it's own scale, which will appear on the right hand side of the chart.

You may want to create a new field called durationMS and use that instead for a nicer output, e.g.

eval durationMS = duration * 1000

then avg(durationMS) in the timechart