Splunk Search

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

kirrusk
Communicator

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 

Labels (5)
Tags (1)
0 Karma

gcusello
Legend

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

 

0 Karma

kirrusk
Communicator

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

0 Karma

gcusello
Legend

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

0 Karma

kirrusk
Communicator

@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
 

Tags (1)
0 Karma

gcusello
Legend

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
Communicator

@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

0 Karma

gcusello
Legend

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

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...