How to get where function to work with inputlookup?

[kmattern]

In an attempt to reduce the number of lookup tables we use we have created a master lookup table that has many columns. For the most part the conversion has worked well but in one type of instance it does not and I can't figure out why. Maybe I'm looking at it too hard and long.

This is the original search and it works perfectly.

index="adviis" sourcetype="adviis" earliest=-120d@d latest=-90d [inputlookup KZNG-INVA.csv | fields cs_username] 
| eval timedelta=now()-_time | eval time_delta_days=floor(timedelta/86400) 
| stats first(time_delta_days) as Access by cs_username 
| eval StatA=Access  
| eval StatA=if(StatA<0,"0",StatA)
| rangemap field=StatA Monitor=0-1 Contact=2-9999 
| rename range as Status
| lookup KZNG-INVA.csv cs_username OUTPUT Client 
| eval "Download MBs"="0"
| eval "Upload MBs"="0"
| table cs_username,  Status, Account, "Download MBs", "Upload MBs" 

This is the new search and it consistently returns zero results. I suspect that it may be in the "where" clause but I'm not certain. The servertype can be one of three values while ClientType can be one of four values.

index="adviis" sourcetype="adviis" earliest=-120d@d latest=-90d 
[inputlookup Master.csv | fields cs_username, servertype, ClientType | where servertype="INVA" AND ClientType="NG"] 
| eval timedelta=now()-_time | eval time_delta_days=floor(timedelta/86400) 
| stats first(time_delta_days) as Access by cs_username 
| eval StatA=Access  
| eval StatA=if(StatA<0,"0",StatA)
| rangemap field=StatA Monitor=0-1 Contact=2-9999 
| rename range as Status
| lookup Master.csv cs_username OUTPUT Client 
| eval "Download MBs"="0"
| eval "Upload MBs"="0"
| table cs_username,  Status, Account, "Download MBs", "Upload MBs" 

Any suggestions are greatly appreciated.

BTW: If I just run a search composed only of the inputlookup clause including the where function I get a list of records associated only with INVA and NG.

[guilmxm · Accepted solution]

Hi, When using inputlookup you should use "search" instead of where, in my experience i had various trouble using where command within inputlookup, but search always worked as expected.

Your subsearch is in the first pipline, ensure your inputlookup search returns fields or you will never get any results, simplify your request for testing purposes to the first pipeline only before trying your full search

Are your fields INVA and NG existing fields in your index ?

[kmattern]

You are correct and I just wasn't seeing it. there are not fields named servertype or ClientType in the sourcetype data. My intent was to get all the cs_username values from the lookup where servertype="INVA" AND ClientType="NG"

I just have to rethink my strategy.

[guilmxm]

If your inputlookup search returns fields (inputlookup Master.csv | fields cs_username, servertype, ClientType | where servertype="INVA" AND ClientType="NG")
you can try:
index="adviis" sourcetype="adviis" earliest=-120d@d latest=-90d | search [inputlookup Master.csv | fields cs_username, servertype, ClientType | where servertype="INVA" AND ClientType="NG"]

[guilmxm]

The first search referenced does not have any where statement in the subsearch with your inputlookup.

Does your first pipeline:

index="adviis" sourcetype="adviis" earliest=-120d@d latest=-90d
[inputlookup Master.csv | fields cs_username, servertype, ClientType | where servertype="INVA" AND ClientType="NG"]

returns events ?

[kmattern]

Been there, done that. Search doesn't work any better. You will note that the first search referenced above works as expected. Yes, all the fields are present in the lookups - no the index.