How to match values if already in table

[katmagee]

I appended a CSV to an index, and right now my results pop up as the 100 lines of CSV, and then 30K of the index.

What I would like is to only return IF the values in the fw field from the index MATCH a value in the 100 lines of the CSV firewall_rule field... thoughts? I have a match in there currently but it's showing no similarities (even though I manually checked, there are many).

| from inputlookup:"firewall-exception-prod.csv"
| append [ search index=gcp_firewall]
| rename data.jsonPayload.rule_details.reference as FW 
| search FW = "network:prod*" OR firewall_rule=*
| rex field=FW "network:prod-corp/firewall:(?.*)"
| eval result=if(match(fw, firewall_rule),"yes", "no")
| table firewall_rule fw result

Do you know what I'm missing? Thank you!!!

[prachisaxena]

Hi,

since you have a CSV file, did you try to use the lookup or join command instead of append, something like below.

search index=gcp_firewall
| rename data.jsonPayload.rule_details.reference as FW
| lookup firewall-exception-prod.csv FW as FWfield outputnew < whatever fields you want to get from csv file>
|search FWfield=*

[katmagee]

so with this, FWfield is the same field from my csv names firewall_rule, correct? and its checking against FW from the index payload? Want to make sure I'm looking at this right

[richgalloway · Accepted solution]

The append command adds the results of its search to the end of the results of the previous search. The two sets of results have no relationship to each other so it's impossible to compare a field in one to a field in the other.

The typical approach to this problem is to put the lookup file in a subsearch. The results of the subsearch become a filter of the main search, getting you indexed events that have a field value in the CSV. It looks something like this:

index=gcp_firewall [ | inputlookup firewall-exception-prod.csv ]
| rename data.jsonPayload.rule_details.reference as FW
| search FW = "network:prod*" OR firewall_rule=
| rex field=FW "network:prod-corp/firewall:(?.)"
| eval result=if(match(fw, firewall_rule),"yes", "no")
| table firewall_rule fw result

The exact query may be different depending on the names of the fields in the lookup.

[katmagee]

Is this doing the lookup right away though? The issue is a need lines 2-4 to happen before the lookup can occur since I have to change the FW field to fw via rex command....

[richgalloway]

Yes, the lookup is done first. To do field extraction before the lookup, try this:

index=gcp_firewall
| rename data.jsonPayload.rule_details.reference as FW
| search FW = "network:prod*" OR firewall_rule=
| rex field=FW "network:prod-corp/firewall:(?.)"
| lookup firewall-exception-prod.csv firewall_rule as fw OUTPUT firewall_rule as fw
| eval result=if(isnotnull(fw),"yes", "no")
| table firewall_rule fw result

[katmagee]

this worked! thanks