i have 4 months data. where i want to display the order count in weekly range.for example
date count
2018/03/01 - 2018/03/07 450
2018/03/08 - 2018/03/14 650
2018/04/22 - 2018/04/28 745
i want output for the weekly time range inbetween and count between those date
@premranjithj you can perform stats by number of the week of the year. You can do so by converting _time with either %U or %W modifier depending on whether you want Sunday or Monday to be the first day of the week.
While performing stats one of the dates of the week needs to be captured. I have used min(_time)
, but you can use max()
, earliest()
or latest()
as any one would work. A date in the week is required for us to get Week Start and Week End Date using relative_time
which gives first day of the week using @w
(for Sunday as I have used %U
for stats which is also Sunday) and last day of week as Saturday using +1w@w-1d
(which is snap-to time to say move ahead to next week Sunday and select 1 day before which is Saturday of current week).
PS: You would notice from screenshot that some of the times your stats will not have events from the first day of week i.e. for the Week Of 03/04 - 03/10, the earliest time event is received is 03/05. However, using relative_time()
with snap to time gives us the requires Week Start and Week End Dates.
Please try out the run anywhere search based on Splunk's _internal index and confirm:
index=_internal sourcetype=splunkd log_level!=INFO
| eval WeekOfYear=strftime(_time,"%Y - %U")
| stats count as Errors min(_time) as MinTime by WeekOfYear
| eval MinTime=strftime(MinTime,"%m/%d/%Y")
| eval WeekDateRange=strftime(relative_time(strptime(MinTime,"%m/%d/%Y"),"-0w@w"),"%m/%d/%Y")." - ".strftime(relative_time(strptime(MinTime,"%m/%d/%Y"),"+1w@w-1d"),"%m/%d/%Y")
%U Week number of the year ( Sunday as the first day of the week
) as a zero padded decimal number. All days in a new year preceding the first Sunday are considered to be in week 0.
%W Week number of the year ( Monday as the first day of the week
) as a decimal number. All days in a new year preceding the first Monday are considered to be in week 0.
@premranjithj you can perform stats by number of the week of the year. You can do so by converting _time with either %U or %W modifier depending on whether you want Sunday or Monday to be the first day of the week.
While performing stats one of the dates of the week needs to be captured. I have used min(_time)
, but you can use max()
, earliest()
or latest()
as any one would work. A date in the week is required for us to get Week Start and Week End Date using relative_time
which gives first day of the week using @w
(for Sunday as I have used %U
for stats which is also Sunday) and last day of week as Saturday using +1w@w-1d
(which is snap-to time to say move ahead to next week Sunday and select 1 day before which is Saturday of current week).
PS: You would notice from screenshot that some of the times your stats will not have events from the first day of week i.e. for the Week Of 03/04 - 03/10, the earliest time event is received is 03/05. However, using relative_time()
with snap to time gives us the requires Week Start and Week End Dates.
Please try out the run anywhere search based on Splunk's _internal index and confirm:
index=_internal sourcetype=splunkd log_level!=INFO
| eval WeekOfYear=strftime(_time,"%Y - %U")
| stats count as Errors min(_time) as MinTime by WeekOfYear
| eval MinTime=strftime(MinTime,"%m/%d/%Y")
| eval WeekDateRange=strftime(relative_time(strptime(MinTime,"%m/%d/%Y"),"-0w@w"),"%m/%d/%Y")." - ".strftime(relative_time(strptime(MinTime,"%m/%d/%Y"),"+1w@w-1d"),"%m/%d/%Y")
%U Week number of the year ( Sunday as the first day of the week
) as a zero padded decimal number. All days in a new year preceding the first Sunday are considered to be in week 0.
%W Week number of the year ( Monday as the first day of the week
) as a decimal number. All days in a new year preceding the first Monday are considered to be in week 0.
@niketnilay .. thanks. it was working
Try this!
If everyday data exists ...
|eval time=_time
|timechart span=1w count min(_time) as start max(_time) as end
|eval date=strftime(start,"%Y/%m/%d")+"-"+strftime(end,"%Y/%m/%d")
|table date count
When editing from _time
|timechart span=1w count
|eval date=strftime(_time,"%Y/%m/%d")+"-"+strftime(relative_time(_time,"+6d@d"),"%Y/%m/%d")
|table date count
try ... | timechart span=1w max(count) as m_count
hope i understood your question
how can i get timerange like this 2018/03/01 - 2018/03/07 in fields
@premranjith, is this for report or dashboard?
not sure what exactly do you mean...
if you mean that 2018/03/01 - 2018/03/07
is a full string (value) of a field, lets call it week
you can maybe use something like this:
| makeresults count=1 | eval date = "2018/03/01 - 2018/03/07,2018/03/08 - 2018/03/14,2018/04/22 - 2018/04/28"
| makemv delim="," date
| mvexpand date
| rex field=date "(?<week_start>\d{4}\/\d{2}\/\d{2})\s+\-\s+(?<week_ends>\d{4}\/\d{2}\/\d{2})"
you can take it from there by converting to _time for timechart or play with it as you like