Mark servers RED based on a lookup and events coming in the index

[macadminrohit]

HI Experts,

I am working on a query which checks the events for a server in an index=tool and i am maintaining a lookup which has the list of all servers we have. If there is a server from the lookup which has events in the index, i would want to mark that as RED using eval.

This is the query i have been using, but for some reason i am not able to get it working.

index="tools" sourcetype="netcool_alerts" ALERTKEY="Failed to Connect to Computer" TYPE=1 
| rename NODE as host | rex field=host "ISP(?<location>\d+)(?<hostType>\w)$" 
| stats count as Events_count by host location hostType
| append 
    [| inputlookup host_list.csv 
    | rex field=host "P(?<location>\d+)(?<hostType>\w)$"
    | table host location hostType] 
| eventstats count as occurence_count by location hostType
| fillnull Value=0 Events_count
| where NOT (occurence_count=2 AND Events_count=0) 
| eval health=if(occurence_count=1 AND Events_count=1,"RED","GREEN")

I think the requirement is simple, i am not able to get it working 😞