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:

DailyDashboardReport
Ok if your following alone we now have our EPM database getting populated with some data. All we need to do now is create our Report and set up a schedule.

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:

clip_image002

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:

https://www.iconfinder.com/search/?q=iconset%3ASnow

In the Report data field click on Images and Add the two images I called mine Stop and Check

clip_image003

Drop on a Table to the report and Set up the following columns

Server

Instance

Agent

Not Corrupt

Job Fails

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

Server

Agent_error_Results

Error_Log_Results

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

clip_image004

In the Image Properties click on the function button for the “use this image:”

clip_image005

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"
)

clip_image006

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)[1]','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

clip_image007

clip_image008

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

clip_image009

clip_image010

Set the Chart Data to match below:

clip_image011

We should be able to run the sub report now and by manually entering a server name get a report:

clip_image012

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

clip_image013

In the subreport properties “Use this report as a subreport” should be set to “Sparkline_ErrorLog”

clip_image014

Also go to the Parameters option and Enter the Name “Server” for the Value [Server]

clip_image015

Confirm the changes by pressing OK and you should be ready to run your report!

clip_image001[1]

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

clip_image016

Change “Delivered by:” to Email.

Add the email address to send the report to. in the “To:” section

and set your schedule.

clip_image017

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..

Summary:

If you are interested in setting up the report, you can down key items from the links below:

Enterprise Policy Management Framework
http://epmframework.codeplex.com/

The Policies
http://sdrv.ms/J0Rxsb

The Report
http://sdrv.ms/1mzwn3X” href=”http://sdrv.ms/1mzwn3X

I hope this helpful and others get as much use out of it as I have.

Advertisements