How to edit my search to group incidents based on resource and average their response time?

[dhiraj027in]

I have a search which gets me the data below:

Assigned to       Short description           Opened           Resolved          duration
Arjay Torreliza   Pending Files               5/31/2016 22:43   6/1/2016 9:49   11:06:00  
Arjay Torreliza   Price Changes Failure 1/1   5/31/2016 19:30   5/31/2016 22:45   3:15:00  
Arjay Torreliza   Price Changes Failure 1/2   5/31/2016 7:17    5/31/2016 8:32    1:15:00   

What I am looking for is below:

Assigned to       Short description       Opened           Resolved          duration   Count   Avg duration
Arjay Torreliza   Pending Files           5/31/2016 22:43   6/1/2016 9:49   11:06:00   1       11:06:00
Arjay Torreliza   Price Changes Failure   5/31/2016 19:30   5/31/2016 22:45   3:15:00    2       2:15:00

I want to group "Assigned to" by a common string from Short Description and also get the Avg Duration for that group. What should I do? Please help.

Below is the search I use:

index="incidents_stores_wisp" date_year="2016" Category="Alert" | eval opened_epoch=strptime(Opened, "%m/%d/%Y %H:%M") | eval resolved_epoch=strptime(Resolved, "%m/%d/%Y %H:%M") | eval duration=tostring(resolved_epoch-opened_epoch, "duration") | rex field=duration "(?\d{0,2})\+?(?\d{2})(?:\d{2}:\d{2})" | eval d=if(len(d)=0, 0, d) | eval duration=(d*24+h).ms | table Number "Assigned to" "Short description" Opened Resolved duration

[somesoni2]

Give this a try

index="incidents_stores_wisp" date_year="2016" Category="Alert" | eval opened_epoch=strptime(Opened, "%m/%d/%Y %H:%M") | eval resolved_epoch=strptime(Resolved, "%m/%d/%Y %H:%M") | eval duration=resolved_epoch-opened_epoch | eval "Short Description"=replace('Short Description',"\d\/\d","") | stats  earliest(Opened) as Opened latest(Resolved) as Resolved sum(duration) as Duration count avg(Duration) as "Avg Duration" by  "Assigned to" "Short description"  |  foreach *Duration [eval "<<FIELD>>"=tostring('<<FIELD>>',"duration")]

[sundareshr]

You will have to extract the common string to a field and then you can group by Assigned and CommonString. For example

index="incidents_stores_wisp" date_year="2016" Category="Alert" | eval opened_epoch=strptime(Opened, "%m/%d/%Y %H:%M") | eval resolved_epoch=strptime(Resolved, "%m/%d/%Y %H:%M") | eval duration=tostring(resolved_epoch-opened_epoch, "duration") | rex field=duration "(?\d{0,2})+?(?\d{2})(?:\d{2}:\d{2})" | eval d=if(len(d)=0, 0, d) | eval duration=(d*24+h).ms | rex field="Short Description" "?<commonstring>Price|Pending)" | stats earliest(Opened) as Opened earliest(Resolved) as Resolved count values(duration) as Duration by "Assigned To" commonstring