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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...