timechart when span set to a week gives a different values , in comparison to span set to a day for a duration of a week.

[shahid285]

I am running a query with a timechart span of '1w' duration of earliest being set to '-4w' and latest set to 'now', the result for a week returned is far different from the results returned, when we run the query for the duration of 1 week with the timechart span set to '1d' . Attached is the screenshot of the issue, but i am restricted here by the forum to post the other example screenshots. you may also see that the span by day is losing a days of data mentioned in the range. Requesting help in this regard, as i need to show the data for 30days spanned by week, and on click of the specific bar (which is a specific week), it need to show up the exact count in the
response spanned by day.

alt text

Thanks in Advance,

Mohammed Shahid Nawaz

[shahid285 · Accepted solution]

After multiple and repeated attempts, the query was unable to return data like the week starting from today, hence i had to go the span of 1w@w1, where the weeks starts from Monday and ends in Sunday. The problem with this approach is the return of additional days data (>30) even when the earliest is set to last 29 days from today.

The following is the query,
index="83299-aci" sourcetype="_internal" earliest=-29d@w1 latest=+1d@d-1s | timechart span=1w@w1 sum(auditsCount) as "count" |eval timeTp=strftime(_time,"%Y-%m-%dT%H:%M:%S%:z") | eval today=relative_time(now(), "@d")|eval yesterday=relative_time(now(), "+1d@d-1s")|eval yesterday = strftime(yesterday,"%Y-%m-%dT%H:%M:%S%:z")| eval weekend=_time+604799 | eval currentWeekend=today-518400 |eval currentWeekend = strftime(currentWeekend,"%Y-%m-%dT%H:%M:%S%:z")|eval weekend=strftime(weekend,"%Y-%m-%dT%H:%M:%S%:z")| eval minTime =min(_time)| eval diff = (today - minTime)| eval diff = round(diff/60/60/24) | eval minTime = strftime(minTime,"%Y-%m-%dT%H:%M:%S%:z") | eval _time = strftime(_time,"%Y-%m-%dT%H:%M:%S%:z")|eval dayBefore =(today-1)|eval dayBefore=strftime(dayBefore,"%Y-%m-%dT%H:%M:%S%:z") | eval tonight =(today+(86400-1))|eval tonight=strftime(tonight,"%Y-%m-%dT%H:%M:%S%:z")| eval today = strftime(today,"%Y-%m-%dT%H:%M:%S%:z")| eval week = case((diff=0),today+" - "+tonight,(diff <= 7),_time+" - "+tonight,(diff > 7) ,_time+" - "+weekend) | eval count = if(count!="" or count != NULL, count,0 ) | table week count

Thank you for your support @DMohn

Regards
Mohammed Shahid Nawaz

[DMohn]

You are looking at different time ranges here ... See the screenshots, your day-by-day example goes from 3/8/19 to 3/14/19, while the other one goes to 3/17/19.

Your second example does not start / end its week summary at the beginning / end of the weeks. Try to modify your time ranges so they snap to the same days (like earliest=-4w@w0) - see https://docs.splunk.com/Documentation/Splunk/7.2.4/Search/Specifytimemodifiersinyoursearch#Special_t... for reference.

[shahid285]

Hi DMohn,
Thanks for the reply, I don't want to snap the time from a starting of the week or sundays, like your suggestion above, But would like to consider the week starting from the day the query is being run till the last 30days. My example is not having the time mentioned by you "3/7/19", neither in the screenshot nor in the results.
My depiction of screenshot is about sum(auditCounts) which varies when i run the query for the week for the duration from 3/8/19 to 3/14/19, with the span of 1d. And, weeks with the span of 1w for the duration of last 30days from today, which as well contains the results for the duration 3/8/19 to 3/14/19, but with different values.

I am unable to attach 2 more examples of the same situation for more clarity, as the blog is not accepting more than 2 screenshots.

let me explain you here the query and its results:

Query 1:
index="83299-aci" sourcetype="_internal" earliest="03/08/2019:00:00:00" latest="03/14/2019:00:00:00" | stats sum(auditsCount) as "count"| return $count

this returns the result of "2401866"
The second query too, is an another example here,

Query2:
index="83299-aci" sourcetype="_internal" earliest="03/08/2019:00:00:00" latest="03/14/2019:00:00:00" | timechart span=1w sum(auditsCount) as "count"|eval timeTp=strftime(_time,"%Y-%m-%dT%H:%M:%S%:z")| table timetp count
returns results as "timetp =2019-03-08T00:00:00+00:00 , count= "2401866"

The previous 2 screenshots, attached gives the drastic difference in the sum for the same weeks

Thanks
Mohammed Shahid Nawaz

[DMohn]

Can you try running the weekly query with a span=7d instead of span=1w and see if the result changes? I still think the aggregation covers different time ranges...

[shahid285]

It still behaves the same, even with a span of 7d instead of 1w. I have ingested the data for last 6 days from today, just to get the better understanding, and this is the following findings , where the sum is wrong even for a day in a timechart query with the span of 1d with the duration set to 7days.

Query 1: index="83299-aci" sourcetype="_internal" earliest=-28d@d latest="03/23/2019:23:59:59" | timechart span=1w sum(auditsCount) as "auditsCount" |eval timeTp=strftime(_time,"%Y-%m-%dT%H:%M:%S%:z")| table timeTp auditsCount

for this query, considering the week from today as 03/16/2019:00:00:00 to 03/22/2019:23:59:59, i am getting the sum as "130939"
However, If i run the query for each whole day the overall sum seems to be vaguely different.

for the duration 😆
earliest="03/16/2019:00:00:00" latest="03/16/2019:23:59:59" i am getting 70547
earliest="03/17/2019:00:00:00" latest="03/17/2019:23:59:59" i am getting 72558
earliest="03/18/2019:00:00:00" latest="03/18/2019:23:59:59" i am getting 0
earliest="03/19/2019:00:00:00" latest="03/19/2019:23:59:59" i am getting 70274
earliest="03/20/2019:00:00:00" latest="03/20/2019:23:59:59" i am getting 0
earliest="03/21/2019:00:00:00" latest="03/21/2019:23:59:59" i am getting 81992
earliest="03/22/2019:00:00:00" latest="03/22/2019:23:59:59" i am getting 130939 [which is same value returned by time chart for the week starting from 03/16/2019:00:00:00 to 03/22/2019:23:59:59]

seeing the results per date the time chart query should have returned me the sum as "426310"(130939+81992+70274+72558+70547).

also the sum i see the discrepency in the results of the below query , as i have ran it for the same duration of week explained above.

index="83299-aci" sourcetype="_internal" earliest="03/16/2019:00:00:00" latest="03/22/2019:23:59:59" | stats sum(auditsCount) as "count"| return $count

I have got the value as "425510" .
however, i see the below query as well returned me the same result of "425510"

index="83299-aci" sourcetype="_internal" earliest="03/16/2019:00:00:00" latest="03/22/2019:23:59:59" | timechart span=1w sum(auditsCount) as "count"|eval timeTp=strftime(_time,"%Y-%m-%dT%H:%M:%S%:z")| table count

Thanks
Mohammed Shahid Nawaz