topic Re: How to exclude results if some conditions? in Splunk Search

How to exclude results if some conditions?

marco_massari11 — Thu, 15 Sep 2022 16:55:14 GMT

Hi All,

I have the following saved search:

| tstats summariesonly=true fillnull_value="N/D" count from datamodel=Change where NOT [|`change_whitelist_generic`] nodename="All_Changes.Account_Management.Accounts_Updated" AND All_Changes.log_region=* AND All_Changes.log_country=* AND (All_Changes.command=passwd OR All_Changes.result_id IN (4723, 4724)) by All_Changes.log_region, All_Changes.log_country, index, host, All_Changes.Account_Management.src_user, All_Changes.user, _time | `drop_dm_object_name("All_Changes")` | rename Account_Management.src_user as src_user

My customer asked to me to exclude results when Account_Management.src_user=user1 and All_Changes.Account_Management.src_nt_domain=All_Changes.Account_Management.dest_nt_domain. So I tried something like that but it seems not working:

| tstats summariesonly=true fillnull_value="N/D" count from datamodel=Change where NOT [| `change_whitelist_generic`] nodename="All_Changes.Account_Management.Accounts_Updated" AND All_Changes.log_region=* AND All_Changes.log_country=* AND (All_Changes.command=passwd OR All_Changes.result_id IN (4723, 4724)) by All_Changes.log_region, All_Changes.log_country, index, host, All_Changes.Account_Management.src_user, All_Changes.user, All_Changes.Account_Management.dest_nt_domain, All_Changes.Account_Management.src_nt_domain, _time | `drop_dm_object_name("All_Changes")` | search NOT (Account_Management.src_user=user1 AND Account_Management.src_nt_domain=Account_Management.dest_nt_domain) | rename Account_Management.src_user as src_user

Have you any advice?

Thank you!

Re: How to exclude results if some conditions?

richgalloway — Thu, 15 Sep 2022 17:08:28 GMT

What does "not working" mean? Are you getting expected results or not? If not, how do the results not meet expectations?

The new criteria likely is failing because the search command does not allow a field name on both sides of the expression. Use where, instead.

Re: How to exclude results if some conditions?

marco_massari11 — Thu, 15 Sep 2022 18:48:56 GMT

Hi @richgalloway ,

"not working" means that in the result I always have the events where src_user=user1 and src=dest. How can I specify it with where?

Thank you in advance!

Re: How to exclude results if some conditions?

richgalloway — Thu, 15 Sep 2022 19:05:30 GMT

As mentioned in my previous reply, use where in place of search.

| tstats summariesonly=true fillnull_value="N/D" count from datamodel=Change where NOT [| `change_whitelist_generic`] nodename="All_Changes.Account_Management.Accounts_Updated" AND All_Changes.log_region=* AND All_Changes.log_country=* AND (All_Changes.command=passwd OR All_Changes.result_id IN (4723, 4724)) by All_Changes.log_region, All_Changes.log_country, index, host, All_Changes.Account_Management.src_user, All_Changes.user, All_Changes.Account_Management.dest_nt_domain, All_Changes.Account_Management.src_nt_domain, _time | `drop_dm_object_name("All_Changes")` | where NOT (Account_Management.src_user=user1 AND Account_Management.src_nt_domain=Account_Management.dest_nt_domain) | rename Account_Management.src_user as src_user

Re: How to exclude results if some conditions?

marco_massari11 — Thu, 15 Sep 2022 20:37:51 GMT

Hi @richgalloway,

as you can see in the screen attached the result returns the specified user with src=dest. Have you any other advice?

Thanks in advance!

Re: How to exclude results if some conditions?

richgalloway — Thu, 15 Sep 2022 23:56:41 GMT

If the target user name is going to be a literal then it should be in quotation marks. Also, sometimes the dot notation produces unexpected results so try renaming fields to not have dots in the names.

| tstats summariesonly=true fillnull_value="N/D" count from datamodel=Change where NOT [| `change_whitelist_generic`] nodename="All_Changes.Account_Management.Accounts_Updated" AND All_Changes.log_region=* AND All_Changes.log_country=* AND (All_Changes.command=passwd OR All_Changes.result_id IN (4723, 4724)) by All_Changes.log_region, All_Changes.log_country, index, host, All_Changes.Account_Management.src_user, All_Changes.user, All_Changes.Account_Management.dest_nt_domain, All_Changes.Account_Management.src_nt_domain, _time | `drop_dm_object_name("All_Changes")` | rename Account_Management.* as * | where NOT (src_user="user1" AND src_nt_domain=dest_nt_domain)