index="os" sourcetype="Service" CaseNumber=* status=* assignment_group=* |dedup _time,CaseNumber,assignment_group |streamstats current=f last(assignment_group) as lg, last(active) as Active,first(assigned_to) as fs,last(assigned_to) as ls by CaseNumber|lookup Team.csv test as assigned_to OUTPUT TeamName| eval is_escalated= if(assignment_group!=lg AND assignment_group="Support L1",1,NULL) |eval is_resolved=if(assignment_group="Support L1" AND status="Complete" AND (isnull(Active) OR Active="true") AND fs=ls,1,NULL)|stats count(is_escalated) AS "Escalated Cases" count(is_resolved) AS "Resolved Cases" by assigned_to,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(assigned_to)=last(assigned_to).
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.