Splunk Search

How to join fields from 2 different sourcetypes into 1 table?

Contributor

I need to join fields from 2 different sourcetypes into 1 table. Sourcetype A contains the field "cvestrlist" that I want, as well as the fields "criticalitydescription" and "advisoryidentifier". "advisoryidentifier" shares the same values as sourcetype b "advisory.advisoryidentifier". From sourcetype b, I'd also like "title", "assigned_to.username".

I believe I can create a new field for advisory.advisoryidentifier and advisoryidentifier using

eval advisory.identifier = coalesce(advisory_identifier,advisory.advisory_identifier)
I'm uncertain how to get the rest of the fields into the search and table so they're mapped to the events from the other sourcetype. I've tried

| stats first(criticality_description) as criticality_description  
first(advisory.title) as advisory.title
first(asset_list.name) as asset_list.name
first(advisory.solution_status_description) as advisory.solution_status_description
by advisory.identifier 

But that seemed to have put the sourcetype a data into different events than the sourcetype b data.
I've also tried the append function, but it doesn't add the cvestrlist field into the fields from the other sourcetype.

sourcetype=a status.name=* queue.name="*" priority.name=* | rename advisory.advisory_identifier AS advisory_identifier | append [search sourcetype=b advisory_identifier=* cve_str_list=*]
|dedup id| table id priority.name asset_list.name  advisory.title last_updated assigned_to.username queue.name status.name advisory_identifier cve_str_list 

Can someone help me in arranging this search? Thank you.

0 Karma

SplunkTrust
SplunkTrust

Give this a try

(sourcetype=a status.name=* queue.name="*" priority.name=* ) OR (sourcetype=b advisory_identifier=* cve_str_list=*)
| eval advisory_identifier = coalesce(advisory_identifier,'advisory.advisory_identifier')
| stats  values(criticality_description) as criticality_description  
 values("advisory.title") as advisory_title
 values("asset_list.name") as asset_list_name
 values("advisory.solution_status_description") as advisory_solution_status_description
 by advisory_identifier
0 Karma

Contributor

That gives me the table of advisoryidentifier, criticalitydescription, advisorytitle, assetlist.name and advisory.solutionstatusdescription. The only fields that have values are advisoryidentifier and criticalitydescription. It doesn't look like this is merging the fields/values of the sourcetypes.

0 Karma