splunk query to get data last two or three months in week range

[kirrusk]

Hi,

I'm trying to figure out how to get data for the past few weeks and data will be filtered.

week start should be from every (previous week)Saturday to Friday.

I will send a report every Friday.

the report should be like this

DATE           COUNT    NAME
21-01-22      58             one
14-01-22      58             one
07-01-22      45             two

Thus on next Friday one more value-added to report.

DATE           COUNT    NAME
28-01-22      61             one
21-01-22      58             one
14-01-22      58             one
07-01-22      45             two

@ITWhisperer 
@gcusello 

[gcusello]

Hi @kirrusk,

there are more ways to do this, I hint the following.

you could schedule every Friday night (or better Saturday night to be sure to have all the events in Friday) a search like this:

index=your_index earliest=-7d@d latest@d
| stats count AS COUNT latest(_time) AS DATE BY NAME
| collect index=my_summary_report

and then run a search on the summary index:

index=my_summary_report
| table DATE COUNT NAME

using as time boundary the time you want.

In this way you have a very eficient search even if the first search is very slow.

Ciao.

Giuseppe

[kirrusk]

@gcusello Thank you, Data I'm using from the summary index, I don't want to increase the memory of the summary index.

[gcusello]

Hi @kirrusk,

the memory (disk storage) that you use in the hinted way is very very little!

Anyway, you could run something like this:

index=your_index earliest=-7d@d latest@d
| bin _time span=7d
| timechart count BY NAME
| rename count AS COUNT
| eval DATE=strftime(_time,"%d-%m-%y")
| table DATE COUNT NAME

Ciao.

Giuseppe

[kirrusk]

@gcusello Thank you, trying to run the below query giving 

earliest=-90d@d latest=now

as I want data for the last three months 

but it is giving a date in DATE field is Thursday(should be Friday), 

DATE 
06-01-22
13-01-22
20-01-22
 

[gcusello]

Hi @kirrusk,

as I said, if you run the search on Saturday, you'll have the count at Friday.

If you want to run the search on Friday, you could run something like this:

index=your_index earliest=-7d@d
| bin _time span=7d
| timechart count BY NAME
| rename count AS COUNT
| eval DATE=strftime(_time,"%d-%m-%y")
| table DATE COUNT NAME

but, beware because you could not take all the events of Friday.

Ciao.

Giuseppe

[kirrusk]

@gcusello it is not working as expected, I think as we kept span=7d in (days)
for example if  i run today, by putting span=7d  Date should be like 
DATE
26/01/22
19/01/22
12/01/22

which we Wednesday of every week

[gcusello]

Hi @kirrusk,

for this reason I hinted to schedule a search every Saturday putting results in a summary index!

It's not possible to define a bin period fixing the starting week day, but using my approach you'll have the wanted results.

Ciao.

Giuseppe