Help with Query!

[kiran331]

Hello,

I'm trying to change the Correlation search 'Excessive Failed Logins' in ES by user, is there a option to exclude values with $. Most of the values I got are with $, when i try to use user!=*$, its showing error.

query i'm using :

| tstats summariesonly=true allowoldsummaries=true values(Authentication.tag) as "tag",dc(Authentication.src) as "srccount",dc(Authentication.dest) as "destcount",count from datamodel=Authentication.Authentication where nodename=Authentication.FailedAuthentication by "Authentication.user" | rename "Authentication.app" as "app","Authentication.user" as "user" | where 'count'>=6 | eval tag=mvjoin(tag,"|") | rename "tag" as "origtag"

[javiergn · Accepted solution]

Try this instead:

| tstats summariesonly=true allow_old_summaries=true values(Authentication.tag) as "tag",dc(Authentication.src) as "src_count",dc(Authentication.dest) as "dest_count",count from datamodel=Authentication.Authentication where nodename=Authentication.Failed_Authentication by "Authentication.user" | rename "Authentication.app" as "app","Authentication.user" as "user" | where 'count'>=6 AND NOT LIKE(user, "%$") | eval tag=mvjoin(tag,"|") | rename "tag" as "orig_tag"

The only change I made is:

| where 'count'>=6 AND NOT LIKE(user, "%$") 

EDIT:
The query above assumes you want to filter out users whose name ends with $. If you want contains or starts with, simply change the location of the % symbol or place the $ symbol it between two of them

[kiran331]

Thank you! It worked