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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...