Compare values of two fields from different field value

[pgadhari]

Hi All,

This is a ticket data. I have a field called "Team" having 2 values "SAP" and "Non-SAP" and the respective team members for both the values SAP and Non-SAP as a different field called "assignee". SAP team has some 30 team members and non-SAP team has some 150 team members. Index and sourcetype is same. So when I do :

index=test sourcetype=test | search team=SAP | dedup assignee | table assignee - I get 30 team member names

index=test sourcetype=test | search team=Non-SAP | dedup assignee | table assignee - I get 150 team member names

Now I have to find out whether there are any common team members which are there in both teams or each team has unique team members, which are not common. I want the stats for the number of common team members and their names, if there is. Please help me in the query.

Regards
PG

[sundareshr · Accepted solution]

Try like this

index=test sourcetype=test | stats count values(team) as teams by assignee | eval teamcount=mvcount(teams) 

value in teamcount will tell you how many teams they are in and the values in teams will tell you which team they belong to. If you want only see assignees on multiple teams, you can add | where teamcount=2

[pgadhari]

can you explain what actually mvcount is doing ? As of now, the output of the above query is giving me only value "1" in teamcount for all the assignees, whether it means that they did not belong to other team ? I am not getting any value "2" in teamcount.

So I am considering that no team member belongs to both the teams, is that right ?

[sundareshr]

mvcount (multi-value count) is the count of values in the field. If the count is 1, then the assignee belongs to only one team. The teams column will show you which team(s) they belong to. You could also change the query to this..

index=test sourcetype=test | stats count values(team) as teams dc(team) as no_of_teams by assignee 

[pgadhari]

Thank you got it. That solved my query. Appreciate it.