Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

how to use specific start date in weekly timechart?

LearningGuy
Builder
‎03-05-2024 12:14 PM


Hello,

How to use specific start date in weekly timechart?
For example: I have a set of Grade (Math, English, Science) data for Student1 and Student2 from 2/8/2024  to 3/1/2024
When I use timechart weekly, it always starts with 02/08/2024.
| timechart span=1w first(MathGrade) by Student useother=f limit=0
How do I start from other date, such as 02/09/2024 or 02/10/2024?
Thank you for your help

LearningGuy_0-1709669479853.png

Here's the search

 

| makeresults format=csv data="_time,Student,MathGrade,EnglishGrade,ScienceGrade
1707368400,Student1,10,10,10
1707454800,Student1,9,9,9
1707541200,Student1,8,8,8
1707627600,Student1,7,7,7
1707714000,Student1,6,6,6
1707800400,Student1,5,5,5
1707886800,Student1,6,6,6
1707973200,Student1,7,7,7
1708059600,Student1,8,8,8
1708146000,Student1,9,9,9
1708232400,Student1,10,10,10
1708318800,Student1,10,10,10
1708405200,Student1,9,9,9
1708491600,Student1,8,8,8
1708578000,Student1,7,7,7
1708664400,Student1,6,6,6
1708750800,Student1,5,5,5
1708837200,Student1,6,6,6
1708923600,Student1,7,7,7
1709010000,Student1,8,8,8
1709096400,Student1,9,9,9
1709182800,Student1,10,10,10
1709269200,Student1,10,10,10
1707368400,Student2,9,9,9
1707454800,Student2,5,5,5
1707541200,Student2,6,6,6
1707627600,Student2,7,7,7
1707714000,Student2,8,8,8
1707800400,Student2,9,9,9
1707886800,Student2,5,5,5
1707973200,Student2,6,6,6
1708059600,Student2,7,7,7
1708146000,Student2,8,8,8
1708232400,Student2,9,9,9
1708318800,Student2,9,9,9
1708405200,Student2,5,5,5
1708491600,Student2,6,6,6
1708578000,Student2,7,7,7
1708664400,Student2,8,8,8
1708750800,Student2,9,9,9
1708837200,Student2,5,5,5
1708923600,Student2,6,6,6
1709010000,Student2,7,7,7
1709096400,Student2,8,8,8
1709182800,Student2,9,9,9
1709269200,Student2,9,9,9"
| table _time, Student, MathGrade, EnglishGrade, ScienceGrade
| timechart span=1w first(MathGrade) by Student useother=f limit=0

 

 



 

Labels (1)
Labels
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

bowesmana
SplunkTrust
SplunkTrust
‎03-07-2024 02:48 PM

Getting day of week from time is simple with

| eval dow=strftime(_time, "%w")

which gives you a value 0-6 (0=Sunday).

Remember that mod will only work for you if your time component is the same - in your example it's 5am, so 5am thursday is 5 * 3600 = 18000 whereas strftime will simply give you dow.

Another function that is useful for determining the range of the search is addinfo, which adds some fields to the event

https://docs.splunk.com/Documentation/SplunkCloud/9.0.2305/SearchReference/addinfo

so you can know the search start and end time range. From there you know what day the user has chosen to start the search from.

Here is something you can do to filter out the data needed before the timechart - it effectively removes all data that is not in the correct day

| addinfo
| where strftime(info_min_time, "%w")=strftime(_time, "%w")
| fields - info*

put that before the timechart.

However, if you can select other options that weekly, your timechart and day-of-week will not be relevant any more. 

View solution in original post

Reply
Solved! Jump to solution

yuanliu
SplunkTrust
SplunkTrust
‎03-05-2024 09:19 PM

1 and 2: I should have noted that I was running the emulations on my laptop that has time zone set to -0800.

3. When you have span option in timechart, every _time is sorted into bins with these spans.  You can observe this with these two commands.

| bin _time span=1w@w

and

| bin _time span=1w
Reply
Solved! Jump to solution

LearningGuy
Builder
‎03-06-2024 07:29 AM

Hello @yuanliu ,

Thank you for assistance. 

Comparing  | bin _time span=1w   (Left)  and    | bin _time span=1w@w   (Right)
When the span was changed from 1w to 1w@w,  it  looks like the data was shifted from 2024-02-08 to 2024-02-04.     Why did Splunk shift the data? Is this a normal behavior?
I expect the data for 2024-02-04 to be NULL. 

Is there a way to leave the data as is (not shifting), when moving the start date to 2024-02-04?

Please suggest.  I appreciate your help.

LearningGuy_1-1709738444965.png

 

 

Tags (2)
0 Karma
Reply
Solved! Jump to solution

bowesmana
SplunkTrust
SplunkTrust
‎03-06-2024 01:17 PM

When you bin by time, the first part of the time specifier indicates the size of the bin, e.g. span=1w

You are looking to make buckets of 7 days - that 7 days will start going back from today, which here today is March 7, so counting back will give you

29 Feb
22 Feb
15 Feb
8 Feb

However, when you use the @ specifier, this tells Splunk to "snap to" a specific time start point within the bucket, so when you do

span=1w@w

it tells Splunk to use a 7 day time bucket, but to snap to the beginning of the week, which is a Sunday. You can change this behaviour of the 'start of the week' by adding a relative indicator after that snap to value, so

span=1w@w1

will tell Splunk to snap to the Monday as start of the week.

When you do any kind of bucket creation with bin or span=1w in a timechart, it effectively changes the _time of all events to be the start of the bin - it has to do this because if Student 1 and 2 have different dates within the same week for their first grade, what date could it use for the single event it produces?

So, when you see Student 1 showing as the week of the 4th Feb, it simply means that Student1 was seen at some time in that 7 day bucket.

See this doc, which has a section on the snap to characteristics.

https://docs.splunk.com/Documentation/Splunk/9.2.0/SearchReference/SearchTimeModifiers

 

Reply
Solved! Jump to solution

LearningGuy
Builder
‎03-06-2024 07:24 PM

Hello @bowesmana,

Thank you for your explanation and assistance.

I am building a dashboard for historical data.
1) Choose Time (Time picker) and Time Frame (drop down) 

Choose TimeStartEnd Chose Time Frame
Date Range2/5/20243/2/2024 Weekly


2) This is only dummy data, the real data (not student grade) is based on summary index of summary index  
    For each day a number is calculated from another summary index from 30 day data
For example:   Math Grade (10) for Student1 on 2/8/2024 is from 1/7/2024 - 2/8/2024, and so on
This is the reason why I don't want to keep data on specific date and don't want to shift data using snap

This is student date with transpose. I put coloring code. Blue=Thursday  Monday=Red  Tuesday=Green

 ThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFriday
Student2/8/20242/9/20242/10/20242/11/20242/12/20242/13/20242/14/20242/15/20242/16/20242/17/20242/18/20242/19/20242/20/20242/21/20242/22/20242/23/20242/24/20242/25/20242/26/20242/27/20242/28/20242/29/20243/1/2024
Student11098765678910109876567891010


If I choose start date 2/8/2024, the weekly will start on Thursday

 ThursdayThursdayThursdayThursday
Student2/8/20242/15/20242/22/20242/29/2024
Student1107710


If I choose start date 2/5/2024, the weekly will start on Monday

 MondayMondayMondayMonday
Student2/5/20242/12/20242/19/20242/26/2024
Student1NULL6107


If I choose start date 2/6/2024, the weekly will start on Tuesday

 TuesdayTuesdayTuesdayTuesday
Student2/6/20242/13/20242/20/20242/27/2024
Student1NULL598



I can start from 2/8/2024 (Thursday Weekly) by using the following search:

 

| search Student="Student1"
| table _time, Student, MathGrade, EnglishGrade, ScienceGrade
| timechart span=1w first(MathGrade) by Student useother=f limit=0
| fields - _span _spandays
| eval _time = strftime(_time,"%m/%d/%Y") 
| transpose 0 header_field=_time column_name=Student

 


I can start from 2/12/2024 (Monday Weekly) by using the following search:

 

| search Student="Student1"
| where (_time % 604800) = 363600
| table _time, Student, MathGrade, EnglishGrade, ScienceGrade
| timechart span=1w first(MathGrade) by Student useother=f limit=0
| fields - _span _spandays
| eval _time = strftime(_time,"%m/%d/%Y") 
| transpose 0 header_field=_time column_name=Student

 


How do I start from 2/13/2024 (Tuesday Weekly)?
Please suggest. I appreciate your help. Thank you



Tags (2)
0 Karma
Reply
Solved! Jump to solution

LearningGuy
Builder
‎03-06-2024 07:47 PM

@bowesmana @yuanliu 
I think I just figured it out using math (modulo)
 Please let me know what you think..   Thank you for your help

For Tuesday           | where (_time % 604800) = 450000
For Wednesday    | where (_time % 604800) = 536400
And so on..

See this table, there is a pattern for each day of the week

Daytime_timeStudentMathGrademod 604800
Thursday2/8/24 5:00 AM1707368400Student11018000
Friday2/9/24 5:00 AM1707454800Student19104400
Saturday2/10/24 5:00 AM1707541200Student18190800
Sunday2/11/24 5:00 AM1707627600Student17277200
Monday2/12/24 5:00 AM1707714000Student16363600
Tuesday2/13/24 5:00 AM1707800400Student15450000
Wednesday2/14/24 5:00 AM1707886800Student16536400
Thursday2/15/24 5:00 AM1707973200Student1718000
Friday2/16/24 5:00 AM1708059600Student18104400
Saturday2/17/24 5:00 AM1708146000Student19190800
Sunday2/18/24 5:00 AM1708232400Student110277200
Monday2/19/24 5:00 AM1708318800Student110363600
Tuesday2/20/24 5:00 AM1708405200Student19450000
Wednesday2/21/24 5:00 AM1708491600Student18536400
Thursday2/22/24 5:00 AM1708578000Student1718000
Friday2/23/24 5:00 AM1708664400Student16104400
Saturday2/24/24 5:00 AM1708750800Student15190800
Sunday2/25/24 5:00 AM1708837200Student16277200
Monday2/26/24 5:00 AM1708923600Student17363600
Tuesday2/27/24 5:00 AM1709010000Student18450000
Wednesday2/28/24 5:00 AM1709096400Student19536400
Thursday2/29/24 5:00 AM1709182800Student11018000
Friday3/1/24 5:00 AM1709269200Student110104400
Saturday3/2/24 5:00 AM1709355600Student19190800
Sunday3/3/24 5:00 AM1709442000Student18277200
0 Karma
Reply
Solved! Jump to solution

yuanliu
SplunkTrust
SplunkTrust
‎03-07-2024 03:41 PM

@LearningGuy In case it is not obvious, strftime(_time, "%w") is precisely performing a modulus function.  Why reinventing the wheel?  Not only that, when you do | bin _time span=1w@w as @bowesmana and I suggested, Splunk does a division, a modulus, then a negative time shift.  Similarly, when you do relative_time(_time, "+5d@d"),  Splunk does a modulus (on day, not week) then a positive time shift.  Splunk is all math.  All these built-in functions have been subjected to hours and hours of QA tests, and require zero maintenance on programmer's part.  I'd recommend using these robust methods.

But really, I failed to comprehend the followups because you didn't not describe exactly what results you got from the proposed formulae and explain why the results did not meet your requirements.  @bowesmana and I have been explaining to you why some counts are displayed on a date string that you believe should not have counts.  But if you count by week, the starting date of your counting method SHOULD be the display date.  This means that if your data begins on a Wednesday but your week starts on Sunday, the first data point SHOULD appear under Sunday, NOT on Wednesday.  If you shift your week's start to Wednesday, but your data only starts on Friday, the very first data point SHOULD appear under Wednesday, NOT on Friday.  Are we clear on this?

Reply
Solved! Jump to solution

LearningGuy
Builder
‎03-07-2024 05:56 PM

Hello @yuanliu 

Sorry if I did not explain it clearly.
Yes, I am clear on how the span and snap works in Splunk.

I appreciate your help as always.  Thank you!!

0 Karma
Reply
Solved! Jump to solution
Solution

bowesmana
SplunkTrust
SplunkTrust
‎03-07-2024 02:48 PM

Getting day of week from time is simple with

| eval dow=strftime(_time, "%w")

which gives you a value 0-6 (0=Sunday).

Remember that mod will only work for you if your time component is the same - in your example it's 5am, so 5am thursday is 5 * 3600 = 18000 whereas strftime will simply give you dow.

Another function that is useful for determining the range of the search is addinfo, which adds some fields to the event

https://docs.splunk.com/Documentation/SplunkCloud/9.0.2305/SearchReference/addinfo

so you can know the search start and end time range. From there you know what day the user has chosen to start the search from.

Here is something you can do to filter out the data needed before the timechart - it effectively removes all data that is not in the correct day

| addinfo
| where strftime(info_min_time, "%w")=strftime(_time, "%w")
| fields - info*

put that before the timechart.

However, if you can select other options that weekly, your timechart and day-of-week will not be relevant any more. 

Reply
Solved! Jump to solution

LearningGuy
Builder
‎03-07-2024 05:53 PM

Hello @bowesmana 
Your last suggestion exactly what I am looking for.   I accepted as solution.
Thank you for your help

This is what I am trying to do.   
Drop down choose weekly token
Sunday[weeky_token=0],   Monday[weeky_token=1], Tuesday[weekly_token=2], Wednesday[weekly_token=3], Thursday[weekly_token=4], Friday[weekly_token=5], Saturday[weekly_token=6]

| table _time, Student, MathGrade, EnglishGrade, ScienceGrade
| where strftime(_time, "%w") = "$weekly_token$"   
| fields - info*
| timechart span=1w first(MathGrade) by Student useother=f limit=0

 

0 Karma
Reply
Solved! Jump to solution

yuanliu
SplunkTrust
SplunkTrust
‎03-05-2024 02:16 PM

First, thank you for including data emulation in the question.  There are two aspects that Splunk chooses to address separately.  First is calendar time anchor. (I think Splunk calls this "snap-to"; see Date and time format variables.)  If you use @w with the span attribute, timechart will snap to beginning of the week, which is deemed to be start of Sunday on CE calendar, whichever timezone the search head uses.  For example, using your emulation with

 

| timechart span=1w@w first(MathGrade) by Student useother=f limit=0

 

gives

_timeStudent1Student2
2024-02-04109
2024-02-1168
2024-02-18109
2024-02-2576

Now, your ask is to begin a week on an arbitrary day.  Given timechart doesn't support this, the hack is to shift time back and forth.  For example, 02/09/2024 is a Friday, or day 5 in Splunk's dow count.

 

| eval _time = relative_time(_time, "-5d@d")
| timechart span=1w@w first(MathGrade) by Student useother=f limit=0
| eval _time = relative_time(_time, "+5d@d")

 

Using the same emulation, the above gives

_timeStudent1Student2
2024-02-02109
2024-02-0986
2024-02-1698
2024-02-2359

Hope this helps.

Tags (2)
Reply
Solved! Jump to solution

LearningGuy
Builder
‎03-05-2024 08:13 PM

Hello,
Thank you for your help.  
1)  I ran your first suggestion on my search, I got different numbers than yours. Any idea why?

 

| timechart span=1w@w first(MathGrade) by Student useother=f limit=0

 

 

_timeStudent1Student2
2/4/2024109
2/11/202477
2/18/2024109
2/25/202465

 

2)  I ran your second suggestion on my search, I also got different numbers than yours.  Any idea why?

 

 

| eval _time = relative_time(_time, "-5d@d")
| timechart span=1w@w first(MathGrade) by Student useother=f limit=0
| eval _time = relative_time(_time, "+5d@d")

 

 

_timeStudent1Student2
2/2/2024109
2/9/202495
2/16/202487
2/23/202468
3/1/2024109


3) If I removed timechart command and only have the data and table, the data is only from  2024-02-08 to 2024-03-1, so there should be no data for 2024-02-04 and 2024-02-02. 
Where did Splunk give data for 2024-02-04 and 2024-02-02 that should be NULL?

---Student Data---
| table _time, Student, MathGrade

 First suggestion - snap to beginning of the week

_timeStudent1 - Math GradeStudent1 - expected Math GradeStudent2 - 
Math Grade		Student2 - expected Math Grade
2024-02-0410NULL9NULL
2024-02-116787
2024-02-18101099
2024-02-257665


Second suggestion - begin on 02/09/2024

_timeStudent1 - Math GradeStudent1 - expected Math GradeStudent2 - 
Math Grade		Student2 - expected Math Grade
2024-02-0210NULL9NULL
2024-02-098965
2024-02-169887
2024-02-235698


Student 1 - Math Grade

LearningGuy_3-1709696560413.png

Student 2 - Math Grade

LearningGuy_4-1709696981626.png

 



 

 

 

0 Karma
Reply
Get Updates on the Splunk Community!

What's New in Splunk Enterprise 9.4: Features to Power Your Digital Resilience

Hey Splunky People! We are excited to share the latest updates in Splunk Enterprise 9.4. In this release we ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...