topic Re: Add additional fields to the end of timechart in Splunk Search

Add additional fields to the end of timechart

dojiepreji — Thu, 31 Oct 2019 06:33:40 GMT

Hello,

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?

Re: Add additional fields to the end of timechart

rmmiller — Fri, 01 Nov 2019 14:53:17 GMT

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!
rmmiller

Re: Add additional fields to the end of timechart

to4kawa — Fri, 01 Nov 2019 16:52:50 GMT

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.

Re: Add additional fields to the end of timechart

dojiepreji — Mon, 04 Nov 2019 09:36:06 GMT

This didn't work. Backlog is still appended in the beginning of the timechart.

Re: Add additional fields to the end of timechart

rmmiller — Mon, 04 Nov 2019 20:01:44 GMT

I made an update to my original answer.

Re: Add additional fields to the end of timechart

dojiepreji — Tue, 05 Nov 2019 11:37:36 GMT

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.

Re: Add additional fields to the end of timechart

rmmiller — Tue, 05 Nov 2019 14:49:56 GMT

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?

Re: Add additional fields to the end of timechart

somesoni2 — Wed, 06 Nov 2019 19:29:46 GMT

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

Re: Add additional fields to the end of timechart

arjunpkishore5 — Thu, 07 Nov 2019 01:18:24 GMT

I have modified your search to make it more efficient.

Try this.

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.

Cheers!