Splunk Search

How to exclude results if some conditions?

marco_massari11
Communicator

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!

Labels (4)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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)
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

marco_massari11
Communicator

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! 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

marco_massari11
Communicator

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!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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)
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

Stay Connected: Your Guide to June Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...