https://community.splunk.com/t5/Splunk-Search/Change-the-span-start-time-defaults-when-using-bin-span-1w-time/m-p/374827#M110171 <P>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.<BR /> 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.</P> <P>Across all sources there are approx 2M events indexed each week so I have started out using tstats to count events.<BR /> 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. </P> <P>I'm hitting two problems - <BR /> 1) I can't get the span for each week to start at midnight Sunday <BR /> 2) I can't get the columns to be ordered from newest to oldest</P> <P>So far I have <BR /> | tstats count where index=test_index sourcetype=test_st by source , _time span=1h <BR /> | eval 4weeks= relative_time(now(),"-4w@w0")<BR /> | where _time &gt; 4weeks<BR /> | sort source<BR /> | bin span=1w _time<BR /> | eval time=strftime(_time,@%m,%d")<BR /> | chart values(count) as count over source by time</P> <P>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<BR /> 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.</P> <P>Search Results based on the search above</P> <P>source 04:10. 04:17. 04:24. 05:01<BR /> source1. 267000. 209703. 212682<BR /> source2. 1019148. 1040676. 1040832<BR /> source3. 28353. 29406. 29094<BR /> source4 22542. 22620. 22549</P> <P>The results above are what I get when running today 3/5 - the span seems to start on Monday eg 05:01<BR /> However as files are normally ingested on Sunday and Monday across all sources I see files for this week as showing in last week<BR /> Also I'd prefer that the table was sorted newest to oldest from right to left.</P> <P>Any thoughts or suggestions welcome. Thanks</P> Tue, 29 Sep 2020 19:20:53 GMT skelly99 2020-09-29T19:20:53Z https://community.splunk.com/t5/Splunk-Search/Change-the-span-start-time-defaults-when-using-bin-span-1w-time/m-p/374827#M110171 <P>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.<BR /> 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.</P> <P>Across all sources there are approx 2M events indexed each week so I have started out using tstats to count events.<BR /> 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. </P> <P>I'm hitting two problems - <BR /> 1) I can't get the span for each week to start at midnight Sunday <BR /> 2) I can't get the columns to be ordered from newest to oldest</P> <P>So far I have <BR /> | tstats count where index=test_index sourcetype=test_st by source , _time span=1h <BR /> | eval 4weeks= relative_time(now(),"-4w@w0")<BR /> | where _time &gt; 4weeks<BR /> | sort source<BR /> | bin span=1w _time<BR /> | eval time=strftime(_time,@%m,%d")<BR /> | chart values(count) as count over source by time</P> <P>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<BR /> 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.</P> <P>Search Results based on the search above</P> <P>source 04:10. 04:17. 04:24. 05:01<BR /> source1. 267000. 209703. 212682<BR /> source2. 1019148. 1040676. 1040832<BR /> source3. 28353. 29406. 29094<BR /> source4 22542. 22620. 22549</P> <P>The results above are what I get when running today 3/5 - the span seems to start on Monday eg 05:01<BR /> However as files are normally ingested on Sunday and Monday across all sources I see files for this week as showing in last week<BR /> Also I'd prefer that the table was sorted newest to oldest from right to left.</P> <P>Any thoughts or suggestions welcome. Thanks</P> Tue, 29 Sep 2020 19:20:53 GMT https://community.splunk.com/t5/Splunk-Search/Change-the-span-start-time-defaults-when-using-bin-span-1w-time/m-p/374827#M110171 skelly99 2020-09-29T19:20:53Z https://community.splunk.com/t5/Splunk-Search/Change-the-span-start-time-defaults-when-using-bin-span-1w-time/m-p/374828#M110172 <P>You have to do it in SPL like this:</P> <PRE><CODE>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) </CODE></PRE> Thu, 03 May 2018 14:21:32 GMT https://community.splunk.com/t5/Splunk-Search/Change-the-span-start-time-defaults-when-using-bin-span-1w-time/m-p/374828#M110172 woodcock 2018-05-03T14:21:32Z https://community.splunk.com/t5/Splunk-Search/Change-the-span-start-time-defaults-when-using-bin-span-1w-time/m-p/374829#M110173 <P>Thanks for the suggestion - unfortunately the raw events don't have timestamps so I don't get the date-* fields. </P> Fri, 04 May 2018 09:30:05 GMT https://community.splunk.com/t5/Splunk-Search/Change-the-span-start-time-defaults-when-using-bin-span-1w-time/m-p/374829#M110173 skelly99 2018-05-04T09:30:05Z https://community.splunk.com/t5/Splunk-Search/Change-the-span-start-time-defaults-when-using-bin-span-1w-time/m-p/374830#M110174 <P>OK, so you create the field, like this:</P> <PRE><CODE>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) </CODE></PRE> Fri, 04 May 2018 21:53:35 GMT https://community.splunk.com/t5/Splunk-Search/Change-the-span-start-time-defaults-when-using-bin-span-1w-time/m-p/374830#M110174 woodcock 2018-05-04T21:53:35Z