How to create a search to join 2 csv files?

[jinishshah]

Hello,

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

Output required:

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

[bowesmana]

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

• Looking up the associated rules for Alert_Type
• Building a composite field of the 2 criteria + header
• Expanding the rows to provide a single set of criteria per Alert_Header
• Breaking out the criteria to the separate fields again
• Performing the logic match 

Hope this helps

[jinishshah]

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"

[bowesmana]

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 

• make all Header values that are null, the word "Others"
• Add an extra test to include the row is that header is exactly Others

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.