topic Re: Stats dc command on the total row? in Dashboards & Visualizations

Stats dc command on the total row?

sumarri — Mon, 26 Aug 2024 20:56:05 GMT

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!

Re: Stats dc command on the total row?

_JP — Mon, 26 Aug 2024 21:10:45 GMT

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

Re: Stats dc command on the total row?

sumarri — Mon, 26 Aug 2024 21:13:37 GMT

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

Re: Stats dc command on the total row?

_JP — Mon, 26 Aug 2024 21:44:48 GMT

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" ]

Re: Stats dc command on the total row?

sumarri — Wed, 28 Aug 2024 14:47:13 GMT

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