Can you help me integrate a lookup into a search?

[donemery]

I am trying to integrate a lookup into a search with no success. My goal is to run the search, lookup the hostname or TID and compare it to the lookup table (HOSTNAME field) and return results based on matches to the ROLE field. The ROLE column in the lookup that defines whether TID is core, cpe or aggregation. Here is my search:

index=my_main "SFP receive power low  alarm set" 
| stats dc(date_mday) as Days, count by TID, J_Port | eval c_TID=upper(c_TID) 
| where Days > 2 AND count > 49 | appendpipe [stats count | where count=0]
| rename count as "Total Errors", TID as Hostname, J_Port as Port
| sort -"Total Errors"

The lookup table file and definition are working. My difficulty is integrating it into my search.

[Vijeta]

Place it at the end of the search, and if you still get the error please paste the complete search with lookup and the error message.

[skoelpin · Accepted solution]

I don't see your lookup in the search. It should follow this format

| lookup filename.csv hostname AS host OUTPUT ROLE

[donemery]

I didn't include it in the search because no matter where I try it, I get an error back when it is executed. Any guidance as to the proper placement of the lookup is appreciated.

[skoelpin]

For performance purposes, its always better to have a stats before the lookup. Place it like this

index=my_main "SFP receive power low  alarm set" 
 | stats dc(date_mday) as Days, count by TID, J_Port | eval c_TID=upper(c_TID) 
| lookup filename.csv hostname AS host OUTPUT ROLE
 | where Days > 2 AND count > 49 | appendpipe [stats count | where count=0]
 | rename count as "Total Errors", TID as Hostname, J_Port as Port
 | sort -"Total Errors"

[donemery]

It's working now. Thanks!