Splunk Enterprise

Need help with custom time report

coldwolf7
Explorer

Hello,

So I am trying to build a report that alerts us when a support ticket is about to hit 24hrs,

The filed we are using is custom time field called REPORTED_DATE and it displays the time in the way 

2023-09-11 08:44:03.0

I need a report That tells us when tickets are within 12hrs or less of crossing the 24 hour mark. 

 

This is our code so far 

 

((index="wss_desktop_os") (sourcetype="support_remedy")) earliest=-1d@d

| search ASSIGNED_GROUP="DESKTOP_SUPPORT" AND STATUS_TXT IN ("ASSIGNED", "IN PROGRESS", "PENDING")

| eval TEST = REPORTED_DATE

| eval REPORTED_DATE2=strptime(TEST, "%Y-%m-%d")

| eval MTTRSET = round((now() - REPORTED_DATE2) /3600)

```| eval MTTR = strptime(MTTRSET, "%Hh, %M")```

| dedup ENTRY_ID

| stats LAST(REPORTED_DATE) AS Reported, values(ASSIGNEE) AS Assignee, values(STATUS_TXT) as Status,values(MTTRSET) as MTTR by ENTRY_ID

 

Any help would be appreciated. I will admit I struggle with time calucations

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Perhaps this will help.

((index="wss_desktop_os") (sourcetype="support_remedy")) ASSIGNED_GROUP="DESKTOP_SUPPORT" STATUS_TXT IN ("ASSIGNED", "IN PROGRESS", "PENDING") earliest=-1d@d
``` Convert REPORTED_DATE to epoch form ```
| eval REPORTED_DATE2=strptime(REPORTED_DATE, "%Y-%m-%d %H:%M:%S")
``` Keep events reported more than 12 hours ago so are due in < 12 hours ```
| where REPORTED_DATE2 <= relative_time(now(), "-12h")
| eval MTTRSET = round((now()-REPORTED_DATE2)/3600)
| dedup ENTRY_ID
| stats LAST(REPORTED_DATE) AS Reported, values(ASSIGNEE) AS Assignee, values(STATUS_TXT) as Status,values(MTTRSET) as MTTR by ENTRY_ID
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Perhaps this will help.

((index="wss_desktop_os") (sourcetype="support_remedy")) ASSIGNED_GROUP="DESKTOP_SUPPORT" STATUS_TXT IN ("ASSIGNED", "IN PROGRESS", "PENDING") earliest=-1d@d
``` Convert REPORTED_DATE to epoch form ```
| eval REPORTED_DATE2=strptime(REPORTED_DATE, "%Y-%m-%d %H:%M:%S")
``` Keep events reported more than 12 hours ago so are due in < 12 hours ```
| where REPORTED_DATE2 <= relative_time(now(), "-12h")
| eval MTTRSET = round((now()-REPORTED_DATE2)/3600)
| dedup ENTRY_ID
| stats LAST(REPORTED_DATE) AS Reported, values(ASSIGNEE) AS Assignee, values(STATUS_TXT) as Status,values(MTTRSET) as MTTR by ENTRY_ID
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Index This | I’m short for "configuration file.” What am I?

May 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with a Special ...

New Articles from Academic Learning Partners, Help Expand Lantern’s Use Case Library, ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Your Guide to SPL2 at .conf24!

So, you’re headed to .conf24? You’re in for a good time. Las Vegas weather is just *chef’s kiss* beautiful in ...