topic Re: Finding most recent event by host in Splunk Search

Finding most recent event by host

anuremanan88 — Tue, 21 Nov 2017 09:20:38 GMT

We are collecting logs from McAfee and Splunk pulls information for each host every 1 Hr. The logs have two fields host and LastUpdateTime. I need to get the most recent event for each host using the LastUpdateTime without losing any other fields. Please help me in writing a query for this.

Re: Finding most recent event by host

niketn — Tue, 21 Nov 2017 10:31:23 GMT

@anuremanan88, Try the following

<YourBaseSearch>
| dedup host
| table *

Refer to Splunk documentation of dedup command: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Dedup

Re: Finding most recent event by host

cmerriman — Tue, 21 Nov 2017 11:53:25 GMT

What format is LastUpdateTime in?
Try something like

|convert mktime(LastUpdateTime) as LastUpdateTimeEpoch timeformat="%Y-%m-%d %H:%M:%S"| stats max(LastUpdateTimeEpoch) as LastUpdateTimeEpoch by host |eval LastUpdateTime=strftime(LastUpdateTimeEpoch,"%Y-%m-%d %H:%M:%S")

Except change the time format to suit your needs. You can also use an |eval LastUpdateTimeEpoch=strptime(LastUpdateTime,"%Y-%m-%d %H:%M:%S") instead of convert

Re: Finding most recent event by host

anuremanan88 — Tue, 21 Nov 2017 11:54:39 GMT

I need to get the most recent event using the field LastUpdateTime field for each host. dedup wont work here.

Re: Finding most recent event by host

anuremanan88 — Tue, 21 Nov 2017 11:57:09 GMT

Using stats will remove other fields present in the event. Is there a way to keep the other fields also

Re: Finding most recent event by host

kamlesh_vaghela — Tue, 21 Nov 2017 12:10:59 GMT

Hi
Can you please try this?

YOUR_SEARCH |eval LastUpdateTimeEpoch=strptime(LastUpdateTime,"%Y-%m-%d %H:%M:%S") | sort - LastUpdateTimeEpoch | dedup host

Re: Finding most recent event by host

cmerriman — Tue, 21 Nov 2017 12:34:01 GMT

you can do ...|stats values(*) as * max(LastUpdateTimeEpoch) as LastUpdateTimeEpoch by host |eval LastUpdateTime=strftime(LastUpdateTimeEpoch,"%Y-%m-%d %H:%M:%S")

Re: Finding most recent event by host

niketn — Tue, 21 Nov 2017 14:56:38 GMT

Can you add some sample data for your field?

Re: Finding most recent event by host

DalJeanis — Tue, 21 Nov 2017 16:01:09 GMT

@kamlesh_vaghela - change that to | sort - LastUpdateTimeEpoch |dedup host and you have it.

Re: Finding most recent event by host

kamlesh_vaghela — Tue, 21 Nov 2017 16:08:09 GMT

Hi @DalJeanis,

Perfect!!