Splunk Search

Get results per week for custom _time field

utk123
Path Finder

Hello,

I am running a search for last 7 days results, and i am using fixed_date field as _time field.

fixed_date can have any value in last 1 year, so I filtering for results of last 6 months. 
I want the weekly results to show for every Monday. Below query shows results for last 2 Mondays, but then it pickup Thursday. 

 

 

 

index=abcd sourcetype=abcd (IP=x.x.x.x OR IP=y.y.y.y)
| eval _time=strptime(fixed_date,"%Y-%m-%d")
| where _time > relative_time(now(), "-6mon")
| bin _time span=w@w1
| stats count by IP ID _time
| stats count as "Fixed vulnerabilities" by _time


Results I get:
_time                                   Fixed vulnerabilities
2020-05-07                      3678
2020-05-14                      1455
....<few weekly results for total 6 months>

......

2020-10-22                      5543

2020-10-29                      2212

2020-11-02                      7732

2020-11-09                       2213

 

 

Only last 2 are Mondays, but all before those are Thursdays. how to get it for every Monday?

Labels (1)
Tags (3)
0 Karma

ITWhisperer
Ultra Champion

What you are doing looks right, however, you could try aligning your earliest date to a Monday as well i.e 

| where _time > relative_time(now(), "-6mon@w1")

Another possibility is perhaps all your fixed_dates apart from the last couple of weeks are Thursdays?

0 Karma

utk123
Path Finder

this doesn't work. same results. 

fixed_dates got results every day, not just monday or thursday. But I want to combine results for a week to Monday or a fixed day in a week, which is not working.

It's because I am running a search for last 7 days, and so I only see last 2 Mondays. 

If I run the same search for last 6 months, then I see results for every Monday, but then the numbers are not correct. 

So I need to run it for last 7 days only to see latest results. 

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!