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

[pkhedwal]

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:

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?

[Richfez]

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

[martin_mueller]

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.