Splunk Search

How to get where function to work with inputlookup?

kmattern
Builder

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.

0 Karma
1 Solution

guilmxm
SplunkTrust
SplunkTrust

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 ?

View solution in original post

guilmxm
SplunkTrust
SplunkTrust

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
Builder

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.

0 Karma

guilmxm
SplunkTrust
SplunkTrust

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"]

0 Karma

guilmxm
SplunkTrust
SplunkTrust

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 ?

0 Karma

kmattern
Builder

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...