How to match fields from logs with CSV and create an alert?

[ap666]

I get logs from a system which has a field that contains names. Lets say Abc.xyz is the name of the field. I have a list of names in a CVS where there are 3 columns: id,name,description. I have already created lookup table files and definitions.

Can someone help me with a query to setup a search query to alert every time any name from test.csv file matches with the names from Abc.xyz field from the logs.

[ap666]

That's perfect. Thank you! Both work but the last one is faster for me. One more thing though, how can I make it so that if Abc.txt contains that name in any form? For example: if I am trying to match it to the name "alan" from the CSV file, and the Abc.txt holds a value "123alanggg" I still want it to match in this case.

[bowesmana]

To match wildacards in lookups, you first need to make a lookup "definition" based on your test.csv lookup file. In the match type field, add WILDCARD(name) and in each of the name values add * characters where you want wildcards to work, so for your example, your lookup would contain *alan*

The lookup for 123alanggg would then work.

As for performance, depending on the size of the lookup and other factors, a subsearch can be faster or slower, so it's always useful to evaluate this. e.g. a very large lookup file as a subsearch will generate this type of structure to the main search, which can slow things down

(A = 1 OR A = 2 OR A = 3 OR A = 4... A = 999999999999)

[bowesmana]

If your Abc.xyz is a multivalue field then just do

| lookup your_lookup.csv name as Abc.xyz

You will get a multivalue field back with the matching names

If you field is not multivalue, then make it one, e.g. if your names are Abc.xyz="your_name,my_name"

do

| eval Abc.xyz=split('Abc.xyz', ",")

Note the single quotes round the field Abc.zyx in eval statements as it contains a . character.

[ap666]

It did not work.

To make it more clear:

Abc.xyx is not a multi value field.  We can assume one of the logs has its value for the field abc.xyz as "Alan" and the test.csv has a column 'name' that contains "Alan" . I can also make the test.csv file with a single column with just the name in it if that makes it easier.

And, all I need is the search query to display the logs that has the field Abc.xyz value matching the name in test.csv .

[bowesmana · Accepted solution]

So, my first example will do what you want

your_search...
| lookup test.csv name as Abc.xyz 

If you want to then remove events where the name was not found in the lookup, do

| where isnotnull(id)

if id is part of the lookup. If you are just looking to use the names in the lookup as constraints to your search and don't care about ID or description you can do it with a subsearch

your_data_search [ 
  | inputlookup test.csv 
  | fields name 
  | rename name as Abc.xyz
]

then you will only get data back for those names in the CSV. You can still then do a lookup to get id and description using the lookup command at the top