topic Re: How to edit my timechart search to compare counts over the last two weeks, and show the count trend in a single value visualization? in Splunk Search

How to edit my timechart search to compare counts over the last two weeks, and show the count trend in a single value visualization?

splunker1981 — Tue, 08 Nov 2016 06:08:50 GMT

Hello Splunk experts -

Not sure how to tackle this problem, hoping someone can assist.

I am trying to compare counts over the last two weeks and use the single value viz to show the count trend. The problem I'm running into is; although I am filtering the events and they start with the earliest event beginning on 2016-10-26 going through 2016-11-08, the timechart command seems to break the events into four groups (as shown below).

2016-10-21  301
2016-10-28  699
2016-11-01  1200
2016-11-08  5

When I go to show the trend, it then uses the last two values (or first 2 if I use reverse), which is an incorrect representation of what I am trying to do.

Is there a way to have it sum or combine the top 2 and bottom 2 values. Something like below:

    2016-10-28  1000
    2016-11-08  1205

I've tried using:

|searchHere  otherFiltering earliest=-14d@d | timechart count span=7d

Any way to work around this issue? Thanks a bunch for the help in advanced.

Re: How to edit my timechart search to compare counts over the last two weeks, and show the count trend in a single value visualization?

gokadroid — Tue, 08 Nov 2016 07:48:49 GMT

Can you please try this:

your base query earliest=-14d@d latest=-d@d
| timechart count span=7d
| eval _time=strftime(_time+7*24*60*60, "%Y-%m-%d")

1) For last 14 days (excluding the current day) earliest=-14d@d latest=-d@d is used to filter events strictly..
2) Timechart will tend to group the event as -14th day till -8th day and -7th day till -1st day keeping the date as -14th day and -7th day respectively. | eval _time=strftime(_time+7*24*60*60, "%Y-%m-%d") command is just to shift those -14th day and -7th day date to -8th day and yesterday respectively and give in the format you required for table.
3) If you wanna use current day as well, adjust the earliest and latest accordingly as remember if you take earliest as -14d and current as todays day technically they are 15days. Timechart will then end up making three rows. In this case summing up the last two rows will actually be incorrect as the last row will represent data only for the single day which is todays day and consequently the sum will be of 8days and not 7.

If you want to try it, use |head 2 in the end to get rid of that one extra day.
Example:

your base query earliest=-14d@d 
    | timechart count span=7d
    | eval _time=strftime(_time+7*24*60*60, "%Y-%m-%d")
    | head 2

Re: How to edit my timechart search to compare counts over the last two weeks, and show the count trend in a single value visualization?

splunker1981 — Tue, 08 Nov 2016 14:08:33 GMT

Thanks for the reply gokadroid - Tried what you suggested and I still get the same results. Timechart returns data for different week ranges, and the counts spread through each of those.

2016-10-21 301
2016-10-28 699
2016-11-01 1200
2016-11-08 5

Re: How to edit my timechart search to compare counts over the last two weeks, and show the count trend in a single value visualization?

splunker1981 — Tue, 08 Nov 2016 18:33:57 GMT

Update:

So I am looking for something like this, which gives me the breakdown by week. What I am unsure of is; how do I use these counts with timechart or is there another way to get the single value trend without using timechart? Whenever I try to use timechart the counts show up as 0.

|searchHere 
| eval a = relative_time(now(), "-7d@d") 
| eval b = relative_time(now(), "-0d@d") 
| eval filter = case(process_time > a, "Cur Week", process_time < b, "P week") 
| eval _time=strftime(process_time + 7*24*60*60, "%Y-%m-%d")
| stats count by filter

Output

Cur week 1000
P week     1205

Re: How to edit my timechart search to compare counts over the last two weeks, and show the count trend in a single value visualization?

somesoni2 — Tue, 08 Nov 2016 19:29:49 GMT

Give this a try

your base search earliest=-14@d 
| eval _time=if(_time<relative_time(now(),"-7d@d"), relative_time(now(),"-7d@d"),relative_time(now(),"@d")
| timechart span=7d count | reverse | head 2

Re: How to edit my timechart search to compare counts over the last two weeks, and show the count trend in a single value visualization?

splunker1981 — Tue, 08 Nov 2016 20:18:42 GMT

This worked, but I have no clue why....Can you explain or point me to something that explains what you did. Thanks for taking the time to help, much appreciated.

Re: How to edit my timechart search to compare counts over the last two weeks, and show the count trend in a single value visualization?

somesoni2 — Tue, 29 Sep 2020 11:43:08 GMT

Line1:....
Line2: Updating the _time value. For any data that is older than relative_time(now(),"-7d@d") e.g. if today is 2016-11-08 , any data older than 2016-11-01 will have the timestamp as 2016-11-01. Else it will have timestamp of 2016-11-08 ( relative_time(now(),"@d") ).
Line3: regular timechart. Since the time range is effectively about 15 days, there will be 3 rows created but first row will have count of 0. So remove that.

Re: How to edit my timechart search to compare counts over the last two weeks, and show the count trend in a single value visualization?

splunker1981 — Tue, 08 Nov 2016 21:23:30 GMT

Thanks for breaking it down for me.

Re: How to edit my timechart search to compare counts over the last two weeks, and show the count trend in a single value visualization?

gokadroid — Wed, 09 Nov 2016 02:47:21 GMT

There shouldn't be a reason why when the span is 7d, there should be groupings of dates which are not 7 days apart, unless month end is playing a role for the dates 11/01 groupings to show up even though previous grouping of 10/28 is not 7 days apart. Somesoni's answer actually tricks all the time values to get assigned to as one of the two values and should work then.