Need get last event occurred time of each day

[paragvidhi]

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.

[scelikok]

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

[ITWhisperer]

Why don't you want to use the table command?

What happens if you just remove the table command?

[paragvidhi]

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)

[ITWhisperer]

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

[paragvidhi]

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.

[ITWhisperer]

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