This article details a few very simple ways to setup auditing reports executed by SQL Server.
This primary example will provide list of emails (or distribution lift) with a nightly report on how many Process Flow Runs were executed in the last 24 hours, grouped by ApplicationServerID and status. This helps you potentially proactively resolve issues before users notice. You might notice one particular App Service has a significantly higher percentage of errored process flows compared to the other, or that one PF itself is constantly erroring.
This same process could easily be tweaked to provide other reports, like number of Message Runs and such. Most of this functionality is covered by Scheduled Views in the Aptify system, however there are some unique reasons why you may want to consider this approach.
The primary reason for using SQL Database Mail and Jobs directly as opposed to the Async Processor is because in this scenario, we also want to get emails if the Async Processor is stuck or failed. If it's not running, you would not get any report. An email report you receive with entirely blank results for Process Flow Runs indicates that there is a problem.
This process is actually very simple to setup and can easily be adapted to other needs. It can also be setup on Production, Test, Dev, and other environments.
The first step is to create a new Database Object in Aptify:
| Name | spGetLastDaysPFRforReport__c |
| Description | Gathers last 24 hours worth of Process Flow Runs, grouped by ApplicatonServerID and status for an email report |
| DB | APTIFY |
| Type | Stored Procedure |
| SQL | CREATE PROCEDURE spGetLastDaysPFRforReport__c AS select ApplicationServerID, Status, count(ID) 'Number of Process Flow Runs' from vwProcessFlowruns where DateRecorded > getdate()-1 group by ApplicationServerID, Status |
| Grant SQL | grant execute on spGetLastDaysPFRforReport__c to EndUsers |
You can then try "exec spGetLastDaysPFRforReport__c" in SSMS to see if any data returns.
The second thing you need to do, is setup a Database Mail profile. You can open the Management tab, right-click on Database Mail and select Configure.
When setting it up, you'll primarily need to know the address for your SMTP provider, what port to use, and whether they are require HTTPS. It is very simple to set this up using Office365 emails, as well as Gmail, and there are plenty of alternatives like SendGrid.
Make note of the Profile Name you've created as you'll need it for later. You can always go back to look it up by right-clicking Configure Database Mail.
Once setup, you can right-click on Database Mail and select "Send Test E-Mail" to confirm it's working.
Lastly, we will create our SQL Job. This job runs nightly (although that can be changed to weekly) executes our aforementioned Stored Procedure to get data, formats it into an email, and then sends to a designated email address. This address can be multiple emails separated by a , it can be a group or distribution list, etc.
IF you'd like to try scripting it, execute this SQL. Note, you will have to edit the Profile Name and the destination email addresses in the section marked as:
To manually set it up:
Under SQL Server Agent, right-click and select New Job. You can name it anything descriptive, and I recommend adding an informative description. Click on Steps on the left hand side.
Create a New Step. You can name it something like "Gather Data and Send Email." This script will execute your Stored Procedure to get data, and then format it into an easy-to-read HTML table. It will also send the email out, please note below where addreses should be changed.
Make sure the database is set to APTIFY, and the type is a T-SQL command. Run as can be left blank. Under the command, paste this text:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
USE APTIFY
GO
DECLARE @tableHTML NVARCHAR(MAX);
DECLARE @body NVARCHAR(MAX);
-- Create a table to hold the results of the stored procedure
CREATE TABLE #PFRResults (ApplicationServerID NVARCHAR(100), Status NVARCHAR(100), RecordCount NVARCHAR(100));
-- Insert the results of the stored procedure into the table
INSERT INTO #PFRResults
EXEC spGetLastDaysPFRforReport__c;
-- Generate the HTML table from the results
SET @tableHTML =
N'<h1>Gathered Data</h1>' +
N'<table border="1">' +
N'<tr><th>Column1</th><th>Column2</th><th>Column3</th></tr>' +
(SELECT
N'<tr><td>' + ISNULL(ApplicationServerID, '') + N'</td>' +
N'<td>' + ISNULL(Status, '') + N'</td>' +
N'<td>' + ISNULL(RecordCount, '') + N'</td></tr>'
FROM #PFRResults
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') +
N'</table>';
-- Set the email body
SET @body = @tableHTML;
-- Send the email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SendGrid',
@recipients = 'aaron.giambattista@communitybrands.com',
@subject = 'Nightly Gathered Data for Process Flow Runs',
@body = @body,
@body_format = 'HTML';
-- Clean up the temporary table
DROP TABLE #PFRResults;
The two parts you must change are the red highlighted sections. The Profile Name needs to be changed to whatever Profile Name you named your Mail provider, and recipients should be changed to whoever should actually get the email. You can list multiple email inboxes, separated by commas, or you can send it to a group or distribution list.
@profile_name = 'DefaultMail',
@recipients = 'aaron.giambattista@communitybrands.com',
You can also optionally change the subject line. Some clients are running this on Prod, Test, Dev, so it's helpful to have the actual machine name in the email title.
@subject = 'Nightly Gathered Data for Process Flow Runs',
Click OK to create your rule. You can copy and paste the SQL from that step and execute it directly in SSMS, to test if it's working- it will send an email, so it can determine if that is a problem or not.
On the Schedules Tab, give it a helpful name and then set how often you wish this to run. If you're running it daily, I would recommend sometime between 10:30pm and 11:59pm. If daily is too much, either in general or for non-Prod environments, you could set this to run weekly, although note you'll have to tweak the Stored Procedure you created, slightly.
Click OK. You should now be able to test it. You should have first tested by executing the SQL directly in SSMS, but this is a full end-to-end test. Right-click on your new job under SQL Server Agent -> Jobs, and select "Start Job at Step One." It should collect the data and email to your designated target email addresses.
That’s it. You should now get a nightly summary of Process Flow Runs, grouped by the Application Server ID and their Status. This can be used in a number of ways:
-
If you see no results at all or a large amount sitting in pending status, the application servers may be down or stopped
-
If you see an unusual amount of failures, you may have a problem with certain process flows or a misconfiguration of the application server
You can use this concept to also send out similar reports such as Process Flow Run status grouped by Process Flow.
If you are having issues
The best way to resolve this is to break it up, and tackle each problem at a time. I would address them in this direction:
- No data is showing? Run "exec spGetLastDaysPFRforReport__c" directly in SSMS to see what returns. You just might not have any data.
- Emails not working? This is the primary culprit. Make sure you can Send Test emails, that tests only the SMTP capability. If still not, try a different email provider or maybe research the ports and server names for your provider
- Job doesn't run at all? This is rare, but make sure it does if you manually execute it. If not, there may be some permissions issues. It also might be something as simple as SQL Server Agent has been stopped.
Comments
Please sign in to leave a comment.