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

[abbam]

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?

[cb_usps]

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

[woodcock]

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

[sundareshr]

Try this

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

[abbam]

afraid nothing, doesnt return any events.

[ryanoconnor]

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.

[abbam]

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?

[ryanoconnor]

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

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

[ryanoconnor]

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