Need help with custom time report

[coldwolf7]

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

[richgalloway · Accepted solution]

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