Splunk Search

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

dhiraj027in
New Member

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
0 Karma

somesoni2
Revered Legend

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

sundareshr
Legend

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
0 Karma
Get Updates on the Splunk Community!

SOC4Kafka - New Kafka Connector Powered by OpenTelemetry

The new SOC4Kafka connector, built on OpenTelemetry, enables the collection of Kafka messages and forwards ...

Your Voice Matters! Help Us Shape the New Splunk Lantern Experience

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Building Momentum: Splunk Developer Program at .conf25

At Splunk, developers are at the heart of innovation. That’s why this year at .conf25, we officially launched ...