how to find sum of the latest values of the fields by a certain field ?

[pgadhari]

I have 2 sourcetypes from Nexpose vulnerability data. One sourcetype is Asset details and other sourcetype is Vulnerability details. Both this sourcetype has common field called "asset_id". Field details are below :

sourcetype Asset contains fields ==> asset_id, vulnerabilities, riskscore, malware_kits,exploits
sourcetype Vul contain fields ==> asset_id, Solution_summary

I want to show "No. of Assets", Vulnerabilities,Riskscore,malware_kits,exploits by solution_summary, so in this case I have to join 2 sourcetypes and do the sum of the fields in sourcetype Asset. But, those fields have different values, so I want to do the sum of the latest values of the fields in vulnerabilities, riskscore, malware_kits and exploits, but somehow it is not working. Also, currently I am using coalesce for asset_id field to merge both the sourcetypes, is it the proper way of doing it ? Please help in writing the search ?

Below is my current search which is adding all the values of the fields, instead of latest value of the field ?

index=rapid7 sourcetype="rapid7:nexpose:vuln" OR sourcetype="rapid7:nexpose:asset" | eval Asset=coalesce(asset_id,asset_id) | stats values(*) as * by Asset | stats dc(asset_id) as Assets, sum(vulnerabilities) as Vulnerabilities, sum(exploits) as Exploits, sum(malware_kits) as "Malware Kits"  sum(riskscore) as Risk by solution_summary | sort - Risk | eval Risk=round(Risk,2) |rename solution_summary as Remediation

But in above query sum(vulnerabilities) is adding all vulnerabilities values which I dont want, I want to consider only the latest value of vulnerability number I am getting in that field when I search for last 30 days or any other time range, same with exploits, malware_kits and riskscore ? Please help resolve the issue ?

Thanks
PG

[dmarling · Accepted solution]

Your stats that joins the data together by Asset is producing the values of every field, which makes it difficult to obtain the latest value since that combines everything into a single line. If you adjust that stats to be a bit more judicious on what data is pulled you should be able to get the latest information you wanted.

index=rapid7 sourcetype="rapid7:nexpose:vuln" OR sourcetype="rapid7:nexpose:asset" 
| eval Asset=coalesce(asset_id,asset_id) 
| stats values(asset_id) as asset_id latest(vulnerabilities) as vulnerabilities latest(exploits) as exploits latest(malware_kits) as malware_kits latest(riskscore) as riskscore values(solution_summary) as solution_summary by Asset 
| stats dc(asset_id) as Assets, sum(vulnerabilities) as Vulnerabilities, sum(exploits) as Exploits, sum(malware_kits) as "Malware Kits" sum(riskscore) as Risk by solution_summary 
| sort - Risk 
| eval Risk=round(Risk,2) 
| rename solution_summary as Remediation

[pgadhari]

Sure. I will check this and revert.

[dmarling]

@pgadhari , Did this work for you? If so, please accept the answer. If not, let me know what the problem is. Thank you!

[pgadhari]

Yes it is working for me. But I am still waiting for some validations to be complete. But so far, the query works. Thanks.

[pgadhari]

Sure. I will check this and revert tomorrow pls. Thanks for your support.

[pgadhari]

can anyone reply on this please ?