Splunk Search

Using Lookup files and identifying the changes in each file

madakkas
Explorer

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.

Tags (3)
0 Karma
1 Solution

adonio
Ultra Champion

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

View solution in original post

0 Karma

adonio
Ultra Champion

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

0 Karma

madakkas
Explorer

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 ?

0 Karma

adonio
Ultra Champion

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

0 Karma

madakkas
Explorer

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

|inputlookup tab1.csv| append [inputlookup tab2.csv]
|table Date $col_num$
|chart count over Date by $col_num$ |eval col_name = "$col_num$" |where N = 1 or Y = 1
|table Date col_name

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

0 Karma

adonio
Ultra Champion

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

0 Karma

madakkas
Explorer

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...