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
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
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
You could ditch all the table
commands and this would be much shorter...
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.
just updated the | "evan" handling_crew to | eval handling_crew
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