Splunk Search

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


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.


Labels (3)
0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...