I have a table like this:
Shops Location Total
1. CK SF 1000
2. CK LA 877
3. CK NY 543
4. CK BOS 436
5. OG NY 2112
6. OG DET 100
7. OG ATL 876
8. OG LA 421
I want a table/report like this:
Shops Location Total
1. CK LA 877
2. CK NY 543
3. OG NY 2112
4. OG LA 421
Code I have:
|tstats count where index=us-shops Shops IN (CK OG) Location=* by Shops,Location |sort - count| streamstats count as rank by Shops
| where rank < 9|stats sum(count) by Shops,Location | rename sum(count) as Total
hi @tjosm
Your query is a bit confusing,given you requirements.
You need to have Total as a by clause in both stats and stream stats. You are loosing the field 'Total' and instead having sum of count as Total which will probably give you output as 1 /(just the unique count), be that as it may I kinda took your initial data in a csv and added this code.
| stats count by Shops,Location,Total
| sort - count| streamstats count as rank by Shops | where rank < 9|stats sum(count) by Shops,Location,Total | rename sum(count) as newtotal
| eventstats count as loc_count by Location
| where loc_count>1
| fields - newtotal, - loc_count
tstats has become stats since i took the first data in a csv
hi @tjosm
Please let us know if your issue has been resolved and accept the answer if it significantly helped your resolution. Do not forget to add additional resolution details for the benefit of other form members.