I have a bar chart that looks like this:
What I want to do is move the "Backlog" field to the end of the bar chart (chart overlay). In this case, I want it to appear on Thu Oct 31.
Here is the search for my chart:
Earliest and latest is derived from a timepicker.
How can I move backlog to last entry in my timechart?
Ah, I see what's happening. I was able to mock up your data using some of my own. Append is what we should be using here instead of appendcols. I was able to get the backlog sum at the end if the time series.
Replace your entire appendcols subsearch with this: updated from original post
| append [ search index=*_internal* | where ticket_status!="Resolved" AND ticket_status!="Closed" AND ticket_status!="Cancelled" | dedup ticket_name | addinfo | stats latest(info_max_time) AS _time, count(ticket_name) as backlog]
If this works, I highly recommend your next step be to optimize the other parts of your search based on the way @to4kawa suggested above (minus the backlog part).
Let's tackle this one step at a time, though, and get your backlog sum at the end first before moving on to improving your search.
Hope that helps!
What it did was simply append the backlog to the end of the table. I want it to appear beside the last entry in the timechart.
Time Total Inflow Total Cancelled Total Outflow Backlog
2019-09 10 10 9
2019-10 11 1 1
2019-11 1 3 4 19
This way, the backlog will appear to the very far right on my bar chart.
I'm also thinking of rewriting the search but I think my current search now is more readable than what @to4kawa did. I think I'm willing to sacrifice a bit of performance just to make it more readable for me and others in the future.
Hmm....I'm not sure I understand what you're after, then.
Your original post asked that the backlog appear on 10/31, which was one day beyond your last data point and the end of your time range. That's what this latest answer provides, too.
Are you saying you want backlog to appear immediately next to your latest non-backlog result, regardless of where it occurs?
For example, if your search in the comment was from the beginning of September through the end of November, and you didn't have any non-backlog results beyond October, you would want the backlog to appear on 10/31?
index=_internal ticket_status=* |timechart span=1d count(eval(ticket_status!="Cancelled")) as ticket_inflow ,count(eval(ticket_status=="Cancelled")) as ticket_cancelled ,count(eval(if(resolved_date >= $time_picker.earliest$ AND resolved_date <= $time_picker.latest$ AND (ticket_status=="Resolved" OR ticket_status=="Closed"),ticket_name,NULL))) as ticket_resolved ,count(eval(ticket_status!="Resolved" AND ticket_status!="Closed" AND ticket_status!="Cancelled")) as backlog |rename ticket_inflow as "Total Inflow", ticket_cancelled as "Total Cancelled" , ticket_resolved as "Total Outflow", backlog as "Backlog"
Hi, It was a dashboard, so I didn't put earliest etc. at first.
Try something like this (keeping your current version of query)
Your query for ticket_inflow | join type=left _time [Your query for tickets_cancelled] | join type=left _time [Your query for tickets_resolved] | reverse | appendcols [ your query for backlog] | reverse
I have modified your search to make it more efficient.
index=_internal earliest=1522540800 latest=1572502991 | eval ticket_inflow=case(_time>1569888000 and ticket_status!="Cancelled", ticket_name) | eval ticket_cancelled=case(_time>1569888000 and ticket_status=="Cancelled", ticket_name) | eval ticket_resolved=case(resolved_date>1569888000 and ticket_status=="Resolved" or ticket_status=="Closed", ticket_name) | eval _time= if(isnotnull(ticket_resolved), resolved_date, _time) | eval backlog=case(_time>1569888000 and ticket_status!="Cancelled" and ticket_status!="Resolved" and ticket_status!="Closed", ticket_name) | eventstats dc(backlog) as backlog | timechart span=1d fixedrange=false dc(ticket_*) as ticket_*, max(backlog) as backlog | eventstats max(_time) as last_time | eval backlog=case(_time==last_time, backlog) | fields - last_time
Hope this helps. Please mark as answer if this is what you were looking for.