Error 'Could not find all of the specified lookup fields in the lookup table.'

[papemalik]

Hello,
i have this issue:
Error 'Could not find all of the specified lookup fields in the lookup table.' for conf 'access_combined_wcookie' and lookup table 'malwaredomainlist'.
Error 'Could not find all of the specified lookup fields in the lookup table.' for conf 'source::access.log.10|host::127.0.0.1|access_combined_wcookie' and lookup table 'malwaredomainlist'.

I'm comparing access logs and a list of malware domain.
- I have tried putting a dummy column in 1st position, but no luck
- I have check the encoding of the excel file and changed it to US ASCII, but no luck, even UTF-8, still the same results
- In the search field my command is: index=* sourcetype=access_combined_wcookie

I really need help on this one.

Thank you

[tormodbp · Accepted solution]

Splunk is able to import any text-based formats, but Excel files with extensions like .xls og .xlsx are not text-based. This means that you cant read the Excel files directly in Splunk, but you have to convert it to CSV. (I might be incorrect here, but I cant find any information about Splunk starting to support Excel files.)

In addition you would have to extend your search string to include som kind of lookup-query.

There was a "guide" for something similar in the Splunk blog a few years back. It might help you out.
http://blogs.splunk.com/2015/01/30/working-with-spreadsheets-in-splunk-excel-csv-files/

Cheers,

[papemalik]

Thank you.

yeah i converted into CSV.

I was just trying to work on the search command, i'm guessing that's what i got wrong.
So i would have something like this:

sourcetype=access_* | stats count by host | lookup Domain as referer_domain

[tormodbp]

The documentation for lookup can be found here:
http://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/Lookup

A quick extract of the syntax you need looks like this:
... | lookup ( [AS ]) [OUTPUT | OUTPUTNEW ( [AS ])

For more information on CSV and external lookups, see http://docs.splunk.com/Documentation/Splunk/6.4.2/Knowledge/Addfieldsfromexternaldatasources

Cheers,

[papemalik]

I follow the tutorial with the http_status.csv

I created the file, respected the encoding, did the the 3 steps in lookup parameters

my command search:
sourcetype=access_* | lookup http_status status as status OUTPUTNEW status_description as description

results:
Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table.

Don't know what am i missing!!!!!

I don't understand these two: [local=] [update=], can you enlighten these for me please?

Thank you very much for taking the time to help, i really appreciate it,

We will get through it (eventually) lol

[tormodbp]

What fields do you have in the sourcetype?

The two parameters localand updateare optional. You do not need them for the CSV http_status tutorial.

[local=] specifies if you wan to run the lookup on the search head in stead of where you specified that the file is located.
[update=] is used if the CSV is updated continuously or in real time, thus requiring a real-time search to include all changes that occur while the search is running. Update would then make Splunk account for the updates and automatically reflect the updates.

You could try to make sure you can access the file by using inputlookup. If this is successful then you know that you are able to read from the lookup.

| inputlookup http_status 

[papemalik]

It's an access log, i have fields such as IP, status, domain, referer_domain (basically the same as Domain), domain country, bytes etc.

Ok thank you for the explanation, i understand now.

yes, Inputlookup is successful

[tormodbp]

I don't know if it matters, but i generally write AS in capital.

You could also try to specify the fields for the CSV-file in the transforms.conf using the syntax

[http_status]
....
fields_list = <field1>, <field2> ..

other than that I'm not really sure. Can't really find anything wrong with the search command. If you followed the tutorial completely this should work.

Sorry for not being able to help you

[papemalik]

No AS didn't change much.

specify the fields in the command search.

Oh no, it's ok. i really appreciated the effort

[sjaworski]

Can you share your search? Sanitize what you need to for security.

[papemalik]

Share what exactly?
I need to be able to detect people that are trying to connect to suspicious domain.
The plan is to be able to detect suspicious activity in a company. the malwaredomainlist is just one part of the search