How to set up the DeliverPoint Scheduled Reports feature

DeliverPoint offers some reports as Scheduled Reports, so you can run them on a recurring basis, but this feature requires some setup first.

After performing the steps outlined in this article, a Schedule menu will appear in the DeliverPoint Online console, and the following reports can be run as Scheduled Reports (as of version 4.3.0.0). [Note: Beginning with version 4.4.0.0, the Schedule menu will appear by default in both the DeliverPoint site extension (the icon in the upper right), and also in the DeliverPoint console, but the list of reports will be greyed out until the Scheduled Reports feature has been configured.]

After scheduling a report to be run, you can click on Scheduled Reports to check the status of your report, and when it's ready to view, you can click the Open icon to open the report within the DeliverPoint UI (new in version 4.4.0.0 - previously, there was a link to the completed report as an Excel file).

NOTE: The installation and configuration steps below should be performed by someone familiar with the steps described. In your organization, this may be an IT administrator who has experience with servers and SQL databases. If you wish to set this up using only Azure resources, please see this article instead.

Components and description

The DeliverPoint (DP) SPFx (SharePoint Framework) Scheduled Reports feature requires 5 components:

DP SPFx client-side application version 4.3.0.0 or higher, deployed within SharePoint (SP) Online. Provides a centralized interface for all DP features including reports, permissions management, license configuration, and scheduled report generation and storage. This is the standard sppkg file that you already have deployed if you are currently using DeliverPoint.
Http-based RESTful Web Application deployed under the IIS. Handles requests from DP SPFx and manages the DP database.
SQL DP database. A SQL database (can be in Azure SQL) to store some sensitive configuration data (certificate name and encrypted certificate password for the registered Azure application), information about scheduled reports (jobs), and also possibly generated reports in the future.
DP Job Processor (a Windows Console application). Connects to SharePoint Online via CSOM to enumerate SP-related entries (Sites, Webs, Lists, Items, Users, Groups, Permissions, Roles), connects to Entra ID to enumerate AD Users, Groups, and Group Members, and executes scheduled jobs (reports) and stores them in a SharePoint Library.
Registered Azure Application. Required for the Job Processor to have access to Entra ID.

Here is a high-level overview of how the Scheduled Reporting feature functions:

Deployment example on Windows Server (could be in Azure)

Required files (you'll find these files in the Scheduled Reports installation files folder of the current DeliverPoint download package):
1. REST service package for IIS deployment (DPLiteBackendService folder)
2. Job processor standalone application package (JobProcessor folder)
Environment prerequisites

1. Windows Server (minimal size/memory/CPU) with .NET 4.7.2 runtime installed. This server must remain on with the JobProcessor application (see Deployment #3) always running. You can use a local admin account on this server.
2. MS or Azure SQL Server instance. We also recommend installing the SQL Management Studio UI.
3. IIS configured to host .NET web services (ASP.NET 4.6 or above).

Deployment steps

Create a SQL database anywhere accessible from your server and the account you're using, and get the connection string.
For example:
Data Source=YourDBServerAddress; Initial Catalog=DatabaseName; Persist Security Info=True; User ID=YourUserID; Password=YourPassword
Deploy and configure ASP.NET Web application under the IIS:
1. Extract the DPLiteBackendService.zip archive content under the C:\inetpub\DPLiteBackendService folder.
2. Update the DPContext database connection string inside of the web.config file.
3. Create an IIS application pool (DPLiteBackendApplicationPool) with local machine Administrator identity. The app pool account will also need to have full access to the DeliverPoint SQL database created in Step 1.
4. Create an IIS Site named DPLiteBackendService with application pool DPLiteBackendApplicationPool and refer it to load content from C:\inetpub\DPLiteBackendService folder.
5. Add https binding with an appropriate certificate for that domain name.
6. If you are using a specific port binding for a newly created Site, please open that port access in your Firewall for incoming and outgoing TCP connections.
Job Processor Console Application
1. Extract the DPJobProcessor.zip archive content under a folder.
2. Update the DPContext database connection string in the JobProcessor.exe.config file.
3. Run the JobProcessor.exe executable. Please note that JobProcessor.exe will generate a local log file in the same folder which contains the process-related logging. Each time you run JobProcessor it will append the log entries to the same file.
Configure Azure Application for AD access
1. Create a new Azure Application from Entra > App registrations > New registration
2. Give it any name and click Register.
3. Go to API permissions, and click the Microsoft Graph link to open the permissions page required for the application.
  
  IMPORTANT: Steps d, e, g, h, and i below have changed significantly from prior versions of the DeliverPoint Scheduled Reporting feature. If you were using this feature prior to DeliverPoint version 4.3.0.0, and you have upgraded the DeliverPoint SPFx app, then you will also need to update the permissions below (or delete the existing Azure Application and create a new one).
4. Select the required Application permissions: Directory.Read.All, Files.Read.All, InformationProtectionPolicy.Read.All, and Mail.Send (new in version 4.4.0.0 to allow notification that the report has completed).
5. Also add the SharePoint > Sites.FullControl.All Application permission. This is required to be able to use the SharePoint CSOM/REST API.
6. Grant admin consent for the selected permissions.
7. Create a certificate for the Azure application by running the following PowerShell command from within the JobProcessor folder:
  
  .\Create-SelfSignedCertificate.ps1 -CommonName "NAME" -StartDate START_DATE -EndDate END_DATE
  
  The PowerShell script Create-SelfSignedCertificate.ps1 is located in the JobProcessor folder. NAME is the desired name of your certificate. START_DATE is the beginning validity date of the created certificate (such as today). END_DATE is the validity ending date of the created certificate. (Note that you will need to perform this step again when the certificate expires, so consider that when choosing an End Date.) Both dates must be specified in the format YYYY-MM-DD. During the execution of this command you will be prompted to provide a password that will protect the created certificate. Both the certificate name and the provided password will be used later for DP SPFx configuration. After running this command, two files with the specified certificate name will be created in the JobProcessor folder: NAME.pfx and NAME.cer
8. Copy the two certificate files (NAME.pfx and NAME.cer) to the folder C:\inetpub\DPLiteBackendService
9. Go to the Certificates & secrets page for the created Azure Application. Go to the Certificates tab, and click Upload Certificate to upload the certificate (.cer file) created in the previous step. You can specify any description for the uploaded file.
  
  Click Add to upload the selected certificate.
10. Get the Application (client) ID key from the Overview page; this will be used for the DP SPFx configuration.

5. Configure DeliverPoint SPFx

1. Specify the DeliverPoint REST service URL for the current tenant. This activates the DP Enterprise features, i.e. the modified configuration pane and the Schedule button. This can be done via the following SharePoint Online Management Shell command:
```
Set-SPOStorageEntity -Site APPCATALOGURL -Key _lt_deliverPointServiceUrl -Value SERVICEURL -Comments DPServiceUrl -Description DPServiceUrl 
```
  Where APPCATALOGURL is the URL of your tenant's app catalog site collection, and SERVICEURL is the URL of the DP REST service ending with /api/ - for example: https://100.101.102.103:12345/api/ (The IP address of the IIS hosting server and the port configured in IIS for the service.) NOTE: be sure to include the / at the end of the URL.
2. Configure the service via the DP SPFx configuration pane. After following the above steps, you will see that if you edit a DeliverPoint web part, there is now a configuration panel as shown below (license information will now be on Page 2 of the panel). The following information must be provided (once for the entire tenant):

Active Directory Domain Name: The name of the domain used by the current tenant

Azure Application ID: ID of the Azure application used by the service to retrieve necessary information from Entra ID

Azure Application Certificate: The name of the created certificate .pfx file

Certificate Password: The password specified when creating the Certificate

SharePoint Administration site URL: URL of the SP administration site for the current tenant (this is the same -admin URL one would use for the SPO Management Shell, e.g. https://contoso-admin.sharepoint.com)

Operator Accounts: List of accounts, for which the generated scheduled reports will not be security trimmed. For all other users, the generated scheduled reports will be security trimmed based on the report initiator's permissions.

Example:

The Certificate Password field must be filled in only when specifying the Certificate initially, or when updating the Azure Application Certificate configuration data. In other cases, this field will display as blank, as DP SPFx is not getting it from the server.

DeliverPoint should now be ready to run Scheduled Reports!

Published Apr 25, 2023

Updated June 30, 2023 (Added clarification about the configuration panel)

Updated July 19, 2023 (Added architecture diagram)

Updated November 17, 2023 (Changed Azure AD to Entra ID, updated to reflect version 4.3.0.0 reports and replacing DeliverPoint crawl account with an Azure Application certificate)

Updated December 22, 2023 (Added reference to Azure-only instructions, added Step 4h, updated some Entra screenshots)

Updated April 17, 2024 (v 4.4.0.0 updates)