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?
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.
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
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
here how result Pass has been calculated?
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?
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?
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.
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 ????
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. 🙂
Could you provide what query you have tried?
As below answer seems to be working...