Dashboards & Visualizations

Stats dc command on the total row?

sumarri
Path Finder

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!

Labels (2)
0 Karma
1 Solution

sumarri
Path Finder

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

 

View solution in original post

0 Karma

_JP
Contributor

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
Path Finder

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

0 Karma

_JP
Contributor

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
Path Finder

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

 

0 Karma
Get Updates on the Splunk Community!

Buttercup Games Tutorial Extension - part 9

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...

Buttercup Games Tutorial Extension - part 8

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...

Introducing the Splunk Developer Program!

Hey Splunk community! We are excited to announce that Splunk is launching the Splunk Developer Program in ...