How to Setup and Configure the Automated Email Reports Utility
Problem
You want to schedule certain PortalGuard reports to be emailed to various contacts on a regular basis
Requirements
- PortalGuard version 6.2.1.0 or Later
- PortalGuard's SQL Back End must be installed and configured:
- Reporting Enabled within PortalGuard
Quick Navigation
Solution
Configure and utilize the Automated Email Reports utility.
Initial Setup
- Navigate to the PortalGuard Installation Kit and access the 'Tools\Automated Email Reports' folder.
- The latest Install Kit for v6.2.1.0 and beyond can always be located HERE
- Extract the contents of 'extReporting.zip' to your PortalGuard Server.
- Run the 'installer.exe' utility as Administrator:
- Click 'Install Service'.
- A DOS Prompt should appear and then disappear once the service installation completes.
- Click 'Change Settings':
- Fill in each setting as needed.
- IMPORTANT: You MUST click the 'save' button after modifying each setting in order to commit the update.
- The following fields MUST be edited/defined:
- SMTPServer
- SMTPPort
- SMTPUser
- SMTPPass
- EmailFrom
- pstarConnectionString
- The following fields SHOULD be viewed/edited as needed:
- EmailSubj
- EmailBody
- WeeklyRunDay
- The default value for when the service executes. You will also be able to determine Daily runs via the Web Front End
- ReportPath
- If PortalGuard is not installed in the default location, this variable needs to point to the 'ReportDefinitions' folder.
- Close the settings window.
- Click the 'Start Service' button.
- Note: To update the settings, you must first 'Stop' the service.
Configure the SQL Back-end
- Navigate to the SQL Database Server housing the PortalGuard database (e.g. 'pstar').
- Important Note: It is highly recommended that you take a backup of the 'pstar' database before you proceed.
- Connect to the Database via SSMS and create a new SQL Login:
- Choose 'SQL Server Authentication'
- Uncheck the 'Enforce Password Policy' box
- Set the 'Default Database' to 'pstar'
- DO NOT click the 'Save' Button:
- Navigate to the 'User Mapping' Page:
- Select 'pstar' on the top half of the page.
- Select 'db_owner' on the bottom half to grant said group membership to this new account.
- Click 'OK'
- Navigate to the extracted folder from step #2 in the Initial Setup Section above.
- Bring the 'upgrade-for-reporting.sql' script to the SQL server and execute it in SSMS
- If you named the database anything other than 'pstar', ensure you update the first line of the script accordingly before executing.
- This script will install the necessary tables and stored procedures for the automated reporting feature!
Install the Web Front End
- Navigate to the extracted folder from step #2 in the Initial Setup Section above.
- Copy the 'Reporting' folder to the following location:
- inetpub\PortalGuard\Exts\
- If the 'Exts' folder does not exist, it must first be created.
- Navigate back to the extracted folder from step #2 in the Initial Setup Section above.
- Copy the following items into the "inetpub\PortalGuard\Bin\' folder:
- AntiXssLibrary.dll
- HtmlSanitizationLibrary.dll
- PGReporting.dll
- If the 'Bin' folder does not exist, it must first be created.
- Navigate to 'inetpub\PortalGuard\Exts\Reporting\' and edit the 'web.config' file in an administrative text editor.
- Search for '<AllowedUsers>' without the quotes.
- Add a new line within the '<AllowedUsers>' node for each user that should be allowed to managed automated reports.
- Note: Use the commented out line as an example with proper formatting.
- If you prefer to utilize AD groups, simply add a new entry under the '<AllowedGroups> node
- Search the file for '<connectionStrings>' without the quotes.
- Modify the connection string to point to the SQL Database modified in the Configure SQL Back End section above.
- Be Sure to update the username and password to utilize the credentials for the newly created user:
- Instructions for encrypting this connection string can be found here:
- Save the file.
- Navigate to the root of your PortalGuard website and edit the 'web.config' file:
- Typically located at "c:\inetpub\PortalGuard\web.config'
- Search for '<modules>' without the quotes.
- Add the following node within the '<modules>' element:
- <add name="ReportAuthz" type="PGReporting.ReportAuthz, PGReporting" preCondition="managedHandler"/>
- Save the file.
- Launch an elevated CMD and execute the 'iisreset' command.
- In a browser, navigate to the following URL to test the login:
- https://{YOUR.PG.URL}/Exts/Reporting/manage.aspx
- Replace {YOUR.PG.URL} with the root URL for your PortalGuard website.
- Ensure you are logging in with a user that is either explicitly defined OR a member of the groups defined in the 'web.config' as noted in step #5 of the Install the Web Front End section above.
Configuring Scheduled Reports
- Navigate to the management Automated Email Reports management URL:
- Click on 'Create New Report':
- Fill Out the Required Fields:
- Title - The Title of the Report.
- Enabled - Whether or not the report will execute on schedule.
- Canned Report - Which report to run.
- Frequency - Daily, Weekly, Monthly (also sets the scope of the data being pulled during the report).
- Add Recipient - Used to add email addresses that should receive the resulting CSV file for this report when executed.
- Recipients - List of all recipient email addresses currently set to receiving the resulting CSV file for this report.
- Save/Cancel
Important Notes
Reports will be execute at 1 AM on a day determined by the 'Frequency' of the report:
Frequency Run time intervals
- Daily - 1 AM every day
- Weekly - 1 AM on the specified weekly run date
- Monthly - 1 AM on the first of the month
Reports can be manually triggered from the 'installer.exe' application by clicking the 'Run all Reports' button.
- As noted in the application, this will run all reports, including those marked as 'disabled'.
REV. 03/2020 | PortalGuard