topic Re: Counts in stats command not working the way I expected in Splunk Search

Counts in stats command not working the way I expected

dbcase — Fri, 02 Feb 2018 20:43:36 GMT

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?

Re: Counts in stats command not working the way I expected

somesoni2 — Fri, 02 Feb 2018 20:53:41 GMT

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"

Re: Counts in stats command not working the way I expected

dbcase — Fri, 02 Feb 2018 20:56:40 GMT

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

Thanks again Somesoni2!!!