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
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.