How to display matched and non matched content from lookup

[shashank_24]

Hi,

I have a lookup which contains one column (name - vanity_url) and around 800 rows. Something like this -

vanity_url

/checkout

/your-details

/billing

My Splunk logs has the event related to these rows in a field called requested_content. Some of them are present in the logs and some are not. I want to print the matched and non matched values from the lookup in a table. Something like this -

requested_content	present
/checkout	yes
/your-details	yes
/billing	yes
/direct-debit	no

I have tried something like this but it doesn't seem to be working.

index=myapp_pp sourcetype=access_combined GET host="my-server-*"
| eval type="MainIndex" 
| fields requested_content type
| appendpipe  
    [| inputlookup vanity.csv  
    | eval type="lookup" 
    | rename vanity_url as requested_content 
    | fields type requested_content ] 
| stats dc(type) as pot, values(*) AS * by requested_content
| where pot=1 and type="lookup"

@to4kawa 

[to4kawa]

index=myapp_pp sourcetype=access_combined GET host="my-server-*"
| lookup vanity.csv vanity_url as requested_content OUTPUT name
| stats count by requested_content name
| eval present=if(isnull(name),"no","yes")

Hi @shashank_24 

please set Lookup table files
Settings » Lookups » Lookup table files , and try this query.

[shashank_24]

@to4kawa I have tried it but it's not working. Are you saying that I should create a new lookup file with 2 columns vanity_url and name? What will the name column contain?

I already have the lookup created with one column as vanity_url which contains the values same as field requested_content in my search.

I am getting this error - Error in 'lookup' command: Could not find all of the specified destination fields in the lookup table.

Can you advice.

[to4kawa · Accepted solution]

index=myapp_pp sourcetype=access_combined GET host="my-server-*"
| lookup vanity.csv vanity_url as requested_content OUTPUT vanity_url as name
| stats count by requested_content name
| eval present=if(isnull(name),"no","yes")

I see your csv is one column. how about this?

[shashank_24]

@to4kawa yes it worked. I should have tried it. Thanks mate for the help. 🙂

[javiergn]

Hi @shashank_24 , have you tried with append instead of appendpipe?

https://docs.splunk.com/Documentation/Splunk/8.0.4/SearchReference/Appendpipe

https://docs.splunk.com/Documentation/Splunk/8.0.4/SearchReference/Append