unique user/unique client

[nithys]

Hi 
Need to find Unique Users(Count of distinct business users )& Clients(Count of distinct system client accounts )

I want to  have Unique Users and unqiue client based on cid.id and its associated groups

example

app	unique user	unique client	groups
name.id	22	1	app.preprod.name
address.id	1	1	app.preprod.address,app.preprod.zipcode

index= AND source="*"
| stats dc( claims.sub) as "Unique Users" ``` dc(claims.sub) as "Unique Users" count(claims.sub) as "Total" ```
```| addcoltotals labelfield="Grand Total"`

{"name":"","hostname":"1","pid":8,"level":,"claims":{"ver":1,"jti":"h7","iss":"https","aud":"https://p","iat":1,"exp":17,"cid":"name.id","uid":"00","scp":["update:","offline_access","read:","readall:","create:","openid","delete:","execute:","read:"],"auth_time":17,"sub":"name@gmail.com","groups":["App.PreProd.name"]},"msg":" JWT Claims -API","time":"2025","v":0}

unique client

index=* AND source="*"
| stats dc( claims.cid) as "Unique Clients" ``` dc(claims.sub) as "Unique Users" count(claims.sub) as "Total" ```
```| addcoltotals labelfield="Grand Total"```

"name":"","hostname":"1","pid":8,"level":,"claims":{"ver":1,"jti":"h7","iss":"https","aud":"https://p","iat":1,"exp":17,"cid":"address.id","uid":"00","scp":["update:","offline_access","read:","readall:","create:","openid","delete:","execute:","read:"],"auth_time":17,"sub":"name@gmail.com","groups":["App.PreProd.address,app.preprod.zipcode"]},"msg":" JWT Claims -API","time":"2025","v":0}

[gcusello]

Hi @nithys ,

when you use json fields use brachets or rename them:

index= AND source="*"
| rename claims.sub AS claims_sub
| stats dc(claims_sub) as "Unique Users" ``` dc(claims.sub) as "Unique Users" count(claims_sub) as "Total" ```
```| addcoltotals labelfield="Grand Total"`

or

index= AND source="*"
| stats dc('claims.sub') as "Unique Users" ``` dc(claims.sub) as "Unique Users" count(claims.sub) as "Total" ```
```| addcoltotals labelfield="Grand Total"`

Ciao.

Giuseppe

[yeahnah]

Hi @nithys 

Something like this should work ...

index=dummy
| append [ | makeresults count=22
| eval json=split("{\"name\":\"\",\"hostname\":\"1\",\"pid\":8,\"level\":\"\",\"claims\":{\"ver\":1,\"jti\":\"h7\",\"iss\":\"https\",\"aud\":\"https://p\",\"iat\":1,\"exp\":17,\"cid\":\"name.id\",\"uid\":\"00\",\"scp\":[\"update:\",\"offline_access\",\"read:\",\"readall:\",\"create:\",\"openid\",\"delete:\",\"execute:\",\"read:\"],\"auth_time\":17,\"sub\":\"name@gmail.com\",\"groups\":[\"App.PreProd.name\"]},\"msg\":\" JWT Claims -API\",\"time\":\"2025\",\"v\":0} | {\"name\":\"\",\"hostname\":\"1\",\"pid\":8,\"level\":\"\",\"claims\":{\"ver\":1,\"jti\":\"h7\",\"iss\":\"https\",\"aud\":\"https://p\",\"iat\":1,\"exp\":17,\"cid\":\"address.id\",\"uid\":\"00\",\"scp\":[\"update:\",\"offline_access\",\"read:\",\"readall:\",\"create:\",\"openid\",\"delete:\",\"execute:\",\"read:\"],\"auth_time\":17,\"sub\":\"name@gmail.com\",\"groups\":[\"App.PreProd.address,app.preprod.zipcode\"]},\"msg\":\" JWT Claims -API\",\"time\":\"2025\",\"v\":0}", " | ")
]
| mvexpand json
| eval _raw=json
| spath
| streamstats count
| eval "claims.sub"=if(count%2=0, count."_".'claims.sub', 'claims.sub')
 ``` ^^^ create dummy events ^^^ ```
| stats dc(claims.sub) as "Unique Users"
        dc(claims.cid) as "Unique Clients"
  BY claims.cid claims.groups{}
| rename claims.cid AS app claims.groups{} AS groups
| table app "Unique Users" "Unique Clients" groups

Hope that helps 

[nithys]

Hi @yeahnah @gcusello 
I used in below way where unique user count is not matching ,why i need to provide specify json...I want to fetch from all event from the splunk log give the unique user list for their specify group .group can respresent in [group 1,group 2] or [group1]...then fetch 
unique user list of [App.Au1,App.Au2] in one row and unique user list of [App.Au1] in second row

[nithys · Accepted solution]

@gcusello @yeahnah 
I want to display in the similar tabular way what showed but not giving  on specific json as taking as makeresult...
I have the event flowing in  two format which i shared earlier in the splunk.can you help on this

[gcusello]

Hi @nithys ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉