Help with Transforming an ingest of timestamped data into a weekly backlog graph

[Tomten72]

Hi forum!

I have a couple of tricky questions on working with same indata and same type of graphs...

I am currently working on some Jira ticket weekly inflow/outflow/backlog column graphs, based on ticket data ingested nightly into Splunk index, from Confluence Rest API into simple CSV data.

Essential JIRA fields -> CSV fields used:

key (ticket ID), created (datestamp), resolved (datestamp), priority

Each day we ingest ALL JIRA tickets for a project into Splunk index (lets say index "project" keyed with source "jira_bugs". Theoretically I should be able to use the last 24 hour ingest into splunk to drive the graphs based on the dates in the data.

Based on that I create a weekly Jira ticket inflow graph, over the last 6 months of data (last 24 hours data):

index="project" source="jira_bugs" | dedup key | 
eval created_date = strptime(created,"%Y-%m-%d") | 
eval resolved_date = strptime(resolved,"%Y-%m-%d") | 
eval _time = created_date | 
where _time > now()-15811200 | 
timechart span=1w partial=false count AS "TR inflow" BY priority | 
addtotals | 
convert timeformat=""W%y%V"" ctime(_time) AS date | 
sort _time | fields - _time | table date *

So, based on the ticket's created date, I use that as _time, I span the data in timechart into weekly scope and then change the time label to a Week label Wyyww. The output drives a stacked columns (per priority), and an overlay linegraph for totals.

Outflow is dito, but _time is instead driven by resolved_date.

The problem with this approach is that if there is no tickets created for a week, it should (but does not) render an empty space for that week. I am thinking that I may perhaps need to chain this query with a preceeding gentimes (7d increment starting 6 months ago?), and then somehow group the count of tickets into the generated time events?

Secondly, I need to create a weekly Jira ticket backlog graph, and this feels even more tricky. For this, I need to count the number of tickets per week that fits within a certain time range, meaning I need to count a ticket for each week if it was open (evaluating if [created_date] < week or certain day] < [resolved_date]).

So same ticket shall be counted (duplicated with different _time stamps?) over several weeks (columns in graph) for which it was open.

Seems like a simple thing, but each time I attack this problem I give up after googling and testing a number of ideas from Splunk! reference and forum.