topic Re: In a query using the tstats command, how do you add a "not" condition before the 'count' function? in Splunk Search

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

AlexeySh — Fri, 08 Feb 2019 13:25:46 GMT

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.

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

renjith_nair — Fri, 08 Feb 2019 13:47:34 GMT

@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

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

AlexeySh — Fri, 08 Feb 2019 14:09:37 GMT

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

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

lakshman239 — Tue, 29 Sep 2020 23:07:29 GMT

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

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

richgalloway — Fri, 08 Feb 2019 16:02:00 GMT

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

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

AlexeySh — Fri, 08 Feb 2019 16:18:13 GMT

Exactly!
I just passed around it 🙂

Thanks for the help!

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

AlexeySh — Fri, 08 Feb 2019 16:19:42 GMT

Yep, that's work!

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

Thanks for the help!