Daily Dashboard – 05 Create the Report and set up the subscription
Every DBA loves a checklist in the morning, to try and speed up my checks I decided to mix a little policy based management and a SSRS subscription, this is a really easy way to set up a daily report (or even weekly / monthly reports) that can cover pretty much anything you want it to for your servers health.
I’ve split this into sections to go through the steps needed to set it up:
Daily Dashboard – 01 Setting up a Central Management Server
Daily Dashboard – 02 Setting up a Enterprise Policy Management Framework Server
Daily Dashboard – 03 Creating the Policies
Daily Dashboard – 04 Setting up the Daily EPM Agent Job
Daily Dashboard – 05 Create the Report and set up the subscription
The report ends up looking like:
For the most part in the post im presuming you know how to create a basic report, ill have some links at the end that can help with learning the basics and bit more should you be interested!
Creating the Report
To create the report like above we need to create a main report, that will house the table and results and a sub report that contains the sparkline for the weekly errorlog.
Start a new project, name it “EPM Custom Reports”
Create a New Item and Select Report (Name it Dashboard)
Add a Data source to your EPM Server/Database
Create a DataSet with the below code:
WITH GroupList ( EvaluatedServer ) AS ( SELECT DISTINCT EvaluatedServer FROM policy.PolicyHistoryDetail WHERE ( CategoryName = 'PROD_DAILY' ) AND ( EvaluationDateTime > GETDATE() - 1 ) ), AgentJobErrorsIn24Hours ( EvaluatedServer, [Agent Job Errors in 24 Hours], ResultDetail ) AS ( SELECT EvaluatedServer , PolicyResult AS 'Agent Job Errors in 24 Hours' , ResultDetail FROM policy.PolicyHistoryDetail AS PolicyHistoryDetail_6 WHERE ( EvaluatedPolicy = 'PROD - Agent Job Errors in 24 Hours' ) AND ( EvaluationDateTime > GETDATE() - 1 ) ), AgentServiceRunning ( EvaluatedServer, [Agent Service Running] ) AS ( SELECT EvaluatedServer , PolicyResult AS 'Agent Service Running' FROM policy.PolicyHistoryDetail AS PolicyHistoryDetail_5 WHERE ( EvaluatedPolicy = 'PROD - Agent Service Running' ) AND ( EvaluationDateTime > GETDATE() - 1 ) ), ErrorLogWarning ( EvaluatedServer, ResultDetail ) AS ( SELECT EvaluatedServer , ResultDetail FROM policy.PolicyHistoryDetail AS PolicyHistoryDetail_4 WHERE ( EvaluatedPolicy = 'PROD - ErrorLogWarning' ) AND ( EvaluationDateTime > GETDATE() - 1 ) ), InstanceOn ( EvaluatedServer, [Instance On] ) AS ( SELECT EvaluatedServer , PolicyResult AS 'Instance On' FROM policy.PolicyHistoryDetail AS PolicyHistoryDetail_3 WHERE ( EvaluatedPolicy = 'PROD - Instance On' ) AND ( EvaluationDateTime > GETDATE() - 1 ) ), Suspect_Pages ( EvaluatedServer, [Suspect_Pages] ) AS ( SELECT EvaluatedServer , PolicyResult AS 'Suspect_Pages' FROM policy.PolicyHistoryDetail AS PolicyHistoryDetail_2 WHERE ( EvaluatedPolicy = 'PROD - Suspect_Pages' ) AND ( EvaluationDateTime > GETDATE() - 1 ) ) SELECT g.EvaluatedServer AS Server , a.ResultDetail.query('/Operator/Function/ResultValue/text()') AS Agent_Errors_Results , running.[Agent Service Running] AS Agent_service_Running , error.ResultDetail.query('/Operator/Function/ResultValue/text()') AS Error_Log_Results , inst.[Instance On] AS Instance_On , sus.[Suspect_Pages] FROM GroupList AS g LEFT OUTER JOIN AgentJobErrorsIn24Hours AS a ON g.EvaluatedServer = a.EvaluatedServer LEFT OUTER JOIN AgentServiceRunning AS running ON g.EvaluatedServer = running.EvaluatedServer LEFT OUTER JOIN ErrorLogWarning AS error ON g.EvaluatedServer = error.EvaluatedServer LEFT OUTER JOIN InstanceOn AS inst ON g.EvaluatedServer = inst.EvaluatedServer LEFT OUTER JOIN Suspect_Pages AS sus ON g.EvaluatedServer = sus.EvaluatedServer
which should result in the following:
How we have our data in and we are ready to start putting pen to paper so to speak.
first we need to have a couple of pictures for Good and Bad! so we can quickly tell if we have:
A SQL engine is down
A Agent Service is down
We have corruption listed in MSDB
If you want to download the same icons I used you can from here:
In the Report data field click on Images and Add the two images I called mine Stop and Check
Drop on a Table to the report and Set up the following columns
Daily Error Log Alerts
Error Log Trend
Populate Numeric and Text Data
Drag and drop the following from your Daily_Prod_Table Data Set into the table data row
These are either text (server Name) or numbers so we are done there.
Populate the picture columns
insert a image from the Toolbox into the Instance data cell
In the Image Properties click on the function button for the “use this image:”
Enter the follow text to set up a switch which will run the Check if row Passes and Stop if its Fails
=switch( Fields!Instance_On.Value = "PASS", "Check", Fields!Instance_On.Value = "FAIL", "Stop" )
Repeat the steps to add images for Agent and Not Corrupt.
If you run the report now, you should be seeing a nice snapshot of your servers that are connected to the EPM.
but we arent done there. We then want to create a sparkline to track the error log count for the last 7 days.
Create Sparkline Sub Report
We are going to need a new Report for this, Call it “Sparkline_ErrorLog”,
Set the Data source to your EPM server, databases
Create a new Data Set with the following code:
WITH ErrorLogWarningPivot (RANK, EvaluatedServer ,ResultDetail) AS ( SELECT RANK() OVER (partition by EvaluatedServer ORDER BY EvaluationDateTime) AS 'Rank', EvaluatedServer , ResultDetail.value('(/Operator/Function/ResultValue)','INT') AS 'Results' FROM policy.PolicyHistoryDetail AS PolicyHistoryDetail_4 WHERE EvaluatedPolicy = 'PROD - ErrorLogWarning' AND EvaluationDateTime > GETDATE()-7 AND EvaluatedServer = @Server ) SELECT * FROM ErrorLogWarningPivot
Add a Parameter called Server
Drag and drop a Sparkline onto your report (fix it to the top left hand of the report and size the report to make the size of the sparkline
Set the Chart Data to match below:
We should be able to run the sub report now and by manually entering a server name get a report:
Save the report and go back to the Dashboard report
Add the SubReport to the Dashboard
In the Dashboard report we now just add a SubReport from the Toolbox into the Error Log Trend data cell
In the subreport properties “Use this report as a subreport” should be set to “Sparkline_ErrorLog”
Also go to the Parameters option and Enter the Name “Server” for the Value [Server]
Confirm the changes by pressing OK and you should be ready to run your report!
Now you hopefully have a report ready for action! its just the simple steps of setting up a subscription and your finished.
Create a Report Email Subscription
For this part you will need your SSRS server to be set up with email relay on your exchange email server.
Upload your reports to your SSRS server (for this it’s the same server, You can either push them up from SSDT / Visual Studio or go to your web page and upload the files to it. (Links are below for more details on that subject!)
Once the files are uploaded, Right click on the Dashboard report and select Subscription
Change “Delivered by:” to Email.
Add the email address to send the report to. in the “To:” section
and set your schedule.
click ok and check the subscription works. you should now get the dashboard sent to you on your schedule (Id send it either close to when you get into work (so the details close to real-time as you can get from an email) or at a set time like midnight, so you know the error logs are for a single day instead of crossing over..
If you are interested in setting up the report, you can down key items from the links below:
Enterprise Policy Management Framework
I hope this helpful and others get as much use out of it as I have.