comparing fields to find identical values

[aaronzabell]

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.

[Mr_Adate]

Hi @ gcusello,

I have similar problem with my use case. I am looking to filter out from two lookup files. I am not using any index. can you help me with to compare filed values from two difference lookup files?

below are the sample data from two lookup file

1.Firewall_NEW_Database.csv o
Hostname Location Datacenter
ABCD           US             xyz
LMNO     SING         ABC

2,Firewall_OLD_Database.csv. 
Firewall Location Datacenter 
LMNO     SING         ABC
ABCD       US         xyz
EFGH        CAN      PQR

in above two lookups I want to compare similar values  base on Hostname and Firewall fields and filter it out with count.

[gcusello]

Hi @Mr_Adate ,

it isn't a good idea to add a new post, even if on the same topic, to an existing question (especially when closed!) because it's more difficoult to have an answer, so, next time open a new question.

Anyway, what do you mean with "compare": do you want to filter the rows of one lookup with the ones of the second?

or do you want to know if a value is present in both the lookups or in one of them?

if the first, please try something like this:

| inputlookup Firewall_NEW_Database.csv WHERE [ | inputlookup Firewall_OLD_Database.csv | rename Firewall AS Hostname | fields Hostname ]
| table Hostname Location Datacenter

if the second, please try:

| inputlookup Firewall_NEW_Database.csv
| eval lookup="Firewall_NEW_Database.csv" 
| append [ 
   | inputlookup Firewall_OLD_Database.csv 
   | rename Firewall AS Hostname 
   | eval lookup="Firewall_OLD_Database.csv" 
   | fields Hostname Location Datacenter lookup ]
| stats 
   values(Location) AS Location
   values(Datacenter) AS Datacenter
   dc(lookup) AS lookup_count
   values(lookup) AS lookup
   BY Hostname 
| eval lookup=if(lookup_count=2, "Both lookups",lookup)
| table Hostname Location Datacenter lookup

Ciao.

Giuseppe

[gcusello]

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

[aaronzabell]

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.

[gcusello]

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

[aaronzabell]

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.

[gcusello · Accepted solution]

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]

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?

[aaronzabell]

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]

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 😉