I have 2 CSVs in my splunk:
Alert.csv having below columns and data:
Alert_Header Alert_type Date
JNA/athena_VICTORIA_Load [FAILED] Autosys 01/03/2022
JNA/athena_VICTORIA_Staging [MAXRUN] Autosys 01/03/2022
JNA/athena_MAIN_Load [FAILED] Autosys 01/03/2022
JNA/athena_OLTP_Staging [MAXRUN] Autosys 01/03/2022
NYP02000 has high_cpu DATABASE 01/03/2022
Mapping.csv having below columns and data:
Alert_Type Inclusion Exclusion Header
Autosys athena VICTORIA ATHENA-Jobs
Autosys VICTORIA NONE VICTORIA-Jobs
Database high_cpu NONE CPU alerts
Alert_Header Alert_type Date Header
JNA/athena_VICTORIA_Load [FAILED] Autosys 01/03/2022 VICTORIA-Jobs
JNA/athena_VICTORIA_Staging [MAXRUN] Autosys 01/03/2022 VICTORIA-Jobs
JNA/athena_MAIN_Load [FAILED] Autosys 01/03/2022 ATHENA-Jobs
JNA/athena_OLTP_Staging [MAXRUN] Autosys 01/03/2022 ATHENA-Jobs
NYP02000 has high_cpu DATABASE 01/03/2022 CPU alerts
Logic: Mapping file is for looking up pattern in the Alert.csv. So if any Alert_Header which has "athena"(mentioned in Inclusion) but doesn't have "Victoria" (mentioned in Exclusion) keyword in it will be termed as "ATHENA-Jobs" . Similarly if any Alert_Header which has "Victoria"(mentioned in Inclusion) only will be termed as "VICTORIA-Jobs". None in Exclusion column would mean there is no exclusion pattern to be searched in Alert_Header.
Can you please help with this query
Here is an example that does what you want. I have made an assumption that the Alert_Type field provides the mapping to find the associated rules to apply.
| inputlookup Alerts.csv | lookup Mapping.csv Alert_Type as Alert_type | eval criteria=mvzip(mvzip(Inclusion, Exclusion, ","), Header, ",") | fields Alert_Header Alert_type Date criteria | mvexpand criteria | rex field=criteria "(?<Inclusion>[^,]*),(?<Exclusion>[^,]*),(?<Header>.*)" | fields - criteria | where match(Alert_Header, Inclusion) AND (Exclusion="NONE" OR !match(Alert_Header, Exclusion)) | table Alert_Header Alert_type Date Header
Note a couple of observations. The "DATABASE" in the Alerts.csv will not lookup 'Database' in Mapping.csv as the case is different - you can make these case insensitive lookup definitions or just convert the lookup field before the lookup command to match the case of the lookup.
Also, note that Alert_Type and Alert_type are different fields, hence the syntax of the lookup command
What this is doing is
Hope this helps
Thanks @bowesmana for your quick response.
Just have one issue with this, it is ignoring the rows which don't match any of pattern. How can I update the query such that all rows which don't match any criteria are termed as Header="Others"
All you need to do is to change the 'where' line to this
| fillnull Header value="Others" | where match(Alert_Header, Inclusion) AND (Exclusion="NONE" OR !match(Alert_Header, Exclusion)) OR match(Header, "^Others$")
This is saying
The reason why it's excluded is that criteria is null for the non matched mappings, hence the expanded variables Inclusion/Exclusion/Header are then null, so for this specific case, you then fix it up at the end.