Since the R7 ticketing integration with ServiceNow just does not bring enough value and has many shortcomings, has anyone built a custom ticketing integration with Azure DevOps or are you bulk exporting vuln from R7 to import into Azure DevOps for bulk User Story creation?
Also, I would LOVE a SQL query report in R7 to export each Solution from a specific Remediation Project, along with the associated vuln. titles that correspond to each Solution in a CSV report.
With such a report, I can import the CSV to Azure DevOps and bulk create User Story work items for each Solution name and then create child Tasks for each of the associated vuln. title.
Anyone here that can help me with SQL report query?
Since R7 support reminded me custom SQL reports are beyond what they can help with, they were nice enough to get me started with:
SELECT
ds.summary AS "Solution Summary",
ds.fix AS "Solution Fix",
dv.title AS "Vulnerability Title",
dv.severity AS "Vulnerability Severity",
dv.cvss_score AS "CVSS Score",
da.ip_address AS "Asset IP",
da.host_name AS "Asset Hostname",
dv.date_published AS "Vulnerability Published Date"
FROM
dim_asset_vulnerability_best_solution davbs
JOIN
dim_solution ds ON davbs.solution_id = ds.solution_id
JOIN
dim_vulnerability dv ON davbs.vulnerability_id = dv.vulnerability_id
JOIN
dim_asset da ON davbs.asset_id = da.asset_id
ORDER BY
ds.summary,
dv.title,
da.ip_address;
This query gives us the Solution in the Remediation project and the correlated vulnerability title with affected assets.