Developing for Splunk Enterprise

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
Revered Legend

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
Revered Legend

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