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?
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
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.