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

[Mr_Adate]

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.

[Mr_Adate]

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?

[gcusello]

Hi @Mr_Adate ,

sorry I forgot a field, please try this:

| inputlookup ABC.csv 
| eval lookup="ABC.csv"
| fields Firewall_Name lookup
| append [ | inputlookup  XYZ.csv | eval lookup="XYZ.csv" | rename  Firewall_Hostname AS Firewall_Name | fields Firewall_Name lookup ]
| chart count OVER lookup BY Firewall_Name

Ciao.

Giuseppe

[Mr_Adate]

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.

[yuanliu]

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.

| inputlookup ABC.csv 
| eval lookup="ABC.csv"
| fields Firewall_Name lookup
| append [ | inputlookup  XYZ.csv | eval lookup="XYZ.csv" | rename  Firewall_Hostname AS Firewall_Name | fields Firewall_Name lookup ]
| stats values(lookup) as lookup by Firewall_Name
| eval lookup = if(mvcount(lookup) > 1, mvjoin(lookup, " + "), lookup . " only")
| stats count by lookup

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

| inputlookup ABC.csv 
| eval lookup="ABC.csv"
| fields Firewall_Name lookup
| append [ | inputlookup  XYZ.csv | eval lookup="XYZ.csv" | rename  Firewall_Hostname AS Firewall_Name | fields Firewall_Name lookup ]
| stats values(lookup) as lookup by Firewall_Name
| eval lookup = case(mvcount(lookup) > 1, mvjoin(lookup, " + "), lookup == "XYZ.csv", lookup . " only", true(), null())
| stats count by lookup

[Mr_Adate]

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.

[yuanliu · Accepted solution]

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

| inputlookup ABC.csv 
| eval lookup="ABC.csv"
| fields Firewall_Name lookup
| append [ | inputlookup  XYZ.csv | eval lookup="XYZ.csv" | rename  Firewall_Hostname AS Firewall_Name | fields Firewall_Name lookup ]
| stats values(lookup) as lookup by Firewall_Name
| eval lookup = case(mvcount(lookup) > 1, mvjoin(lookup, " + "), lookup == "XYZ.csv", lookup . " only", true(), null())
| stats count values(Firewall_Name) as Firewall by lookup
| eval Firewall = if(lookup == "ABC.csv + XYZ.csv", null(), lookup)

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

[Mr_Adate]

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 ?

[yuanliu]

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.

| inputlookup ABC.csv 
| eval lookup="ABC.csv"
| fields Firewall_Name lookup
| append [ | inputlookup  XYZ.csv | eval lookup="XYZ.csv" | rename  Firewall_Hostname AS Firewall_Name | fields Firewall_Name lookup ]
| stats values(lookup) as lookup by Firewall_Name
| eval lookup = if(mvcount(lookup) > 1, mvjoin(lookup, " + "), lookup . " only")
| stats count values(Firewall_Name) as Firewall by lookup
| eval Firewall = if(lookup == "ABC.csv + XYZ.csv", null(), lookup)

[gcusello]

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:

| inputlookup ABC.csv 
| eval lookup="ABC.csv"
| fields Firewall_Name
| append [ | inputlookup  XYZ.csv | eval lookup="XYZ.csv" | rename  Firewall_Hostname AS Firewall_Name | fields Firewall_Name]
| chart count OVER lookup BY Firewall_Name

Ciao.

Giuseppe