How to count fields as well as count the results of the initial count

[kmccowen]

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count sum(count) by acct

What I'm getting:
acct count sum
8352300501686035 26

8345780121409274 19

8351600230030301 11

8357190023109516 11

8245114320198317 10

8245114730027858 10

8351100021993809 10

8751150011628754 10

8245100920152382 9

What I want:
acct count sum
8352300501686035 26 1
8345780121409274 19 1

8351600230030301 11 2

8357190023109516 11

8245114320198317 10 4

8245114730027858 10

8351100021993809 10

8751150011628754 10

8245100920152382 9 1

[kmccowen]

I figured it out I think. I used an appendcols and wrote the same query again but added where count=1. Below is what I ended up with.

search index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="" | stats count by acct | where count=1| eventstats count as sum by count | fields sum, count | dedup count | sort sum | reverse | append [ search index=ctap host=sc58 sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count by acct | eventstats count as sum by count | fields sum, count | dedup count | sort sum | reverse]

[acharlieh · Accepted solution]

So you want to order accounts by the number of times they've called and count how many are on each. I have two options for you to consider: What about using eventstats to help us out?

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count by acct | eventstats count as sum by count 

Another option might be a slightly different stats command after your initial stats to have multivalued fields:

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count by acct | stats values(acct) as accts count as sum by count

(you may need a sort of some sort of variety or another afterwards, but I'm not certain about that offhand)

[kmccowen]

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count by acct | eventstats count as sum by count | sort - count | fields sum, count | dedup count |

This worked great, but I've ran into a new problem with my dedup. I'm using the dedup by count to remove the extra Sum values so that I get this:

instead of this:
1 1 18
2 1 15
3 1 13
4 2 12
5 3 11
6 3 10
7 8 9
8 8 8

instead of this:
1 1 18
2 1 15
3 1 13
4 2 12
5 2 12
6 3 11
7 3 11
8 3 11
9 3 10
10 3 10

This works great but I end up loosing the count of "1" with the dedup command so I can't see how many customers called only once. Any ideas?

[acharlieh]

I'm not sure if I'm following what's happening with the dedup command, but from your search, it looks like you're only wanting sum as the number of accounts who called count times, but not the account numbers themselves? If so, then I'd use:

index=ctap host=sc58* sourcetype=gateway "CTIPOP CALL RECEIVED" acct="*" | stats count by acct | stats count as sum by count | sort - count

[kmccowen]

I've tried subsearches, appendcols, streamstats, etc. I want to count how many times each Count is listed in my results so seems like a subsearch is neccessary like in subquery in sql but when I try to count the values of the "count" of my first search it always comes up as "0".

1 account called 26 times
1 account called 19 times
2 accounts called 11 times
4 accounts called 10 times...etc