We want a report that identify any machine that has a known malware kit, metasploit, or is listed in the exploit database. Where the machine was first discovered 2 or more weeks ago. And has checked in within the last week.
You could try this… change your dates and timeframes accordingly
WITH vulnerable_assets AS (
SELECT
fa.asset_id,
fa.first_discovered,
fa.last_scan_date,
fa.exploits, – Tracks if machine is in the exploit database
fa.malware_kits – Tracks if a malware kit is present
FROM fact_asset fa
WHERE
fa.first_discovered <= CURRENT_DATE - INTERVAL ‘14 days’ – Discovered at least 2 weeks ago
AND fa.last_scan_date >= CURRENT_DATE - INTERVAL ‘7 days’ – Scanned within the last week
AND (fa.exploits > 0 OR fa.malware_kits > 0) – Has known exploits or malware kits
)
SELECT
ds.name AS “Site Name”,
da.asset_id AS “Asset ID”,
da.ip_address AS “IP Address”,
da.host_name AS “Host Name”,
dos.description AS “OS Name”,
va.first_discovered AS “First Discovered”,
va.last_scan_date AS “Last Scanned”,
va.exploits AS “Exploit Count”,
va.malware_kits AS “Malware Kit Count”
FROM vulnerable_assets va
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id) – Join to get Site Name
ORDER BY va.last_scan_date DESC;
should be represented like this.
| Column 1 | Column 2 | Column 3 | Column 4 | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|
| Site Name | Asset ID | IP Address | Host Name | OS Name | First Discovered | Last Scanned | Exploit Count | Malware Kit Count |
| HQ Office | 12345 | 192.168.1.10 | Server1 | Windows Server 2019 | 2024-01-01 | 2024-02-10 | 5 | 1 |
| Data Center | 67890 | 192.168.1.20 | LinuxHost | Ubuntu 20.04 | 2024-01-15 | 2024-02-08 | 3 | 2 |