topic Re: comparing fields to find identical values in Splunk Search

comparing fields to find identical values

aaronzabell — Tue, 15 Dec 2020 00:45:13 GMT

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.

Re: comparing fields to find identical values

gcusello — Tue, 15 Dec 2020 07:54:03 GMT

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

Re: comparing fields to find identical values

aaronzabell — Tue, 15 Dec 2020 13:51:36 GMT

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.

Re: comparing fields to find identical values

gcusello — Tue, 15 Dec 2020 14:11:30 GMT

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

Re: comparing fields to find identical values

aaronzabell — Tue, 15 Dec 2020 14:44:29 GMT

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.

Re: comparing fields to find identical values

gcusello — Tue, 15 Dec 2020 14:58:34 GMT

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

Re: comparing fields to find identical values

aaronzabell — Tue, 15 Dec 2020 16:05:50 GMT

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?

Re: comparing fields to find identical values

aaronzabell — Tue, 15 Dec 2020 17:19:29 GMT

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

Re: comparing fields to find identical values

gcusello — Tue, 15 Dec 2020 17:21:56 GMT

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 😉

Re: comparing fields to find identical values

Mr_Adate — Tue, 05 Dec 2023 16:58:07 GMT

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.

Re: comparing fields to find identical values

gcusello — Tue, 05 Dec 2023 18:04:20 GMT

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:

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