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?
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
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
Try this
index=data | lookup test.csv domain_name OUTPUT Reference | where isnotnull(Reference) | stats count by Reference
afraid nothing, doesnt return any events.
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.
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?
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.
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
@abbam were you able to verify if the lookup definition is configured?