Introduction
Fortify’s Software Security Center (SSC) not only has a powerful UI that developers can use to manage their backlog of security weaknesses. It also has a series of powerful reports. One of those reports shows weaknesses grouped by the categories in OWASP’s Top 10 (2017 edition). I use that all the time, and it gives me a great way to trend how my applications look from the perspective of some of the more common weaknesses — weaknesses so well known that the probability of exploit is higher.
Trouble is, if I have an inventory of dozens or hundreds of applications, manually running that report is pain. And seriously, who has time for that? Fortunately, it’s straight forward to run the query yourself, which means you can easily automate it. But only after you crack the code!
This post will walk you through creating the query for your applications.
Picking the Right Category
You know the pulldown that lets you pick what categories you can use to group the display of weaknesses in SSC?
This “Group by” list doesn’t come out of thin air.
That material comes from a table. To replicate the Group By, you’ll need the guid of the OWASP Top 10 2017. Fortunately, Fortify SSC’s database is well designed and reasonable, so once you find the table, it’s easy to find the guid. The query you’ll need is:
SELECT * FROM catpackexternallist WHERE description LIKE '%OWASP%';
The results look something like this:
You can easily see which guid belongs to “OWASP Top 10 2017.” You can also see it’s not the only OWASP list you can group your reports by!
We can see that the guid we need is:
3C6ECB67-BBD9-4259-A8DB-B49328927248
Building the Query
The next step in our journey is the place where SSC stores the results of the last scan. It’s a view called “defaultissueview.” We’ll want to pull the data for a given application (for our example, we’ll use vdh_admin_ui, an application I wrote that never went into production — so sad!). If you’ve used Static Code Analyzer (SCA) and SSC, then you’ll know that a given application can have at least one, and maybe many, build IDs. However, it can only have one active Build ID. So, our query will have to reflect that.
We’ll also have to map the value of defaultissueview’s column named mappedCategory back to the catpackexternallist table. It’s not a direct mapping, but fortunately, Fortify provides a stored procedure to do the work for us. That stored procedure is named:
getExternalCategories
It takes two arguments: The value of the mappedCategory column and the guid of the report we want to run.
At this point, we have enough information to build the query. Here it is in all of its glory:
SELECT COUNT(*) AS thecount, dbo.getExternalCategories(a.mappedCategory, '3C6ECB67-BBD9-4259-A8DB-B49328927248') AS OWASPTop10
FROM defaultissueview a
WHERE a.projectVersion_id = (SELECT b.id FROM project a, projectversion b WHERE a.name = 'vdh_admin_ui' AND b.project_id = a.id AND b.active = 'Y')
AND a.scanStatus <> 'REMOVED'
AND a.suppressed = 'N'
AND a.id NOT IN (SELECT b.issue_id FROM auditvalueview b WHERE b.issue_id = a.id AND upper(lookupValue) = upper('Not an Issue'))
GROUP BY dbo.getExternalCategories(a.mappedCategory, '3C6ECB67-BBD9-4259-A8DB-B49328927248')
ORDER BY dbo.getExternalCategories(a.mappedCategory, '3C6ECB67-BBD9-4259-A8DB-B49328927248');
The results for vdh_admin_ui look like this:
The results will show the count of vulnerabilities, minus anything that’s marked “Not an Issue,” grouped by the OWASP Top 10 from 2017.
Notice that I only have 6 A1 and 4 A3 weaknesses! It’s because my code is so awesome, right? Nah. It’s because the code’s so small. It shouldn’t have any weaknesses at all!
Since I’m so dedicated to sharing these techniques, I intentionally included these errors so I’d have results to show you! Let’s go with that…
Summary
That’s all there is to it! The only real variable in the query is the name of your application. You should be able to copy that query into your SQL Server query editor, change the application name, and run the query to see that application’s results.
