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

[AlexeySh]

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.

[richgalloway · Accepted solution]

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

[AlexeySh]

Exactly!
I just passed around it 🙂

Thanks for the help!

[renjith_nair]

@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

[AlexeySh]

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

[lakshman239]

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")

[AlexeySh]

Yep, that's work!

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

Thanks for the help!