topic Re: How to extract date of the latest event after group by? in Splunk Search

How to extract date of the latest event after group by?

sagar1992 — Thu, 14 Mar 2019 15:23:12 GMT

Hi Team,

I am facing issue after using group by clause. (Need date of the grouped event in DD-MM-YYYY )

The search that I am using is below:

index="test_mulesoft"  sourcetype="SFTP-Highradius" 61c1bf00-45e7-11e9-bb4e-12376871b014 | rex field=_raw "corelationid.*:\W+(?.*)\"" | stats count as result values(numberOfRequests) as request_id by numberOfRequests, | eval result = if (result==2,"SUCCESS","ERROR REPORTED")  | table request_id,result,DateTime

Basically, I am grouping with correlation id, once grouped i need timestamp of any event. (Screenshot below)

Re: How to extract date of the latest event after group by?

damann — Thu, 14 Mar 2019 15:36:52 GMT

after your |stats count ... you will lose your field DateTime.
You can use eventstats instead of stats which will hold all your fields.

To make things clear: does your search results all have the same value for DateTime? Then you could add DateTime to your by clause in your stats command

index="test_mulesoft" sourcetype="SFTP-Highradius" 61c1bf00-45e7-11e9-bb4e-12376871b014 | rex field=_raw "corelationid.:\W+(?.)\"" | stats count as result values(numberOfRequests) as request_id by numberOfRequests DateTime | eval result = if (result==2,"SUCCESS","ERROR REPORTED") | table request_id,result,DateTime

Re: How to extract date of the latest event after group by?

sagar1992 — Thu, 14 Mar 2019 16:51:18 GMT

Thanks for the reply @damann, however after using eventstats command, I am no longer able to get a single entry. however, able to retrieve date.

Re: How to extract date of the latest event after group by?

sagar1992 — Tue, 29 Sep 2020 23:42:18 GMT

@damann it worked.!! Thank You so much!! I used dedup command to get rid of duplicate entry.

Still in the learning phase. Thanks once again.

index="test_mulesoft" sourcetype="SFTP-Highradius" 61c1bf00-45e7-11e9-bb4e-12376871b014 | rex field=_raw "corelationid.:\W+(?.)\"" | eventstats latest(numberOfRequests) as request_id by numberOfRequests | dedup request_id | table _time,request_id