topic Re: How to search all fields from a lookup CSV file in index event log in Splunk Search

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

m4jk3l — Thu, 22 Feb 2024 14:30:36 GMT

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

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

ITWhisperer — Thu, 22 Feb 2024 14:39:03 GMT

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

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

m4jk3l — Fri, 23 Feb 2024 09:24:48 GMT

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!

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

ITWhisperer — Fri, 23 Feb 2024 09:29:17 GMT

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"

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

m4jk3l — Fri, 23 Feb 2024 09:31:43 GMT

it works 🙂 Thank you!

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

m4jk3l — Fri, 23 Feb 2024 09:47:03 GMT

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.

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

ITWhisperer — Fri, 23 Feb 2024 09:50:25 GMT

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?

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

m4jk3l — Fri, 23 Feb 2024 10:01:14 GMT

Each ClientName is in a separate row

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

ITWhisperer — Fri, 23 Feb 2024 10:03:15 GMT

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

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

m4jk3l — Fri, 23 Feb 2024 11:13:36 GMT

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

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

ITWhisperer — Fri, 23 Feb 2024 11:27:22 GMT

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.

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

m4jk3l — Wed, 28 Feb 2024 12:27:18 GMT

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