topic Re: How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week? in Reporting

How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week?

mumblingsages — Wed, 16 Aug 2017 18:01:20 GMT

I have a report that I'd like to create but I need to set the earliest clause based on the current day of the week. So for example. On Mondays I need to set earliest to -3d at 07:30:00 (So records from Friday @ 7:30am onward are captured). The rest of the days of the week I would like to set it to -1d at 07:30:00.

I have the logic figured out on how to determine the day of the week, but things go sideways on me when I specify the earliest clause.

Re: How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week?

sbbadri — Wed, 16 Aug 2017 18:05:57 GMT

to find current day of the week use like below

| eval DayOfWeek=strftime(_time, "%A")

Re: How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week?

mumblingsages — Tue, 29 Sep 2020 15:22:43 GMT

Right. I have that part.... More specifically.....

| eval start=if( (strftime(Now(),"%a") == "Mon"), "-3d0", "-1d") | eval r_time=strftime(relative_time(now(), start),"%m/%d/%Y:07:30:00") | where earliest=r_time

However, it's not finding any results even thought I know they exist.

Re: How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week?

cmerriman — Wed, 16 Aug 2017 18:15:22 GMT

I think what you may have to do is set earliest=-3d@d and then add in the logistics to filter out based on the current day.

|eval filter=if(relative_time(now(),"%w")=1,relative_time(now(),"-3d@d+7h+30m"),relative_time(now(),"-1d@d+7h+30m"))|where _time>=filter

Re: How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week?

somesoni2 — Wed, 16 Aug 2017 18:34:34 GMT

Try like this

index=foo sourcetype=bar [| gentimes start=-1 | eval earliest=if(lower(strftime(now(),"%a"))="mon",relative_time(now(),"-3d@d+7h+30m"),relative_time(now(),"-1d@d+7h+30m") | table earliest  ]   | rest of the search

Re: How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week?

mumblingsages — Tue, 29 Sep 2020 15:22:51 GMT

I think you are close.... I adapted what you wrote to the following....

but now I seem to be getting everything.... Looking at _time and r_time they are of different formats...

_time = 2017-04-25 19:59:00
r_time = 1502022600.000000

Is that why??

Re: How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week?

sbbadri — Tue, 29 Sep 2020 15:22:54 GMT

add this before where condition | eval r_time=strftime(r_time,"%Y-%m-%d %H:%M:%S)

Re: How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week?

cmerriman — Tue, 29 Sep 2020 15:22:56 GMT

_time is in epoch, but displays in human-readable. if you were to add |eval time=_time it should display time as epoch, as well.
try changing strftime(now(),"%a"), to relative_time(now(),"%a") in your start eval

Re: How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week?

mumblingsages — Wed, 16 Aug 2017 19:13:42 GMT

Very strange. Now I've got nothing.... But the formats are matching..
....

Re: How can I set a report to run that includes events from 1 or 3 days prior based on the current day of the week?

mumblingsages — Wed, 16 Aug 2017 19:17:36 GMT

cmerriman... That seems to have done the trick!!

Thank you both!!