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!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

&#x1f342; Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...