topic Re: How to create a search to join 2 csv files? in Splunk Search

How to create a search to join 2 csv files?

jinishshah — Tue, 12 Apr 2022 16:39:35 GMT

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

Re: How to create a search to join 2 csv files?

bowesmana — Tue, 12 Apr 2022 23:45:28 GMT

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

Re: How to create a search to join 2 csv files?

jinishshah — Wed, 13 Apr 2022 14:06:45 GMT

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"

Re: How to create a search to join 2 csv files?

bowesmana — Wed, 13 Apr 2022 23:45:22 GMT

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.