Good thought about simply using another sort.
I had already found a solution that worked myself using this:
sourcetype=caslog action="SERVICE_TICKET*" | fields _time,action,user,ticket,service,client_ip |
eval service=mvjoin(mvindex(split(service,"/"),0,3),"/") | eval Action=replace(action,"SERVICE_TICKET_","ST_") |
sort 0 ticket _time | streamstats first(service) as Service by ticket | streamstats first(user) as User by ticket |
streamstats first(_time) as stime by ticket | search action!="SERVICE_TICKET_CREATED" |
eval etime=_time-stime | eval stime=substr(strftime(stime, "%m/%d:%H:%M"),1,20) |
streamstats max(etime) as maxtime by Action,Service | eval maxticket=if(maxtime = etime,ticket,maxticket) |
eval maxuser=if(maxtime = etime,User,maxuser) |
stats count as Count, avg(etime) as ea, Min(etime) as el, max(etime) as eh, last(maxticket) as Max_Ticket,
last(maxuser) as Max_User by Action,Service | eval "Avg Ticket Time"=round(ea,3) |
eval "Low Ticket Time"=round(el,3) | eval "High Ticket Time"=round(eh,3) |
table Count,Action,Service,"Avg Ticket Time","Low Ticket Time","High Ticket Time",Max_Ticket,Max_User
Which uses streamstats to find the max time and then eval if to match the user and ticket for the max time. It works, but your solution was more efficient - cutting the time nearly in half.
I had also found my flaw in the subsearch for action after I added the eval to reduce the space occupied by the action. I had not caught my flaw in the first sort which should have been on ticket and time. For CAS service tickets that was key, so thanks for that.
Results are looking good now (although I can't do too long of time span or limits chops my results without telling me).
... View more