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
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 *
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 *
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
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
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
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 _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
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.
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,
Andrew