Splunk Search

comparing fields to find identical values

aaronzabell
Path Finder

I imported a csv into Splunk and now I need to compare two of the fields to find identical values. Compare the values of "Customer_Full_Name" and "User_Full_Name" to find who, if anyone, is both a customer and a user.

I feel like eval should be able to help here but can't think of how to do it.

Once I have that figured out I need to see if there are users looking at the records of customers that happen to also be users but I'll leave that for another question later.

Labels (1)
0 Karma
1 Solution

gcusello
Esteemed Legend

Hi @aaronzabell,

let me understand do you want to:

  • compare two fields in the same events,
  • or check if the values of the field "Customer_Full_Name" is present in the field "User_Full_Name"?

 In the first case my previous answer is correct.

In the second case, you have to use a subsearch:

<your_search> [ search 
     <your_search> 
     | dedup "Customer_Full_Name" 
     | rename "Customer_Full_Name" AS "User_Full_Name" 
     | fields "User_Full_Name" 
     ]
| table _time "Customer_Full_Name"

Remember that there's the limit of 50,000 results on subsearches.

Ciao.

Giuseppe

View solution in original post

gcusello
Esteemed Legend

Hi @aaronzabell,

I think that you want to compare fields in the same event.

In this case you can use the search command

| search "Customer_Full_Name" = "User_Full_Name"

having only the events with this condition.

If instead you want all the events but with highlighted the corrisponding ones you can use eval

| eval check=if("Customer_Full_Name"="User_Full_Name","Yes","Not")

Ciao.

Giuseppe

0 Karma

aaronzabell
Path Finder

The first search function works just fine but not exactly what I'm looking for.
The second option just ends up returning all of the events and doesn't break anything down.
What I want to do, is eliminate from the results all customers that aren't users.

0 Karma

gcusello
Esteemed Legend

Hi @aaronzabell,

as I said the second search tags events that match the condition but it doesn't filter results.

If you want only results that match the condition you have to use the first one.

What do you mean when you say that it isn't exactly what you're looking for?

Ciao.

Giuseppe

0 Karma

aaronzabell
Path Finder

If I only want the events where they look up their own record I can use this

 

index=customers | where Customer_Full_Name=User_Full_Name

 

Which is similar to the eval you posted, just without tags.

 

| search "Customer_Full_Name" = "User_Full_Name"

 

returns no results at all.

The search needs to take the value of Customer_Full_Name in a single event and compare it with the results of User_Full_Name in every event to see if it matches then return only those results. I'm hoping that I'm making at least some sense.

0 Karma

gcusello
Esteemed Legend

Hi @aaronzabell,

let me understand do you want to:

  • compare two fields in the same events,
  • or check if the values of the field "Customer_Full_Name" is present in the field "User_Full_Name"?

 In the first case my previous answer is correct.

In the second case, you have to use a subsearch:

<your_search> [ search 
     <your_search> 
     | dedup "Customer_Full_Name" 
     | rename "Customer_Full_Name" AS "User_Full_Name" 
     | fields "User_Full_Name" 
     ]
| table _time "Customer_Full_Name"

Remember that there's the limit of 50,000 results on subsearches.

Ciao.

Giuseppe

aaronzabell
Path Finder

So here's my modification on what you provided

index=import [ search 
     index=import 
     | dedup Customer_Full_Name 
     | rename Customer_Full_Name AS User_Full_Name 
     | fields User_Full_Name 
     ]
| table _time User_Full_Name Customer_Full_Name

I think it's working as there are much fewer results 1,241 (The total number of events in this index is around 60,000) and only 7 users are listed having accessed records.

If I reverse the field values thus:

index=import [ search 
     index=import 
     | dedup User_Full_Name 
     | rename User_Full_Name AS Customer_Full_Name
     | fields Customer_Full_Name
     ]
| table _time User_Full_Name Customer_Full_Name

My results are different. Shouldn't I get the same results?

0 Karma

aaronzabell
Path Finder

I tried creating a new index and imported a much smaller subset of data that I knew the results for so it was easier to verify the Splunk search results.

This worked:

 

index=easy_c [ search 
     index=easy_c 
     | dedup User_Full_Name 
     | rename User_Full_Name AS Customer_Full_Name
     | fields Customer_Full_Name 
     ]
| table _time User_Full_Name Customer_Full_Name

 

Thank you so much for your help in getting this figured out @gcusello 

gcusello
Esteemed Legend

Hi @aaronzabell,

good for you!

Please, accept the answer for the other poeple of Community.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma
Get Updates on the Splunk Community!

Set Up More Secure Configurations in Splunk Enterprise With Config Assist

This blog post is part 3 of 4 of a series on Splunk Assist. Click the links below to see the other ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Enterprise Security Content Update (ESCU) v3.54.0

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...