lookup Compare value for 2 different Columns

[raomu]

This is my search -

| metadata type=hosts 
| table host 
| lookup Device.csv Hostname as host OUTPUT Status
| where (Status="Active")
| table host 

---

What I want is to compare either Hostname OR Ip -- lookup Device.csv Hostname OR ip as host OUTPUT status

[pradeepkumarg · Accepted solution]

What you can do is, use the lookup command twice, one for host and one for ip and store it in different values. Then combine and take the non null value from these two fields into a new field

| metadata type=hosts 

| table host 

| lookup Device.csv Hostname as host OUTPUT Status_host | lookup Device.csv ip as host OUTPUT Status_ip | eval Status=coalesce(Status_host,Status_ip)

| where (Status="Active")

| table host 

[somesoni2]

Another variation of this could be this

| metadata type=hosts 
| table host 
| lookup Device.csv Hostname as host OUTPUT Status | lookup Device.csv ip as host OUTPUTNEW Status 
| where (Status="Active")
| table host

The OUTPUTNEW in the lookup command will ensure that lookup is done only if first lookup based on Hostname returns no Status.