Delta between two Splunk search results

Question

I am trying to find out delta between two searches.

index="xyz-index" userId | rename attributes.privateGroups as privateGroups 
| join type=inner userId [ search index="xyz-index" userId | rename attributes.publicGroups as publicGroups]
| table userId, privateGroups, publicGroups

1. I want to find out userIds which are in both privateGroups and publicGroups
2. I want to find out userIds only in privateGroups but not in publicGroups or vice versa

For the one i tired with inner query as mentioned above but i get two different search results when i changed the search order.

Please help me in the second query as well? Below are the 3 events, 101 user id is in two groups whereas 102 is only one group

{
    userId : 101
    levle : INFO
    timestamp : 2020-06-10
    attributes: {
        privateGroups :  JohnOrg
    }
}
{
    userId : 101
    levle : INFO
    timestamp : 2020-05-09
    attributes: {
        publicGroups :  DistrictOrg
    }
}
{
    userId : 102
    levle : INFO
    timestamp : 2020-05-09
    attributes: {
        publicGroups :  DistrictOrg
    }
}

Answer 1

You can do this without a join like this:

index="xyz-index" userId=*
| rename attributes.privateGroups as privateGroups attributes.publicGroups as publicGroups

Make sure the privateGroups and publicGroups fields exist in all events

| fillnull value="-" privateGroups publicGroups

Because we know fields with "-" in them were filled with fillnull:

| eval both=if(privateGroups!="-" AND publicGroups!="-",1,0)
| eval inPrivate=if(privateGroups!="-",1,0)
| eval inPublic=if(publicGroups!="-",1,0)

Ensure there is only one event per "type" (public-vs-private-vs-both) with userId:

| stats count by userId both inPrivate inPublic

Lastly, give a report of userIds that are in both, just public, and just private:

| stats sum(both) as both_count sum(inPrivate) as private_count sum(inPublic) as public_count by userId