Is it possible to use an inputlookup command with an OR statement?

[a212830]

Hi,

I have a query that uses this search to look for hosts that we need to validate:

|tstats count WHERE index=* AND [ |inputlookup testSVB2.csv |fields + host] groupby host, index, sourcetype 

I'd like to expand this, so that it uses additional columns against the host field. I'd have an IP column, and a fully qualified domain name (FQDN) column in the lookup, and then search and compare those to the host field.

I'm guessing that an "OR" statement is the best option, but I don't see any way to do that in this scenario. Does anyone have a suggestion?

[sloshburch]

Don't forget about some commands that might help take this in a different approach:

• Multisearch - to collect data from multiple streams
• Eval's coalesce - a more elegant 'case' structure for null checking

While not perfect, it could allow you to solve this with a more streaming style approach.

[vupham]

you'll want to combine all your fields from your csv into one like :

[| inputlookup testSVB2.csv | eval host=mvappend(host,ip,fqdn) | fields + host | format]

The format at the end might be redundant, but if you do the subsearch on its own without the brackets, the format command allows you to see what the resulting filter is.

It'll probably look like

( ( ( host="*hostname1*" OR host="*ip1*" OR host="*fqdn1*" ) ) ) AND ( ( ( host="*hostname2*" OR host="*ip2*" OR host="*fqdn2*" ) ) ) [AND ...]

you can also modify the format command delimiters to change the behavior of the subsearch results

[a212830]

Thanks. This is great...

Is there a way to validate that a match has occurred against the row from the lookup? Each row contains host, ip (and possibly fqdn). We get lists with dozens of servers/ip's and while this search is great, it would help if I could easily identify that a match was made (or wasn't made) against the row.

[vupham]

I'm not sure if there is an elegant way to do that, especially when you're dealing with different columns. Maybe you can start by expanding the csv so that you have the hostname, ip, and fqdns on separate rows with some column to indicate what row they were in. do the join, and then recombine the rows afterwards.

This might get you halfway there.
|inputlookup testSVB2.csv | streamstats count as row | eval host=mvappend(host,ip,fqdn) | fields - ip fqdn | mvexpand host | join type=left host [|tstats count WHERE index=* AND [ | inputlookup testSVB2.csv | eval host=mvappend(host,ip,fqdn) | fields + host | format ] by host, index, sourcetype]

[HiroshiSatoh]

I think that it should convert it like this sample and use it.

ex.
    inputlookup testSVB2.csv | rex field=host "^(?<host>[^\.]+)|fields + host"