topic How to exclude duplicate field values from different fields in Splunk Search

How to exclude duplicate field values from different fields

MikeElliott — Tue, 29 Sep 2020 17:35:20 GMT

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?

Re: How to exclude duplicate field values from different fields

micahkemp — Tue, 09 Jan 2018 04:15:00 GMT

| 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?

Re: How to exclude duplicate field values from different fields

mayurr98 — Tue, 09 Jan 2018 04:36:51 GMT

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 !

Re: How to exclude duplicate field values from different fields

MikeElliott — Tue, 09 Jan 2018 05:16:19 GMT

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.

Re: How to exclude duplicate field values from different fields

micahkemp — Tue, 29 Sep 2020 17:31:12 GMT

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

Re: How to exclude duplicate field values from different fields

MikeElliott — Tue, 09 Jan 2018 05:23:13 GMT

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.

Re: How to exclude duplicate field values from different fields

micahkemp — Tue, 09 Jan 2018 05:24:24 GMT

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

Re: How to exclude duplicate field values from different fields

p_gurav — Tue, 09 Jan 2018 05:27:53 GMT

Hi MikeElliott,

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

Re: How to exclude duplicate field values from different fields

MikeElliott — Tue, 09 Jan 2018 05:33:59 GMT

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

Re: How to exclude duplicate field values from different fields

mayurr98 — Tue, 09 Jan 2018 05:35:38 GMT

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

Re: How to exclude duplicate field values from different fields

micahkemp — Tue, 29 Sep 2020 17:31:15 GMT

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.

Re: How to exclude duplicate field values from different fields

MikeElliott — Tue, 13 Mar 2018 17:17:23 GMT

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!