Hi All,
I would like to get last event occurred time of each day, my searching window area is last 30 days.
For example : If my query return 3 events for day1 and 5 events for day 2 than I need only two event in output.
last event time of day 1 and last event time of day 2 and so on.
I tried to get that with help of table command. it works for me. but I need to do that without using of table command.
worth if you could help me to find rename or create duplicate field of date_mday and _time
search | table date_mday, _time | dedup date_mday | sort date_mday.
Hi @paragvidhi,
To get difference you should calculate the diff before time conversions. Please try below;
search A
| eval Date=strftime(_time, "%d/%m/%Y")
| stats latest(_time) AS Latest by Date
| join Date
[search search B
| eval Date=strftime(_time, "%d/%m/%Y")
| stats earliest(_time) AS Earliest by Date
]
| eval time_diff= Endtime_mail - starttime_mail
| eval time_diff=tostring(time_diff,"duration")
| eval starttime_mail=strftime(Earliest,"%Y/%m/%d %H:%M:%S")
| eval Endtime_mail=strftime(Latest,"%Y/%m/%d %H:%M:%S")
| table starttime_mail,Endtime_mail, time_diff
Why don't you want to use the table command?
What happens if you just remove the table command?
Actually I need use that data to another search.
so if i give you more details. so I would like to get total time taken.
I have two search A and B .
In search A I will get only single event for each day. so I am consider event time as starttime.
In search B I will get multiple event in a day. so the last event occurred on that day I consider endtime of that event.
Now I need to display result like below.
Date starttime endtime timetaken(starttime-endtime)
If search A is purely to find the start and search B is from the same source, you could try
search | stats earliest_time(_time) as start latest_time(_time) as end by date_mday
You could return these with every event by using eventstats instead of just stats if you still need the event data
I got my query result in another way but its partial.
Here I use below query.
search A
| eval Date=strftime(_time, "%d/%m/%Y")
| stats latest(_time) AS Latest by Date
| eval Endtime_mail=strftime(Latest,"%Y/%m/%d %H:%M:%S")
| join Date
[search search B
| eval Date=strftime(_time, "%d/%m/%Y")
| stats earliest(_time) AS Earliest by Date
| eval starttime_mail=strftime(Earliest,"%Y/%m/%d %H:%M:%S")
]
| table starttime_mail,Endtime_mail
Now I am not able get date-time difference between starttime_mail and Endtime_mail.
Difference should be like 1 day ,3 hour, 43 minute.
search A
| eval Date=strftime(_time, "%d/%m/%Y")
| stats latest(_time) AS Latest by Date
| join Date
[search search B
| eval Date=strftime(_time, "%d/%m/%Y")
| stats earliest(_time) AS Earliest by Date
]
| eval timediff=Latest-Earliest
| eval duration_mail=tostring(timediff,"duration")
| eval Endtime_mail=strftime(Latest,"%Y/%m/%d %H:%M:%S")
| eval starttime_mail=strftime(Earliest,"%Y/%m/%d %H:%M:%S")
| table starttime_mail,Endtime_mail,duration_mail