SQL Help Needed FIRST FOUND DATE messing my Reports up

I have SQL which I dropped at the bottom it is including vulnerabilities that have been found between 30 and 45 days (new ones). This report is meant to be escalated if a admin doesnt fix his/her vulns by 30 days it send out a report saying “hey this 1 vuln cvss 8 or greater hasnt been remediated” if it doesnt get fixed by 45 days it gets slapped on to the 45 day report exactly like this only with 45 days. No new vulns are suppose to be included in this report in between those days, what it picks up on 30th day report is what continues on in the 45 day report and 60 day.

This report is somehow picking up FIRST FOUND DATE in btw 30 and 45 and adding it to 45 day (because asset gets scanned and it picks new vulnerabilities cvss8 or greater) it adds it to the 45 day and admins have no knowledge of it because they dont get that report they only get it at 30 day mark so they see what will be escalated.

I need the new ones to stop being picked up because of the first found date i think thats whats messing it up. I didnt generate these reports so i’m not sure how to tweak it to leave that found date out… I dont really know how its doing it… ive tried tirelessly and have been unsuccessful. It had been working but admin caught they were being told on before they knew about the vulnerability.

Any help would be great! i have a case in but i’m waiting to see if they can help but wondered if i can quicker response here.

Heres the sql

WITH asset_count AS (
** SELECT DISTINCT fava.asset_id, COUNT (*) AS asset_count**


** FROM fact_asset_vulnerability_age fava**
** JOIN dim_vulnerability dv USING (vulnerability_id)**


** WHERE dv.cvss_v2_score >= 8 AND fava.age_in_days > 60**
** GROUP BY fava.asset_id**
** ),**


** owner_tags AS (**
** SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags **


** FROM dim_tag **
** JOIN dim_tag_asset USING (tag_id) **


** WHERE tag_type = ‘OWNER’ **
** GROUP BY asset_id **
** ),**


** custom_tags AS (**
** SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS custom_tags **


** FROM dim_tag **
** JOIN dim_tag_asset USING (tag_id) **


** WHERE tag_type = ‘CUSTOM’ **
** GROUP BY asset_id**
** )**


SELECT CASE
** WHEN ct.custom_tags LIKE ‘%ITCS%’ THEN ‘ITCS’**
** ELSE ‘DEPT’**
** END AS “ITCS or Dept”,**
** da.host_name AS “Host Name”,**
** da.ip_address AS “IP Address”,**
** ac.asset_count AS “Vulnerabilities”,**
** ot.owner_tags AS “Asset Owner”**


FROM asset_count ac
** LEFT JOIN dim_asset da USING (asset_id)**
** LEFT JOIN owner_tags ot USING (asset_id)**
** LEFT JOIN custom_tags ct USING (asset_id)**


ORDER BY ac.asset_count DESC

So looking at the main query the FROM statement is referencing the asset_count temporary table. That table is built on the fact_asset_vulnerability_age table but might work better if it’s referencing the fact_asset_vulnerability_finding table instead.

Would i still have to Join FAVA or take it completely out? I was trying instances i can seem to make anything work or it just may be my lack of knowledge on SQL.

Ahh actually now that I’m looking at it I see you were also filtering in on the age being greater than 60 days so you can’t just remove that.

yes it keeps telling me age in days or finding in days doesnt exist lol that number changes from 30 45 60 days; i guess i was trialing the 60 day one but really the admin caught it in the 45 day and hes like Hey these were not on my 30 day report…

Have you toyed with this to make something work? by chance?