Loading...

Detecting PII exists in SharePoint List using AWS Comprehend

Detecting PII exists in SharePoint List using AWS Comprehend

Personally Identifiable Information (PII) is information that, when used alone or with other relevant data, can identify an individual. Sensitive personally identifiable information can include full name, SSN, driver’s license, financial information and medical records. As PII can be used to identify an individual, signify a major threat to companies. If breached, this information can lead to lawsuits and can damage company’s trustworthiness.

Amazon Comprehend is a natural-language processing (NLP) service that uses machine learning to uncover valuable insights and connections in text and documents. We can now use Amazon Comprehend to protect and control who has access to sensitive data by identifying and redacting Personally Identifiable Information (PII) from text and documents.

Also, we can redact documents(stored in an Amazon S3 bucket) by using Amazon Comprehend asynchronous analysis job. We can choose redaction mode Replace to mask PII entity with character and replace the characters in PII entities with a symbol(!, #, $, %, &, *, or @). Asynchronous PII redaction batch processing will be a great use case for SharePoint Document Libraries.

In this post, I showcased using Amazon Comprehend to detect PII entities from a specific SharePoint List column and record the results in another list and CSV report via the AWS CLI and CLI for Microsoft 365. Take a look at this script, give it a try, and please send me your feedback via the contact page.

Thank you, Sriharsha M S, for your valuable article on this topic.

Prerequisites

  • CLI for Microsoft 365
  • AWS CLI
  • SharePoint Online Lists(as shown in screenshots)
    • PII List: Items containing dummy/real PII data
    • PII Audit: To store the PII Audit audit detials
  • Necessary permission to access SharePoint Lists, Amazon Comprehend, and to write CSV files in local machine via

Source & Target Lists

pii-source-list.png

pii-audit-report-list-schema.png

PowerShell Script

$spolHostName = "https://spridermvp.sharepoint.com"
$spolSiteRelativeUrl = "/sites/dev"
$spolListToAuditTitle = "PII List"
$spolListToSaveAuditResponse = "PII Audit"
$spolListFields = "ID,Title,Content"
$resultDir = "Output"
$executionDir = $PSScriptRoot
$outputDir = "$executionDir/$resultDir"

if (-not (Test-Path -Path "$outputDir" -PathType Container)) {
    Write-Host "Creating $outputDir folder..." -ForegroundColor Yellow
    New-Item -ItemType Directory -Path "$outputDir"
}

$spolSiteUrl = "${spolHostName}${spolSiteRelativeUrl}"
$spolListItems = m365 spo listitem list --title $spolListToAuditTitle --webUrl $spolSiteUrl  --fields $spolListFields -o json | ConvertFrom-Json -AsHashtable

if ($spolListItems.Count -gt 0) {
    ForEach ($spolListItem in $spolListItems) {
        $spolListItemId = $spolListItem.Id
        $spolListItemContent = $spolListItem.Content
        Write-Host "Auditing Item Id: ${spolListItemId} in ${spolListToAuditTitle}" -ForegroundColor Green
        $response = aws comprehend detect-pii-entities --language-code en --text $spolListItemContent
        $auditResponse = $response | ConvertFrom-Json -AsHashtable 
        if ($auditResponse.Entities) {
            $auditEntitiesCount = $auditResponse.Entities.Count
            if ($auditEntitiesCount -gt 0) {
                Write-Host "Findings Count: ${auditEntitiesCount}" -ForegroundColor Magenta
                $piiFindings = @()
                ForEach ($piiEntity in $auditResponse.Entities) {
                    $piiFinding = New-Object -TypeName PSObject
                    $piiFinding | Add-Member -MemberType NoteProperty -Name "Score" -Value $piiEntity.Score
                    $piiFinding | Add-Member -MemberType NoteProperty -Name "Type" -Value $piiEntity.Type
                    $piiFinding | Add-Member -MemberType NoteProperty -Name "BeginOffset" -Value $piiEntity.BeginOffset
                    $piiFinding | Add-Member -MemberType NoteProperty -Name "EndOffset" -Value $piiEntity.EndOffset
                    $piiFindings += $piiFinding
                }
                $outputFilePath = "${outputDir}/$(get-date -f yyyyMMdd-HHmmss)-PIIFindings.csv"
                $piiFindings | Export-Csv -Path $outputFilePath -NoTypeInformation
                $auditEntry = m365 spo listitem add --contentType Item --listTitle $spolListToSaveAuditResponse --webUrl $spolSiteUrl --Title $spolListToAuditTitle --ItemID $spolListItemId --AuditCount $auditResponse.Entities.Count --AuditResult $outputFilePath -o json | ConvertFrom-Json -AsHashtable
                $auditEntryId = $auditEntry.Id
                Write-Host "Audit added for source Item Id: ${spolListItemId} with Item Id ${auditEntryId} in target list ${spolListToSaveAuditResponse}" -ForegroundColor Green
            }
            else {
                Write-Host "There are no findings in this item" -ForegroundColor Yellow
            }
        }
        else {
            Write-Host "There are no findings in this item" -ForegroundColor Yellow
        }
    }
}
else {
    Write-Host "No items in this list" -ForegroundColor Yellow
}

Output

pii-audit-report-list.png

pii-audit-report-data.png

Published on:

Learn more
Need help with this product?

We can help you with Detecting PII exists in SharePoint List using AWS Comprehend

If you want help implementing, troubleshooting, or improving this product, contact us and we’ll point you in the right direction.

Home | Joseph Velliah
Home | Joseph Velliah

Fulfilling God’s purpose for my life

Share post:

Related posts

Jev Does Not Paint: Putting TypeSafe in Front of Gemini

I wanted a reason to use TypeSafe that was not another chatbot wrapper. Most of the AI I wire into software still wants to talk. I needed some...

9 days ago

I Asked God to Hold My Hand

Almost 20 years ago, I was waiting outside my company to collect my documents and start my first IT job. I sat under a banyan tree. That day ...

17 days ago

Identity-Aware SRE Agents with kagent on Akamai LKE

I wanted a reason to put an AI agent in front of a real Kubernetes cluster and watch what happens when two different people ask it to fix the ...

1 month ago

Vasanam Studio: How I Built a Bible Verse Video Generator for My Church as a Hobby Project

Every morning at 5 AM, the women of my church gather for prayer. At the end of the session, our pastor’s wife shares a Bible verse and sends a...

3 months ago

The demo worked. That was the problem.

Over a weekend I built a small Kubernetes demo to play with zero trust. Three little services calling each other in a chain, a login page in f...

4 months ago

Notes from building an agent on AgentCore end to end

I wanted a reason to use AgentCore end to end. Runtime, memory, guardrails, identity, the whole thing. A Bible Q&A agent felt like a good ...

5 months ago

Building a Rust gRPC AI Security Gateway for LLM Traffic

I wanted a small, honest implementation of the GenAI governance shape in code: a component on every LLM call that applies policy first, option...

6 months ago

Claude Code Security: The Smart Way to Integrate AI

Anthropic just dropped Claude Code Security, and if you’re anywhere near AppSec or DevSecOps, you’ve probably already seen the debate lighting...

7 months ago

How I Built a Semantic Cache Using Only AWS Services

LLM calls are expensive and slow, but here’s the thing - users ask the same questions in different ways all the time. “What’s your refund poli...

8 months ago

How to Build Better AI Agent Tools: Cut Costs by 70% (MCP Server Case Study)

Building tools for AI agents isn’t the same as building regular APIs. This guide shows you how to design tools that reduce token costs by 60-7...

8 months ago

Newsletter

Get the latest Dynamics 365 and Power Platform content in your inbox

A curated digest of community blogs, product news, videos, and podcasts — delivered without the noise.

Weekly updates Unsubscribe anytime Fresh community picks
We use your email only for the newsletter and you can unsubscribe at any time.
By subscribing, you agree to the privacy policy.