Need help with subtotals in search

[Akita881]

Using the search below i get the results in the first table. I would like to show subtotals (in some fashion) like the second table below. Thanks in advance.

POST /profiles/login  
| rex ".*DY_ID=(?<User>(.*?));"
| stats count by clientip  | where  count > 50 
| appendcols [search POST /profiles/login 
    | rex ".*DY_ID=(?<Usergt;(.*?));" 
    |stats  dc(User) as UserCount,  values(User) as UserID,  count by clientip  | where  count > 50 ] 
| table count,clientip,UserCount,DynUser

count...............clientip............UserCount................UserID
...62.............11.111.111.11..............0

.107.............22.222.222.22...........18..................1051265912
............................................................................1051265912
............................................................................1276856930
............................................................................1389348513
............................................................................2710273231
............................................................................3195242289
............................................................................3203819132
.102.............33.333.33.333...........51..................5885625406
............................................................................5885625472
............................................................................5885625517
............................................................................5885625571
............................................................................5885625601

NEEDED:

count...............clientip............UserCount.................UserID............UserID Count
...62.............11.111.111.11..............0

.107.............22.222.222.22............7...................1051265912.................12
............................................................................1051265912.................10
............................................................................1276856930.................34
............................................................................1389348513.................22
............................................................................2710273231.................17
............................................................................3195242289...................8
............................................................................3203819132...................4
.102.............33.333.33.333............5...................5885625406.................31
............................................................................5885625472.................23
............................................................................5885625517.................16
............................................................................5885625571.................19
............................................................................5885625601.................13

[lguinn2 · Accepted solution]

Because "this post is awaiting moderation" I can't post an answer, only a comment.

Try this

POST /profiles/login  
| rex ".*DY_ID=(?<User>(.*?));"
| stats count by clientip User
| stats sum(count) as count dc(User) as UserCount list(User) as UserID list(count) as UserID_count by clientip
| where clientip_count > 50
| fields count clientip UserCount UserID_count UserID_count

You don't need a appendcols or a sub-search: this should be considerably faster.

[Akita881]

Thank you very much for the help.

[martin_mueller]

Should be all good now.