topic Re: Need get last event occurred time of each day in Splunk Search

Need get last event occurred time of each day

paragvidhi — Thu, 25 Mar 2021 16:22:18 GMT

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.

Re: Need get last event occurred time of each day

ITWhisperer — Thu, 25 Mar 2021 16:31:17 GMT

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

What happens if you just remove the table command?

Re: Need get last event occurred time of each day

paragvidhi — Thu, 25 Mar 2021 16:41:54 GMT

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)

Re: Need get last event occurred time of each day

ITWhisperer — Thu, 25 Mar 2021 17:56:38 GMT

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

Re: Need get last event occurred time of each day

paragvidhi — Thu, 25 Mar 2021 19:09:09 GMT

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.

Re: Need get last event occurred time of each day

scelikok — Fri, 26 Mar 2021 06:20:53 GMT

Hi @paragvidhi,

To get difference you should calculate the diff before time conversions. Please try below;

Re: Need get last event occurred time of each day

ITWhisperer — Fri, 26 Mar 2021 06:29:32 GMT