help with sum time between event from other sourcetypes

[nisim651]

Hi,

I have some events that can be transfered from one crew to anothe, and their status also can be changed.

I have 3 sourcetypes (1 sourcetype (ALL_EVENTS) is dump and the othe two are tails)
The first sourcetype contains the events, and the other 2 contain the history of the events (1 contains all the history about changes of statuses and the other contains

the changes / transfers of the event from one crew to another)

I want to sum the overall time (duration) the crew "IT-1" handled the overall events which their last status is not CANCEL, i.e the overall time takes the crew ("IT-1") to handle the events.

---

It looks like this:

sourcetype: ALL_EVENTS

event_id        CURRENT_CREW        CURRENT_STATUS
----------      -------------       -----------------
1111            IT-1                CLOSE
2222            IT-1                CANCEL
3333            IT-1                CLOSE
4444            IT-2                IN_PROGRESS

sourcetype: STstatus

event_id        Update_date         old_status      new_status      handling_crew
----------      --------------      -----------     -----------     --------------
1111            1/1/14 07:59:00     NULL            OPEN            NULL
2222            1/1/14 08:05:00     NULL            OPEN            NULL
1111            1/1/14 08:05:00     OPEN            IN_PROGRESS     IT-1
3333            1/1/14 08:09:00     NULL            OPEN            NULL
4444            1/1/14 08:11:00     NULL            OPEN            NULL
1111            1/1/14 08:20:00     IN_PROGRESS     CANCEL          IT-1
1111            1/1/14 08:23:00     CANCEL          REOPEN          IT-1
1111            1/1/14 08:25:00     REOPEN          CLOSE           IT-1
3333            1/1/14 08:26:00     OPEN            IN_PROGRESS     IT-1
3333            1/1/14 08:27:00     IN_PROGRESS     CLOSE           IT-1
2222            1/1/14 08:30:00     OPEN            CANCEL          IT-1
4444            1/1/14 08:31:00     OPEN            IN_PROGRESS     IT-2

sourcetype: STcrew

event_id        Update_date     old_crew        new_crew
-------     ----------------    ----------      ------------    
1111        1/1/14 08:00:00     NULL            IT-1
2222        1/1/14 08:05:05     NULL            HELP_DESK-1
3333        1/1/14 08:10:00     NULL            IT-1
4444        1/1/14 08:11:05     NULL            IT-2
1111        1/1/14 08:15:00     IT-1            IT-2
2222        1/1/14 08:16:00     HELP_DESK-1     IT-1        
1111        1/1/14 08:19:00     IT-2            IT-1

---

I want to sum the overall time the crew "IT-1" handled the overall events which their last status is not CANCEL
it needs to calculate the time like this:
1/1/14 08:00:00 - 1/1/14 08:15:00 +
1/1/14 08:19:00 - 1/1/14 08:20:00 +
1/1/14 08:23:00 - 1/1/14 08:25:00 +
1/1/14 08:10:00 - 1/1/14 08:27:00 = 15 + 1 + 2 + 17 = 35

and the result I will see is (in mintues): 35

the schema for the calc is:

1. don't calc the event which last status is CANCEL
2. start sum all the events where (new_crew = "IT-1" AND current_status is not CLOSE / CANCEL) OR (new_status = OPEN / REOPEN)
3. sum until (old_crew = "IT-1") OR (new_status is CLOSE / CANCEL AND handling_crew = "IT-1")

I tried to do a part of this (cause i don't know how to add another field to the list)

 index="x" sourcetype="STstatus" | streamstats first(new_status) as firstStatus by event_id  | search NOT firstStatus = "CANCEL" | eval updatetime = strftime (Update_date, "%d-%m-%y %H:%M:%S") | transaction event_id mvlist=new_status startswith=eval(new_status="OPEN" OR new_status="REOPEN") endswith=eval(new_status="CLOSE" OR new_status="CANCEL") | stats sum(duration) as sDuration by event_id | eval SumMin = sDuration / 60

[lguinn2]

New answer: this one should work even when the tickets are passed back and forth. I also leverage the idea that I got from @somesoni2 that the intervening events don't really matter - just the beginning and ending events. This will reduce the number of events that get passed to the transaction command and should make it faster.

index=x sourcetype=STcrew old_crew!="NULL"
| eval new_status = "END"
| eval handling_crew=old_crew
| append [ search index=x sourcetype=STcrew new_crew !=" NULL" 
    | eval new_status="START"
    | eval handling_crew = new_crew ]
| append [ search index=x sourcetype=STstatus (new_status="OPEN" OR new_status="REOPEN" OR new_status="CLOSE" OR new_status="CANCEL") ]
| fields handling_crew new_status event_id 
| transaction event_id handling_crew mvlist=new_status keep_evicted=f startswith=eval(new_status="OPEN" OR new_status="REOPEN" OR new_status="START") endswith=eval(new_status="CLOSE" OR new_status="CANCEL" OR new_status = "END")
| where new_status != "CANCEL"
| eval duration = round(duration / 60,1)
| stats sum(duration) as TotalDuration by event_id handling_crew

[somesoni2]

Hope this helps:

This should give duration for all the crew (tested with your sample data). Please validate the field names and sourcetype name.

sourcetype="ST_status" NOT handling_crew="NULL" (new_status="CLOSE" OR new_status="CANCEL" OR new_status="OPEN" OR new_status="REOPEN") 
| table event_id, Update_date, old_status, new_status, handling_crew, _time
 | eval assigned_crew =IF( new_status="CLOSE" OR new_status="CANCEL",  "NOT ".handling_crew, handling_crew )
 | table event_id, Update_date,assigned_crew, _time,handling_crew
| append [search sourcetype="ST_crew"  | eval assigned_crew=new_crew.",NOT ".old_crew | eval assigned_crew=split(assigned_crew,",") | mvexpand assigned_crew
| table event_id, Update_date, assigned_crew, _time  | eval handling_crew=replace(assigned_crew,"NOT ","") | search  NOT handling_crew="NULL" ] 
| search [search sourcetype="allevents" NOT CURRENT_STATUS="CANCEL" | table event_id] 
|sort event_id, Update_date | eval x=event_id.",".handling_crew | transaction event_id,handling_crew maxevents=2 | stats sum(duration) as duration by handling_crew

[lguinn2]

You could ditch all the table commands and this would be much shorter...

[lguinn2]

This should work well, as long as the crews don't hand the event back-and-forth. In other words, once IT-1 has handed off the event, if the same event_id doesn't come back to IT-1 later, this will work. (BTW, your solution may have had the same problem.)

index=x sourcetype=STcrew old_crew!="NULL"
| eval old_status = "NULL"
| eval new_status = "END"
| eval handling_crew=old_crew
| append [ search index=x sourcetype=STcrew new_crew !=" NULL" 
    | eval old_status = "NULL" 
    | eval new_status="START"
    | eval handling_crew = new_crew ]
| append [ search index=x sourcetype=STstatus ]
| stats range(_time) as duration latest(new_status) as last_status by event_id handling_crew
| where last_status != "CANCEL"
| eval duration = round(duration / 60,1)

This will give you a list of durations by the event and crew. If you wanted to summarize this further, you could add another stats command at the end:

| stats sum(duration) as TotalDuration by handling_crew

would give you the overall handling time by crew for example.

[linu1988]

just updated the | "evan" handling_crew to | eval handling_crew

[nisim651]

Thank you for your answer, but I also need to calc the time of the events that came back to the crew.
I will be very happy if you or someone else can help me.
ty