Monitor Security Score using KQL/ Kusto using Log Analytics Workspace

Question

Hello Team,

The security Score in my Azure Subscription fluctuates every time. I'm trying to configure an Azure Monitor Alert using Log Analytics Workspace. I enabled Continuous Export features from Microsoft Defender for the cloud under my subscription to export security score logs in Log Analytics Workspace.
SecurityScores and SecurityScoreControls both tables provide info for current Secure Score in Log Analytics Workspace

I'm trying to Jon both the Tables using the below KQL Query that shows me an error.   SecureScores
//| where CurrentScore < 60
| extend Percent=PercentageScore100
| summarize avg(Percent) by bin(TimeGenerated,1d), SubscriptionId=_SubscriptionId
| where avg_Percent < 80
| join kind=leftouter (
SecureScoreControls
| extend Percent=PercentageScore100
| summarize avg(Percent) by bin(TimeGenerated,1d), SubscriptionId=_SubscriptionId
)
on $left.CurrentScore == $right.CurrentScore
//on $left.AssessedResourceId == $right.AssessedResourceId and $left.SecureScoresSubscriptionId== $right.SecureScoresSubscriptionId
Does anyone have any input on this?
Thanks In advance

Answer 1

This will fix the KQL error, use had a error in the extend() lines and also hadn't mapped Current Score in the summarize commands, so the join wasn't possible.

SecureScores
| extend Percent=PercentageScore
| summarize avg(Percent) by bin(TimeGenerated,1d), SubscriptionId=_SubscriptionId, CurrentScore
| where avg_Percent < 80
| join kind=leftouter (
SecureScoreControls
| extend Percent=PercentageScore
| summarize avg(Percent) by bin(TimeGenerated,1d), SubscriptionId=_SubscriptionId, CurrentScore
)
on $left.CurrentScore == $right.CurrentScore


// Question, wouldn't you be better to join on something other than the Score % unless you are 100% sure they are // for the same resource?   Maybe try this instead?


SecureScores
| extend Percent=PercentageScore
| summarize avg(Percent) by bin(TimeGenerated,1d), SubscriptionId=_SubscriptionId, AssessedResourceId
| join 
(
    SecureScoreControls
    | where AssessedResourceId has "ascScore"
    | parse AssessedResourceId with AssessedResourceId '/secureScoreControl' *
    | extend Percent=PercentageScore
    | summarize avg(Percent) by bin(TimeGenerated,1d), SubscriptionId=_SubscriptionId, CurrentScore, AssessedResourceId
) on AssessedResourceId