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

[manderson7]

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

I believe I can create a new field for advisory.advisory_identifier and advisory_identifier 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 cve_str_list 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.

[somesoni2]

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

[manderson7]

That gives me the table of advisory_identifier, criticality_description, advisory_title, asset_list.name and advisory.solution_status_description. The only fields that have values are advisory_identifier and criticality_description. It doesn't look like this is merging the fields/values of the sourcetypes.