How to exclude duplicate field values from different fields

[MikeElliott]

Hi All,

I am writing a search string for Windows, which should return events where a privileged user (Source_User) has added a non-privileged (Target_User) user to a privileged group, or has assigned new privileges to this account.

When running my search, I am receiving a number of events where the Source_User and Target_User values are the same (E.g. Privileges assigned at logon for a service account).

I would like to remove duplicate values from my search (I.e. Source_User!=Target_User). I have attempted what I'd consider to be the usual suspects (listed below), but am getting no where.

| where Source_User!=Target_User

| search Source_User!=Target_User

Can anyone suggest other ways to do this?

[micahkemp · Accepted solution]

It seems likely that you have run a search like:

<search>
| stats values(Source_User) AS Source_User, values(Target_User) AS Target_User
| where Source_User!=Target_User

If this is the case, try this instead:

<search>
| where Source_User!=Target_User
| stats values(Source_User) AS Source_User, values(Target_User) AS Target_User

You are comparing a single event's Source_User and Target_User field, so you need to make sure you perform that comparison prior to running a reporting command.

[MikeElliott]

Hi micahkemp,

Apologies for leaving this so late, but after tweaking my search slightly, I found that your solution was the one for me!

Thank you!

[p_gurav]

Hi MikeElliott,

could you please share what search you are running with some sample data?

[mayurr98]

hey

I think there might be a problem of case sensitivity.

<your_base_query>| eval Source_User=lower(Source_User) | eval Target_User=lower(Target_User) | where Source_User!=Target_User

I hope that helps !

[MikeElliott]

Hi mayurr98,

Thank you for your help. Unfortunately, when adding your suggestions to the search, all results have been excluded.

I tried renaming the fields to use lower case characters and then using the |where command, but still, all results were excluded.

[micahkemp]

Are the Source_User and Target_User values exact matches? Does one field use domain\user and the other just user, for instance?

[MikeElliott]

Yes, the field values are exact matches - Just the usernames. domain\user comes under a different field in this index.

[micahkemp]

| where Source_User!=Target_User should work, as shown by this run-anywhere search:

| makeresults | eval Source_User="user1", Target_User="user1"
| append [| makeresults | eval Source_User="user1", Target_User="user2"]
| where Source_User!=Target_User

Can you include some sample data that doesn't work as expected?

[MikeElliott]

Hi miachkemp,

Many thanks for your suggestion. I have included example data below.

Source_User      Target_User
Admin_001        Admin_001
Admin_001        Admin_001
Admin_001        User_001
Admin_002        User_002
Admin_001        User_003
svc_account      svc_account
Admin_003        User_004
User_004              some_account

I would like to be able to exclude events where there is a duplicate account under "Source User" and "Target User" headings.

[mayurr98]

Also can you share output table wrt input table that you have given?

[micahkemp]

Is this a single event with multiple values per field? Or is each line above a separate event?