How to edit my search to filter out events where the string values between two fields are the same? (field names contain spaces)

[caliburn7]

Hello,

I am trying to filter out events when the source username and destination username are the same, but it is not working when I use the where and NOT field1= field2 function. Is it because I have spaces in the field names? I tried to rename them as different fields and tried the where clause, but it still didn't work. Any help is greatly appreciated.

Here is the search:

| NOT ("Source User Name"="ANONYMOUS LOGON" OR "Source User Name"=*$)  Name!="A user account was changed."   | stats dc(Name) as UniqueActionCount, values(Name) as UniqueAction by "Source User Name" | where NOT "Source User Name"="Destination User Name"

Thank you.

[woodcock · Accepted solution]

Try this:

... | search NOT ("Source User Name"="ANONYMOUS LOGON" OR "Source User Name"=*$)  Name!="A user account was changed." | stats dc(Name) as UniqueActionCount, values(Destination User Name) AS "Destination User Name" values(Name) as UniqueAction by "Source User Name" | where NOT $Source User Name$=$Destination User Name$

[caliburn7]

Thank you, this helped!

[sundareshr]

Try putting single quotes around your fields. Like below. But what is *$"?

| where NOT ('Source User Name'='ANONYMOUS LOGON' OR 'Source User Name'="*$") AND Name!="A user account was changed."   | stats dc(Name) as UniqueActionCount, values(Name) as UniqueAction by "Source User Name" | where NOT 'Source User Name'='Destination User Name'

[caliburn7]

username=*$ was to filter all usernames that end with $, which are system authentications, and when not an actual user is present.

[somesoni2]

Issue 1: After your stats there is no field called "Destination User Name", so even if the where syntax was correct, it will not give you any result
Issue 2. In where clause, fields names should be enclosed in single quotes if they contain spaces/dots etc.

[caliburn7]

Thank you, this helped 🙂