Splunk Search

Counts in stats command not working the way I expected

dbcase
Motivator

Hi,

I have this query. It "works" (well mostly). What I'm confused about is the resulting stat table

index=wholesale_app   [search index="wholesale_app"  product=* CustomAnalytic Properties.index=33 (refreshWsCVRToken OR obtainWsCVRToken) |table clientSessionId] customerId OR (refreshWsCVRToken OR obtainWsCVRToken)|rex "(?<accountid>\w+)..,..customerId"|rex "sites.\d+.(?<token_type>\w+)"|stats values(accountid) as aid count(eval(token_type="refreshWsCVRToken")) as rcount count(eval(token_type="obtainWsCVRToken")) as ocount by clientSessionId  |sort -Count|fields - clientSessionId|where aid!=""|where len(aid)<=15 |rename aid as "Account ID" rcount as "Refresh token" ocount as "Obtain token"

Resulting stats table. I see three entries for the same account number with 1 as the count. I'd like 1 entry for the account but with a count of 3. Any thoughts?

alt text

Tags (2)
0 Karma
1 Solution

somesoni2
Revered Legend

Your aggregation was done based on customerId and it seems one Account ID was associated with multiple customerId hence you get 3 different rows (one for each customerId) with same Account ID. May be do a second aggregation by aid, like this

index=wholesale_app   [search index="wholesale_app"  product=* CustomAnalytic Properties.index=33 (refreshWsCVRToken OR obtainWsCVRToken) |table clientSessionId] customerId OR (refreshWsCVRToken OR obtainWsCVRToken)|rex "(?<accountid>\w+)..,..customerId"|rex "sites.\d+.(?<token_type>\w+)"|stats values(accountid) as aid count(eval(token_type="refreshWsCVRToken")) as rcount count(eval(token_type="obtainWsCVRToken")) as ocount by clientSessionId 
|where aid!="" AND isnotnull(aid) AND len(aid)<=15
| stats sum(*count) as *count by aid  |sort -rcount |rename aid as "Account ID" rcount as "Refresh token" ocount as "Obtain token"

View solution in original post

0 Karma

somesoni2
Revered Legend

Your aggregation was done based on customerId and it seems one Account ID was associated with multiple customerId hence you get 3 different rows (one for each customerId) with same Account ID. May be do a second aggregation by aid, like this

index=wholesale_app   [search index="wholesale_app"  product=* CustomAnalytic Properties.index=33 (refreshWsCVRToken OR obtainWsCVRToken) |table clientSessionId] customerId OR (refreshWsCVRToken OR obtainWsCVRToken)|rex "(?<accountid>\w+)..,..customerId"|rex "sites.\d+.(?<token_type>\w+)"|stats values(accountid) as aid count(eval(token_type="refreshWsCVRToken")) as rcount count(eval(token_type="obtainWsCVRToken")) as ocount by clientSessionId 
|where aid!="" AND isnotnull(aid) AND len(aid)<=15
| stats sum(*count) as *count by aid  |sort -rcount |rename aid as "Account ID" rcount as "Refresh token" ocount as "Obtain token"
0 Karma

dbcase
Motivator

Wow, one day when I grow up I want to be able to do what you do :slightly_smiling_face:

Thanks again Somesoni2!!!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...