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

@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

Happy Splunking!
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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...