topic Help with Query! in Splunk Enterprise Security

Help with Query!

kiran331 — Tue, 29 Sep 2020 11:13:00 GMT

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 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 | eval tag=mvjoin(tag,"|") | rename "tag" as "orig_tag"

Re: Help with Query!

javiergn — Mon, 26 Sep 2016 14:18:36 GMT

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

Re: Help with Query!

kiran331 — Mon, 26 Sep 2016 14:23:33 GMT

Thank you! It worked