Using a Logic App to monitor Storage Account activity from SFTP (blob delete, rename, directory create, delete, rename)

As a follow up to my previous post:

Using a Logic App to automate the copying of files that are uploaded to a Storage Account to SharePoint Online https://blog.terenceluk.com/2024/05/using-a-logic-app-to-automate-the-copying-of-files-that-are-uploaded-to-a-storage-account-to-sharepoint-online.html

One of the additional features the SFTP service required was to notify administrators for the following activities:

1. File name renames
2. Folder renames
3. Folder created
4. Folder deleted
5. File delete

This meant that an additional Logic App would need to be triggered when any of the above actions are performed so it can collect the information and send the information to a distribution group. The following are the steps of the configuration.

Step #1 – Create a Logic App with a “When a HTTP request is received” trigger 

Begin by creating a Logic App with a When a HTTP request is received trigger, save the configuration, reopen the trigger and copy the URL to notepad: The Logic App will need to have permissions to read blob contents of the storage account and the best way to provide this is to use a System assigned managed identity. Proceed to navigate to the Identity blade of the newly created Logic App and turn on the feature:

Next, navigate to the storage account and grant the managed identity Storage Blob Data Reader:

Step #2 – Configure an Event Grid Subscription

The next step is to configure an Event Subscription for the storage account that you want to monitor so that the monitored activities to the storage account will trigger an Event. The easiest way to do this is simply navigate to the storage account, select the Events blade, and click on + Event Subscription:

Fill out the following properties of the Event Subscription: Name: Provide a name for the Event Subscription Event Schema: Event Grid Schema Topic Type: This should already pre-populated with Storage account Source Resource: This should be pre-populated with the storage account name System Topic Name: As I already have an existing event subscription configured, this field is already populated but if your Storage Account does not have a subscription then provide a name for the System Topic Select the following Filter to Event Types:

1. Blob Deleted
2. Directory Created
3. Director Deleted
4. Blob Renamed
5. Directory Renamed

For the Endpoint Type, select Web Hook which will display the option to Configure an Endpoint. Click on the Configure an Endpoint URL to display the Subscriber endpoint pane on the right:

Paste the Logic App HTTP URL into the Subscriber endpoint that we pasted into notepad earlier and click on Confirm Selection:

Proceed to create the Event Subscription:

An Event Subscription will now be created for the storage account, which will generate an event whenever one of the events selected above happen in the storage account. Proceed to click into the subscription we have just created. Note that I already had an event subscription configured so there are 2 in the screenshot below.

Each Event Subscription provides customization options and the one we are interested in for this example is the Filters under the Filters tab:

The Filter to Event Types for this event subscription set to the 5 events selected earlier. These will capture events that are generated by the Storage Account API or from the portal. You can try deleting a file from the storage account to confirm the new Logic App will be triggered as shown in the following screenshot:

Note that the data.api value in the body is specified as DeleteFile in the screenshot above. The following is the full content of the body:

What we would like to monitor with this Logic App are SFTP activities so we’ll need to leverage Advanced Filters to filter SFTP events. To accomplish this, we’ll be using the SFTP events as defined in the following Microsoft document: Azure Blob Storage as an Event Grid source – Microsoft.Storage.BlobDeleted event (SFTP) https://learn.microsoft.com/en-us/azure/event-grid/event-schema-blob-storage?tabs=cloud-event-schema#microsoftstorageblobdeleted-event-sftp  These events identified in data.api are:

1. SftpRemove < Blob delete
2. SftpRename < Blob renamed, directory renamed
3. SftpMakeDir < Directory created
4. SftpRemoveDir < Directory deleted

Note that SftpRename is used for Blob renamed and Directory Renamed. Here are the JSON samples for the two as per the Microsoft document above:

Blob renamed

[{ “source”: “/subscriptions/{subscription-id}/resourceGroups/Storage/providers/Microsoft.Storage/storageAccounts/my-storage-account”, “subject”: “/blobServices/default/containers/testcontainer/blobs/my-renamed-file.txt”, “type”: “Microsoft.Storage.BlobRenamed”, “time”: “2022-04-25T19:13:00.1522383Z”, “id”: “831e1650-001e-001b-66ab-eeb76e069631”, “data”: { “api”: “SftpRename”, “requestId”: “831e1650-001e-001b-66ab-eeb76e000000”, “destinationUrl”: “https://my-storage-account.blob.core.windows.net/testcontainer/my-renamed-file.txt”, “sourceUrl”: “https://my-storage-account.blob.core.windows.net/testcontainer/my-original-file.txt”, “sequencer”: “00000000000004420000000000028963”, “identity”:”localuser”, “storageDiagnostics”: { “batchId”: “b68529f3-68cd-4744-baa4-3c0498ec19f0” } }, “specversion”: “1.0” }]

Directory Renamed

[{ “source”: “/subscriptions/{subscription-id}/resourceGroups/Storage/providers/Microsoft.Storage/storageAccounts/my-storage-account”, “subject”: “/blobServices/default/containers/testcontainer/blobs/my-renamed-directory”, “type”: “Microsoft.Storage.DirectoryRenamed”, “time”: “2022-04-25T19:13:00.1522383Z”, “id”: “831e1650-001e-001b-66ab-eeb76e069631”, “data”: { “api”: “SftpRename”, “requestId”: “831e1650-001e-001b-66ab-eeb76e000000”, “destinationUrl”: “https://my-storage-account.blob.core.windows.net/testcontainer/my-renamed-directory”, “sourceUrl”: “https://my-storage-account.blob.core.windows.net/testcontainer/my-original-directory”, “sequencer”: “00000000000004420000000000028963”, “identity”:”localuser”, “storageDiagnostics”: { “batchId”: “b68529f3-68cd-4744-baa4-3c0498ec19f0” } }, “specversion”: “1.0” }] Note that the type value is what differentiates renaming a file vs renaming a directory. Important note: Although the type field provided in the JSON file is named as type, what I noticed that the JSON body that is sent from the event subscription is actually eventType. This means that we should not be extract the field type in our logic app but rather look for eventType:

With the data.api identified and eventType understood, we can proceed to configure the advanced filters: Key: data.api Operator: String is in Value: SftpRemove SftpRename SftpMakeDir SftpRemoveDir

Proceed to save the configuration and test deleting a file via SFTP to confirm that an event is triggered as shown in the screenshot below:

Step #3 – Configure the Logic App to process SFTP uploaded files

Now that we have the events trigger in place, we want to extract the relevant information of the event and send an email notification. Understanding that the event types are:

1. SftpRemove
2. SftpRename
3. SftpMakeDir
4. SftpRemoveDir

We can map out the types of events into different cases:

The report we’ll create will be an email that provides the information in each of the table columns above. Begin by navigating to the Logic app designer of the Logic App, update the trigger When a HTTP request is received with a schema by clicking on Use sample payload to generate schema:

Defining a schema is important because if a schema is not defined then future steps will not allow you to selectively extract the various keys in the JSON payload.  One of the considerations to note for capturing the events in this context is that the contents of the body for the events are different. The following screenshot show how BlobDeleted information is different than BlobRenamed.

There are also differences between the other directory related events. What I’ve done is compare all of the 5 different events and created a sample JSON covering all the key value pairs:

Paste the above into the Enter or paste a sample JSON payload window:

Note that if the above fails to generate the schema, copy the content from here: https://github.com/terenceluk/Azure/blob/main/Logic%20App/Merged.json The Request Body JSON Schema should now be populated:

Given that we know the field type is incorrect, update the field to eventType:

Click on the + button to add an action and search for initialize variable: Provide a meaning name for this action, a name for the variable we are initializing, and select String as the type since this value will be text. This example will use eventType.

Proceed to add another step and search for set variable and select the action:

Provide a meaning for name for this action, select the previous initialized variable named Type and click into the Value text field, which will display 2 symbols to the left. Select the lightening icon:

Since a schema was defined for the inbound HTTP request, we will see the individual keys available for select. This variable is configured to store the subject value so proceed to select the eventType value under When a HTTP request is received. Note that there are more keys available than what is displayed and the additional ones can be displayed by clicking on the See More (19) text as shown in the screenshot below:

Click on the carrot > text to minimize the variable window and note that a For Each step (I’ve renamed it to Extract JSON Key Pairs) is automatically created for this Set Variable step because the action will traverse through JSON body payload to extract the type:

With the eventType value extracted and stored in a variable, initiate a test by renaming a folder and you should see an output similar to the following screenshot: Sample body:

Now that we’ve confirmed the eventType is extracted correctly, we’ll proceed to repeat the same procedure for the other fields of interest:

• subject
• destinationUrl
• sourceUrl

Create 3 additional Initialize variable action for each of the key value pairs:

Repeat the same for configuration for Set variable within the Extract JSON Key Pairs:

Save the Logic App and test the 5 different events to confirm the appropriate event values are captured. The following is an example of creating a new directory:

DirectoryCreated

Note that BlobDeleted, DirectoryCreated, and DirectoryDeleted returns the value null for destinationUrl and sourceUrl as these events do not have those key value pairs.

Step #4 – Configure the Logic App to send notification emails

We can now configure the notification emails now that we’ve successfully extracted the values of the events we want to capture. Begin by initializing a variable for the HTML code:

As with my previous demonstrations, we’ll name the action Initialize variable for HTML code and the actual variable HTML Code:

Then we’ll proceed to set the variable with the HTML code that you can retrieve from my GitHub repo here: https://github.com/terenceluk/Microsoft-365/blob/main/HTML/SFTP-Modification-Notification.html

Note that I used a function to check if the destintationUrl or sourceUrl is null and if so, replace the empty value with Not Applicable. I originally used the function coalesce and noticed that it checks for null value and not for empty or blank strings, which is what actually returns so I used an if statement to catch an empty string as well as if the value null is returned.

We can now proceed to configure the email notification using the HTML code:

This is what the email notification looks like:

Hope this helps anyone looking for a similar solution with Logic Apps.