How do i create new field with last day of reporting period to my search?

[iamsplunker]

I have a report which runs every week on Monday , I'm using earliest and latest time in my search .  Now I wanted to add a new field to my search called lastdate say if a report period is between 07/01 to 07/07 the lastdate field should display 07/07 and For my monthly report how do I create new field called MonthEnd and this  should displays the values as June 30 for month ending date, Please help

[isoutamo · Accepted solution]

Hi

add to your stats

| stats .... latest(_time) as lastDay range(_time) AS dateRange ....
| eval lastDayOfMonth = strftime (lastDay, "%B %d"),
       lastDay = strftime (lastDay, "%d/%m"),
       reportPeriod = if (dateRange > 604800, "Monthly", "Weekly") ....

and then use those fields lastDay and lastDayOfMonth.

r. Ismo

[iamsplunker]

Thanks Sautamo the lastday field works just fine. But I also want to add a field called Report Period the value should represent the Week/Month depending on the granularity of the report. 

[isoutamo]

Hi

I updated my previous answer by adding reportPeriod.

r. Ismo

[iamsplunker]

Thanks soutamo. I accepted your answer. I hope this is the last comment in this thread. Can you please explain about  the value you've mentioned  604800 . For both date ranges 6/1 -6/7 and 6/1-6/30 it is showing the Granularity as Weekly. for 6/1-6/30 it should show as Monthly. Thanks for all your help

[isoutamo]

It is seven days in seconds. Current stats needs that there are events (_time) for start and end date/time. Of course you could use those from your given start and end dates where this would works even there haven’t been any events. 
r. Ismo

[to4kawa]

earliest="07/01/2020:00:00:00" latest="07/07/2020:23:59:59" index=_internal | head 1
| addinfo
| eval lastdate=strftime(info_max_time,"%F")
| eval MonthEnd=strftime(relative_time(info_min_time,"@month-1d"),"%F")
| table lastdate MonthEnd