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?
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"
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"
Wow, one day when I grow up I want to be able to do what you do 🙂
Thanks again Somesoni2!!!