How to grab a count of logs over a field while excluding specific logs.

[Splunkster45]

I have a search query that has a field called "message_text" that I run a stats command, counting the number of log entries per message_text.

index=main env=prod  | stats count(message_text) as count | where count > 100000

For one particular message_text, I would like to exclude a specific subset of logs. I have a built a rex command that allows me to specifically query these log files.

index=main env=prod  message_text="Failed to*" | rex field=_raw "INSERT  INTO (?<model>\w+\.\w+).*"| search model="MODEL_1" 

However, I'm not sure how to combine the two ideas. I want to grab a count of all logs by message_text while excluding logs for a specific message_text that match a rex command. Below is what I thought would work, but it doesn't.

index=main env=prod | rex field=_raw "INSERT  INTO (?<model>\w+\.\w+).*"| where model!="MODEL_1" | stats count(message_text) as count | where count > 100000

I feel that when I add the rex command, followed by the where clause, my search is too granular. Now instead of looking at all message_texts, I'm feel that the above query would only be looking at logs that have a field call model whose value doesn't equal the specified model.

Does anyone have any thoughts on the matter?

Splunkster45

[woodcock · Accepted solution]

Try this:

 index=main env=prod  message_text="Failed to*" | rex "INSERT  INTO (?<model>\w+\.\w+).*"| search NOT model="MODEL_1" | stats count(message_text) as count | where count > 100000

The problem is that NOT model="MODEL_1" is not the same as model!="MODEL_1". The former is the exact opposite of model="MODEL_1", which is what you desire; the latter is similar but drops all events that do not have a (non-null) field named model (i.e. field model exists AND does not have value MODEL_1), which is why you were losing events unexpectedly.

[Splunkster45]

Thanks, this works great! I've never seen the explanation of != and Not, but I've seen that they are different. Thanks for the explanation.