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.
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
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.