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

[dsnytkine]

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?

[tiagofbmm · Accepted solution]

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?

[dsnytkine]

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

[dsnytkine]

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

[niketn]

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.

[tiagofbmm]

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

[niketn]

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

[dsnytkine]

Looks like it worked too, very interesting solution.