Help joining two different sourcetypes from the same index that both have a field with the same value but different name

[Berfomet96]

Hello everybody,

I'm trying to join two different sourcetypes from the same index that both have a field with the same value but different name.

i.e:

sourcetype 1 - field name=x - value=z | sourcetype 2 - field name=y - value=z

I've tried this two queries but had no success at joining these two:

index=rapid7 sourcetype="rapid7:insightvm:asset:vulnerability_finding" finding_status=new 
| eval date=strftime(now(),"%m-%d")
| eval date_first=substr(first_found,6,5)
| where date=date_first
| join type=outer left=L right=R where L.vulnerability_id=R.id 
    [ search index=rapid7 sourcetype="rapid7:insightvm:vulnerability_definition" ]

index=rapid7 sourcetype="rapid7:insightvm:asset:vulnerability_finding" OR sourcetype="rapid7:insightvm:vulnerability_definition"
| eval id=vulnerability_id
| transaction id

As you can see, I didn't even tried with the transaction one because I haven't finished to understand how it works.

The main issue I have is that I want to work with all values so I can build a table or a stats command that displays the most recent vulnerabilities found by the InsightVM dataset, however, I only get the values from the left search. Whenever I add a stats or a table command to the query using the join command I get empty values in my table.

i.e:

| table L.asset_hostname, R.title, R.description, L.solution_fix

I have already manually tested to see if the values from the different fields are the same and they are, I'd appreciate if someone would be kind enought to shed some light onto this and help me understand what am I doing wrong.

Thanks in advance.

[yuanliu]

You want to refine the problem statement.  What is the use case (end result) that compels you to find field names with the same value?  Can values be the same by accident?  If not, why are such mapping not static?  Is this just an exercise to build a static mapping (as suggested by @johnhuang)?  Or is there a deeper problem you are trying to solve?

As you illustrate domain-specific code, it is important to illustrate domain-specific data (anonymize as needed), too.  What are some examples you are looking at?

[johnhuang · Accepted solution]

You should create a lookup for the vulnerability definition -- you can adjust the fields to save into the lookup as necessary. 

First create the initial lookup:

index=rapid7 sourcetype="rapid7:insightvm:vulnerability_definition" earliest=-7d@d
| dedup id
| fields id added categories cves cvss_v2_access_complexity cvss_v2_access_vector cvss_v2_authentication cvss_v2_availability_impact cvss_v2_confidentiality_impact cvss_v2_exploit_score cvss_v2_impact_score cvss_v2_integrity_impact cvss_v2_score cvss_v2_vector cvss_v3_attack_complexity cvss_v3_attack_vector cvss_v3_availability_impact cvss_v3_confidentiality_impact cvss_v3_exploit_score cvss_v3_impact_score cvss_v3_integrity_impact cvss_v3_privileges_required cvss_v3_scope cvss_v3_score cvss_v3_user_interaction cvss_v3_vector denial_of_service description links modified pci_cvss_score pci_fail pci_severity_score pci_status published references risk_score severity severity_score title 
| rename id AS vulnerability_id 
| table vulnerability_id categories title description cves cvss_v2_score cvss_v3_score pci_cvss_score severity severity_score added modified 
| outputlookup rapid_vulnerability_definition_lookup.csv

To keep the lookup up to date, schedule a report (daily or as often as you need)

index=rapid7 sourcetype="rapid7:insightvm:vulnerability_definition" earliest=-1d@d
| rename id AS vulnerability_id 
| append [| inputlookup rapid_vulnerability_definition_lookup.csv]
| dedup vulnerability_id 
| table vulnerability_id categories title description cves cvss_v2_score cvss_v3_score pci_cvss_score severity severity_score added modified 
| outputlookup rapid_vulnerability_definition_lookup.csv

 
To use the lookup:

index=rapid7 sourcetype="rapid7:insightvm:asset:vulnerability_finding" finding_status=new 
| eval date=strftime(now(),"%m-%d")
| eval date=strftime(now(),"%m-%d")
| eval date_first=substr(first_found,6,5)
| where date=date_first
| lookup rapid_vulnerability_definition_lookup.csv vulnerability_id OUTPUT title description