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.
Hi
Can you please try this?
YOUR_SEARCH |eval LastUpdateTimeEpoch=strptime(LastUpdateTime,"%Y-%m-%d %H:%M:%S") | sort - LastUpdateTimeEpoch | dedup host
@kamlesh_vaghela - change that to | sort - LastUpdateTimeEpoch |dedup host
and you have it.
Hi @DalJeanis,
Perfect!!
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
Using stats will remove other fields present in the event. Is there a way to keep the other fields also
you can do ...|stats values(*) as * max(LastUpdateTimeEpoch) as LastUpdateTimeEpoch by host |eval LastUpdateTime=strftime(LastUpdateTimeEpoch,"%Y-%m-%d %H:%M:%S")
@anuremanan88, Try the following
<YourBaseSearch>
| dedup host
| table *
Refer to Splunk documentation of dedup command: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Dedup
I need to get the most recent event using the field LastUpdateTime field for each host. dedup wont work here.
Can you add some sample data for your field?