All Apps and Add-ons
Highlighted

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

Builder

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 ==> assetid, vulnerabilities, riskscore, malwarekits,exploits
sourcetype Vul contain fields ==> assetid, Solutionsummary

I want to show "No. of Assets", Vulnerabilities,Riskscore,malwarekits,exploits by solutionsummary, 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, malwarekits and exploits, but somehow it is not working. Also, currently I am using coalesce for assetid 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

0 Karma
Highlighted

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

Builder

can anyone reply on this please ?

0 Karma
Highlighted

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

Builder

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
If this comment/answer was helpful, please up vote it. Thank you.

View solution in original post

0 Karma
Highlighted

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

Builder

Sure. I will check this and revert.

0 Karma
Highlighted

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

Builder

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

If this comment/answer was helpful, please up vote it. Thank you.
0 Karma
Highlighted

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

Builder

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

0 Karma
Highlighted

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

Builder

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

0 Karma