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 WorkOrderID
| eval Month=strftime(CompletedDate,"%Y-%m (%b)")
| eval CompletionTime=round((CompletedDate-ActualStartDate)/86400,0)
| stats count(CompletionTime<14d) as Lessthan14 by Month
I get my Months, Lessthan14 column but my data is at zero
@tmanuel1 can you post a desensitized an example of the log for a WorkOrderID
index=remedy source=srmwo ASGRP="Packaging" WorkOrderID = "INSERT VAL"
Key information needed need is:
- Does each entry contain the first entry date ?
- What defines it as completed ?
A WorkOrderID is identified a number such as WO0000003336389
I dedup the WorkOrderID because there tends to be some duplicates if I don't
A completed Work Order has the Status of 5 and there is a CompletedDate
A assigned Work Order has a Status of 4 and there is a ActualStart_Date
I hope that is what you are asking and Thanks!
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 WorkOrderID.
After this, let's aggregate what you want.