How to edit my appendcols search to group all required fields based on one field?

[surekhasplunk]

I have written below search where i have used appendcols option so that all the result will come under one table view but how do i group all the required fields based on EmployeeGDDLoginName?

As you can see i have several small searches which works well, but i want EmployeeGDDLoginName to be used once as a group by so that for that particular EmployeeGDDLoginName, i should be able to get rest of the corresponding columns.

Pls Help

source="EFFORT TRACKER REQUESTED.csv" host="BDC6-LX-6021DXD" index="level3" sourcetype="effort_tracker"|rename EmployeeGddLoginName as "EmployeeName"| stats count(TaskID) as "Total Count" by "EmployeeName"|appendcols[search index="level3" sourcetype="effort_tracker" "Task Name"=INC* |rename EmployeeGddLoginName as "Employee"| stats count("Task Name") as "Incident Count" by "Employee"] |appendcols[search index="level3" sourcetype="effort_tracker" "Task Name"=TASK* | stats count("Task Name") as "Task Count" by EmployeeGddLoginName]|appendcols[search index="level3" sourcetype="effort_tracker" "Task Name"=Vacation* |rename EmployeeGddLoginName as "Employe"| stats count("Task Name") as "Vacation" by Employe] | appendcols[search index="level3" sourcetype="effort_tracker" Phase=*raining*|rename EmployeeGddLoginName as "Employ"| stats count("Phase") as "Training" by Employ]

[cmerriman · Accepted solution]

Why are you renaming EmployeeGddLoginName in all of your appendcols if that's what you want to join them by? Can you try something like this instead of all of the appends?

source="EFFORT TRACKER REQUESTED.csv" host="BDC6-LX-6021DXD" index="level3" sourcetype="effort_tracker"
|stats count(TaskID) as "Total Count" count(eval(match("Task Name","INC.*"))) as "Incident Count" count(eval(match("Task Name","TASK.*"))) as "Task Count" count(eval(match("Task Name","Vacation.*"))) as Vacation count(eval(Phrase="Training")) as "Training by EmployeeGddLoginName 

[surekhasplunk]

Thanks for the answer I was getting 0 results for "Incident Count", "Task Count", Vacation and Training.
But when i renamed "Task Name" to tast_name and used that then it started working fine.
Thank u thanks a lot for the query

Now i have another column called effort where i have effort mentioned in hours. and for each employee i want to calculate the sum of that effort only that too where task_name has INC* and TASK* in it and display in the output as "totaltickteffort" how do i do that ???

source="EFFORT TRACKER REQUESTED.csv" host="BDC6-LX-6021DXD" index="level3" sourcetype="effort_tracker" | rename "Task Name" as task_name
|stats count(eval(match(task_name,"INC*"))) as "Incident Count" count(eval(match(task_name,"TASK*"))) as "Task Count" count(TaskID) as "Total Count" count(eval(match(task_name,"Vacation*"))) as "Vacations" count(eval(match(task_name,"T|training*"))) as "Training" sum(Effort) as "Total Effort" sum(eval(if(task_name like "INC%",B,null()))) as sumofinc sum(eval(if(task_name like "TASK%",B,null()))) as sumoftask sum(B) as totalticketeffort by EmployeeGddLoginName

currntly am using above query but not getting any results columns getting created with blank values

[cmerriman]

source="EFFORT TRACKER REQUESTED.csv" host="BDC6-LX-6021DXD" index="level3" sourcetype="effort_tracker" | rename "Task Name" as task_name
|stats count(eval(match(task_name,"INC*"))) as "Incident Count" count(eval(match(task_name,"TASK*"))) as "Task Count" count(TaskID) as "Total Count" count(eval(match(task_name,"Vacation*"))) as "Vacations" count(eval(match(task_name,"T|training*"))) as "Training" sum(Effort) as "Total Effort" sum(eval(if(task_name like "INC%",Effort,null()))) as sumofinc sum(eval(if(task_name like "TASK%",Effort,null()))) as sumoftask  by EmployeeGddLoginName|eval totalticketeffort =sumofinc+sumoftask|fields - sumofinc - sumoftask

try this. i think it's what you're trying to get

[surekhasplunk]

Hi Thanks,
But how can i add two field names to search for same pattern in the LIKE function.
Suppose i want to add task_name and phase filed both with the like function "TASK%" then how do i do that. Because i have task_name field and phase filed both with values like TASK* so if that exists in either of the fields then i have to show that value.

One more question is how can i use token name as script argument to a shell script in the alert actions.
something like sendmail.sh $result.emailid$
where sendmail.sh is my script name and emailid is one of the fields in the search result query.