WITH job_execution (id) AS(SELECT top 1 je.id
    FROM  dbo.Job j JOIN dbo.JobExecution je ON j.id = je.idJob
    WHERE j.id = 2086 ORDER BY id DESC
),
files (f) AS (SELECT DISTINCT fe.VALUE
    FROM dbo.TargetHost h
    JOIN dbo.Scan s ON s.idTargetHost = h.id
    LEFT JOIN dbo.XccdfScanResult r ON r.idScan = s.id
    LEFT JOIN dbo.XccdfFileEnumeration fe ON r.idXccdfFile = fe.id
    WHERE s.idJobExecution IN (SELECT id FROM job_execution)
),
rules (r) AS (SELECT DISTINCT ruleNode.VALUE('(@id)[1]', 'varchar(max)')
    FROM dbo.SyncFilesCache fc
    CROSS apply fc.fileXml.nodes('(//*:Rule)[@severity="high"], (//*:Rule)[@severity="critical"]') AS T(ruleNode)
    WHERE fc.filePath IN (SELECT f FROM files)
)

SELECT COUNT(*) FROM (
    SELECT Perc FROM (
        SELECT connectionAddress,
            CASE WHEN green+red > 0
                THEN
                    CONVERT(
                        DECIMAL(8,2),
                        100*green/CAST(green+red AS DECIMAL(8,2))
                )
            ELSE 0
        END AS Perc
        FROM (
            SELECT s.id, s.idJobExecution,
                h.connectionAddress,
                COUNT(*) AS total,
                SUM(CASE WHEN r.RESULT = 0 AND rules.r IS NOT NULL THEN 1 ELSE 0 END) AS green,
                SUM(CASE WHEN r.RESULT = 1 AND rules.r IS NOT NULL THEN 1 ELSE 0 END) AS red,
                SUM(CASE WHEN r.RESULT = 2 AND rules.r IS NOT NULL THEN 1 ELSE 0 END) AS yellow
            FROM dbo.TargetHost h
                JOIN dbo.Scan s ON s.idTargetHost = h.id
                LEFT JOIN dbo.XccdfScanResult r ON r.idScan = s.id
                LEFT JOIN rules ON r.xccdfRule = rules.r
            WHERE s.idJobExecution IN (SELECT id FROM job_execution) AND h.connectionAddress LIKE '192.168.100%'
            GROUP BY s.id, s.idJobExecution, h.connectionAddress
        ) AS results LEFT JOIN dbo.FakeDNS d ON d.host = results.connectionAddress) AS res) AS res2 WHERE Perc < 80 AND Perc <> 0