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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...