SQL Query that obtains the same Risk Score as listed on the Vulnerabilities Tab in GUI

InsightVM SQL Queries
1

Hi, I am trying to figure out how Nexpose rounds the riskscore on the Vulnerabilities Tab in the GUI in order for my SQL Query Risk score to match what the CIO exports to CSV. Currently we keep getting hit on our queries because the risk score doesn’t match up with what they see. I see that some of the scores are rounded to the whole number, but others are rounded to the first decimal and my script rounds everything to the nearest tenth which is causing the scores to be different.

For instance, in one vulnerability my script is showing one of the vulnerability scores as 150.8 but when you look in the GUI, it shows 151. In another vulnerability on the same host, my script shows 98.7 and when you look in the GUI, it shows 98.7.

Does anyone know how Rapid7 is determining when to use decimal in the score and when not to?

Just for clarification, When I say GUI, I am using the URL of /vulnerability/listing.jsp

2

I think I found out when the rounding is occurring. It looks like anything below 100 is rounded to the nearest tenth but everything 100 and over is rounded to the whole number.

Now the question is, How would I write the script to only round numbers below 100 to the nearest tenth place and keeping all results in the same column? I tried using a CASE statement, but that only returned true or false.

Currently I am using the following syntax to accomplish rounding,but this rounds every value:

Round(cast(vuln.riskscore AS Numeric), 1)

3

Could you post your script or your CASE statement here? I like to refer to this example for them. It shows how you can specify a condition and then a value for the column based on that condition.

4

I finally figured it out. My original CASE statement was configured as a true/false statement so I had to readjust it by doing the following, which is populating the same values as the GUI:

SELECT
CASE WHEN riskscore < 100.0 THEN Round(cast(vuln.riskscore AS Numeric), 1)
WHEN riskscore >= 100.0 THEN Round(cast(vuln.riskscore AS Numeric), 0)
END AS riskscore
FROM dim_vulnerability vuln

Thanks for your assistance and the link.

1 Like
5

Nice! Glad you got it figured out.