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

[skelly99]

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

[woodcock]

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)

[skelly99]

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

[woodcock]

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)