topic Re: how to use specific start date in weekly timechart? in Splunk Search

how to use specific start date in weekly timechart?

LearningGuy — Tue, 05 Mar 2024 20:15:19 GMT

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

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

Re: how to use specific start date in weekly timechart?

yuanliu — Tue, 05 Mar 2024 22:16:06 GMT

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

_time	Student1	Student2
2024-02-04	10	9
2024-02-11	6	8
2024-02-18	10	9
2024-02-25	7	6

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

_time	Student1	Student2
2024-02-02	10	9
2024-02-09	8	6
2024-02-16	9	8
2024-02-23	5	9

Hope this helps.

Re: how to use specific start date in weekly timechart?

LearningGuy — Wed, 06 Mar 2024 04:15:05 GMT

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

_time	Student1	Student2
2/4/2024	10	9
2/11/2024	7	7
2/18/2024	10	9
2/25/2024	6	5

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")

_time	Student1	Student2
2/2/2024	10	9
2/9/2024	9	5
2/16/2024	8	7
2/23/2024	6	8
3/1/2024	10	9

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

_time	Student1 - Math Grade	Student1 - expected Math Grade	Student2 - Math Grade	Student2 - expected Math Grade
2024-02-04	10	NULL	9	NULL
2024-02-11	6	7	8	7
2024-02-18	10	10	9	9
2024-02-25	7	6	6	5

Second suggestion - begin on 02/09/2024

_time	Student1 - Math Grade	Student1 - expected Math Grade	Student2 - Math Grade	Student2 - expected Math Grade
2024-02-02	10	NULL	9	NULL
2024-02-09	8	9	6	5
2024-02-16	9	8	8	7
2024-02-23	5	6	9	8

Student 1 - Math Grade

Student 2 - Math Grade

Re: how to use specific start date in weekly timechart?

yuanliu — Wed, 06 Mar 2024 05:19:05 GMT

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

Re: how to use specific start date in weekly timechart?

LearningGuy — Wed, 06 Mar 2024 15:29:48 GMT

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.

Re: how to use specific start date in weekly timechart?

bowesmana — Wed, 06 Mar 2024 21:17:38 GMT

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

Re: how to use specific start date in weekly timechart?

LearningGuy — Thu, 07 Mar 2024 03:24:37 GMT

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 Time	Start	End		Chose Time Frame
Date Range	2/5/2024	3/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

Thursday

Friday

Saturday

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Student

2/8/2024

2/9/2024

2/10/2024

2/11/2024

2/12/2024

2/13/2024

2/14/2024

2/15/2024

2/16/2024

2/17/2024

2/18/2024

2/19/2024

2/20/2024

2/21/2024

2/22/2024

2/23/2024

2/24/2024

2/25/2024

2/26/2024

2/27/2024

2/28/2024

2/29/2024

3/1/2024

Student1

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

	Thursday	Thursday	Thursday	Thursday
Student	2/8/2024	2/15/2024	2/22/2024	2/29/2024
Student1	10	7	7	10

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

	Monday	Monday	Monday	Monday
Student	2/5/2024	2/12/2024	2/19/2024	2/26/2024
Student1	NULL	6	10	7

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

	Tuesday	Tuesday	Tuesday	Tuesday
Student	2/6/2024	2/13/2024	2/20/2024	2/27/2024
Student1	NULL	5	9	8

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

Re: how to use specific start date in weekly timechart?

LearningGuy — Thu, 07 Mar 2024 03:47:00 GMT

@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

Day	time	_time	Student	MathGrade	mod 604800
Thursday	2/8/24 5:00 AM	1707368400	Student1	10	18000
Friday	2/9/24 5:00 AM	1707454800	Student1	9	104400
Saturday	2/10/24 5:00 AM	1707541200	Student1	8	190800
Sunday	2/11/24 5:00 AM	1707627600	Student1	7	277200
Monday	2/12/24 5:00 AM	1707714000	Student1	6	363600
Tuesday	2/13/24 5:00 AM	1707800400	Student1	5	450000
Wednesday	2/14/24 5:00 AM	1707886800	Student1	6	536400
Thursday	2/15/24 5:00 AM	1707973200	Student1	7	18000
Friday	2/16/24 5:00 AM	1708059600	Student1	8	104400
Saturday	2/17/24 5:00 AM	1708146000	Student1	9	190800
Sunday	2/18/24 5:00 AM	1708232400	Student1	10	277200
Monday	2/19/24 5:00 AM	1708318800	Student1	10	363600
Tuesday	2/20/24 5:00 AM	1708405200	Student1	9	450000
Wednesday	2/21/24 5:00 AM	1708491600	Student1	8	536400
Thursday	2/22/24 5:00 AM	1708578000	Student1	7	18000
Friday	2/23/24 5:00 AM	1708664400	Student1	6	104400
Saturday	2/24/24 5:00 AM	1708750800	Student1	5	190800
Sunday	2/25/24 5:00 AM	1708837200	Student1	6	277200
Monday	2/26/24 5:00 AM	1708923600	Student1	7	363600
Tuesday	2/27/24 5:00 AM	1709010000	Student1	8	450000
Wednesday	2/28/24 5:00 AM	1709096400	Student1	9	536400
Thursday	2/29/24 5:00 AM	1709182800	Student1	10	18000
Friday	3/1/24 5:00 AM	1709269200	Student1	10	104400
Saturday	3/2/24 5:00 AM	1709355600	Student1	9	190800
Sunday	3/3/24 5:00 AM	1709442000	Student1	8	277200

Re: how to use specific start date in weekly timechart?

bowesmana — Thu, 07 Mar 2024 22:48:44 GMT

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.

Re: how to use specific start date in weekly timechart?

yuanliu — Thu, 07 Mar 2024 23:44:58 GMT

@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?

Re: how to use specific start date in weekly timechart?

LearningGuy — Fri, 08 Mar 2024 01:53:02 GMT

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

Re: how to use specific start date in weekly timechart?

LearningGuy — Fri, 08 Mar 2024 01:56:38 GMT

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!!