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!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...