Splunk Search

How to get count of repeated, unique and total values by group?

trinath465
Engager

Hi

I am working on query to retrieve count of repeated, unique and total visits by user through different channels. The user can access my application through different channels like Email,SMS and Apps. 

For every channel count and output the number of new users (only one event), repeated users (more than one event) and final totals (=new + repeated).

The log data is in JSON format and there are two main fields relevant to achieve results.  First cust_id (which is a unique customer id) and second filed is channel_type.

Example expected results output:

channel_type      repeated_customers    new_customers             total

-----------------       ---------------------------      ---------------------       ----------------

Apps                                       4                                            1                                    5

Email                                      2                                            2                                    4

SMS                                        1                                            5                                   6

 

So far I have developed the below query which is not giving expected result.

index=cust_app sourcetype=cust_rec
| search log="*Cus Responeded*"
| rex field=log "(?<applog>{(?:[^}{]+|(?R))*+})"
| spath input=applog output=channel_type path=channel_type
| spath input=applog output=cust_id path=cust_id
| stats count by channel_type cust_id

How to get the expected results from the given filed values in the data.  Thanks in advance.

@niketn @elliotproebstel @twinspop 

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults count=100
| eval cust_id=random()%20
| eval channel=mvindex(split("Apps|Email|SMS","|"),random()%3)
| stats count by channel cust_id
``` the lines above generate some random dummy data ```
| eval new_cust=if(count==1,cust_id,null())
| stats count(new_cust) as new_customers count as total by channel
| eval repeated_customers=total - new_customers

View solution in original post

trinath465
Engager

This is exactly what was required. Thank you very much.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults count=100
| eval cust_id=random()%20
| eval channel=mvindex(split("Apps|Email|SMS","|"),random()%3)
| stats count by channel cust_id
``` the lines above generate some random dummy data ```
| eval new_cust=if(count==1,cust_id,null())
| stats count(new_cust) as new_customers count as total by channel
| eval repeated_customers=total - new_customers
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...