data and lookup field problem

[secure]

Hi All

i have a csv look up with below data

Event_Code

AUB01

AUB36

BUA12

i want to match it with a dataset which has field name  Event_Code with several values i need to extract the count of the event code per day from the matching csv lookup 

my query

index=abc 
|rex field=data "\|(?<data>[^\.|]+)?\|(?<Event_Code>[^\|]+)?\|"
| lookup dataeventcode.csv Event_Code
| timechart span=1d dc(Event_Code)

however the result is showing all 100 count per day instaed of matching the event code from the CSV and then give the total count per day

[richgalloway]

The query checks the lookup file, but then does nothing with it.  That's why all events are counted.  Try this

index=abc 
|rex field=data "\|(?<data>[^\.|]+)?\|(?<Event_Code>[^\|]+)?\|"
| lookup dataeventcode.csv Event_Code OUTPUT Event_Code as found
| where isnotnull(found)
| timechart span=1d dc(Event_Code)

If the Event_Code field did not need to be extracted via rex then we could have used inputlookup to give Splunk a list of codes to search for.

[gcusello · Accepted solution]

Hi @secure ,

if you want to filter results from main search using the Event_Codes from the lookup, you must use a subsearch:

index=abc 
| rex field=data "\|(?<data>[^\.|]+)?\|(?<Event_Code>[^\|]+)?\|"
| search [ | inputlookup dataeventcode.csv | fields Event_Code ]
| timechart span=1d dc(Event_Code)

If you extract the Event_Code field before the search as a field, you can put the subsearch in the main search.

Ciao.

Giuseppe