Getting Data In

time range to display count of weekly

DataOrg
Builder

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

0 Karma
1 Solution

niketn
Legend

@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).

alt text

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@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).

alt text

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

DataOrg
Builder

@niketnilay .. thanks. it was working

HiroshiSatoh
Champion

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
0 Karma

adonio
Ultra Champion

try ... | timechart span=1w max(count) as m_count
hope i understood your question

0 Karma

DataOrg
Builder

how can i get timerange like this 2018/03/01 - 2018/03/07 in fields

0 Karma

niketn
Legend

@premranjith, is this for report or dashboard?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

adonio
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...