tech --verbose

Follow me on GitHub

Azure timer function to scan for any pwned email addresses and store pwned items in SharePoint list • November 01, 2017

I wrote an Azure timer function that pulls email addresses from Azure AD using the Microsoft Graph API. Those email addresses are submitted to HaveIBeenPwned.com. If any results are returned, they are stored in a SharePoint list, again using the Microsoft Graph API. Finally, I created a Flow workflow on the SharePoint list to send a notification to a Microsoft Teams channel any time a new item is added to the SharePoint list.

Visual Studio

Ensure you have the Azure development workload install as part of Visual Studio 2017

  1. In Visual Studio, select ‘File’ -> ‘New’ -> ‘Project…’. Under ‘Cloud’, select Azure Functions. Name your project and click ‘OK’

visual studio new project

  1. The Azure Storage Emulator does not support Azure Functions locally, so a connection to an Azure Storage Account is required. To connect to an Azure Storage Account from your Azure Function, open Cloud Explorer (under the View menu or Ctrl+\, Ctrl+X). Connect to Azure, and navigate to a Storage Account. If you do not have a Storage Account, go to the Azure portal and create one (About Azure storage accounts). Click on your storage account and click on the Properties tab. There will be a property called ‘Primary Connection String’. Copy the connection and paste it into the value of AzureWeJobsStorage in the local.settings.json within the project.

storage account

  1. Right click on your Project and from the context menu, select ‘Add’ -> ‘New Item…’. Search or scroll through items to find ‘Azure Function’. Enter a name for the class and click ‘Add’.

add new function

  1. Add a ‘Timer trigger’ function. The Schedule is based on NCrontab in the 6 field format. Note: the schedule I attempted to use was every Sunday at midnight. I messed up the schedule in the image below. It should be: 0 0 0 * * 0.

timer trigger


see https://github.com/dbarkwell/EnterprisePwned

The README.md contains the local.settings.json template to use. Enter values for the following fields:

  • AzureWebJobsStorage
  • Tenant
  • ClientId
  • ClientSecret
  • SiteId
  • ListId

Register Application

  1. Navigate to https://apps.dev.microsoft.com in your browser.

  2. Click ‘Add an app’.

  3. Enter your Application Name. Take note of the Application Id. This will be your Client Id.

  4. Click ‘Generate New Password’. Take note of the Password. This will be your Client Secret.

  5. Click ‘Add Platform’. Add a web platform. For local testing, add your localhost address.

  6. Under Microsoft Graph Permissions, click ‘Add’ next to Application Permissions. This will add permissions for an app as opposed to added delegated permissions to a user. Add ‘Directory.Read.All’, ‘Sites.ReadWrite.All’, and ‘User.Read.All’ permissions.

  7. Click ‘Save’.

  8. Perform a GET request to the following address:

https://login.microsoftonline.com/{tenant}/adminconsent?client_id={Client Id / Application Id}&redirect_uri={localhost}

Replace tenant, client id, and localhost values. This will authorize the changes you just made.


  1. Create a SharePoint (Office 365) site or add a list to an existing SharePoint site.

  2. The list should contain the following fields:

SP list

  1. The Key field is used to only store unique values in the list.

SP key


Whenever a new item gets added to a list, send a notification to a Microsoft Teams channel.

  1. From the SharePoint list, click on the ‘Flow’ dropdown. Click ‘Create a Flow’.

SP Flow

  1. Click ‘Show more’. Click ‘See more templates’.

  2. Click on ‘Create a flow from blank’.

Create Flow

  1. In the search box for ‘Add a trigger’, search for SharePoint. Select ‘SharePoint - When an item is created’.

SP trigger

  1. Enter site address if it does not appear in the drop down. Select the list name. Click on ‘New step’.

SP item created trigger

  1. Add a condition. Add: @not(empty(triggerBody()?[‘Title’])) as the condition. This will only trigger if there is an email address.

Add condition

  1. Under ‘If yes’, click ‘Add an action’.

Choose action

  1. Search for Teams. Select ‘Microsoft Teams - Post message’.

  2. Enter the ‘Team Id’, ‘Channel Id’, and ‘Message’. For the ‘Message’, you can add in fields from the SharePoint list.

Teams action

Teams message

  1. Click ‘Save flow’

Save Flow





  1. Log into the Azure Portal

  2. Click ‘New’ to create a resource.

New resource

  1. Search for ‘function’. Click ‘Function App’ in the ‘Web + Mobile’ category.

Function app

  1. Click ‘Create’


  1. Add ‘App name’, ‘Subscription’, ‘Resource Group’, ‘Hosting Plan’, ‘Location’, and ‘Storage’. Click ‘Create’.

Setup Function

  1. Once your Function App has been deployed, you can navigate to it from the Notifications, or from the left Navigation.

  2. Click on + beside ‘Functions’ under your Function App heading.

Function deploy

  1. These steps will outline deploying from source control. The function could also be added directly in the Azure Portal. Click ‘Start from source control’.

Deploy from source

  1. Click ‘Setup’ to setup the deployment. Click ‘Choose Source’. You can connect to serveral source control repositories or file stores. Normally I deploy from Visual Studio Team Services, but in this example I will deploy from GitHub. Click ‘GitHub’

Source control

  1. Add ‘Authorization’, ‘Choose project’, ‘Choose branch’ and click ‘Ok’.

Deploy setup

  1. After the Deployment has been setup, wait for the function to deploy and navigate back to the Function Apps screen. Click ‘Application settings’.

Application settings

  1. Under ‘Application settings’, click ‘Add new setting’. This is where you will add the Environment Variables:
  • Tenant
  • ClientId
  • ClientSecret
  • SiteId
  • ListId

AzureWebJobsStorage has already been populated from the Function setup, so you do not need to change that setting. Click ‘Save’.

Environment Variables

  1. Restart your function.


Final Thoughts

At some point I will go back and change the SharePoint functionality. There should be two lists. One list would hold the breach information and the other would hold the breached addresses with a lookup to the breach information.