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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...