Splunk Search

Change the span start time defaults when using bin span=1w _time

skelly99
Explorer

Hi - I'm trying to display a count of all sources over a 4 week period for a specific source type as part of a data quality process.
There are several sources for this source type which all get ingested once per week. We want to look for issues where a source event count deviates significantly from the previous weeks.

Across all sources there are approx 2M events indexed each week so I have started out using tstats to count events.
I want to display the results in table format with the source column as the leftmost column and the count per week over the last 4 weeks as columns from newest to oldest in date order.

I'm hitting two problems -
1) I can't get the span for each week to start at midnight Sunday
2) I can't get the columns to be ordered from newest to oldest

So far I have
| tstats count where index=test_index sourcetype=test_st by source , _time span=1h
| eval 4weeks= relative_time(now(),"-4w@w0")
| where _time > 4weeks
| sort source
| bin span=1w _time
| eval time=strftime(_time,@%m,%d")
| chart values(count) as count over source by time

Note I use span=1h on the tstats command as a file take several seconds to index so I want to only show one time for this source
I have to also set a time field in order to have the time in a readable format - passing _time in the by clause on the chart command ends up with _time being display using unix time format.

Search Results based on the search above

source 04:10. 04:17. 04:24. 05:01
source1. 267000. 209703. 212682
source2. 1019148. 1040676. 1040832
source3. 28353. 29406. 29094
source4 22542. 22620. 22549

The results above are what I get when running today 3/5 - the span seems to start on Monday eg 05:01
However as files are normally ingested on Sunday and Monday across all sources I see files for this week as showing in last week
Also I'd prefer that the table was sorted newest to oldest from right to left.

Any thoughts or suggestions welcome. Thanks

Tags (3)
0 Karma

woodcock
Esteemed Legend

You have to do it in SPL like this:

index=_audit date_wday=*
| dedup date_wday
| bin _time span=1w 
| table _time date_wday
| eval _time=_time - (tonumber(strftime(now(), "%w")) * 60 * 60 *24)
0 Karma

skelly99
Explorer

Thanks for the suggestion - unfortunately the raw events don't have timestamps so I don't get the date-* fields.

0 Karma

woodcock
Esteemed Legend

OK, so you create the field, like this:

Your Base Search Here
| eval date_wday = strftime(_time, "%a")
| dedup date_wday
| bin _time span=1w 
| table _time date_wday
| eval _time=_time - (tonumber(strftime(now(), "%w")) * 60 * 60 *24)
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...