topic Re: Why does my chart lose interactivity with a search using "appendcols"? in Splunk Search

Why does my chart lose interactivity with a search using "appendcols"?

andrewtrobec — Mon, 27 Feb 2017 15:36:47 GMT

Hello All,

Currently using Splunk 6.5.1.

As the question implies, I have a search that uses the appendcols command to add an additional column to a timechart making sure to use the same interval so it aligns correctly. I've added this new column as an overlay to the chart, but keeping it on the same axis.

I've noticed that the interactive nature of the chart is lost so I cannot highlight any values. The only way to solve it is to remove the appendcols command. Is this a known issue, or is there a way to fix it?

Thank you and best regards,

Andrew

Re: Why does my chart lose interactivity with a search using "appendcols"?

rjthibod — Mon, 27 Feb 2017 16:28:16 GMT

The community can most efficiently help you if you share details about your search. Please share your SPL if you can.

Re: Why does my chart lose interactivity with a search using "appendcols"?

andrewtrobec — Mon, 27 Feb 2017 18:40:30 GMT

Thanks for the response!

Here is the SPL:

index=my_index
| eval _time = strptime(Closed, "%Y-%m-%d %H:%M:%S")
| timechart span=1w count by Category
| appendcols [ search index=my_index
 | timechart span=1w count as inbound ]

This will generate a table that looks like this:

_time,cat_a,cat_b,cat_c,cat_d,inbound
2016-08-04,0,3,1,2,1
2016-08-11,0,3,8,0,0
2016-08-18,0,22,10,12,36
2016-08-25,0,15,42,7,70
2016-09-01,0,39,56,12,137
2016-09-08,0,29,61,5,98
2016-09-15,0,29,65,7,242
2016-09-22,0,22,24,2,219
2016-09-29,0,16,13,2,228
2016-10-06,0,16,4,4,231
2016-10-13,0,6,6,2,256
2016-10-20,0,5,17,3,211

When formatting the chart, the first thing I notice is that when I try to make Inbound as an overlay, the list of fields does not prepopulate as it usually does (I have to type it all in manually):

The final result is the following, but it is not interactive:

I hope this helps to clarify the situation!

Thank you and best regards,

Andrew

Re: Why does my chart lose interactivity with a search using "appendcols"?

rjthibod — Mon, 27 Feb 2017 18:57:55 GMT

I can't explain why there is an issue, but I think I can help you another way.

Try this instead. Improves performance by using tstats and removes the ambiguity of using appendcols.

 index=my_index
| eval _time = strptime(Closed, "%Y-%m-%d %H:%M:%S")
| chart limit=0 count over _time span=1w by Category
| append [ tstats count as inbound where index=my_index by _time span=1w]
| timechart span=1w latest(*) as *

Re: Why does my chart lose interactivity with a search using "appendcols"?

andrewtrobec — Tue, 28 Feb 2017 06:41:48 GMT

Hello and thanks for the suggestion.

In my case I am tracking items that have an open date and a closed date, with _time set to the open date. What I want to do is show how many items are closed per week while also showing how many items are opened. The appendcols command seems ideal, and I can avoid calling an external script to process the data. Unless there is another way?

Best regards,

Andrew

Re: Why does my chart lose interactivity with a search using "appendcols"?

rjthibod — Tue, 28 Feb 2017 11:43:21 GMT

OK understood. Going back to your original, try this.

index=my_index
| chart limit=0 count over _time span=1w by Category
| append [ tstats count as inbound where index=my_index by _time span=1w]
| timechart span=1w latest(*) as *

Re: Why does my chart lose interactivity with a search using "appendcols"?

andrewtrobec — Tue, 28 Feb 2017 12:32:34 GMT

This works, I had to add an eval to set _time to Closed, but the structure is perfect.

In the meantime I found another way of doing it:

 index=my_index
| eval _time = strptime(Closed, "%Y-%m-%d %H:%M:%S")
| timechart span=1w count by Category
| join type=left _time [ search index=my_index
 | timechart span=1w count as inbound ]

(all I did was replace appendcols with a join type=left _time)

In my solution the join type means that the leftmost search will determine which values to show, whereas yours will show instances of the rightmost search where there is no match with the leftmost search.

Thank you very much!

Regards,

Andrew

Re: Why does my chart lose interactivity with a search using "appendcols"?

rjthibod — Tue, 28 Feb 2017 13:22:01 GMT

I am sorry for removing the eval from my solution. That was an error on my part.

In general, you should avoid using join as much as possible. There are numerous entries in Splunk answers and a Splunk .conf 2016 talk about why. I will update my original answer with my latest one. You should accept that answer as the final solution.

Re: Why does my chart lose interactivity with a search using "appendcols"?

andrewtrobec — Tue, 28 Feb 2017 14:16:15 GMT

Thank you so much, this works perfectly! I have one additional query:

I see that tstats is fast for index-time fields and works in this particular case. What is the best approach, though, if the appended search has to apply filters and perform more advanced operations? Here is a similar scenario:

index=my_index
| search Category="A")
| timechart span=1d count as EnterCount
| join type=left _time [ search index=my_index
 | search (Category="A")
 | eval _time = strptime(Exit, "%Y-%m-%d %H:%M:%S")
 | timechart span=1d count as ExitCount ]
| fillnull value=0

How would the join be replaced with append in this case?

Best regards,

Andrew

Re: Why does my chart lose interactivity with a search using "appendcols"?

rjthibod — Tue, 28 Feb 2017 15:38:37 GMT

Here is an improved version.

Note, your filter of Category="A" should be moved to the first search step where it will be much, much faster.

You can almost always replace join _time type=left with append followed by stats, chart, or timechart that will transform the data on the _time dimension. In my example, I don't merge the columns until the last command.

index=my_index Category="A"
| chart count as EnterCount over _time span=1d
| append 
  [ 
    search index=my_index Category="A"
    | eval _time = strptime(Exit, "%Y-%m-%d %H:%M:%S")
    | chart count as ExitCount over _time span=1d ]
  ]
| timechart span=1d latest(*) as *
| fillnull value=0