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

[andrewtrobec]

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

[rjthibod · Accepted solution]

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 *

[andrewtrobec]

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

[rjthibod]

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

[andrewtrobec]

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

[rjthibod]

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 *

[andrewtrobec]

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

[rjthibod]

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.

[rjthibod]

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

[andrewtrobec]

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