How to calculate backlog over weeks in Splunk IT Service Intelligence?

[deepankshi09]

Hi,
I have a requirement to calculate backlog over weeks
Backlog current Week (Backlog) = Backlog1+ (Open - Closed) -->(open & closed in that latest same week)
Backlog Previous Week (Backlog1) = Backlog2 + (Open - Closed) -->(open & closed in that same week)
Backlog Previous Week (Backlog2) = closed - open -->(open & closed in that same week)

i am trying this -

index="aiam_itsm_ticket_walgreensbootsalliancein1" ticket_number =INC* | dedup ticket_number  |eval time_submitted1=strptime(time_submitted,"%d/%m/%y %H:%M:%S") |eval time_submitted2=strftime(time_submitted1, "%Y-%m-%d")  | bin span=1d time_submitted1| eval time_submitted3=relative_time(time_submitted1, "+6d@w5")|stats count as Logged by time_submitted3 |append[search index="aiam_itsm_ticket_walgreensbootsalliancein1" ticket_number =INC* current_ticket_state="Resolved" | dedup ticket_number  |eval time_submitted1=strptime(last_resolved_date,"%d/%m/%y %H:%M:%S") |eval time_submitted2=strftime(time_submitted1, "%Y-%m-%d")  | bin span=1d time_submitted1| eval time_submitted3=relative_time(time_submitted1, "+6d@w5")|stats count as Resolved by time_submitted3 ]|append[search index="aiam_itsm_ticket_walgreensbootsalliancein1" ticket_number =INC* current_ticket_state="Closed" | dedup ticket_number  |eval time_submitted1=strptime(last_resolved_date,"%d/%m/%y %H:%M:%S") |eval time_submitted2=strftime(time_submitted1, "%Y-%m-%d")  | bin span=1d time_submitted1| eval time_submitted3=relative_time(time_submitted1, "+6d@w5")|stats count as Closed by time_submitted3 ]|stats first(*) as * by time_submitted3|fillnull value=0 |eval closed=Closed+Resolved |eval backlog =closed-Logged |eval time_submitted4=strftime(time_submitted3, "%F")|eval Date=strftime(time_submitted3, "%F")|reverse | streamstats current=f window=1 values(backlog) as previous_backlog|eval backlog=coalesce(previous_backlog,0)+backlog |fields - previous_backlog

Please help me on this

[yannK]

It will never fly on a KPI search, because of the timerange restrictions.

You want to move the search to a regular scheduled search, and maybe store the values of previous weeks in a lookup to make the calculation faster. (or summarize)