Splunk Search

Getting count of data by days

tmanuel1
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 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

Tags (2)
0 Karma

to4kawa
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 Work_Order_ID.
After this, let's aggregate what you want.

0 Karma

anmolpatel
Builder

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

0 Karma

tmanuel1
New Member

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!

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...