KQL using SecurityResources needs to return Last Scan time of the AZ SQL database not of the host

Nguyen, Hoa I kindly request you to provide the details mentioned in the private message so we can identify the root cause and address the issue effectively.

Finally saw the private message from you. I provided the answers to the questions. Please let me know what else you require...Thank you!

Hello @Nguyen, Hoa,

Please use the below query and let us know the behavior-

SecurityResources
| where type == "microsoft.security/assessments/subassessments" 
      and properties.additionalData.assessedResourceType == "SqlVirtualMachineVulnerability"   
      and properties.status.code == "Unhealthy"
| extend resourceId = iff(properties.resourceDetails.id != "", properties.resourceDetails.id, extract("(.+)/providers/Microsoft.Security", 1, id))
| where resourceId contains "/databases/"
| extend DatabaseName = tostring(split(resourceId, "/databases/", 1)[0])
| extend resourceName = properties.resourceDetails.ResourceName
| extend vulnerability = properties.displayName,
         description = properties.description,
         severity = properties.status.severity,
         threat = properties.additionalData.threat,
         impact = properties.impact,
         fix = properties.remediation,
         vulnId = properties.id,
         App = iff(
            tostring(id) contains "npd" or id contains "iqa" or id contains "txm" or id contains "ace" or id contains "chip" or id contains "lab" 
            or id contains "vdi" or id contains "api-shared",
            "Non-NIM",
            "NIM"),
         SeverityLevel = case(
            properties.status.severity == "Critical", 1,
            properties.status.severity == "High", 2,
            properties.status.severity == "Medium", 3,
            properties.status.severity == "Low", 4,
            5),
         ScanTime = coalesce(
            todatetime(properties.additionalData.scanTime),
            todatetime(properties.additionalData.scanDate),
            properties.timeGenerated)
| project App, ScanTime, resourceName, DatabaseName, SeverityLevel, severity, vulnId, vulnerability, description, impact, fix
| order by tostring(App) asc, tostring(resourceName) asc, tostring(SeverityLevel) asc, tostring(vulnId) asc

Please note that ScanTime = coalesce(...) pulls from the actual scanTime or scanDate fields before falling back to timeGenerated. Used todatetime() to ensure correct formatting.

Please let us know the behavior either as a comment or private chat.