Splunk Search

Compare values of two fields from different field value

pgadhari
Builder

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

0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

sundareshr
Legend

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
Builder

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 ?

0 Karma

sundareshr
Legend

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 
0 Karma

pgadhari
Builder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...