Splunk Search

How to exclude duplicate field values from different fields

MikeElliott
Communicator

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?

Tags (2)
0 Karma
1 Solution

micahkemp
Champion

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.

View solution in original post

0 Karma

micahkemp
Champion

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.

0 Karma

MikeElliott
Communicator

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!

0 Karma

p_gurav
Champion

Hi MikeElliott,

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

0 Karma

mayurr98
Super Champion

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 !

0 Karma

MikeElliott
Communicator

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.

0 Karma

micahkemp
Champion

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

0 Karma

MikeElliott
Communicator

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

0 Karma

micahkemp
Champion

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

0 Karma

MikeElliott
Communicator

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.

0 Karma

mayurr98
Super Champion

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

0 Karma

micahkemp
Champion

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...