Stats dc command on the total row?

[sumarri]

So, I want the the distinct count of user_numbers by device, but in the same chat/table, I want the distinct count of all the user_numbers in the last column  called total is this possible to get a stats formula with different fields in the by? 

This is some thing that I have:

| stats dc(user_numbers) by device

but I also want to show in the same table as the total:
| stats dc(user_numbers)

right now I count duplicates and show this: 

| addcoltotals label="Total Members" labelfield=device

I really hope this is possible! Thank you!

[_JP]

You could do something like this using eventstats:

| makeresults format=json data="[{\"device\":\"foo\", \"user_numbers\":19}, {\"device\":\"foo\", \"user_numbers\":39}, {\"device\":\"bar\", \"user_numbers\":39}, {\"device\":\"foo\", \"user_numbers\":44}]"

| eventstats dc(user_numbers) as overall_distinct_user_count
| stats dc(user_numbers), first(overall_distinct_user_count) as overall_distinct_user_count by device

[sumarri]

Is is possible to get it to the last row??? 

[_JP]

You can use appendpipe to add a row using the current search pipeline context:

| makeresults format=json data="[{\"device\":\"foo\", \"user_numbers\":19}, {\"device\":\"foo\", \"user_numbers\":39}, {\"device\":\"bar\", \"user_numbers\":39}, {\"device\":\"foo\", \"user_numbers\":44}]"

| eventstats dc(user_numbers) as overall_distinct_user_count
| stats dc(user_numbers) as distinct_users_for_device, first(overall_distinct_user_count) as overall_distinct_user_count by device
| appendpipe [stats max(overall_distinct_user_count) as overall_distinct_user_count | eval device = "All Devices" ]

[sumarri · Accepted solution]

Thank you for this, to get in the same column , I renamed the "as overall_distinct_user_count" in the appendpipe command. 

| eventstats dc(user_numbers) as overall_distinct_user_count
| stats dc(user_numbers) as distinct_users_for_device, first(overall_distinct_user_count) as overall_distinct_user_count by device
| appendpipe [stats max(overall_distinct_user_count) as distinct_users_for_device | eval device = "All Devices" ]
| table device, distinct_users_for_device