Financial company: create an inventory of all users across all managed and cloud applications

Problem

A financial company uses multiple (50+) managed and cloud-based systems, from Office 365 and Salesforce to the applications developed in-house and by third-party programs. For auditing and reconciliation purposes, they need to periodically collect the information about all the users in all the systems, together with permissions, and store it in the database. They use an internally developed web portal as a dashboard to reassign the roles and send notifications to the users. 

Requirements

• The solution must be able to collect users and permissions from all 50+ internal and external systems.
• The solution must be able to store the information in a database (full refresh).
• When the portal application updates the database, the solution must be able to send email notifications to all affected users.

Solution

In this tutorial, we will be collecting information from Office 365 and Salesforce only. The actual solution, developed by the team, collects it from all 50+ managed and cloud-based applications.

The Flow which collects users and permissions from Office 365

Step 1. Create a new Microsoft Graph API Connection pointed to the users endpoint.

• URL: https://graph.microsoft.com/v1.0/user

Step 2. Create a new Microsoft Graph API Connection pointed to the roles endpoint:

• URL: https://graph.microsoft.com/v1.0/directoryRoles

Step 3. Create a new Microsoft Graph API Connection pointed to the members endpoint:

• URL: https://graph.microsoft.com/v1.0/directoryRoles/{role_id}/members

Step 4. Create a Connection to the relational database with users.

Step 5. Create a Connection to the temporary database.

Step 6. Create a JSON Format with all default settings.

Step 7. Create a new Flow by selecting the Web Service to database in the gallery. This Flow will be loading Office 365 users without roles into the database.

Step 8. Add a new source-to-destination transformation using Microsoft Graph API Connection for users endpoint (created in step 1) as a source and a database Connection as a destination. Configure per-field mapping if needed.

Step 9. Repeat step 7 and add a new source-to-destination transformation using Microsoft Graph API connection for roles endpoint (created in step 2) as a source and a temporary database Connection as a destination. Configure per-field mapping if needed.

Step 10. Repeat step 7 and add a new source-to-destination transformation using Microsoft Graph API Connection for members endpoint (created in step 3) as a source and a database Connection as a destination. Configure per-field Mapping if needed. 

Step 11. Create a nested Flow with the following steps:

1. The Flow that loads users without roles.
2. The Flow that loads roles.
3. The Flow that loads members of the roles (users with roles). 

Step 12. Click the pen button next to the Flow Refresh Office 365 user for role and configure the database loop as shown below.

The Flow which collects users and permissions from Salesforce 

Step 1. Create a new Salesforce Connection which uses the following URL to populate users and roles:

https://instance.lightning.force.com/services/data/v20.0/
query?q=SELECT+name+,+email+,+Username+,+LastName+,+FirstName+,+IsActive+,
UserRole.Name+,+Id+from+User

Step 2. Create a JSON Format with all default settings.

Step 3. Create a new Flow by selecting the Extract data from the Web Service and load into the database in the gallery. This Flow will be loading Salesforce users into the database.

Step 4. Add a new source-to-destination transformation using Salesforce Connection as a source and a database Connection as a destination. Configure per-field Mapping if needed.

Now you can combine Office 365 and Salesforce Flow in a nested Flow.

The Flow which sends email notifications triggered by updates in the portal database

Typically, the portal database is getting updated when the user submits the data using a web form. It is easy to add an additional call in the submit action to the user-defined PUSH API. 

Step 1. Create a user-defined PUSH API that will be receiving a payload from the submit action.

Step 2. The source for the Flow is going to be an HTTP Listener configured for the API and the destination is going to be an outbound email Connection.

Read how to create a Flow that sends emails.