use lookup file inside if statement

[Sharzi]

Hi, 

I'm trying to use a lookup file inside an if statement, and it doesn't return any data. I would appreciate it if anyone could help me. Thanks!

The lookup file has 4 columns (TenantName, tenantId, Region, DB) and my base search is returning 5 columns (_time, TenantName, tenantId, Region, Status). I need to find the database name (or DB) for each record, and it should be done by using tenantId in base search wherever tenantId is not "Unknown".

<base search>
| table _time TenantName tenantId Region Status
| eval Database=if(tenantId!="Unknown", 
[| inputlookup myLookup
| where tenantId=tenantId
| return $DB],
[| inputlookup myLookup
| where TenantName=TenantName
| return $DB])

[nmohammed]

@Sharzi try this to return records where DB is NOT "unknown"

base_search  [ inputlookup myLookup | fields TenantName, tenantId | where tenantId!="Unknown" AND TenantName=TenantName] | lookup myLookup tenantId TenantName output TenantName, tenantId, Region, DB |
| table _time TenantName tenantId Region Status DB

updated reply to correct spellings and missing fields