topic Re: How to match a fieldA from an index to fieldA in a CSV lookup file and output the corresponding fieldB? in Splunk Search

How to match a fieldA from an index to fieldA in a CSV lookup file and output the corresponding fieldB?

abbam — Thu, 09 Jun 2016 08:19:16 GMT

Hi guys,

Wondering if anyone can help me and if this can be done.

I have a CSV file with two columns.

CSV file looks like this:

domain_name           Reference
abc.com               ABC
bbc.co.uk             BBC

In my index, I have a field called domain_name in which I can run the following search on:

index=data [|inputlookup test.csv | fields domain_name]

The above search works, however, I want to also reference that domain_name to the Reference column in the CSV file. This is where the problem starts because there isn't a Reference field in the index=data, so the search fails to find any results.

My end goal is to get a number of References against the domain_names so it looks like this:

Reference      count
ABC              150
BBC               25

Can this be done at all?

Re: How to match a fieldA from an index to fieldA in a CSV lookup file and output the corresponding fieldB?

ryanoconnor — Tue, 29 Sep 2020 09:55:30 GMT

Is there a domain_name field inside of index=data? If so you might want to use the lookup command (http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Lookup) instead of inputlookup. That way you can lookup the domain_name field of each event and return the Reference and then apply some stats commands on that.

Re: How to match a fieldA from an index to fieldA in a CSV lookup file and output the corresponding fieldB?

abbam — Thu, 09 Jun 2016 10:53:41 GMT

Yes domain_name exists, Reference doesn't... however, I tried to butcher something together and it didn't work.

how would something like this be written?

Re: How to match a fieldA from an index to fieldA in a CSV lookup file and output the corresponding fieldB?

sundareshr — Thu, 09 Jun 2016 11:25:36 GMT

Try this

index=data | lookup test.csv domain_name OUTPUT Reference | where isnotnull(Reference) | stats count by Reference

Re: How to match a fieldA from an index to fieldA in a CSV lookup file and output the corresponding fieldB?

abbam — Thu, 09 Jun 2016 12:18:32 GMT

afraid nothing, doesnt return any events.

Re: How to match a fieldA from an index to fieldA in a CSV lookup file and output the corresponding fieldB?

ryanoconnor — Tue, 29 Sep 2020 09:55:54 GMT

You've uploaded the Lookup Table, have you also defined a Lookup Definition? You'll need to do that to use it in a search.

http://docs.splunk.com/Documentation/Splunk/6.4.1/Knowledge/Usefieldlookupstoaddinformationtoyourevents#Edit_existing_lookup_definitions_or_define_a_new_file-based_or_external_lookup

The following is basic but should work provided you have the correct permissions to use the lookup.

index=data | lookup test.csv domain_name OUTPUT Reference | table domain_name Reference | stats count by Reference

Re: How to match a fieldA from an index to fieldA in a CSV lookup file and output the corresponding fieldB?

woodcock — Mon, 13 Jun 2016 16:17:42 GMT

The "right" way to do it is to setup the CSV as a lookup table. But let's look at doing it directly without doing that. Assume your file is called test.csv and try this:

index=data | eval dataset="nonCSV"
| appendpipe  [|inputlookup test.csv | eval dataset="CSV" ]
| stats values(*) AS * BY domain_name
| stast count BY Reference

Re: How to match a fieldA from an index to fieldA in a CSV lookup file and output the corresponding fieldB?

ryanoconnor — Mon, 13 Jun 2016 17:23:15 GMT

@abbam were you able to verify if the lookup definition is configured?

Re: How to match a fieldA from an index to fieldA in a CSV lookup file and output the corresponding fieldB?

cb_usps — Mon, 13 Jun 2016 18:45:54 GMT

If you setup your lookup table to do automatic lookups, then your Reference field will be a field in each event which has a domain_name.
At that point, you can then write a search similar to this:

 _your_search_here_ | stats count(domain_name) as count by Reference