topic Re: Using Lookup files and identifying the changes in each file in Splunk Search

Using Lookup files and identifying the changes in each file

madakkas — Tue, 13 Feb 2018 04:05:55 GMT

Hi All,

I have a question that I am trying to solve …

I have two files which I can upload to be used as inputlookup.csv

Sample as below
tab1.csv
Date Col1 Col2 Col3 Col4 Col5
1-Jan-18 Y N N N N
2-Jan-18 Y N N N N
3-Jan-18 Y Y Y Y Y
4-Jan-18 Y Y Y Y Y
5-Jan-18 Y Y Y Y Y
6-Jan-18 Y Y Y Y Y
7-Jan-18 Y N N N Y
8-Jan-18 Y N N N Y
9-Jan-18 Y Y N Y Y
10-Jan-18 Y Y Y Y Y

tab2.csv

Date Col1 Col2 Col3 Col4 Col5
1-Jan-18 Y N N N N
2-Jan-18 Y N N N N
3-Jan-18 Y Y Y Y Y
4-Jan-18 N Y Y Y Y
5-Jan-18 Y Y Y Y Y
6-Jan-18 Y Y Y Y Y
7-Jan-18 Y N N N Y
8-Jan-18 Y N N N Y
9-Jan-18 Y Y Y Y Y
10-Jan-18 Y Y Y Y Y

I am looking for a way to join these two tables and sort the areas where there is a change as below

Date Col1 Col2 Col3 Col4 Col5 Output
4-Jan-18 N

9-Jan-18 Y

|inputlookup tab1.csv
|lookup tab2.csv Date Col1 Col2.

this gives me a complete set of results along with the mismatched columns. How can i remove all except the mismatched columns.

thank You in advance.

Re: Using Lookup files and identifying the changes in each file

adonio — Tue, 13 Feb 2018 15:55:34 GMT

try this out as a base maybe we can work form there:

 | inputlookup tab2.csv
    | search NOT [| inputlookup tab1.csv ]

this will give you the entire line in which there was a change
hope it helps a little

Re: Using Lookup files and identifying the changes in each file

madakkas — Wed, 14 Feb 2018 02:33:33 GMT

it just gave me the entire tab2.csv, not the differences in them .

Re: Using Lookup files and identifying the changes in each file

adonio — Wed, 14 Feb 2018 03:54:53 GMT

@madakkas,
copied your sample to the letter. here is a screenshot.
can you double check

Re: Using Lookup files and identifying the changes in each file

madakkas — Wed, 14 Feb 2018 04:05:53 GMT

Sorry mate , I had the dates in two different formats and hence the whole differences where thrown up . It did get the work moving, but is there a way we can identify the column which had the change ?

Re: Using Lookup files and identifying the changes in each file

adonio — Wed, 14 Feb 2018 11:10:59 GMT

i am puzzled by it as well. trying to come up with a solution and will post it here once i figure it out

Re: Using Lookup files and identifying the changes in each file

madakkas — Tue, 29 Sep 2020 18:03:31 GMT

I did something as below , though it is a bit complicated. Will as well wait for your advise if you have any simpler thoughts.

Kind of created a macro as below

and then will have to do an append to call the macro multiple times with the number of columns available.