topic Re: comparing 2 KV files in Splunk Search

How to compare two KV files?

akshaycloud11 — Mon, 05 Jun 2023 15:50:51 GMT

Need to compare 2 KV files and report the missing records of File1 in File2

File 1:

Row#	roll numbers	Name	Registration #
1	5
2	7	Ajay	999
3	13	Kishore	123
4	10
5		Vijay

File2:

Row#	Class	roll numbers	Section	Name	Registration #
1	V	2	A	Aaron	565
2	VI	4	B	Michel	321
3	IV	3	D	Jeff	678
4	VIII	7	E	Ajay	999
5	X	8	H	Kumar	767
6	XII	10	F		098
7	XI	12	N	Evan	345

now, I want to compare the following columns:
Roll numbers(File1) against Roll numbers(File2)
Name(File1) against the Name(File2)
Registration #(File1) against the Registration#(File2)

if we find any one of the record(Roll Numbers/ Name/ Registration #) of File1 in File2 then we will not report that, I shall only report the record which we couldn't find anything by either roll number/name/registration#

Note - We have to compare FILE1 against FILE 2 and report the FILE1 missing records in File2

Desired OUTPUT

Row #	roll numbers	Name	Registration #
1	5
2	13	Kishore	123
3		Vijay

Total 3 records not found (present in File1 and not present in File2)

Re: comparing 2 KV files

fredclown — Mon, 05 Jun 2023 15:32:24 GMT

How about something like this? I took your data and saved it into csv lookups called file1.csv and file2.csv. Also, based on your example data I believe you should have four results returned because the line with "roll numbers" set to 10 does not have a matching "Registration #".

| inputlookup file1.csv | lookup file2.csv "roll numbers" as "roll numbers", Name as Name, "Registration #" as "Registration #" OUTPUT "Row#" as row_num | search NOT row_num=* | streamstats count as "Row#" | table Row# "roll numbers" Name "Registration #"

Re: comparing 2 KV files

fredclown — Wed, 07 Jun 2023 15:33:16 GMT

Did this get what you want? If so, please accept the solution. If not, let me know and I'll try and fix whatever isn't right in it. Thanks.