What is the most efficient way to correlate fields from different sourcetypes in the same index by an asset_id key

[packet_hunter]

I have some vulnerability and asset data I need to correlate but I am not sure of the best method to use...

index=rapid7 sourcetype="rapid7:nexpose:asset" | stats  values(os)  values(hostname)  by asset_id

1234    Microsoft blah   some_asset_name.corp.com   

1235    Linux blah  some_asset_name.corp.com    

index=rapid7 sourcetype="rapid7:nexpose:vuln"  "some vuln of interest" | stats values(signature) values(solution_summary) by asset_id

1234    signature_value  allows this blah blah attack   solution_summary_value disable blah bah

1235    signature_value  allows this blah blah attack   solution_summary_value disable blah bah

The two queries give me values based on the asset_id number, I just need a fast way to correlate the queries so I can report

os  hostname  signature   solution_summary   asset_id

Any advice appreciated.

Thank you

[DalJeanis · Accepted solution]

This should be just slightly more efficient than that ...

index=rapid7 (sourcetype="rapid7:nexpose:asset" OR 
(sourcetype="rapid7:nexpose:vuln"  "some vuln of interest") )
 | stats values(hostname) as hostname, 
     values(os) as os,
     values(signature) as signature, 
     values(solution_summary) as solution_summary 
     values(sourcetype) as sourcetype by asset_id
 | where mvcount(sourcetype)>1

Coded this way, it only has to search the index for the vuln sourcetype once. However, if you have a large number of os records, relative to vuln records, then your way will be more efficient.

[packet_hunter]

yes this is faster...
Thank you

[ranich]

In a similar vein, I am stuck on getting values returned as intended. I am trying to get riskscore values for assets that have an exception applied, but only the exception values of risk.

Something like this, but I am unclear how to differentiate the risk:
index=rapid7 | transaction asset_id | makemv nexpose_tags delim=";" | stats sum(riskscore) as totalrisk, values(review_comment), values(submitted_by) by nexpose_tags | eval totalrisk=round(totalrisk) | sort -totalrisk

Perhaps some version of your query above would fit into this query to show the asset group and its risk that is being excluded in Nexpose?

[DalJeanis]

When theory matches performance, believe performance.

When theory does NOT match performance, believe performance.

[packet_hunter]

I came up with...

index=rapid7 sourcetype="rapid7:nexpose:asset" OR sourcetype="rapid7:nexpose:vuln" [search index=rapid7  sourcetype="rapid7:nexpose:vuln" TLS 1.0  | fields asset_id] |stats values(hostname) values(os) values(signature) values(solution_summary) by asset_id

any other ideas on this?