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
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")
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")
This worked perfectly for my usecase.. just that i had to reverse the time. Thank you so much for your timely help.. 🙂
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.
Thanks for your response... But how to calculate backlog as per requirement.. Like backlog2=backlog1+closed - open.. Is this doable?
can some one please help