Daily/Weekly/Monthly Span on CSV dATA

[reverse]

I have data in CSV like below -
How can I put span=1w on this after pulling into splunk?
I tried assigning this date to _time ->didn't work 😞
Please help.

5/1/2019    0
5/2/2019    0
5/3/2019    0
5/4/2019    0
5/5/2019    0
5/6/2019    0
5/7/2019    0
5/8/2019    136
5/9/2019    62208
5/10/2019   56432
5/11/2019   618
5/12/2019   5604
5/13/2019   130244
5/14/2019   152660
5/15/2019   137472
5/16/2019   147968

[denzelchung · Accepted solution]

You will need to convert your CSV data's date into epoch format first in order to filter by 1 week ago.

The following example takes in a date (e.g. today, 16/07/2019), format it using strptime, then filter the date within now and 1 week ago.

source=*
| eval date="16/07/2019"  
| eval formattedDate=strptime(date, "%d/%m/%Y") 
| where formattedDate > relative_time(now(), "-1w@w") 
| table date formattedDate

[reverse]

Thanks for the help @denzelchung .. seems half done..

so now i have 3 columns where date is the CSV date | eval formattedDate=strptime(Date, "%Y-%m-%d")

| table Date formattedDate XXX

How can I timechart XXX over 7d or 1w

[denzelchung]

What is XXX? You can filter formattedDate to the past 1 week, then replace _time since timechart span relies on _time.

| where formattedDate > relative_time(now(), "-1w")
| eval _time=formattedDate
| timechart count span=7d

[reverse]

right now it is starting Thursday for unknown reason
@denzelchung

[reverse]

@denzelchung

why there is break of 4 days .. please observe last 4 entries..

| eval formattedDate=strptime(Date, "%Y-%m-%d")
| where formattedDate > relative_time(now(), "-10w@w")
| eval _time=formattedDate
| timechart sum(xxx) span=1w@w4
| eval avgCount = round(avgCount,0)

Date    xxx
2019-05-05T00:00:00.000-0400    119394
2019-05-12T00:00:00.000-0400    705593
2019-05-19T00:00:00.000-0400    724051
2019-05-26T00:00:00.000-0400    622243
2019-06-02T00:00:00.000-0400    923656
2019-06-09T00:00:00.000-0400    1040106
2019-06-16T00:00:00.000-0400    1117687
2019-06-23T00:00:00.000-0400    1331860
2019-06-30T00:00:00.000-0400    779990
2019-07-07T00:00:00.000-0400    838488
2019-07-11T00:00:00.000-0400    884224

[reverse]

You are amazing ! @denzelchung
It worked!!!

One more help please .. how can I choose start of the week - sunday/monday in 7d span ?

[denzelchung]

Currently we're using "span=1w", which is 1 week from today. If we run the same search tomorrow, it would probably start on Friday for you.

To start from the start of the week, use "1w@w". The additional "@w" would snap the time to the beginning of the week. Take a look at https://answers.splunk.com/answers/5350/possible-to-redefine-w-to-start-on-different-day.html for more details.
You can also specify which day you want to start on (e.g. w0 = Sunday, w1 = Monday, etc.)

[reverse]

it worked!

[reverse]

Date        XXX
 5/1/2019    0
 5/2/2019    0
 5/3/2019    0
 5/4/2019    0
 5/5/2019    0
 5/6/2019    0
 5/7/2019    0
 5/8/2019    136
 5/9/2019    62208
 5/10/2019    56432
 5/11/2019    618
 5/12/2019    5604
 5/13/2019    130244
 5/14/2019    152660
 5/15/2019    137472
 5/16/2019    147968

In CSV i have data since feb 2019 .. I want to timechart avg(xxx) span=1w

[reverse]

Please guide. @jnudell_2 @Vijeta

[reverse]

@renjith.nair -Please guide