How to get employee name in chart?

[kumasaua]

Dear All,

I have one employee master csv that have employee name, departmentname, projectname.
If the employee is the group head then in project , it mention Group head, otherwise it mentions project name
like:

Name      DeptName       ProjectName
saurabh  softwaredept  skyject
gaurav     softwaredept  GroupHead
manish    rnd                    hrmanagement
rekesh     rnd                    GroupHead

I want to show data like Deptname , Name of the grophead of this dept, No of employees.

Result like:

softwaredept  gaurav   2
rnd                    rakesh   2

Requesting you to please provide query for this requirement.

[harishalipaka]

@niketnilay answer will work or you can try with small change in count by like .

| eventstats count by ProjectName| search ProjectName=GroupHead
 | table DeptName Name count

[kumasaua]

Thank you.. it works..

[niketn · Accepted solution]

@kumasaua, please try the following using eventstats

 <yourCurrentSearch>
| eventstats count by DeptName
| search ProjectName=GroupHead
| table DeptName count

Following is a run anywhere search based on the sample data provided:

| makeresults
| eval data="saurabh softwaredept skyject;gaurav softwaredept GroupHead;manish rnd hrmanagement;rekesh rnd GroupHead"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval Name=mvindex(data,0),DeptName=mvindex(data,1),ProjectName=mvindex(data,2)
| table Name DeptName ProjectName
| eventstats count by DeptName
| search ProjectName=GroupHead
| table DeptName count

[kumasaua]

Dear Niketnilay,

When i use first query then it show result like
Departname count but i want to show Group name also like
Departname Groupheadname count.

Can you help me for this.

[kumasaua]

Thank you , now it works.