How to do "Date offset". Query giving output between "Thursday to Thursday", Instead I want it from "Monday to Sunday"

[sudarshan391]

I am using below query to get the data on weekly basis, It is giving me the output on weekly basis but the date that it selects is from "Thursday to Thursday", Instead I want it from "Monday to Sunday". Please help:
How can i get the data from between Monday to Sunday.

index=c sourcetype=c 
      | where State in ("Closed","Resolved")
      | rename "Resolving Time" as rt 
      | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Resolved)
      | eval _time = relative_time(Resolved,"@w")
      | rename _time as Time
      |timechart span=1w count as "Number of Tickets" eval(round(avg(rt),2)) as "Average Operational Time" 
      |eval Time=strftime(Time, "%U %Y")

[cmerriman · Accepted solution]

don't use timechart, it will rebucket your time. also, "@w1" will go Monday-Sunday, "@w" will go Sunday-Saturday. try something like this. I combined your Time evals for efficiency:

index=c sourcetype=c 
       | where State in ("Closed","Resolved")
       | rename "Resolving Time" as rt 
       | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Resolved)
       | eval Time=strftime(relative_time(Resolved,"@w"),"%U %Y")
       |chart count as "Number of Tickets" eval(round(avg(rt),2)) as "Average Operational Time" by Time

your other syntax, you'll want to do something similar.

 index=comos sourcetype=comos  |eval _time=relative_time(_time,"@w")| chart count(eval(State = "Closed" OR State= "Resolved")) as "Closed", count(eval(State = "Assigned" OR State= "Open")) as "Still Open", count(eval(State = "Pending")) as "Pending" by _time |  streamstats sum(*) as *

[sudarshan391]

@cmerriman Many Thanks for your fast reply. appreciate your time.

I have tried the first query (Average Operational Time) and it worked for me but now the issue which I am facing is that the output is not in Right order, Splunk has data for both 2016 and 2017 year , Ideally it should display data week wise first for 2016 and then 2017 but the output is coming like below

28 2016
28 2017
29 2016
29 2017

Any suggestion over this. Thanks.

[cmerriman]

can you switch the year and week around? That will sort by year and then week.

| eval Time=strftime(relative_time(Resolved,"@w"),"%Y %U")

if you really need the week displayed first, try this:

| eval Time=relative_time(Resolved,"@w")
 |chart count as "Number of Tickets" eval(round(avg(rt),2)) as "Average Operational Time" by Time
| eval Time=strftime(Time,"%U %Y")

[sudarshan391]

Thanks cmerriman, both options worked. 🙂
First gave the output like "2017 30" and second one gave the output like "30 2017".

Let me try the second query and I am sure that will work too.

Thanks again for your quick and helpful reply.

Regards,
Sudarshan

[sudarshan391]

Hi cmerriman, Just having one more query, is it possible to Label the respective week on X axis, right now it is blank and it is required to label the week no there.

Thanks
Sud

[cmerriman]

How many weeks are you looking at? the x axis can only handle displaying so many values and if there gets to be so many, sometimes it will drop all. you could try to change the axis rotation and display them at a 45 degree angle or vertically and see if that helps. My first suggestion is to look at a smaller range or bucket by a larger frame than one week. you could also try to swap the chart command out with stats and see if that helps.

[sudarshan391]

Same issue with the below query also, It it giving data between ""Thursday to Thursday" instead "Monday to Sunday" Please help.

index=comos sourcetype=comos | timechart count(eval(State = "Closed" OR State= "Resolved")) as "Closed", count(eval(State = "Assigned" OR State= "Open")) as "Still Open", count(eval(State = "Pending")) as "Pending" span=1w |  streamstats sum(*) as *

[cmerriman]

what day are you running this on? is it scheduled or are you running it ad-hoc, so sometimes you'll run it on a Monday, other times you might run it on a Wednesday, etc.?

[sudarshan391]

No, there are no fix days to run this. Currently I am running this on ad-hoc basis. As it is running in cumulative mode It is giving the correct output for last week but If I see the output of just one week before the last week it is showing the wrong data as it is counting from "Thursday to Thursday."