topic How to find users that had only errors for certain event? in Splunk Search

How to find users that had only errors for certain event?

dsnytkine — Wed, 14 Mar 2018 13:40:01 GMT

We have log entries in format like this:

LogLevel=info  username=some1 eventID=update

So in case of error the LogLevel will be LogLevel=error
LogLevel can also be debug

What I need to do is to find all users that had eventID=update but always had LogLevel=error and present them in table format
Username | count

If I simply search
eventID=update LogLevel=error then I will get all matching entries for users with LogLevel=error but some of these users probably also had entries with LogLevel=info (or debug)
How do I find users that had only LogLevel=error with this eventID?

Re: How to find users that had only errors for certain event?

niketn — Wed, 14 Mar 2018 14:44:23 GMT

@dsnytkine, can you please try the following?

<YourBaseSeach> LogLevel="*" eventID="update"
| stats dc(LogLevel) as distinctCountLogLevel values(LogLevel) as LogLevels by username
| search distinctCountLogLevel=1 AND LogLevels="ERROR"

The values() statistical function gives unique values of LogLevel. Similarly dc() gives distinct count of LogLevel values. So only results filtered are those which have only one unique LogLevel and that value is ERROR.

Re: How to find users that had only errors for certain event?

tiagofbmm — Wed, 14 Mar 2018 14:46:21 GMT

This gets you all the users with eventID="update" and only had log_level equals to error.

index=yourindex eventID="update"
| stats sum(eval(if(log_level!="error",1,0))) as HadOtherLogLevels by user
| where HadOtherLogLevels==0

Was this what you intended?

Re: How to find users that had only errors for certain event?

dsnytkine — Wed, 14 Mar 2018 14:48:37 GMT

Sounds like this is what I need. Is this the most efficient way? Thank you, I will test the solution.

Re: How to find users that had only errors for certain event?

dsnytkine — Wed, 14 Mar 2018 14:54:02 GMT

Yes, it worked, thank you. I'm wondering how common is this scenario?

Re: How to find users that had only errors for certain event?

tiagofbmm — Wed, 14 Mar 2018 14:56:51 GMT

Great. Honestly never had seen that requirement, but it is under the normal logic of splunk things

Re: How to find users that had only errors for certain event?

dsnytkine — Wed, 14 Mar 2018 16:56:34 GMT

Looks like it worked too, very interesting solution.

Re: How to find users that had only errors for certain event?

niketn — Wed, 14 Mar 2018 17:33:10 GMT

Splunk's _internal logs also maintains log_level as "INFO", "ERROR","WARN" and "FATAL". So with a query like this you can search for the Splunk component which has always thrown error. For example after upgrade, install or config change etc. to isolate the issue. There could be several different use cases on similar lines.