How to search all fields from a lookup CSV file in index event log

[m4jk3l]

Hello Splunk members!

I have a CSV Lookup file with 2 columns

ClientName

HWDetSystem

BD-K-027EY     VMware

I have an index with ASA Firewall log which I want to search and find events for all the ClientNme in the CSV

234654252.234 %ASA-3-2352552: Certificate was successfully validated. serial number: 1123423SSDDG23442234234DSGSGSGGSSG8, subject name: CN=BD-K-027EY.bl.emea.something.com.

Between the CSV lookup file and event the common is the ClientName and a portion of the subject name.

If I look for successfully and provide a single client name i get the event I want, but I am struggling to look it up for all the clients and make it uniqe.

At the end I just want a list of ClientName for which the even was logged.

thanks

[m4jk3l]

When I extracted the Field from the Event Log and I named it as "ClientName" it started to work.

[ITWhisperer · Accepted solution]

<your index> [| inputlookup <your lookup> | table ClientName] "Certificate was successfully validated"

[m4jk3l]

Thank you for you reply.

For some reasons it doesn't work yet.

index=my_index [| inputlookup blank_clients_test.csv | table ClientName] "Certificate was successfully validated"

 For test purposes in the blank_clients_test.csv I have just put a single ClientName  I get 0 results

When I search for the following:

index=my_index BB-H-282XY "Certificate was successfully validated"

I am getting a match in the event.

What could be wrong? Does the second Column in the lookup table is also included? If, yes then it would not work. I want to exclude the second column in the lookup

HWDetSystem

Thanks!

[ITWhisperer]

It possibly doesn't work if ClientName has not already been extracted as a field. Try it this way

<your index> [| inputlookup <your lookup> | table ClientName | rename ClientName AS search] "Certificate was successfully validated"

[m4jk3l]

I was a bit too quick in accepting your Answer as Solution.

Now it seems that the search is looking just for the first ClientName in the lookup file and ignoring all the rest.

If I put the ClientName that I am sure will be in the event log as first entry it works.

When I put the ClientName as 3rd or 4th Entry in the Column it doesnt find the event.

[ITWhisperer]

What do you mean by 3rd or 4th entry? Are you using multi-value fields, or are the entries all on separate rows in the look up file?

[m4jk3l]

Each ClientName is in a separate row

[ITWhisperer]

In that case, it should work. Please share your search, in case there is something else stopping it from working.

[m4jk3l]

index=my_index [| inputlookup blank_clients.csv | table ClientName | rename ClientName AS search] "Certificate was successfully validated"

If I execute just this code, I get all the ClientName entries:

| inputlookup blank_clients.csv | table ClientName | rename ClientName AS search

[ITWhisperer]

With nearly 19k entries in your lookup table you have probably blown some lime - try splitting up your searches. For example, you could use head and tail to reduce the number of rows returned.

[m4jk3l]

it works 🙂 Thank you!