How to exclude results if some conditions?

[marco_massari11]

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!

[richgalloway]

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.

[marco_massari11]

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! 

[richgalloway]

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

[marco_massari11]

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!

[richgalloway · Accepted solution]

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)