Splunk Dev

how to take previous week value to compute next week backlog

k_harini
Communicator

Hi,
I have a requirement to calculate backlog over weeks
Backlog Current Week (Backlog1) = Current Backlog
Backlog Previous Week (Backlog2) = Backlog1+ (Open - Closed)
Backlog Previous Week (Backlog3) = Backlog2 + (Open - Closed)
current open count - current backlog
index=idx earliest=-23w latest=now | dedup ticket_number | fillnull value="Not Defined" problem_mapping system_user asset_id auto_sub_cat3 work_queue owner_name ticket_source state |itsm_ticketanalysis_to_time(time_submitted,Time,source) |search ticket_type="incident" Project="*" | bin Time span=1w|chart count(eval(State="Opened")) as open_count count(eval(State="Completed")) as closed_count by Time |eval backlog = closed_count - open_count|eval Time=strftime(relative_time(Time,"@w0"),"%d %b %y")

I tried this. but i could not grab previous week values dynamically. How to do this considering previous week? please provide any leads

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index=idx earliest=-23w latest=now | dedup ticket_number | fillnull value="Not Defined" problem_mapping system_user asset_id auto_sub_cat3 work_queue owner_name ticket_source state |`itsm_ticketanalysis_to_time(time_submitted,Time,source)` |search ticket_type="incident" Project="*" | bin Time span=1w|chart count(eval(State="Opened")) as open_count count(eval(State="Completed")) as closed_count by Time |eval backlog = closed_count - open_count
| streamstats current=f window=1 values(backlog) as previous_backlog
| eval backlog=coalesce(previous_backlog,0)+backlog | fields - previous_backlog
|eval Time=strftime(relative_time(Time,"@w0"),"%d %b %y")

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index=idx earliest=-23w latest=now | dedup ticket_number | fillnull value="Not Defined" problem_mapping system_user asset_id auto_sub_cat3 work_queue owner_name ticket_source state |`itsm_ticketanalysis_to_time(time_submitted,Time,source)` |search ticket_type="incident" Project="*" | bin Time span=1w|chart count(eval(State="Opened")) as open_count count(eval(State="Completed")) as closed_count by Time |eval backlog = closed_count - open_count
| streamstats current=f window=1 values(backlog) as previous_backlog
| eval backlog=coalesce(previous_backlog,0)+backlog | fields - previous_backlog
|eval Time=strftime(relative_time(Time,"@w0"),"%d %b %y")
0 Karma

k_harini
Communicator

This worked perfectly for my usecase.. just that i had to reverse the time. Thank you so much for your timely help.. 🙂

0 Karma

alemarzu
Motivator

Hi there @k_harini

Try this please, and see if it suits you.

index=idx earliest=-23w latest=now | dedup ticket_number | fillnull value="Not Defined" problem_mapping system_user asset_id auto_sub_cat3 work_queue owner_name ticket_source state |`itsm_ticketanalysis_to_time(time_submitted,Time,source)` |search ticket_type="incident" Project="*"| eval date_var=strftime(_time,"%Y-%U") | chart count(eval(State="Opened")) as open_count count(eval(State="Completed")) as closed_count by date_var |eval backlog = closed_count - open_count

Hope it helps.

0 Karma

k_harini
Communicator

Thanks for your response... But how to calculate backlog as per requirement.. Like backlog2=backlog1+closed - open.. Is this doable?

0 Karma

k_harini
Communicator

can some one please help

0 Karma
Get Updates on the Splunk Community!

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, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...