Azure kql Securescorecontrols and Assessments join

Question

I have been working on a query that will combine the Recommedation categories( 13 listed under the Classic View in recommendations) and the individual assessments associated to those categories:

securityresources
    | where type == "microsoft.security/securescores/securescorecontrols"
    | extend category_name = tostring(properties.displayName) //category name
    | extend Tenant_Id=tostring(tenantId)
    | extend healthy = properties.healthyResourceCount
    | extend unhealthy = properties.unhealthyResourceCount
    | extend notApplicable = properties.notApplicableResourceCount
    | extend score = properties.score
    | extend scr= parse_json(score)
    | project category_name, healthy, unhealthy, notApplicable, CurrentScore=scr.current, MaxScore=scr.max, Tenant_Id
    | join  (
        securityresources
        | where type == "microsoft.security/assessments"
        | extend assessment_name = tostring(properties.displayName) //assessment name
        | extend Tenant_Id=tostring(tenantId)
        | extend resourceName = properties.resourceDetails.ResourceName
        | extend status = properties.status.code
        | extend metadata = properties.metadata
        | extend severity = metadata.severity
        | project assessment_name, resourceName, status, severity, Tenant_Id
    ) on Tenant_Id
    | project category_name, assessment_name, resourceName, status, severity, healthy, unhealthy, notApplicable, CurrentScore, MaxScore,Tenant_Id

This is a work in progress script, I do get a valid script but I know it is not working like I need it to work. For example, when I run this script, I get for "assessment_name: EDR solution should be installed on Virtual Machines" but for the "category_name" I get "Restrict unauthorized network access". It should be category_name = Enable endpoint protection. I'm trying to find a valid join field but not getting it correctly. Perhaps I need to add anothere "Type" but I'm not sure which.
Please advise, Serge

Answer 1

Azure kql Securescorecontrols and Assessments join:

As per my observations, the issue of join operator is occurring with the tenant ID field while it is not exactly enough to match and join both the data tables. In order to provide a valid join operator fields, you can use a field which is equally shared by both data tables (secure score controls & assessments).

As detailed in this blog, there is a concept called

Azure Secure Score which is a security measurement provided by Microsoft to assess the security posture of an Azure subscription. It calculates the score based on security controls and recommendations that are configured or not in the subscription.

As mentioned above, refer here for sample azure secure score resource graph queries related to the secure score controls. I found that there is a common field for both of the data tables is control Id and the query is given as shown below clearly.

securityresources
| where type == "microsoft.security/securescores/securescorecontrols"
| extend category_name=properties.displayName,
controlId=properties.definition.name
| project properties, controlId

securityresources
| where type == "microsoft.security/assessments"
| extend controlName=properties.displayName,
controlId=properties.definition.name
| project properties, controlId, controlName

After reviewing the properties and fields of both data tables, I have used the following KQL query using the join operator.

securityresources
| where type == "microsoft.security/securescores/securescorecontrols"
| extend category_name = tostring(properties.displayName)
| extend Tenant_Id = tostring(tenantId)
| extend healthy = properties.healthyResourceCount
| extend unhealthy = properties.unhealthyResourceCount
| extend notApplicable = properties.notApplicableResourceCount
| extend score = properties.score
| extend scr = parse_json(score)
| extend controlId = tostring(properties.definition.name) 
| project category_name, healthy, unhealthy, notApplicable, CurrentScore = scr.current, MaxScore = scr.max, controlId, Tenant_Id
| join (
    securityresources
    | where type == "microsoft.security/assessments"
    | extend assessment_name = tostring(properties.displayName)
    | extend Tenant_Id = tostring(tenantId)
    | extend resourceName = properties.resourceDetails.ResourceName
    | extend status = properties.status.code
    | extend metadata = properties.metadata
    | extend severity = metadata.severity
    | extend controlId = tostring(properties.definition.name)
    | project assessment_name, resourceName, status, severity, controlId, Tenant_Id
) on Tenant_Id, controlId
| project category_name, assessment_name, resourceName, status, severity, healthy, unhealthy, notApplicable, CurrentScore, MaxScore, Tenant_Id