by Dr. Radut

Getting Defender's 'Secure Score' with Logic Apps an ADX

Published by Laurie Rhodes -
icrosoft Secure Score

This article is intended to show how Logic Apps can be used with Azure Data Explorer (ADX).  Normally, I would try to ensure that all data ingested into ADX came through Event Hubs as they provide a resiliency and ability to support Regional redundancy with clusters.  I am conscious that for cost effectiveness, there are situations where I don't want to dedicate an Event Hub for small volume, low-value data. 

This is an opportunity to:

  • Show how Logic Apps can be used to write to ADX.
  • Demonstrate how to transform data as it arrives in ADX.
  • Show how the Secure Score is derrived.

Solution Overview

Solution Components

The role of the Logic App is run GET requests against Microsoft's Security Graph endpoints and write data to two tables in ADX using streaming ingest.  Once the data arrives in ADX we can transform the data to produce reportable tables about Secure Score.

The Logic App

The Logic App used with this demonstration can be found at: https://github.com/LaurieRhodes/PUBLIC-Scripts/tree/main/Azure_Automation/Logic_App/Get-GraphSecureScore

  1. Deploying the Logic Workflow will be the first task as it will create a Managed Identity that can be used to assign permissions to Microsoft's Graph Security APIs.

  2. Once the workflow has been deployed, take note of the Logic App Principal Id that has been created under the Identity tab.

    Service Principal ID
  3. We now need to assign the 'SecurityEvents.Read.All' role to the Service Principal. We will do this with PowerShell.

# Install AzureAD module if not already installed 
Install-Module -Name AzureAD -Force -AllowClobber  

# Connect to Azure AD 
Connect-AzureAD

# 'Enter your managed identity Object ID'
$MIObjectId = 'cb575ea5-0d1b-4e4c-a370-b9dbf4bafa0a'

# Use the Security Graph App ID to get the Service Principal
$MDEAppId       = "00000003-0000-0000-c000-000000000000"

$PermissionName = 'SecurityEvents.Read.All'

$MDEServicePrincipal = $null
$MDEServicePrincipal = Get-AzureADServicePrincipal -Filter "appId eq '$MDEAppId'"

# Use the Service Principal to get roles
$AppRole = $MDEServicePrincipal.AppRoles | Where-Object {$_.Value -eq $PermissionName -and $_.AllowedMemberTypes -contains 'Application'}

# Assign the role to the logic App
New-AzureAdServiceAppRoleAssignment -ObjectId $MIObjectId -PrincipalId $MIObjectId  -ResourceId $MDEServicePrincipal.ObjectId -Id $AppRole.Id

It can take some time for Managed Identity claims to become available in Azure.  In the past I've had to wait up to 24 hours for assigned authorisations to become active with the Manged Identity.  In the meantime, we need to provide that Identity permissions with Azure Data Explorer.

Azure Data Explorer Permissions

Streaming ingestion must be enabled on the cluster level of ADX.  This may be done through the portal under Settings -> Configurations.

Enable Streaming Ingestion

You will also need to ensure that the Application is granted an Ingestor role permission either to the database or table.   Database Ingestor role can be added through the portal on the database Overview -> Permissions -> Add.

ADX Ingestor role

Streaming ingestion must be enabled against individual tables using KQL in the Query plane of your database.  This solution will be ingesting two different data streams from the Logic App so I'm going to create two tables for holding the raw data stream, each with a dynamic column titled 'records' that objects can be streamed into.

.create table SecureScoreRaw (['records']: dynamic) 

.create table SecureScoreControlProfilesRaw (['records']: dynamic)

Now that the raw data tables are created I can enable streaming ingestion.

.alter table SecureScore policy streamingingestion '{"IsEnabled": true}'

.alter table SecureScoreControlProfilesRaw policy streamingingestion '{"IsEnabled": true}'

 

 The next day...

 Customising the Logic App

I'm confident that the permissions changes for the Managed Identity have ocurred.

I need to update the Logic App with the ADX Cluster and database details that I need my data written to.

Logic App Parameters

 

If I run the workflow, I should now be able to run the workflow and get raw data.

Secure Score data

A couple of points to note:

  1. You can see the raw data submitted to ADX through the run history of the Logic App
http response

The SecureScores data by itself isn't enough to replicate the Secure Scores values displayed in the Portal.  We can query that data in ADX to get a better look at what the submitted object looks like.

Sending data to ADX uses the Ingest API path for the database.  This method requires setting the host property in the header.  We are also going to place all received records in a 'records' column of the raw data table. 

Sending data to ADX action

Take note of the authentication needed for writing to ADX.  The audience must be set to https://kusto.kusto.windows.net

Managed Identity Audience

Working with ADX

There are some irritating inconsistencies with Secure Score which will force us to look at all the raw data used to derive this indicator.

Secure Score through the portal


I am going to create a SecureScore table in ADX.  Microsoft haven't published a Schema for this so it's my best guess of what Microsoft would be creating to present data like other Azure Monitor tables.

I'm also going to create a function to expand records from the raw data table into my SecureScore table.  Then I'll attach the function as an update policy so the SecureScore table is updated from every record written to the raw data table.  I'm going to do exactly the same with the SecureScoreControls data written by the Logic App.

.create table SecureScore (
TenantId: string, 
TimeGenerated: datetime, 
Id: string, 
ActiveUserCount: int, 
CurrentScore: int, 
MaxScore: int, 
PercentageScore: double, 
EnabledServices: dynamic,
LicensedUserCount: int,
VendorInformation: dynamic,
AverageComparativeScores: dynamic,
ControlScores: dynamic )

.create-or-alter function SecureScoreExpand {
SecureScoreRaw
// First make sure we only ingest the latest record from SecureScore
// the API returns months of records and typical ODATA filtering doesnt stop this
| mv-expand events = records 
| extend 
    TenantId = tostring(events.azureTenantId),
    TimeGenerated = todatetime(events.createdDateTime),
    Id = tostring(events.id),
    ActiveUserCount = toint(events.activeUserCount),
    CurrentScore = toint(events.currentScore),
    MaxScore = toint(events.maxScore),
    PercentageScore = round((todouble(events.currentScore) / todouble(events.maxScore)) * 100, 2),
    EnabledServices = todynamic(events.enabledServices),
    LicensedUserCount = toint(events.licensedUserCount),
    VendorInformation = todynamic(events.vendorInformation),
    AverageComparativeScores = todynamic(events.averageComparativeScores),
    ControlScores = todynamic(events.controlScores)
| summarize arg_max(TimeGenerated, *) by TenantId  // Select the latest record by `createdDateTime` for each `azureTenantId`
| project-away records, events
}

.alter table SecureScore policy update @'[{"Source": "SecureScoreRaw", "Query": "SecureScoreExpand()", "IsEnabled": "True", "IsTransactional": true}]'

.create  table SecureScoreControls (
TenantId: string, 
TimeGenerated: datetime, 
ControlType: string, 
ControlName: string, 
Description: string, 
ControlScore: int, 
PercentageScore: int, 
LastSynced: datetime,
ImplementationStatus: string,
On: string)

.create-or-alter function SecureScoreControlsExpand {
SecureScore
| mv-expand Control = ControlScores 
| project TenantId, 
TimeGenerated, 
ControlType=tostring(Control.controlCategory),
ControlName=tostring(Control.controlName),
Description=tostring(Control.description),
ControlScore=toint(Control.score),
PercentageScore=toint(Control.scoreInPercentage),
LastSynced=todatetime(Control.lastSynced),
ImplementationStatus=tostring(Control.implementationStatus),
On=tostring(Control.on)
}

.alter table SecureScoreControls policy update @'[{"Source": "SecureScore", "Query": "SecureScoreControlsExpand()", "IsEnabled": "True", "IsTransactional": true}]'

After my Logic App runs next I can see data written to the SecureScore table.  

Secure Score in ADX

Frustratingly, the Security Portal doesn't caclulate the same secure score that is presented with raw data!  It is however close enough and what's important are the trends rather than the scores themselves.

The portal provides a breakdown of my points in categories along with recommendations.  I only partially get this data with the SecureScore API.  If I'm going to go to the trouble of reporting on this I need more detailed information to see how the scores and recommendations are derived.  This information comes from the SecureScoreControlProfiles table that is populated by the LogicApp:

SecureScoreControlProfiles detail

For every control in my assessment, I need to join every assessed control with data from the full control definition.  This will let me see additional data for each of the controls including the recommendations.

 SecureScore
| summarize arg_max(TimeGenerated, *) by TenantId   // Select only the latest record
| mv-expand Control = ControlScores 
| project TenantId, 
          TimeGenerated, 
          ControlType = tostring(Control.controlCategory),
          ControlName = tostring(Control.controlName),
          Description = tostring(Control.description),
          ControlScore = toint(Control.score),
          LastSynced = todatetime(Control.lastSynced),
          ImplementationStatus = tostring(Control.implementationStatus),
          On = tostring(Control.on)
| join kind=inner ( 
    SecureScoreControlProfiles
       | summarize arg_max(TimeGenerated, *) by Id  // Select only the latest record for each Id
    | project Id, Title, MaxScore, Rank, Remediation, RemediationImpact, Service
) on $left.ControlName == $right.Id
| project TenantId, 
          TimeGenerated, 
          ControlType, 
          ControlName, 
          Description, 
          ControlScore, 
          MaxScore, 
          PercentageScore = todouble(round(ControlScore / MaxScore * 100, 2)),
          LastSynced, 
          ImplementationStatus, 
          On, 
          Title, 
          Rank, 
          Remediation, 
          RemediationImpact, 
          Service

SecureScore data specifics

The output of data provides all the raw scores in each control type.  In this basis you can sum the controls in each category through ADX.

Secure Conrols Output

Of the four categories of Secure Score, I've been finding that Device and Data categories will match the portal summary but Apps and Identity are slightly out with what looks like Microsoft is using a more granular evaluation with some of these controls than the integer values reported.

Portal control categories

Secure Score Recommendations

The recommendations in the portal come from the rank score of controls when ordering results by the lowest scores received in the environment. 

Secure Score Recommendations

... which you can see from the raw data:

With the raw data I can use PowerBI or workbooks to incorporate Secure Score into regular reporting.