Splunk Search

How to distinct count and separate by type?

vitorvmiguel
Explorer

I need to make a distinct count of clients and together count what clients had at least one error message?

I have client code, and type success, warning and error, and the same client makes multiple transactions, so i need to count how many distinct clients i have, and if this distinct clients had some error, count only one error per client. I don´t want to use append to keep my search light. Any suggestions?

stats distinct_count(eval(clientcode)) as UniqueClient...

Tags (2)
0 Karma
1 Solution

jacobwilkins
Communicator

Lots of ways to do this.

| eval is_not_success=if(match(type,"I"),0,1)
| stats sum(is_not_success) AS error_warn_count by clientcode
| stats count AS TotalDistinctClients, count(eval(error_warn_count>0)) AS DistinctClientsWithErrors

Apologies about the weird negation on "is_not_success" but it is necessary for the "sum".

View solution in original post

jacobwilkins
Communicator

Lots of ways to do this.

| eval is_not_success=if(match(type,"I"),0,1)
| stats sum(is_not_success) AS error_warn_count by clientcode
| stats count AS TotalDistinctClients, count(eval(error_warn_count>0)) AS DistinctClientsWithErrors

Apologies about the weird negation on "is_not_success" but it is necessary for the "sum".

vitorvmiguel
Explorer

It worked! Thanks

0 Karma

Thomas_Aneiro
Explorer

Would something like this work for you?

eventtype=*  | eventstats dc(user) as userCount | dedup user, error | table user, error, userCount
0 Karma

vitorvmiguel
Explorer

What is this eventtype=* ? My event field is called "tipo", and its possible values are Error, Success, Warning

0 Karma

Thomas_Aneiro
Explorer

You can disregard this, I was simply using "Eventtype=*" as a place holder for the search. You probably want a search closer to the following.

index="raw_internet" produto="1" pessoa="F" date_hour!=0 | eventstats dc(user) as userCount | dedup user, tipo | table user, tipo, userCount
0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

Can you post a sample event? At present, it looks like the eval in count is unnecessary.

0 Karma

vitorvmiguel
Explorer

I don´t have any sample, because i´ve tested a ton of functions and none of them took the desirable result, here is my actual querie searching only the disctinct clients, so beside that i have a field type that has 3 possible values E (Error), W (Warning) and I (Success), so i need to filter inside those unique clients who faced an error.

index="raw_internet" produto="1" pessoa="F" date_hour!=0 | stats distinct_count(codigoAcesso) as ClientesUnicos

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...