I'm having trouble using the lookup module in my search query

Modified on Fri, 17 Nov 2023 at 10:28 AM

Let's take a look at the problem query and discuss

We often get questions about specific queries and although they may be slightly different than yours, you may run into a similar issue and find this information helpful.

The below query is throwing a "lookup (module idx 2) error: Column as not found" error:

tag=syslog-sd* syslog Timestamp Message 
| json -e Message -s l7Proto srcIp name 
| lookup -r pdassets verifiedhost as Hostname 
| count by l7Proto srcIp name 
| eval l7Proto!="" 
| eval srcIp!="" 
| table srcIp l7Proto name count

The lookup module can be a little tough to get used to. Besides the resource specification (-r pdassets) it needs three arguments. The first specifies an *enumerated value* from earlier in the pipeline. The second specifies a column name in the resource; lookup will search through that column until it finds an entry *which matches the value of the first argument*. The third argument is another column name; having found a match, lookup will extract the contents of this column into a new enumerated value.

Looking at your query, it looks like you want to look up hostnames based on the srcIp IP address. Try tweaking the query to something like:

lookup -r pdassets srcIP verifiedip verifiedhost as Hostname

That will try to match srcIP against the verifiedip column, then when it finds a match it'll extract the corresponding value from the verifiedhost column and set it as "Hostname".

If you have a problem query that is leaving you scratching your head please reach out to support@gravwell.io and we'll help you get it sorted out!