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,
The community can most efficiently help you if you share details about your search. Please share your SPL if you can.
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,
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
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 *
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?
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 *
This works, I had to add an
eval to set
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!
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.
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?
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
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