index="os" sourcetype="Service" CaseNumber=* status=* assignmentgroup=* |dedup _time,CaseNumber,assignmentgroup |streamstats current=f last(assignmentgroup) as lg, last(active) as Active,first(assignedto) as fs,last(assignedto) as ls by CaseNumber|lookup Team.csv test as assignedto OUTPUT TeamName| eval isescalated= if(assignmentgroup!=lg AND assignmentgroup="Support L1",1,NULL) |eval isresolved=if(assignmentgroup="Support L1" AND status="Complete" AND (isnull(Active) OR Active="true") AND fs=ls,1,NULL)|stats count(isescalated) AS "Escalated Cases" count(isresolved) AS "Resolved Cases" by assignedto,TeamName| fields - TeamName
The above query display the person wise resolved and escalated count.The persons names we are reading from Team.csv file.
1)Now i want to display count of only one person resolved entire case(from first to last means first(assignedto)=last(assignedto).
2)Now i want to display persons who is involved in that case while resolving partcular case.
EX:Case No :1111,assigned_to: ramesh,raju,ramu.
So three members worked for this case.so this case should comes under all three.
3)Two steps same for Escalated cases as well.
How to do this?