Sum of total files with same date

[373782073]

Hi,

I've got a search that provides a table of 60 filenames.
30 filenames with a -3 days from today's date and 30 filenames with a -2 days from today's date.
Everytime the search will run it is expected to result in 60 events with 60 filenames found containing 2 separate dates within their names.

Search output:

Filename
filename1-2020-01-21
filename2-2020-01-21
...
filename30-2020-01-21
filename1-2020-01-22
filename2-2020-01-22
filename3-2020-01-22
...
filename30-2020-01-22

How can I modify my table so that a second column with the total number of files is also created with the first field having the total =30 for all files from -3days and 30 files with -2days?

Also is there a way a way to setup a third column that will say INVALID if the filename doesnt contain either -3days date or -2days date? Example NOT 2020-01-22 or 2020-01-21?

Expected Search output:

Filename Total Valid
filename1-2020-01-21 30 VALID
filename2-2020-01-21 "
... "
filename30-2020-01-21 "

filename1-2020-01-18 1 INVALID

filename1-2020-01-22 VALID
filename2-2020-01-22 "
filename3-2020-01-22 "
... "
filename30-2020-01-22 "

Thanks

[renjith_nair · Accepted solution]

@373782073 ,

Try

"your current search"
|rex field=filename ".*(?<date>\d{4}-\d{2}-\d{2})"
|eventstats count as Total by date
|eval day3=strftime(relative_time(now(),"-3d"),"%Y-%m-%d")
|eval day2=strftime(relative_time(now(),"-2d"),"%Y-%m-%d")
|eval Status=if(date==day3 OR date==date2,"VALID","INVALID")
|streamstats count by date|eval Total=if(count==1,Total,null())
|fields filename,Total,Status

[373782073]

That worked great.
Thanks a lot