Splunk Search

Add additional fields to the end of timechart

dojiepreji
Path Finder

Hello,

I have a bar chart that looks like this:

alt text

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:
alt text

Earliest and latest is derived from a timepicker.

How can I move backlog to last entry in my timechart?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

arjunpkishore5
Motivator

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!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

to4kawa
Ultra Champion
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.

0 Karma

rmmiller
Contributor

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

0 Karma

dojiepreji
Path Finder

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

0 Karma

rmmiller
Contributor

I made an update to my original answer.

0 Karma

dojiepreji
Path Finder

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.

0 Karma

rmmiller
Contributor

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?

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...