topic Getting count of data by days in Splunk Search

Getting count of data by days

tmanuel1 — Wed, 30 Sep 2020 04:48:02 GMT

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

Re: Getting count of data by days

anmolpatel — Wed, 30 Sep 2020 04:51:00 GMT

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

Re: Getting count of data by days

tmanuel1 — Wed, 30 Sep 2020 04:48:19 GMT

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!

Re: Getting count of data by days

to4kawa — Wed, 30 Sep 2020 04:51:30 GMT

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.