Lookup search query field comparison and output

[pm2012]

Hi Team,

I would like to achieve something similar to below

1- I have a csv lookup table name - customer-devices.csv having below two columns 

hostname	DeviceType
hostname1	Cisco
hostname2	Cisco
hostname3	Cisco

2- I am searching events having above hostname field for past 24 hr. My requirement it should print all hostnames in the output result which are there in the lookup and if those hostname are also in the search mark them Active if those are not there in search mark them Not Active.

Like it print all three hostname which is there in lookup having status Active and Non Active basis on its availablility in the search log

hostname	DeviceType	Status
hostname1	Cisco	Active
hostname2	Cisco	Active
hostname3	Cisco	Not Active

[gcusello · Accepted solution]

Hi @pm2012 ,

I suppose that you have the hostname field also in the main search, if not, you have to renabme that field.

So if you want only the logs from hostnames that are in the lookup, you could try somethng like this:

<your_search> [ | inputlookup customer_devices.csv | fields hostname ]
| eval hostname=lower(hostname)
| stats count BY hostname
| append [ | inputlookup customer_devices.csv | eval hostname=lower(hostname), count=0 | fields hostname DeviceType count ]
| stats sum(count) AS total values(DeviceType) AS DeviceType BY hostname
| eval Status=if(total=0, "Non Active", "Active)

 If instead you want to check also new hostnames that aren't in the lookup, you could try:

<your_search>
| eval hostname=lower(hostname)
| stats count BY hostname
| append [ | inputlookup customer_devices.csv | eval hostname=lower(hostname), count=0 | fields hostname DeviceType count ]
| stats sum(count) AS total values(DeviceType) AS DeviceType BY hostname
| eval Status=case(NOT DeviceType=*, "New hostname", total=0, "Non Active", total>0, "Active)

Ciao.

Giuseppe