Secusage

COL category FOR A15
COL feature FOR A50
COL o.value FOR A10
BREAK ON category SKIP 1

SELECT category, feature, MAX("o.value") AS "o.value", SUM(detected_usages) AS detected_usages, MAX(last_usage_date) AS last_usage_date
FROM (
-- DBA_FEATURE_USAGE_STATISTICS
SELECT CASE WHEN LOWER(f.name) LIKE '%audit%' OR LOWER(f.name) LIKE '%fine-grained auditing%' THEN 'AUDIT' WHEN LOWER(f.name) LIKE '%encryption%' THEN 'ENCRYPTION' WHEN LOWER(f.name) LIKE '%vault%' OR LOWER(f.name) LIKE '%redaction%' OR LOWER(f.name) LIKE '%security%' OR LOWER(f.name) LIKE '%fine-grained access%' OR LOWER(f.name) LIKE '%vpd%' THEN 'SECURITY' ELSE 'MISC' END AS category, f.name AS feature, o.value AS "o.value", f.detected_usages, f.last_usage_date
FROM dba_feature_usage_statistics f LEFT JOIN v$option o ON LOWER(o.parameter) = LOWER(f.name)
WHERE LOWER(f.name) LIKE '%audit%' OR LOWER(f.name) LIKE '%fine-grained%' OR LOWER(f.name) LIKE '%encryption%' OR LOWER(f.name) LIKE '%vault%' OR LOWER(f.name) LIKE '%redaction%' OR LOWER(f.name) LIKE '%security%' OR LOWER(f.name) LIKE '%vpd%' UNION ALL
--  Native Network Encryption (only one row)
SELECT
'N/W ENCRYPTION' category,
'Native SQLNet ENC' feature,
CASE WHEN SUM(CASE WHEN network_service_banner LIKE '%Enc%service adapter%' THEN 1 END) > 0
THEN 'YES' ELSE 'NO' END "o.value",
COUNT(DISTINCT CASE WHEN network_service_banner LIKE '%Cry%service adapter%'
THEN network_service_banner END) detected_usages,
NULL AS last_usage_date
FROM v$session_connect_info UNION ALL
-- SSL Network Encryption 
SELECT
'N/W ENCRYPTION' category,
 'ssl/tls conn ENC' feature,
CASE WHEN SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')='TCPS'
THEN 'ENABLED' ELSE 'DISABLED' END o_value,
CASE WHEN SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')='TCPS'
THEN 1 ELSE 0 END detected_usages,
NULL AS last_usage_date
FROM dual UNION ALL
-- External Authentication
SELECT 'AUTHENTICATION' AS category,
'External Authentication OS/OLAP?RADIUS' AS feature,
CASE
WHEN (SELECT COUNT(*)
FROM dba_users
WHERE authentication_type IN ('EXTERNAL','GLOBAL')
AND oracle_maintained = 'N') > 0
THEN 'YES'
ELSE 'NO'
END AS "o.value",
(SELECT COUNT(*)
FROM dba_users
WHERE authentication_type IN ('EXTERNAL','GLOBAL')
AND oracle_maintained = 'N') AS detected_usages,
NULL AS last_usage_date
FROM dual) t GROUP BY category, feature ORDER BY CASE category WHEN 'AUDIT' THEN 1 WHEN 'ENCRYPTION' THEN 2 WHEN 'SECURITY' THEN 3 WHEN 'AUTHENTICATION' THEN 4 ELSE 5 END, detected_usages DESC NULLS LAST;