Can I use foreach to measure the time taken between creating a ticket and a specific action/update?

[jackreeves]

Hi,

I am looking at IT ticket logging data & trying to create compliance measures between different updates being added to the ticket.
Each event equals one update for a specific ticket (i.e. one ticket could have ten updates). I want to then measure the time taken between a ticket being raised & a specific action/update.

Therefore if there have been six updates there will be six events with the same TICKET_ID (see below)

EXAMPLE

TICKET_ID UPDATE_TIME UPDATE_TYPE

0001 06/12/2017 12:01 A

0001 06/12/2017 12:10 B
0001 06/12/2017 12:10 C
0001 06/12/2017 12:10 D
0001 06/12/2017 12:10 E

Some of the measures I want:
Time between Update A and B
Time between Update B and C
Time between Update A and D

Any idea how I could go about this? Is the foreach function correct way to go?

[somesoni2 · Accepted solution]

Give this a try

your current search giving fields TICKET_ID UPDATE_TIME UPDATE_TYPE
| eval UPDATE_TIME=strptime(UPDATE_TIME,"%m/%d/%Y %H:%M")
| chart values(UPDATE_TIME) over TICKET_ID by UPDATE_TYPE

This should give a column for each of the UPDATE_TYPE with value as epoch timestamp when that update happened. Now you can just add eval command to calculate difference between two UPDATE_TYPE, e.g. eval DiffAB='A'-'B'

[DalJeanis]

Nope. foreach is used within a single event, and you are dealing with multiple events.

You use streamstats to copy the prior event time onto the next event so you can calculate the difference in time.

You can also use eventstats to calculate things across the entire grouping. For instance, you could calculate the time of A across the entire grouping, then subtract it from D to find the lag between the two. In this case, you can get the time of A with streamstats as well, so eventstats wasn't needed.

In this case, something like this might work...

your search
| sort 0 _time
| streamstats current=f last(_time) as priorTime first(_time) as firstTime by TICKET_ID 
| eval firstTime=coalesce(firstTime,_time)
| eval stepLag = _time - coalesce(priorTime,_time)
| eval totalLag = _time - firstTime 
| eventstats max(totalLag) as totalDuration count as mycount by TICKET_ID 
| eval avgLag = if(mycount<=1,0, round(TotalDuration/(mycount-1),0))

[somesoni2]

Is the values for column UPDATE_TYPE fixed/static?

[jackreeves]

Yes - there only roughly six UPDATE_TYPE'S I want to track

[elliotproebstel]

What is the desired output? A table containing just the results of those calculations? Or for source events to have new appended fields? The approach will vary based on your desired output.

[jackreeves]

So desired state would be a table showing whether the different measures were a success. Firstly the KPI criteria is below:

Time between Update A and B - KPI 1 (<=20mins = Y, >20mins=N)
Time between Update B and C - KPI 2 (<=10mins=Y, >10mins=N)
Time between Update A and D - KPI 3 (<=40mins=Y, >40mins=N)

Therefore final output:

TICKET_ID KPI 1 KPI2 KPI3
0001 Y N N
0002 Y Y Y
0003 N N N