topic Re: Table with count and latest date in Splunk Search

Table with count and latest date

giuces — Tue, 16 Nov 2021 17:25:41 GMT

Hi all,

i need to create a table that count for every product how many events are accepted or rejected.

In addition to this fields the latest event date should be shown with the count. Table should be like this

Product	Latest Accepted	Total Accepted	Latest Rejected	Total Rejected
Bike	10/11/2021	35	12/11/2021	14
Skate	11/11/2021	99	13/11/2021	5

the first part of the query is pretty easy:

...| stats count(eval(action="accepted)) AS "Total Accepted" count(eval(action="rejected)) AS "Total rejected" by product | rename product AS Product

I'm not able to retrieve the latest date for every kind of action, tried with latest(_time) without success.

Many thanks

Re: Table with count and latest date

aasabatini — Tue, 16 Nov 2021 17:33:10 GMT

Hi @giuces

try the stats function like this:

stats latest(eval(if(action="rejected", _time, NULL()))) AS "Latest Rejected"

Ale

Re: Table with count and latest date

giuces — Tue, 16 Nov 2021 17:54:06 GMT

thanks aasabatini,

i'm little be confused where do i have to insert portion of search yo suggest in the old one

...| stats count(eval(action="accepted)) AS "Total Accepted" count(eval(action="rejected)) AS "Total rejected" by product | rename product AS Product | table Product "Total Accepted" "Total Rejected" ?

Regards

Re: Table with count and latest date

aasabatini — Mon, 22 Nov 2021 13:20:16 GMT

Hi @giuces

try like this

stats count(eval(action="accepted)) AS "Total Accepted" count(eval(action="rejected)) AS "Total rejected" latest(eval(if(action="rejected", _time, NULL()))) AS "Latest Rejected" by product | rename product AS Product