topic Re: How do i use if/case functions using lookup table search? in Splunk Search

How do i use if/case functions using lookup table search?

umithchada — Tue, 14 Nov 2023 21:39:32 GMT

Hello,

I have a use case where I have a bunch of email alerts that I need to determine the system name for.

Examples,

lets say i have the alerts:

1. File system alert on AAA

2. File system alert on server servernameaaaendservername

3. File system alert on server BBB

I have the list of these system names in a lookup table (Around 100 unique names), so adding 100 lines of field_name LIKE "%systemname1%","systemname1" doesn't seem efficient. Is there a way to use the conditional statement with the lookup table to match the statments?

Trying to get the below output by using the system names found in the lookup table

If systemname is found in the lookup table that matches on what is found in the alert, output systemname

Alert Name || System Name

File system alert on AAA || AAA

File system alert on server servernameaaaendservername || AAA
File system alert on server BBB || BBB

Re: How do i use if/case functions using lookup table search?

bowesmana — Wed, 15 Nov 2023 01:34:43 GMT

Just use the lookup as a lookup - that's what it's intended for

It's a little unclear what exists in the alert and what exists in the lookup based on this statement

If systemname is found in the lookup table that matches on what is found in the alert, output systemname

so I'm assuming you have an Alert Name in your data, so just so

| lookup your_lookup_file.csv "Alert Name" OUTPUT "System Name"

Assuming those are the names of your fields in the data/lookup (Alert Name) and the name of the field in the lookup is "System Name"

Re: How do i use if/case functions using lookup table search?

umithchada — Wed, 15 Nov 2023 02:51:55 GMT

Hello,

You are correct, the alert name is in the data. It is under a single field called "Subject" in a form of a string.

But the data in the lookup table is like this, with a single field " System_name",example:

AAA

BBB

CCC

DDD

The main data is has just a single field as well called "Subject" ( each row a string):

File system alert on AAA

File system alert on server serveraaaname

File system alert on BBB

I just want the output to be like in 2 fields:

Subject || system_name

Fils system alert on AAA || AAA

File system alert on serveraaaname || AAA

File system alert on BBB || BBB

Hopefully this makes sense.

Re: How do i use if/case functions using lookup table search?

bowesmana — Wed, 15 Nov 2023 03:41:04 GMT

OK, so you don't have any correlation in the lookup to match against the event...

So, If you have a field 'Subject' containing the string

"File system alert on ..."

then you can get the system name from that like this

| rex field=Subject "File system alert on (?<system>.*)"

which will work for AAA and BBB, but I am not sure how you would map 'server serveraaaname' to AAA in your example - what is the rule for that mapping?

Re: How do i use if/case functions using lookup table search?

umithchada — Wed, 15 Nov 2023 03:50:05 GMT

So I have various alerts which have the system name somehow embedded in any place.

I am looking for a query which says , " if system name is found anywhere in the alert (upper or lower case) it should output the appropriate"system name" in the "system_name" field.

Re: How do i use if/case functions using lookup table search?

bowesmana — Wed, 15 Nov 2023 04:05:14 GMT

If you have logic that can convert serveraaaname to AAA then you can write the SPL to do extract that name and show it as system name. If you want to take any characters between the two words server and name, then it's simply

| rex field=blabla "server(?<systemname>.*)name" | eval systemname = upper(systemname)