Hi guys! I am looking to get the number of tickets that are completed in under 14 days, 30 days, 45 days and 45+ days by month. I am gauging this from when the ticket is assigned to completion. Below is what I have so far but I am not sure how to actually get there. I get columns but getting a '0' as my total. I only have been testing with Less than 14 days. Status=5 = Complete
index=remedy source=srmwo ASGRP="Packaging" Status="5"
| dedup Work_Order_ID
| eval Month=strftime(Completed_Date,"%Y-%m (%b)")
| eval Completion_Time=round((Completed_Date-Actual_Start_Date)/86400,0)
| stats count(Completion_Time<14d) as Less_than_14 by Month
I get my Months, Less_than_14 column but my data is at zero
index=remedy source=srmwo ASGRP="Packaging" (Status=5 OR Status=4)
| eval date=coalesce(Completed_Date,Actual_Start_Date)
| table date Work_Order_ID
| bin span=1d date
| reverse
| streamstats values(date) as range by Work_Order_ID
| reverse
| eval start=mvindex(range,0), end=mvindex(range,1)
| streamstats current=f values(start) as start_p1 by Work_Order_ID
| eval end=if(start=start_p1,null(),end)
| eval timerange=if(isnull(end),start,mvrange(start,end,86400))
| fields Work_Order_ID timerange
| mvexpand timerange
| rename timerange as _time
| table _time Work_Order_ID
This query makes daily log by each Work_Order_ID.
After this, let's aggregate what you want.
@tmanuel1 can you post a desensitized an example of the log for a Work_Order_ID
index=remedy source=srmwo ASGRP="Packaging" Work_Order_ID = "INSERT VAL"
Key information needed need is:
- Does each entry contain the first entry date ?
- What defines it as completed ?
A Work_Order_ID is identified a number such as WO0000003336389
I dedup the Work_Order_ID because there tends to be some duplicates if I don't
A completed Work Order has the Status of 5 and there is a Completed_Date
A assigned Work Order has a Status of 4 and there is a Actual_Start_Date
I hope that is what you are asking and Thanks!