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
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")]
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