How to create a true comparison where it matches values, and provides a percentage based on matches that are true?

[Minasdad]

I've been comparing two lookup files, but its more pure arithmetic, where I am trying to implement a true comparison where it looks to match values, and provide a percentage based on which matches were true. So if I have three values in file1, and only two values match in file2, the percentage would equal to 66.6%.

| inputlookup file1
| eventstats dc(Serial Number) as file1_Count
| dedup file1_Count
| inputlookup append=T file2
| eventstats dc(System Serial Number) as file2_Count
| dedup file2
| fields file1_Count,file2_Count
| eval percentage=round('file2_Count'/'file1_Count'*100,2)."%"

[Minasdad]

Looking back at the question its not the result I truly want. I believe this is fairly simple but keeps slipping through my fingers.  File1.csv has a field of "Serial Number" and File2.csv has a field of "System Serial Number", all I want to do is compare and produce the results that do not match.

[richgalloway]

Try this approach. Begin by reading one lookup file and marking the entries as "file1".  Then read in the other lookup file and mark the entries as "file2".  Rename the main column to match the first lookup.  Use the stats command to merge the two result sets together, counting them by Serial Number.  Any entry with a count less than 2 means one of the lookups was missing that value.  The "file1" or "file2" mark will tell you which file has the value so it must be missing from the other one.

| inputlookup file1
| eval src="file1"
| append [ | inputlookup file2
  | eval src="file2"
  | rename "System Serial Number" as "Serial Number"
]
| stats count, values(*) as * by 'Serial Number'
| where count < 2
| table "Serial Number" src

[richgalloway]

Consider writing a custom command in Python that accepts two lookup file names, compares them, and returns the percentage match in an output field.