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!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...