Manage Office 365 customers’ security alerts with SharePoint, Microsoft Flow and Azure Functions

The Microsoft Graph Security API is an extremely useful addition to the Microsoft Graph for Office 365 admins, however it can be especially useful for Microsoft Partners and Managed Service Providers.

Not only can it be used to retrieve customers’ secure scores, you can also use it to retrieve and update security alerts without having to set up notifications for each tenant manually.

Security alerts on the Microsoft Graph can come from a variety of sources, including Office 365, Azure, Cloud App Security and other Microsoft and third party products.

This guide will demonstrate how to sync customers security alerts from the Microsoft Graph with a SharePoint list, and provide a simple method to add comments and resolve alerts from a central location.

Any comments, along with the name and email of the person who resolved it, are attached to the resolved alert in the customer’s tenant.

While this guide is designed with Microsoft Partners and Managed Service Providers in mind, it will also work for individual Microsoft 365/Office 365 tenants with a small modification

Microsoft Graph Security Alert Management solution outline

Script 1 – Authorising an Azure AD Application to access alerts in customer tenants

This script creates an Azure Active Directory application in your own tenant and provides it with access to the security alerts of your customers’ Office 365 tenants.

Script 2 – Syncing Office 365 alerts for all customers with a SharePoint list

This script collects security alerts from your own and your customers’ tenants and adds them to a new SharePoint list in your root SharePoint site called Alert Register

Formatting your Alert Register SharePoint List

This section demonstrates how to make your SharePoint List easier to manage at a glance using conditional column formatting and custom views.

Managing Alerts with Microsoft Flow and Azure Functions

Here we’ll create Microsoft Flows and Azure Functions to allow your team to manage customer alerts without having to log into each tenant. We’ll also add the alert syncing script to an Azure Function  to ensure that your customers environments are regularly checked for security alerts

Prerequisites

• To set up this solution you’ll need to be a Global Admin in your own tenant, and preferably have access to customer tenants via delegated administration.
• To register the Azure AD application you’ll need to have the Azure AD PowerShell module installed. If you don’t have it yet, just open PowerShell as an administrator and run:

  Install-Module AzureAD

Script 1 – Authorising an Azure AD Application to access alerts in customer tenants

1. Double click the below script to select it.
2. Copy and paste the script into a new file in Visual Studio Code and save it with a .ps1 extension
3. Install the recommended PowerShell module if you haven’t already
4. Modify the $homePage and $logoutURI values to any valid URIs that you like. They don’t need to be actual addresses, so feel free to make something up. Set the $appIDUri variable to a use a valid domain in your tenant. eg. https://yourdomain.com/$((New-Guid).ToString())
5. If you are running the script for a single tenant, comment out the following two lines:
6. Press F5 to run the script
7. Sign in to Azure AD using your global admin credentials. Note that the login window may appear behind Visual Studio Code.
8. Wait for the script to complete.
9. Retrieve the client ID, client secret and tenant ID from the exported CSV at C:\temp\azureadapp.csv. (below image is just an example.)

PowerShell Script to authorise Azure AD Application to access Microsoft Graph security alerts

# This script needs to be run by an admin account in your Office 365 tenant
# This script will create an Azure AD app in your organisation with permission
# to access resources in yours and your customers' tenants.
# It will export information about the application to a CSV located at C:\temp\.
# The CSV will include the Client ID and Secret of the application, so keep it safe.
   
# Confirm C:\temp exists
$temp = Test-Path -Path C:\temp
if ($temp) {
    #Write-Host "Path exists"
}
else {
    Write-Host "Creating Temp folder"
    New-Item -Path C:\temp -ItemType directory
}
   
$applicationName = "GCITS Alert Manager Demo"
   
# Change this to true if you would like to overwrite any existing applications with matching names. 
$removeExistingAppWithSameName = $false
# Modify the homePage, appIdURI and logoutURI values to whatever valid URI you like. 
# They don't need to be actual addresses, so feel free to make something up.
$homePage = "https://secure.gcits.com"
$appIdURI = "https://secure.gcits.com/$((New-Guid).ToString())"
$logoutURI = "https://portal.office.com"
   
$URIForApplicationPermissionCall = "https://graph.microsoft.com/beta/security/secureScores"
$ApplicationPermissions = "SecurityEvents.ReadWrite.All Directory.Read.All Sites.Manage.All"
   
Function Add-ResourcePermission($requiredAccess, $exposedPermissions, $requiredAccesses, $permissionType) {
    foreach ($permission in $requiredAccesses.Trim().Split(" ")) {
        $reqPermission = $null
        $reqPermission = $exposedPermissions | Where-Object {$_.Value -contains $permission}
        Write-Host "Collected information for $($reqPermission.Value) of type $permissionType" -ForegroundColor Green
        $resourceAccess = New-Object Microsoft.Open.AzureAD.Model.ResourceAccess
        $resourceAccess.Type = $permissionType
        $resourceAccess.Id = $reqPermission.Id    
        $requiredAccess.ResourceAccess.Add($resourceAccess)
    }
}
   
Function Get-RequiredPermissions($requiredDelegatedPermissions, $requiredApplicationPermissions, $reqsp) {
    $sp = $reqsp
    $appid = $sp.AppId
    $requiredAccess = New-Object Microsoft.Open.AzureAD.Model.RequiredResourceAccess
    $requiredAccess.ResourceAppId = $appid
    $requiredAccess.ResourceAccess = New-Object System.Collections.Generic.List[Microsoft.Open.AzureAD.Model.ResourceAccess]
    if ($requiredDelegatedPermissions) {
        Add-ResourcePermission $requiredAccess -exposedPermissions $sp.Oauth2Permissions -requiredAccesses $requiredDelegatedPermissions -permissionType "Scope"
    } 
    if ($requiredApplicationPermissions) {
        Add-ResourcePermission $requiredAccess -exposedPermissions $sp.AppRoles -requiredAccesses $requiredApplicationPermissions -permissionType "Role"
    }
    return $requiredAccess
}
Function New-AppKey ($fromDate, $durationInYears, $pw) {
    $endDate = $fromDate.AddYears($durationInYears) 
    $keyId = (New-Guid).ToString()
    $key = New-Object Microsoft.Open.AzureAD.Model.PasswordCredential($null, $endDate, $keyId, $fromDate, $pw)
    return $key
}
   
Function Test-AppKey($fromDate, $durationInYears, $pw) {
   
    $testKey = New-AppKey -fromDate $fromDate -durationInYears $durationInYears -pw $pw
    while ($testKey.Value -match "\+" -or $testKey.Value -match "/") {
        Write-Host "Secret contains + or / and may not authenticate correctly. Regenerating..." -ForegroundColor Yellow
        $pw = Initialize-AppKey
        $testKey = New-AppKey -fromDate $fromDate -durationInYears $durationInYears -pw $pw
    }
    Write-Host "Secret doesn't contain + or /. Continuing..." -ForegroundColor Green
    $key = $testKey
   
    return $key
}
   
Function Initialize-AppKey {
    $aesManaged = New-Object "System.Security.Cryptography.AesManaged"
    $aesManaged.Mode = [System.Security.Cryptography.CipherMode]::CBC
    $aesManaged.Padding = [System.Security.Cryptography.PaddingMode]::Zeros
    $aesManaged.BlockSize = 128
    $aesManaged.KeySize = 256
    $aesManaged.GenerateKey()
    return [System.Convert]::ToBase64String($aesManaged.Key)
}
function Confirm-MicrosoftGraphServicePrincipal {
    $graphsp = Get-AzureADServicePrincipal -All $true | Where-Object {$_.displayname -eq "Microsoft Graph"}
    if (!$graphsp) {
        $graphsp = Get-AzureADServicePrincipal -SearchString "Microsoft.Azure.AgregatorService"
    }
    if (!$graphsp) {
        Login-AzureRmAccount -Credential $credentials
        New-AzureRmADServicePrincipal -ApplicationId "00000003-0000-0000-c000-000000000000"
        $graphsp = Get-AzureADServicePrincipal -All $true | Where-Object {$_.displayname -eq "Microsoft Graph"}
    }
    return $graphsp
}
Write-Host "Connecting to Azure AD. The login window may appear behind Visual Studio Code."
Connect-AzureAD
   
Write-Host "Creating application in tenant: $((Get-AzureADTenantDetail).displayName)"
   
# Check for the Microsoft Graph Service Principal. If it doesn't exist already, create it.
$graphsp = Confirm-MicrosoftGraphServicePrincipal
   
$existingapp = $null
$existingapp = get-azureadapplication -SearchString $applicationName
if ($existingapp -and $removeExistingAppWithSameName) {
    Remove-Azureadapplication -ObjectId $existingApp.objectId
}
   
# RSPS 
$rsps = @()
if ($graphsp) {
    $rsps += $graphsp
    $tenant_id = (Get-AzureADTenantDetail).ObjectId
    $tenantName = (Get-AzureADTenantDetail).DisplayName
   
    # Add Required Resources Access (Microsoft Graph)
    $requiredResourcesAccess = New-Object System.Collections.Generic.List[Microsoft.Open.AzureAD.Model.RequiredResourceAccess]
    $microsoftGraphRequiredPermissions = Get-RequiredPermissions -reqsp $graphsp -requiredApplicationPermissions $ApplicationPermissions -requiredDelegatedPermissions $DelegatedPermissions
    $requiredResourcesAccess.Add($microsoftGraphRequiredPermissions)
   
    # Get an application key
    $pw = Initialize-AppKey
    $fromDate = [System.DateTime]::Now
    $appKey = Test-AppKey -fromDate $fromDate -durationInYears 99 -pw $pw
   
    Write-Host "Creating the AAD application $applicationName" -ForegroundColor Blue
    $aadApplication = New-AzureADApplication -DisplayName $applicationName `
        -HomePage $homePage `
        -ReplyUrls $homePage `
        -IdentifierUris $appIdURI `
        -LogoutUrl $logoutURI `
        -RequiredResourceAccess $requiredResourcesAccess `
        -PasswordCredentials $appKey `
        -AvailableToOtherTenants $true
       
    # Creating the Service Principal for the application
    $servicePrincipal = New-AzureADServicePrincipal -AppId $aadApplication.AppId
   
    Write-Host "Assigning Permissions" -ForegroundColor Yellow
     
    # Assign application permissions to the application
    foreach ($app in $requiredResourcesAccess) {
        $reqAppSP = $rsps | Where-Object {$_.appid -contains $app.ResourceAppId}
        Write-Host "Assigning Application permissions for $($reqAppSP.displayName)" -ForegroundColor DarkYellow
        foreach ($resource in $app.ResourceAccess) {
            if ($resource.Type -match "Role") {
                New-AzureADServiceAppRoleAssignment -ObjectId $serviceprincipal.ObjectId `
                    -PrincipalId $serviceprincipal.ObjectId -ResourceId $reqAppSP.ObjectId -Id $resource.Id
            }
        }
    }
     
    # This provides the application with access to your customer tenants.
    $group = Get-AzureADGroup -Filter "displayName eq 'Adminagents'"
    Add-AzureADGroupMember -ObjectId $group.ObjectId -RefObjectId $servicePrincipal.ObjectId
 
    Write-Host "App Created" -ForegroundColor Green
     
    # Define parameters for Microsoft Graph access token retrieval
    $client_id = $aadApplication.AppId;
    $client_secret = $appkey.Value
    $tenant_id = (Get-AzureADTenantDetail).ObjectId
    $resource = "https://graph.microsoft.com"
    $authority = "https://login.microsoftonline.com/$tenant_id"
    $tokenEndpointUri = "$authority/oauth2/token"
   
    # Get the access token using grant type password for Delegated Permissions or grant type client_credentials for Application Permissions
   
    $content = "grant_type=client_credentials&client_id=$client_id&client_secret=$client_secret&resource=$resource"
   
    # Try to execute the API call 6 times
   
    $Stoploop = $false
    [int]$Retrycount = "0"
    do {
        try {
            $response = Invoke-RestMethod -Uri $tokenEndpointUri -Body $content -Method Post -UseBasicParsing
            Write-Host "Retrieved Access Token" -ForegroundColor Green
            # Assign access token
            $access_token = $response.access_token
            $body = $null
   
            $body = Invoke-RestMethod `
                -Uri $UriForApplicationPermissionCall `
                -Headers @{"Authorization" = "Bearer $access_token"} `
                -ContentType "application/json" `
                -Method GET  `
   
            Write-Host "Retrieved Graph content" -ForegroundColor Green
            $Stoploop = $true
        }
        catch {
            if ($Retrycount -gt 5) {
                Write-Host "Could not get Graph content after 6 retries." -ForegroundColor Red
                $Stoploop = $true
            }
            else {
                Write-Host "Could not get Graph content. Retrying in 5 seconds..." -ForegroundColor DarkYellow
                Start-Sleep -Seconds 5
                $Retrycount ++
            }
        }
    }
    While ($Stoploop -eq $false)
   
    $appInfo = [pscustomobject][ordered]@{
        ApplicationName        = $ApplicationName
        TenantName             = $tenantName
        TenantId               = $tenant_id
        clientId               = $client_id
        clientSecret           = $client_secret
        ApplicationPermissions = $ApplicationPermissions
    }
       
    $AppInfo | Export-Csv C:\temp\AzureADApp.csv -Append -NoTypeInformation
}
else {
    Write-Host "Microsoft Graph Service Principal could not be found or created" -ForegroundColor Red
}

Script 2 – Syncing Microsoft Graph Security Alerts with a SharePoint List

From here on, I’ll refer to this script as the syncing script.

This script will run through your customers and retrieve their Microsoft Graph security alerts. If it finds a security alert in any of your customer tenants, it will create a SharePoint list if one doesn’t exist, then create or update an alert in SharePoint.

Important: This script will only create a SharePoint list if it detects an alert in any of your customers environments. This is because the script uses the properties of the alert object to define the columns required for the SharePoint list.

Unfortunately, if you’re fortunate enough to have no security alerts in your customer’s environments, the script will not create a SharePoint list for you. In this case, it’s still a good idea to run this script on a schedule so that when an alert is discovered, the list is created for you in SharePoint.

1. Double click the below script to select it.
2. Copy and paste the script into a new file in Visual Studio Code and save it with a .ps1 extension
3. Replace $appId, $secret, and $ourTenantId with your client ID, client secret and Tenant Id values retrieved from the CSV exported by Script 1.
4. Press F5 to run the script and wait for it to complete.

Script to sync Microsoft Graph Security Alerts with a SharePoint List

$appId = "ENTER_YOUR_CLIENTID_HERE"
$secret = "ENTER_YOUR_CLIENTSECRET_HERE"
$ourTenantId = "ENTER_YOUR_CLIENTSECRET_HERE"
$ListName = "Alert Register"
$siteid = "root"
$graphBaseUri = "https://graph.microsoft.com/v1.0"

function Get-GCITSAccessToken($appCredential, $tenantId) {
    $client_id = $appCredential.appID
    $client_secret = $appCredential.secret
    $tenant_id = $tenantid
    $resource = "https://graph.microsoft.com"
    $authority = "https://login.microsoftonline.com/$tenant_id"
    $tokenEndpointUri = "$authority/oauth2/token"
    $content = "grant_type=client_credentials&client_id=$client_id&client_secret=$client_secret&resource=$resource"
    $response = Invoke-RestMethod -Uri $tokenEndpointUri -Body $content -Method Post -UseBasicParsing
    $access_token = $response.access_token
    return $access_token
}

function Get-GCITSMSGraphResource($Resource) {
    $graphBaseUri = "https://graph.microsoft.com/v1.0"
    $values = @()
    $result = Invoke-RestMethod -Uri "$graphBaseUri/$resource" -Headers $headers
    if ($result.value) {
        $values += $result.value
        if ($result."@odata.nextLink") {
            do {
                $result = Invoke-RestMethod -Uri $result."@odata.nextLink" -Headers $headers
                $values += $result.value
            } while ($result."@odata.nextLink")
        }
    }
    else {
        $values = $result
    }
    return $values
}


function New-GCITSSharePointColumn($Name, $Type, $Indexed, $longText) {
    if ($longText) {
        $column = [ordered]@{
            name    = $Name
            indexed = $Indexed
            $Type   = @{
                maxLength                   = 0
                allowMultipleLines          = $True
                appendChangesToExistingText = $False
                linesForEditing             = 6
                textType                    = "plain"
            }
            
        }  
    }
    else {
        $column = [ordered]@{
            name    = $Name
            indexed = $Indexed
            $Type   = @{}
        }    
    }
    return $column
}

function New-GCITSSharePointColumn($Name, $Type, $Indexed, $lookupListName, $lookupColumnPrimaryName, $lookupColumnName, $longText) {
    if ($longText) {
        $column = [ordered]@{
            name    = $Name
            indexed = $Indexed
            $Type   = @{
                maxLength          = 0
                allowMultipleLines = $True
            }
            
        }  
    }
    else {
        $column = [ordered]@{
            name    = $Name
            indexed = $Indexed
            $Type   = @{}
        }
     
        if ($lookupListName -and $type -contains "lookup") {
            $list = Get-GCITSSharePointList -ListName $lookupListName
            if ($list) {
                $column.lookup.listId = $list.id
                $column.lookup.columnName = $lookupColumnName
            }
        }
    }

    return $column
}
 
function New-GCITSSharePointList ($Name, $ColumnCollection) {
    $list = @{
        displayName = $Name
        columns     = $columnCollection
    } | Convertto-json -Depth 10
      
    $newList = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists/" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method POST -Body $list
    return $newList
}
 
function Remove-GCITSSharePointList ($ListId) {
    $removeList = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists/$ListId" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method DELETE
    return $removeList
}
 
function Remove-GCITSSharePointListItem ($ListId, $ItemId) {
    $removeItem = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists/$ListId/items/$ItemId" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method DELETE
    return $removeItem
}
 
function New-GCITSSharePointListItem($ItemObject, $ListId) {
 
    $itemBody = @{
        fields = $ItemObject
    } | ConvertTo-Json -Depth 10
 
    $listItem = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists/$listId/items" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method Post `
        -Body $itemBody
}
 
function Get-GCITSSharePointListItem($ListId, $ItemId, $Query) {
 
    if ($ItemId) {
        $listItem = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items/$ItemId `
            -Method Get -headers $SPHeaders `
            -ContentType application/json
        $value = $listItem
    }
    elseif ($Query) {
        $listItems = $null
        $listItems = Invoke-RestMethod -Uri "$graphBaseUri/sites/$siteid/lists/$listId/items/?expand=fields&`$filter=$Query" `
            -Method Get -headers $SPHeaders `
            -ContentType application/json  
        $value = @()
        $value = $listItems.value
        if ($listitems."@odata.nextLink") {
            $nextLink = $true
        }
        if ($nextLink) {
            do {
                $listItems = Invoke-RestMethod -Uri  $listitems."@odata.nextLink"`
                    -Method Get -headers $SPHeaders `
                    -ContentType application/json
                $value += $listItems.value
                if (!$listitems."@odata.nextLink") {
                    $nextLink = $false
                }
            } until (!$nextLink)
        }
    }
    else {
        $listItems = $null
        $listItems = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items?expand=fields `
            -Method Get -headers $SPHeaders `
            -ContentType application/json  
        $value = @()
        $value = $listItems.value
        if ($listitems."@odata.nextLink") {
            $nextLink = $true
        }
        if ($nextLink) {
            do {
                $listItems = Invoke-RestMethod -Uri  $listitems."@odata.nextLink"`
                    -Method Get -headers $SPHeaders `
                    -ContentType application/json
                $value += $listItems.value
                if (!$listitems."@odata.nextLink") {
                    $nextLink = $false
                }
            } until (!$nextLink)
        }
    }
    return $value
}
 
function Set-GCITSSharePointListItem($ListId, $ItemId, $ItemObject) {
    $listItem = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items/$ItemId/fields `
        -Method Patch -headers $SPHeaders `
        -ContentType application/json `
        -Body ($itemObject | ConvertTo-Json)
    $return = $listItem
}

function Get-GCITSSharePointList($ListName) {
    $list = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists?expand=columns&`$filter=displayName eq '$ListName'" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method GET
    $list = $list.value
    return $list
}

$appCredential = @{
    AppId  = $appid
    Secret = $secret
}


$accessToken = Get-GCITSAccessToken -appCredential $appCredential -tenantId $ourTenantId
$SPHeaders = @{Authorization = "Bearer $accesstoken"}

$headers = @{
    Authorization = "Bearer $accesstoken"
}

$customers = @()
$customers += @{
    customerid        = $ourtenantid
    defaultDomainName = "gcits.com"
    displayName       = "GCITS"
}
$customers += Get-GCITSMSGraphResource -Resource contracts
$existingItems = $null
foreach ($customer in $customers) {

    Write-Output $customer.displayName
    $tenant_id = $customer.customerid
    $accessToken = Get-GCITSAccessToken -appCredential $appCredential -tenantId $tenant_id
    $headers = @{
        Authorization = "Bearer $accessToken"
    }
    try {
        $alerts = Get-GCITSMSGraphResource -Resource security/alerts
    }
    catch {
        Write-Output "Couldn't retrieve alerts for $($customer.displayName)"
    }
    
    if ($alerts.id) {
        
        Write-Output "Found $($alerts.count) alerts for $($customer.displayname)"
        $alertArray = @()
        $alerts = $alerts | Where-Object {$_.azureTenantId -eq $tenant_id}
        foreach ($alert in $alerts) {
            $properties = $alert | get-member | Where-Object {$_.membertype -eq "NoteProperty"}
            $object = [pscustomobject][ordered]@{}
            $Object | Add-Member Customer $customer.displayname
            foreach ($property in $properties) {
                if ($property.definition -match "Object\[\] " -or $property.Definition -match "System.Management.Automation.PSCustomObject") {
                    if ($alert.$($property.name)) {
                        $stringArray = @()
                        foreach ($propertyObject in $alert.$($property.name)) {
                            $subProperties = $propertyObject | get-member | Where-Object {$_.membertype -eq "NoteProperty"}
                            foreach ($sub in $subProperties) {
                                $stringArray += "$($sub.name): $($propertyObject.$($sub.name))"
                            }
                        }
                        $string = $stringArray -join "`n"
                        $object | Add-Member $($property.name) $string
                    }
                    else {
                        $object | Add-Member $($property.name) " "
                    }
                }
                else {
                    if ($alert.$($property.name)) {
                        if ($property.name -eq "id") {
                            $object | Add-Member alertId $alert.$($property.name)
                        }
                        elseif ($property.name -eq "title") {
                            $object | Add-Member Title $alert.$($property.name)
                        }
                        else {
                            $object | Add-Member $($property.name) $alert.$($property.name)
                        }
                    }
                    else {
                        $object | Add-Member $($property.name) " "
                    }
                }   
            }
            $alertArray += $object
        }
        $list = Get-GCITSSharePointList -ListName $ListName
        if (!$list) {
            $columnCollection = @()
            $fields = $object | get-member | Where-Object {$_.membertype -eq "NoteProperty"}
            
            foreach ($field in $fields) {
                if ($field.name -notcontains "title") {
                    if ($field.name -ne "customer" -and $field.name -ne "severity" -and $field.name -ne "status" -and $field.name -ne "azureTenantId" -and $field.name -ne "category") {
                        $columnCollection += New-GCITSSharePointColumn -Name $field.name -Type text -Indexed $false -longText $true
                    }
                    else {
                        $columnCollection += New-GCITSSharePointColumn -Name $field.name -Type text -Indexed $true -longText $false
                    }
                }
            }
            
            $list = New-GCITSSharePointList -Name $ListName -ColumnCollection $columnCollection
            $list = Get-GCITSSharePointList -ListName $ListName
        }
        else {
            $existingItems = Get-GCITSSharePointListItem -ListId $list.id
        }

        $columns = $list.columns.name
        foreach ($alert in $alertArray) {
            $properties = $alert.psobject.properties.name
            foreach ($property in $properties) {
                if ($columns -notcontains $property) {
                    $alert.PSObject.Properties.Remove($property)
                }
            }
            $matchingItem = $existingItems | Where-Object {$_.fields.alertid -eq $alert.alertid} | Select-Object -First 1
            if (!$matchingItem) {
                Write-Host "Creating item"
                New-GCITSSharePointListItem -ListId $list.id -ItemObject $alert
            }
            else {
                Write-Host "Updating item"
                Set-GCITSSharePointListItem -ListId $list.id -ItemId $matchingItem.id -ItemObject $alert
            }
        }
    }
}

Formatting your Alert Register SharePoint list

1. Provided some security alerts were found, you’ll find your new SharePoint list by navigating to your root SharePoint site at https://yourtenantname.sharepoint.com.
2. Click Site Contents, and find the Alert Register list
3. When you first visit the SharePoint list, you may notice that the default view has no columns. (I’m not sure why this sometimes happens, it may be because there are too many columns for the default view). If this happens to you, click Edit Current View under the All Items button, then deselect a bunch of columns that you don’t want to see in the default list view and click Save.
4. Rearrange the columns as you see fit by dragging and dropping the column headers.
5. You can also use column formatting in SharePoint to highlight alert severity and status.
6. To do this, click the status column and choose Column settings, Format this column
7. Copy and paste in the following JSON Schema

   {
     "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
     "elmType": "div",
     "attributes": {
       "class": "=if(@currentField == 'resolved', 'sp-field-severity--good', 'sp-field-severity--severeWarning') + ' ms-fontColor-neutralSecondary'"
     },
     "children": [
       {
         "elmType": "span",
         "style": {
           "display": "inline-block",
           "padding": "0 4px"
         },
         "attributes": {
           "iconName": "=if(@currentField == 'resolved', 'CheckMark', 'Warning')"
         }
       },
       {
         "elmType": "span",
         "txtContent": "@currentField"
       }
     ]
   }

8. Click the severity column, choose Column settings, Format this column
9. Copy and paste in the following JSON Schema

   {
     "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
     "elmType": "div",
     "attributes": {
       "class": "=if(@currentField == 'informational', 'sp-field-severity--good', if(@currentField == 'low', 'sp-field-severity--low', if(@currentField == 'medium', 'sp-field-severity--warning', if(@currentField == 'high', 'sp-field-severity--severeWarning', '')))) + ' ms-fontColor-neutralSecondary'"
     },
     "children": [
       {
         "elmType": "span",
         "style": {
           "display": "inline-block",
           "padding": "0 4px"
         },
         "attributes": {}
       },
       {
         "elmType": "span",
         "txtContent": "@currentField"
       }
     ]
   }

10. You should now have columns that change colour depending on the severity and status of the alert.
11. If you have alerts with a status of newAlert, you can easily create a new alert view by filtering your list by Status – newAlert.
12. You can save this view as a new view by clicking All Items – Save view as
13. Call it New Alerts, then return to that menu to Set current view as default

Managing Microsoft Graph security alerts with Microsoft Flow and Azure Functions

To make the alert register interactive, we’ll be using Azure Functions. PowerShell Azure Functions are an extremely handy tool for automating processes. Essentially, they’re just PowerShell scripts that can be scheduled to run on a timer or on demand.

Set up a HTTP Triggered Azure Function

1. Log into https://portal.azure.com and setup an Azure Function app if you don’t already have one.

Note: when setting up an Azure Function app, you’ll be prompted to choose whether you want it on a Consumption Plan or App Service Plan.

Consumption plans are much cheaper than App Service Plans, however the scripts can only run for up to 5 minutes. App Service Plans allow you to run long scripts, but you’re paying for a reserved underlying VM. If you have spare Azure credits I recommend using the App Service plan for your new function app.

If you don’t use Azure Functions on an app service plan already, you may decide to run the main syncing script on a secure server with a scheduled task, and run the alert handler HTTP function in an Azure Function app on a consumption plan.

1. Once you have set up your function app, we’ll create a new Azure Function inside it.
2. Choose to create a HTTP Triggered PowerShell Azure Function. You may need to ensure that Experimental Language Support is enabled on the top right. You can call this function HT-ResolveAlert
   
3. Open the function and paste in the below script (HTTP Triggered Azure Function Script), remember to update the $appId, $secret and $ourtenantid values with the same values from the syncing script.
4. Click Save
5. Click Get function URL and copy the URL of the function. Save it in a notepad file until we can use it in our Microsoft Flows.
6. Next we’ll set up the Script 2 (the syncing script) to run on a timer. Create a timer triggered PowerShell Azure Function. I called this one TT-SyncAlertsToSharePoint.
7. You can have it run every 30 minutes day using the following cron trigger.

   0 */30 * * * *

8. Paste the syncing script into the new timer triggered function and click Save and Run.

HTTP Triggered Azure Function Script

# POST method: $req
$in = Get-Content $req -Raw | ConvertFrom-Json

$appId = "ENTER_YOUR_CLIENTID_HERE"
$secret = "ENTER_YOUR_CLIENTSECRET_HERE"
$ourTenantId = "ENTER_YOUR_TENANTID_HERE"
$ListName = "Alert Register"
$siteid = "root"
$graphBaseUri = "https://graph.microsoft.com/v1.0"

function Get-GCITSAccessToken($appCredential, $tenantId) {
    $client_id = $appCredential.appID
    $client_secret = $appCredential.secret
    $tenant_id = $tenantid
    $resource = "https://graph.microsoft.com"
    $authority = "https://login.microsoftonline.com/$tenant_id"
    $tokenEndpointUri = "$authority/oauth2/token"
    $content = "grant_type=client_credentials&client_id=$client_id&client_secret=$client_secret&resource=$resource"
    $response = Invoke-RestMethod -Uri $tokenEndpointUri -Body $content -Method Post -UseBasicParsing
    $access_token = $response.access_token
    return $access_token
}

function Get-GCITSMSGraphResource($Resource) {
    $graphBaseUri = "https://graph.microsoft.com/v1.0"
    $values = @()
    $result = Invoke-RestMethod -Uri "$graphBaseUri/$resource" -Headers $headers
    if ($result.value) {
        $values += $result.value
        if ($result."@odata.nextLink") {
            do {
                $result = Invoke-RestMethod -Uri $result."@odata.nextLink" -Headers $headers
                $values += $result.value
            } while ($result."@odata.nextLink")
        }
    }
    else {
        $values = $result
    }
    return $values
}
function Get-GCITSSharePointListItem($ListId, $ItemId, $Query) {
 
    if ($ItemId) {
        $listItem = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items/$ItemId `
            -Method Get -headers $SPHeaders `
            -ContentType application/json
        $value = $listItem
    }
    elseif ($Query) {
        $listItems = $null
        $listItems = Invoke-RestMethod -Uri "$graphBaseUri/sites/$siteid/lists/$listId/items/?expand=fields&`$filter=$Query" `
            -Method Get -headers $SPHeaders `
            -ContentType application/json  
        $value = @()
        $value = $listItems.value
        if ($listitems."@odata.nextLink") {
            $nextLink = $true
        }
        if ($nextLink) {
            do {
                $listItems = Invoke-RestMethod -Uri  $listitems."@odata.nextLink"`
                    -Method Get -headers $SPHeaders `
                    -ContentType application/json
                $value += $listItems.value
                if (!$listitems."@odata.nextLink") {
                    $nextLink = $false
                }
            } until (!$nextLink)
        }
    }
    else {
        $listItems = $null
        $listItems = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items?expand=fields `
            -Method Get -headers $SPHeaders `
            -ContentType application/json  
        $value = @()
        $value = $listItems.value
        if ($listitems."@odata.nextLink") {
            $nextLink = $true
        }
        if ($nextLink) {
            do {
                $listItems = Invoke-RestMethod -Uri  $listitems."@odata.nextLink"`
                    -Method Get -headers $SPHeaders `
                    -ContentType application/json
                $value += $listItems.value
                if (!$listitems."@odata.nextLink") {
                    $nextLink = $false
                }
            } until (!$nextLink)
        }
    }
    return $value
}

function Set-GCITSSharePointListItem($ListId, $ItemId, $ItemObject) {
    $listItem = Invoke-RestMethod -Uri $graphBaseUri/sites/$siteid/lists/$listId/items/$ItemId/fields `
        -Method Patch -headers $SPHeaders `
        -ContentType application/json `
        -Body ($itemObject | ConvertTo-Json)
    $return = $listItem
}

function Get-GCITSSharePointList($ListName) {
    $list = Invoke-RestMethod `
        -Uri "$graphBaseUri/sites/$siteid/lists?expand=columns&`$filter=displayName eq '$ListName'" `
        -Headers $SPHeaders `
        -ContentType "application/json" `
        -Method GET
    $list = $list.value
    return $list
}

$appCredential = @{
    AppId  = $appid
    Secret = $secret
}


$accessToken = Get-GCITSAccessToken -appCredential $appCredential -tenantId $ourTenantId
$SPHeaders = @{Authorization = "Bearer $accesstoken"}



$list = Get-GCITSSharePointList -ListName $ListName

$sharePointItems = @()
$item = Get-GCITSSharePointListItem -ListId $list.id -ItemId $in.sharepointid

if ($in.allAlerts) {
    $allItems = Get-GCITSSharePointListItem -ListId $list.id | Where-Object {$_.fields.azureTenantId -eq $item.fields.azureTenantId} #-and $_.fields.status -ne "resolved"}
    $sharePointItems += $allItems
}
else {
    $sharePointItems += $item
}

$accessToken = Get-GCITSAccessToken -appCredential $appCredential -tenantId $item.fields.azureTenantId
$headers = @{
    Authorization = "Bearer $accesstoken"
}

foreach ($item in $sharePointItems) {
    $alert = Get-GCITSMSGraphResource -Resource "/security/alerts/$($item.fields.alertId)"

    $updateItem = @{
        assignedTo        = $in.assignedTo
        comments          = [array]$in.comments
        status            = $in.Status
        vendorInformation = $alert.vendorInformation
    } | ConvertTo-Json
    
    $updatedAlert = Invoke-RestMethod -uri $graphBaseUri/security/alerts/$($item.fields.alertId) `
        -Method patch -Headers $headers -Body $updateItem -ContentType application/json
    
    $alert = Get-GCITSMSGraphResource -Resource "/security/alerts/$($item.fields.alertId)"
    $updateSharePoint = @{
        status     = $alert.status
        comments   = ($alert.comments -join ", ")
        assignedTo = $alert.assignedTo
    }
    
    Set-GCITSSharePointListItem -ListId $list.id -ItemId $item.id -ItemObject $updateSharePoint  
}


Out-File -Encoding Ascii -FilePath $res -inputObject "Completed request to update alert"

Get notified of new alerts using Microsoft Flow

You can get notified of new alerts in the Alert Register in a couple of ways. You can set up an alert on the list itself and receive an email when an alert appears, or you can use Microsoft Flow. In our case, I’ve set up both – the built-in SharePoint alerts go to me, and the flow alerts go to our service desk. This is just because our service desk tool doesn’t seem to receive the built in SharePoint Alerts.

Setting up new item alerts using SharePoint

1. Click the 3 dots next to the Flow menu item in SharePoint and choose Alert me
2. Modify the Change Type option to New items are added
3. You can also add additional email addresses to receive the alerts in the Send Alerts To field.

Setting up new item alerts using Microsoft Flow

1. Navigate to Flow, Create a flow
2. Select one of the built in flow options or just choose the complete a custom action flow.
3. Add in a send an email step to send an email to your support desk when a new item is added to the list

Resolve Microsoft Graph security alerts with Microsoft Flow

Now that we’ve created out Azure Functions and set up our alert notifications, we can set up some Microsoft Flows to manage the alerts. These flows will allow us to resolve a single alert, or resolve all alerts for a tenant in one go. They will also allow us to add comments which will be associated with the alert in the customers tenant.

Create a flow to resolve an alert

1. On the alert register list, click the three dots next to the title of an existing item, then choose Flow, Create a flow
2. Rename the flow to Resolve Alert
3. On the For a selected item action, click Show advanced options, Add an input and choose Text.
4. Call the new input Comments.
5. Next, add in a HTTP Action
6. Set the Method to POST and paste in the URI of the HTTP Triggered Azure Function that we saved earlier.
7. Add a Content-Type header with application/json, and paste in the following JSON.

   {
     "SharePointId": "@{body('Get_item')?['ID']}",
     "Status": "resolved",
     "allAlerts": false,
   "comments":"@{triggerBody()['text']}",
   "assignedTo": "@{triggerOutputs()['headers']['x-ms-user-name-encoded']} - @{triggerOutputs()['headers']['x-ms-user-email-encoded']}"
   }

8. Your flow should now look like this
9. Click Save, return to your list of flows, and open the flow you just created.
10. Click Add another owner under the Owners section, and add the group or members that will need to use the flow

Create a flow to resolve all alerts for that tenant

1. On the Resolve Alert flow, click More, Save As
2. Save the alert as Resolve all alerts for tenant
3. Return to your list of flows and open the Resolve all alerts for tenant flow
4. Click the On switch to enable it
5. Set the flow owners to the appropriate people or groups
6. Edit the flow and replace the JSON in the HTTP Step with the following JSON.

   {
     "SharePointId": "@{body('Get_item')?['ID']}",
     "Status": "resolved",
     "allAlerts": true,
   "comments":"@{triggerBody()['text']}",
   "assignedTo": "@{triggerOutputs()['headers']['x-ms-user-name-encoded']} - @{triggerOutputs()['headers']['x-ms-user-email-encoded']}"
   }

7. Click Save

And that’s it! When your team receives a security alert, they can now investigate, add comments and resolve the alert directly from your SharePoint list.