topic Re: Comparing two files and output the difference for one column in Splunk Search

Comparing two files and output the difference for one column

Derben — Thu, 08 Feb 2018 16:15:47 GMT

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?

Re: Comparing two files and output the difference for one column

elliotproebstel — Thu, 08 Feb 2018 16:21:53 GMT

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?

Re: Comparing two files and output the difference for one column

493669 — Thu, 08 Feb 2018 16:22:07 GMT

here how result Pass has been calculated?

Re: Comparing two files and output the difference for one column

elliotproebstel — Thu, 08 Feb 2018 16:23:49 GMT

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

Re: Comparing two files and output the difference for one column

kamlesh_vaghela — Thu, 08 Feb 2018 16:35:06 GMT

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

Re: Comparing two files and output the difference for one column

Derben — Fri, 09 Feb 2018 14:34:58 GMT

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?

Re: Comparing two files and output the difference for one column

493669 — Fri, 09 Feb 2018 14:38:35 GMT

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

Re: Comparing two files and output the difference for one column

elliotproebstel — Fri, 09 Feb 2018 14:40:10 GMT

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.

Re: Comparing two files and output the difference for one column

Derben — Fri, 09 Feb 2018 15:30:32 GMT

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

Re: Comparing two files and output the difference for one column

elliotproebstel — Fri, 09 Feb 2018 16:54:13 GMT

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:

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:

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.

Re: Comparing two files and output the difference for one column

elliotproebstel — Fri, 09 Feb 2018 16:54:53 GMT

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