Splunk Search
Highlighted

Getting count of data by days

New Member

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 Completion
Time=round((CompletedDate-ActualStartDate)/86400,0)
| stats count(Completion
Time<14d) as Lessthan14 by Month

I get my Months, Lessthan14 column but my data is at zero

Tags (2)
0 Karma
Highlighted

Re: Getting count of data by days

Builder

@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 ?

0 Karma
Highlighted

Re: Getting count of data by days

New Member

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 Actual
Start_Date

I hope that is what you are asking and Thanks!

0 Karma
Highlighted

Re: Getting count of data by days

Ultra Champion
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.

0 Karma