Splunk Search

Can you help me combine 2 queries into a timechart?

cindywee
New Member

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

0 Karma

woodcock
Esteemed Legend

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
0 Karma

stuartidelta01
Path Finder

(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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...