topic How to compare matching values and their count from two fields from two lookup files in Splunk Search

How to compare matching values and their count from two fields from two lookup files

Mr_Adate — Wed, 06 Dec 2023 07:53:02 GMT

Hello Friends,

I need your help to find out matching fields values and their total count by comparing from two different lookup files.

| inputlookup ABC.csv | fields Firewall_Name | stats count
| inputlookup XYZ.csv | fields Firewall_Hostname | stats count

My goal is to compare two lookup files by using field name Firewall_Name with Firewall_Hostname and get matching field values count. EX. if in ABC.csv file field name Firewall_Name total count is 1000 and in second lookup file XYZ.csv field name Firewall_Hostname total count is 850 then my result should display all matched values with their count. so I can get confirmation that from file name XYZ.csv all fields are matching with file ABC.csv and all firewalls are up and running with their total matched firewall count 850.

Re: How to compare matching values and their count from two fields from two lookup files

gcusello — Wed, 06 Dec 2023 07:59:03 GMT

Hi @Mr_Adate ,

the solution is the same of my previous answer, you have to rename the fields in the two lookups having the same field name to compare values from the two lookups:

Ciao.

Giuseppe

Re: How to compare matching values and their count from two fields from two lookup files

Mr_Adate — Wed, 06 Dec 2023 09:45:32 GMT

Hi gcusello,

thank you very much for your prompt reply. I appreciate that

I tried with you code but I guess something is wrong with last line code. I am getting 0 result. can you please confirm it again?

Re: How to compare matching values and their count from two fields from two lookup files

gcusello — Wed, 06 Dec 2023 09:56:05 GMT

Hi @Mr_Adate ,

sorry I forgot a field, please try this:

Ciao.

Giuseppe

Re: How to compare matching values and their count from two fields from two lookup files

Mr_Adate — Thu, 07 Dec 2023 08:01:43 GMT

Hi gcusello,

somehow this solution is not working for me. in my first lookup table I have 1000 firewall count and in second lookup file 850 firewall count. I manually checked in spreadsheet by comparing each other and found there is only 1 firewall is not available in first lookup so my solution should be out of 1000 firewall 849 firewall are matching and 1 is not hence it should display like;

Firewall Name which is not matching Count of FW which is not matching Count of FW which is matching

ABCDFW 1
all reaming firewalls 849

hope you understand my requirement now.

Re: How to compare matching values and their count from two fields from two lookup files

yuanliu — Thu, 07 Dec 2023 09:43:25 GMT

In other words, you only want count of matching records as well as count of non-matching records. Using a similar aggregation as @gcusello provides, but just do counts.

In the scenario you described, you will get a table like

lookup	count
ABC.csv only	150
ABC.csv + XYZ.csv	849
XYZ.csv only	1

If you don't care about ABC.csv only, you can eliminate in the if function, like

Re: How to compare matching values and their count from two fields from two lookup files

Mr_Adate — Thu, 07 Dec 2023 10:18:26 GMT

Thank you very much yuanliu,

now only one task is pending is what if I want to see name of the firewall should be display there from fields either firewall_name or firewall_hostname which are not matching along with their count on single search. Please help me with that also.

Re: How to compare matching values and their count from two fields from two lookup files

yuanliu — Fri, 08 Dec 2023 06:53:36 GMT

That would be as easy as add values to the stats.

Even though the above removes matching firewall names, you still want to consider how practical it is to show all non-matching names.

Re: How to compare matching values and their count from two fields from two lookup files

Mr_Adate — Wed, 20 Dec 2023 15:16:51 GMT

Hello Yuanliu,

Sorry to bother you again. with your code I am getting values for " ABC.csv + XYZ.csv " and XYZ.csv only file but not getting for ABC.csv only.

can help me to get output for not matching count for ABC.csv only data as well ?

Re: How to compare matching values and their count from two fields from two lookup files

yuanliu — Thu, 21 Dec 2023 04:00:37 GMT

Somewhere I read/interpreted that you only wanted those from XYZ.csv and discard the ABC.csv-only ones. To preserve all, use the following.