topic Re: How can I remove the 0 values for time > now in a timechart that compares today to last week? in Splunk Search

How can I remove the 0 values for time > now in a timechart that compares today to last week?

dlovett — Wed, 10 Oct 2012 20:35:26 GMT

I have a timechart that plots today's and last week's values using the following search:

sourcetype="Remedy_Tickets" Type="TKT" earliest=-0d@d latest=now | eval ReportKey="today" | append [search sourcetype="Remedy_Tickets" earliest=-7d@d latest=-6d@d | eval ReportKey="Last Week" | eval new_time=_time+(60*60*24*7)] | eval _time=if(isnotnull(new_time), new_time, _time) | timechart span=1h count by ReportKey

The timechart superimposes the values correctly; However, the values for today's time period > now() are 0 (which was 3:30pm in this example). See table below:

                   _time     Last Week today
1   10/10/12 12:00:00.000 AM    6       4
2   10/10/12 1:00:00.000 AM     1       1
3   10/10/12 2:00:00.000 AM     1       0
4   10/10/12 3:00:00.000 AM     1       2
5   10/10/12 4:00:00.000 AM     0       1
6   10/10/12 5:00:00.000 AM     7       4
7   10/10/12 6:00:00.000 AM     10      11
8   10/10/12 7:00:00.000 AM     52      43
9   10/10/12 8:00:00.000 AM     97      67
10  10/10/12 9:00:00.000 AM     112     86
11  10/10/12 10:00:00.000 AM    110     87
12  10/10/12 11:00:00.000 AM    126     69
13  10/10/12 12:00:00.000 PM    105     56
14  10/10/12 1:00:00.000 PM     83      57
15  10/10/12 2:00:00.000 PM     122     67
16  10/10/12 3:00:00.000 PM     73      51
17  10/10/12 4:00:00.000 PM     56      0
18  10/10/12 5:00:00.000 PM     23      0
19  10/10/12 6:00:00.000 PM     11      0
20  10/10/12 7:00:00.000 PM     2       0
21  10/10/12 8:00:00.000 PM     10      0
22  10/10/12 9:00:00.000 PM     4       0
23  10/10/12 10:00:00.000 PM    7       0
24  10/10/12 11:00:00.000 PM    5       0

I would like the line for today to end at now() just like the timechart in the Splunk Deployment Monitor Application. Instead, my chart plots today at zero after now().

NOTE: I would include images as a visual aid but I don't have enough karma yet 😉

Any help would be appreciated.

Re: How can I remove the 0 values for time > now in a timechart that compares today to last week?

dlovett — Wed, 10 Oct 2012 20:37:32 GMT

This query produces the same result:

sourcetype=Remedy_Tickets Type=TKT earliest=-7d@d latest=-6d@d | eval marker = "Last Week" | eval _time=_time+(60*60*24*7) | append [search sourcetype=Remedy_Tickets Type=TKT earliest=-0d@d latest=now | eval marker = "Today"] | timechart fixedrange=f span=30m count by marker

Re: How can I remove the 0 values for time > now in a timechart that compares today to last week?

lguinn2 — Thu, 11 Oct 2012 06:33:16 GMT

You could do this (I just added a where command on the next-to-last line):

sourcetype="Remedy_Tickets" Type="TKT" earliest=-0d@d latest=now 
| eval ReportKey="today" 
| append [search sourcetype="Remedy_Tickets" earliest=-7d@d latest=-6d@d 
       | eval ReportKey="Last Week" 
       | eval new_time=_time+(60*60*24*7)] 
| eval _time=if(isnotnull(new_time), new_time, _time) 
| where _time <= now()
| timechart span=1h count by ReportKey

now() is the time that the search started. So this would keep only events that occurred before "now."

Re: How can I remove the 0 values for time > now in a timechart that compares today to last week?

echalex — Thu, 11 Oct 2012 08:13:58 GMT

One option is to snap the subsearch to the hour, rather than day:

latest=-167h@h

For me at least, -7d@h doesn't work, as it leaves out the last hour, that's why I specify -(6*24+23) = -167

Re: How can I remove the 0 values for time > now in a timechart that compares today to last week?

dlovett — Thu, 11 Oct 2012 12:49:48 GMT

That did help. But that cuts off last week's timeline at now and mgt wants to see the full timeline of last week superimposed over today's timeline. Many tx though! you've given me some ideas to experiment with 🙂

Re: How can I remove the 0 values for time > now in a timechart that compares today to last week?

dlovett — Thu, 11 Oct 2012 12:50:54 GMT

tx for the feedback. this helped too. but again it cuts off last weeks timeline at now() and mgt wants to see the full timeline of last week superimposed over today's timeline.

Re: How can I remove the 0 values for time > now in a timechart that compares today to last week?

echalex — Thu, 11 Oct 2012 12:59:27 GMT

Aha, so I misinterpreted your needs, apparently?
You want the full day from last week, but you don't want the zeros for today to show up, because they look bad to management and management doesn't always understand that you can't see tickets from the future?

Re: How can I remove the 0 values for time > now in a timechart that compares today to last week?

dlovett — Thu, 11 Oct 2012 17:33:33 GMT

The problem is they see what they want in other dashboards. ie The Splunk Deployment Monitor 🙂

One thing I did notice is that application (Deployment Monitor) uses summary indexes. Dunno if that makes a difference or not. But I can experiment.

Re: How can I remove the 0 values for time > now in a timechart that compares today to last week?

lguinn2 — Thu, 11 Oct 2012 18:15:53 GMT

Next attempt an at answer! Make the zeros into nulls and then use the "Missing Values: Omit" formatting option on the chart to skip them.

sourcetype="Remedy_Tickets" Type="TKT" earliest=-0d@d latest=now 
| eval ReportKey="today" 
| append [search sourcetype="Remedy_Tickets" earliest=-7d@d latest=-6d@d 
       | eval ReportKey="Last Week" 
       | eval new_time=_time+(60*60*24*7)] 
| eval _time=if(isnotnull(new_time), new_time, _time) 
| timechart span=1h count by ReportKey
| eval today = if( _time <= now(),today,null())

The "today" portion of the graph should end at the time that the search started.

Re: How can I remove the 0 values for time > now in a timechart that compares today to last week?

dlovett — Thu, 11 Oct 2012 19:28:23 GMT

it worked! Many many tx!