How to distinct count and separate by type?

[vitorvmiguel]

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...

[jacobwilkins · Accepted solution]

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]

It worked! Thanks

[Thomas_Aneiro]

Would something like this work for you?

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

[vitorvmiguel]

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

[Thomas_Aneiro]

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

[aljohnson_splun]

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

[vitorvmiguel]

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