I am trying to find a solution for adjust my time interval for time to resolve. There are two indexes being used, the first captures the summary records with CLOSE_TIME and OPEN_TIME, the second captures the supporting activities SUSPENDED and UNSUSPENDED.
The summary events have the following fields - this is the summary index (lets call it index=smmary)
ID
OPEN_TIME
CLOSE_TIME
SUSPEND_COUNT
The activities events have the following fields - this is the activities index (lets call it index=activities)
ID - matches the ID above in the summary table
DATESTAMP - time that activity happened
TYPE - whether it was suspended or unsuspended
I am trying to adjust the summary data for the matching activities in in order to adjust CLOSE_TIME - OPEN_TIME to reflect the actual time that a ticket was open for - using TYPE=SUSPENDED and TYPE=UNSUSPENDED , and pulling their DATESTAMP field to make the adjustment.
Most examples of calculating duration between events dont help here. The tricky part is that for each ID in index=summary, I have to check each index=activities for a mathcing ID, and if it matches I have to handle TYPE=SUSPENDED AND TYPE=UNSUSPENDED differently. I have to take the difference of UNSUSPENDED - SUSPENDED and subtract that amount from the original CLOSE_TIME - OPEN_TIME difference in the index=summary.
The other tricky part is that there can be any number of SUSPENDED and UNSUSPENDED events for a given ID, or there can be none at all.
These last two challenges have been what separates my use case from the others I have found.
Here is the SPL I have written so far:
index=main source=summary
| dedup ID
| join type=left max=0 INCIDENT_ID [search index=main source="activities" TYPE=Suspended TYPE=UNSUSPENDED]
| eval openTimeNum = strptime(OPEN_TIME, "%Y-%m-%d %H:%M:%S")
| eval closeTimeNum = strptime(CLOSE_TIME, "%Y-%m-%d %H:%M:%S")
| eval mttr_unadjusted = (closeTimeNum-openTimeNum)
| eval finalTimeInterval = 0
| foreach DATESTAMP [
eval curTimeEvent = strptime(< >, "%Y-%m-%d %H:%M:%S")
| eval finalTimeInterval = if(isnull(DATESTAMP), mttr_unadjusted, if(index=0, curTimeEvent - closeTimeNum, curTimeEvent - finalTimeInterval))
| eval index = index + 1
]
This doesnt work obviously, I was just playing with it, but this is the base I have so far. I added the index variable as an attempt to monitor how many iterations the foreach loop has used.
I have attempted using transactions, though I am not having success when using the following SPL (to be honest I am not exactly sure what is happening here, and even the transaction command is capable of handling the logic I require):
index=main source=summary OR source=activities TYPE=Suspended OR TYPE=UNSUSPENDED
| transaction ID
| chart count by duration
---> Yields the following results: (The zero value has a count because there are some summary records that do not have any supporting activities)
duration count
0 2563
18 3
19 6
20 3
21 3
23 4
24 2
25 1
28 3
29 2
31 4
32 3
34 1
35 1
36 1
37 2
Any help would be appreciated, as I just need to know at least where to look. So far I still believe that foreach is the best option (but that is just because I am a native javascript developer and am used to handling logic that way)
Thank you for your time and consideration!
... View more