I have 2 fact tables containing vulnerability data, a finding table and a finding_date table. The finding table is overwritten with the current findings after scans are run, the finding_date table is appended with the same data. Vulnerabilities found in the finding_date table that are not in the current finding table can be considered remediated. I created a unique_key column by combining the asset_id + vulnerability_id. For each unique_key, the remediation_date would be the LASTDATE() + 1 for all unique_keys that are not found in the finding table.
What I would like to do is create a calculated table from these 2 tables with the following columns: asset_id, vulnerability_id, (or unique_key), first_found_date, vulnerability_instances, remediation_date (if available). I can then add calculated columns for measuring SLA's and vulnerability status.
Thank you for the response @sturlaws ! However this would assign the min date found in the entire table to all the vulnerabilities. I would like to do something like a GROUPBY(unique_key) and find the MIN/MAX value within each group... if that makes sense.
@tvm_analyst, since you have not made any sample data available, I don't know excatly how your data looks, so the code should be consider more as guidelines for you to continue working on to get your desired result