Splunk Search

How to populate a column that generates time difference for the results which are generated from a stats command?

pkhedwal
New Member

I have this query.

 index=azure Operation=UserLoggedIn  user!=Unknown|sort - _time | iplocation  ClientIP | eval Time=strftime(_time, "%m-%d-%y %H:%M:%S") | stats Values(src_ip), values(Country) as Country, dc(Country) as count_country,  Values(Time) count by user | where count_country>1

Results are:
alt text

Now I want to calculate the time difference between 1st and last time which are present in the column Values(Time).
I want a column to be populated right after this column.

Any help?

0 Karma

Richfez
SplunkTrust
SplunkTrust

You could give this a try.

index=azure Operation=UserLoggedIn  user!=Unknown|
| iplocation  ClientIP | eval Time=strftime(_time, "%m-%d-%y %H:%M:%S") 
| stats Values(src_ip), values(Country) as Country, latest(_time) AS earlyTime, earliest(_time) AS lateTime, dc(Country) as count_country,  Values(Time) count by user 
| eval elapsedSeconds = tostring(lateTime - earlyTime, "duration") | where count_country>1

We add in the two fields you need (earliest and latest time) into the stats, then do a little math and format the results.

Give that a try, and let us know how it works!
-Rich

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

First off, remove the sort command - it contributes nothing to your result, and causes only the first 10000 rows to be considered.

If _time and Time are identical, you can add range(_time) as range to your stats to get the difference.

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...