Splunk Search

Comparing two files and output the difference for one column

Derben
New Member

Hope you can help !

I have two CSV files: RESULTS1 and RESULTS2

RESULTS1 has two columns

CAR TOTAL
Vauxhall 25
BMW 30
Mercedes 50

RESULTS2 has several columns…

DATE TIME CAR NUMBER
00/00/2018 00:00 Ford 10
00/00/2018 00:00 Vauxhall 20
00/00/2018 00:00 Renault 12
00/00/2018 00:00 BMW 10
00/00/2018 00:00 Mercedes 25

The output I would like to see is… so basically comparing column CAR and outputting the difference...

CAR RESULT
Ford Pass
Renault Pass

Any ideas?

Tags (1)
0 Karma

elliotproebstel
Champion

I'm adding this as an answer so that I can include some screenshots. Based on your comments, it sounds like you don't have these files uploaded as lookup files, which is getting in the way. So let's take it down to basics. Start by ensuring you have two files on your local computer. One is named RESULTS1.csv and the other is named RESULTS2.csv. Now we need to upload those two files into Splunk.

First, go to Settings > Lookups. From the menu that loads, click on "Add New" for Lookup Files, as identified in this screenshot:
alt text

From the next menu, select the destination app for your CSV file. You mentioned you'd created an app for this, so select that app from the dropdown. (I redacted my app name in the screenshot.) Use the "Choose File" button to locate RESULTS1.csv on your computer. In the "Destination filename" field, enter RESULTS1.csv. Here's the menu you'll be using for this:
alt text
Repeat these steps to upload RESULTS2.csv.

Now run this query from within your app:

| inputlookup RESULTS2.csv
| lookup RESULTS1.csv CAR OUTPUT CAR AS car_found
| where isnull(car_found)
| eval RESULT="Pass"
| fields CAR RESULT

If you don't get the results you're seeking, then the lookup files probably are not formatted as you thought. To test that, you can run the following commands:
| inputlookup RESULTS1.csv

and then

| inputlookup RESULTS2.csv

If you still need help after following these instructions, please copy and paste the results of those two inputlookup searches, and we'll get you sorted out.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

hi @Derben ,
can you please provide more details about how you comparing two lookup files which gives you pass/ fail result?
And also your sample search if any..

Thanks

0 Karma

elliotproebstel
Champion

If you're just looking for values of CAR that are present in RESULTS2 but not in RESULTS1:

| inputlookup RESULTS2
| lookup RESULTS1 CAR OUTPUT CAR AS car_found
| where isnull(car_found)
| eval RESULT="Pass"
| fields CAR RESULT

493669
Super Champion

here how result Pass has been calculated?

0 Karma

elliotproebstel
Champion

This will be pretty easy, but first - what determines "PASS"? Is it only if a car is present in RESULTS2 but not in RESULTS1? Or are you actually comparing the NUMBER/TOTAL fields somehow?

0 Karma

Derben
New Member

Basically, just need the difference in the CAR column...

CAR
Ford
Renault

i.e. the two cars missing from RESULTS1

Tried the inputlookup in various guises but keep getting "Invalid argument: CAR" Can't seem to get it to recognize the CAR column in the csv files?

0 Karma

elliotproebstel
Champion

Perhaps you could copy and paste to us the result of the following two queries, run separately:

| inputlookup RESULTS1

and

| inputlookup RESULTS2

This may help us diagnose the problem in the SPL syntax.

0 Karma

Derben
New Member

Mmm, that could be my problem. Created a new App which contains the two .csv files as 'sourcetype'

inputlookup RESULTS1 or 2 doesn't return anything back when run in that App ????

0 Karma

elliotproebstel
Champion

Please see the instructions I posted below as a new answer. Hopefully, we can get you on the same page as we expect, and then the SPL syntax work will be a breeze. 🙂

0 Karma

493669
Super Champion

Could you provide what query you have tried?
As below answer seems to be working...

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 ...