Splunk Search

In a query using the tstats command, how do you add a "not" condition before the 'count' function?

AlexeySh
Communicator

Hello,

We use an ES ‘Excessive Failed Logins’ correlation search:

| tstats summariesonly=true allow_old_summaries=true values(Authentication.tag) as "tag",dc(Authentication.user) as "user_count",dc(Authentication.dest) as "dest_count",count from datamodel=Authentication.Authentication where   nodename=Authentication.Failed_Authentication  by "Authentication.app","Authentication.src"  | rename "Authentication.app" as "app","Authentication.src" as "src" | where 'count'>=6

But we would like to add an additional condition to the search, where ‘signature_id’ field in Failed Authentication data model is not equal to 4771.

At the end of the search, we tried to add something like |where signature_id!=4771 or |search NOT signature_id =4771, but of course, it didn’t work because count action happens before it.

Do you have an idea how we can implement that condition?

Thank for the help.

Alex.

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Have you tried this?

| tstats summariesonly=true allow_old_summaries=true values(Authentication.tag) as "tag",dc(Authentication.user) as "user_count",dc(Authentication.dest) as "dest_count",count from datamodel=Authentication.Authentication where   nodename=Authentication.Failed_Authentication AND Authentication.signature_id!=4771 by "Authentication.app","Authentication.src"  | rename "Authentication.app" as "app","Authentication.src" as "src" | where 'count'>=6
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Have you tried this?

| tstats summariesonly=true allow_old_summaries=true values(Authentication.tag) as "tag",dc(Authentication.user) as "user_count",dc(Authentication.dest) as "dest_count",count from datamodel=Authentication.Authentication where   nodename=Authentication.Failed_Authentication AND Authentication.signature_id!=4771 by "Authentication.app","Authentication.src"  | rename "Authentication.app" as "app","Authentication.src" as "src" | where 'count'>=6
---
If this reply helps you, Karma would be appreciated.

AlexeySh
Communicator

Exactly!
I just passed around it 🙂

Thanks for the help!

0 Karma

renjith_nair
Legend

@AlexeySh ,
Have your tried adding the condition in where clause of data model ie. ...count from datamodel=Authentication.Authentication where nodename=Authentication.Failed_Authentication AND signature_id!=4771. Sorry doesn't have a system to test it

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

AlexeySh
Communicator

@ renjith.nair unfortunately it doesn’t work.

When I try to enclose AND signature_id!=4771 just after where nodename=Authentication.Failed_Authentication there is no results found.

And if I try to enclose it after by "Authentication.app","Authentication.src" there is a “invalid argument” error.

0 Karma

lakshman239
Influencer

I tried your search including signature_id and it works for me in my env and normally we use fields to be excluded in there where clause for our other searches as well

| tstats summariesonly=true allow_old_summaries=true values(Authentication.tag) as "tag",dc(Authentication.user) as "user_count",dc(Authentication.dest) as "dest_count",count from datamodel=Authentication.Authentication where nodename=Authentication.Failed_Authentication Authentication.signature_id!=4771" by "Authentication.app","Authentication.src" |drop_dm_object_name("Authentication")

0 Karma

AlexeySh
Communicator

Yep, that's work!

I tried the query with simple signature_id!=4771 condition and not Authentication.signature_id!=4771 😞

Thanks for the help!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...