How to plot backlog data on timechart?

[SanjayReddy]

Hi All,

I need help on plotting backlog data on timechart

We have set of tickets in backlog on specific dates with workgroups, wanted to show them in Timechart 

Below is the situation 

example ticket123 is backlog on 1st Oct with group A

and same ticket123 moved to group B on 03rd Oct and with them till 05th Oct. 

at last ticket moved Group C on 06th

Now below is the table that shows in Splunk. 

Date        Ticket     Workgroup  status
01-Oct     123             A                 Pending
03-Oct     123             B                 Pending
06-Oct     123            C                  Pending

 
from above table  if we do timechart its shows ticket123 in backlog on 01st , 03rd and 06th 

however ticket 
ticket123,   in backlog on 01st and 02nd in group A
ticket123,   in backlog on 03rd,04th and 05th in group B
ticket123,   in backlog on 06th in group B

how to get dates in  02nd,04th,05th in Table so that we can show on the timechart that the ticket in the backlog has specific dates.  

[alinabo12]

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

[richgalloway]

To fill in the gaps in dates, use the makecontinuous command.

...
```Convert Date to integer for makecontinuous```
| eval date=strptime(Date, "%d-%b")
```Fill in missing days```
| makecontinuous span=1d date
```Convert new dates to desired format```
| eval Date=strftime(date, "%d-%b")
```Fill in remaining fields```
| filldown | fields - date

[SanjayReddy]

Hi  richgalloway  and  ITWhisperer , 

Many thanks for your reply 

this is working fine one ticket,
However If I check for Multiple tickets , dates are not sorted properly 

ex: 
Ticket A and Ticket B
Date        Ticket     Workgroup  status
01-Oct     123             A                 Pending
03-Oct     123             B                 Pending
06-Oct     123            C                  Pending

Date        Ticket     Workgroup  status
04-Oct     456             A                 Pending
07-Oct    456             B                 Pending
08-Oct    456             C                  Pending

Current output 

Date        Ticket     Workgroup  status
01-Oct     123             A                 Pending
02-Oct     123             A                 Pending
03-Oct     123             B                 Pending
04-Oct     456             A                 Pending
05-Oct     456             A                 Pending
06-Oct     456            C                  Pending
06-Oct     123            C                  Pending
07-Oct    456             B                 Pending
08-Oct    456             C                  Pending

expected  output  with group  events for each tickets at one place

expected  output

Date        Ticket     Workgroup  status
01-Oct     123             A                 Pending
02-Oct     123             A                 Pending
03-Oct     123             B                 Pending
04-Oct     123            C                  Pending
05-Oct     123            C                  Pending
06-Oct     123            C                  Pending

04-Oct     456             A                 Pending
05-Oct     456             A                 Pending
06-Oct     456             A                 Pending
07-Oct     456             C                  Pending
08-Oct    456             C                  Pending

[ITWhisperer]

| sort 0 Ticket _time

[ITWhisperer]

Assuming you are not worried about status, and your Date field is actually _time (if it isn't then set _time to be the epoch datetime version of this field), you could try something like this:

| timechart span=1d values(Workgroup) by Ticket
| makecontinuous _time span=1d
| filldown *