Limiting result from lookup

[vm_molson]

I am trying to figure out how to include a lookup in my search, but only some records. My current search is below. My company has two issues:

1. We do not log app version anywhere easy to grab, so I need to have this pulled via rex.
2. We manually maintain a list of clients (some are on an old version and we don't populate the "client" field for them) and what host they are on. Some clients have both their application and DB on the same host, so my search below results in some weird duplicates where the displayName is listed twice for a single record in my result set (a field containing two values somehow).

I want the lookup to only include records where the "host_type" is "application", not "db". Here is my search:

`Environments(PRODUCTION)` sourcetype=appservice "updaterecords" AND "version"
| eval host = lower(host)
| lookup clientlist.csv hostname as host, OUTPUT clientcode as clientCode
| eval displayName = IF(client!="",client,clientCode)
| rex field=_raw "version: (?<AppVersion>.*)$"
| eval VMVersion = replace(AppVersion,"release/","")
| eval CaptureDate=strftime(_time,"%Y-%m-%d")
| dedup clientCode
| table displayName,AppVersion,CaptureDate

 I did try including host_type right after "..hostname as host.." and using a |where clause later, but that did not work.

[bowesmana · Accepted solution]

You can do conditional lookup using the eval form of lookup

https://docs.splunk.com/Documentation/Splunk/9.3.2/SearchReference/ConditionalFunctions#lookup.28.26...

| eval LookupResult=if(host_type="application", lookup("clientlist.csv", json_object("hostname", host), json_array("clientcode")), null())

You will get back a field called LookupResult like 

{"clientcode":"abc"}

and you can then extract the value abc from the result.