Splunk Search

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

abbam
Explorer

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?

0 Karma

cb_usps
Explorer

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
0 Karma

woodcock
Esteemed Legend

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
0 Karma

sundareshr
Legend

Try this

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

abbam
Explorer

afraid nothing, doesnt return any events.

0 Karma

ryanoconnor
Builder

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.

0 Karma

abbam
Explorer

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?

0 Karma

ryanoconnor
Builder

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

0 Karma

ryanoconnor
Builder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...