The purpose of this guide is to explain how to import a custom DPM SQL report into DPM 2012 SP1.
NVINT handles dozens of DPM server builds throughout the year. Because the reports built into DPM aren’t very informative, we have created a custom DPM report that we use on all of our DPM servers in production. The following scenario is using DPM2012 SP1 with a local instance of SQL on Windows Server 2012 Standard. We use the following five steps when building each new DPM server:
- Install SQL Server Report Builder
- Create SQL Report User
- Create Custom Data Source
- Import and Configure the Custom Report
- Schedule the DPM Report.
Step 1: Install SQL Server Report Builder
Download SQL Server Report Builder from the Microsoft Download Center to the DPM server.
Open the executable that you downloaded to start the Install wizard. Click “Next.”
Accept the license agreement and click “Next.”
Enter your username and company info and click “Next.”
Use the default Feature Selection and click “Next.”
For the Default Target Server use http://YourDPMServerName/reportserver_MSDPM2012.
Note: If you are not sure about your report server URL you can open reporting services configuration manager, click on Web Service URL and view the information there.
Click “Next” to install report builder.
Once the installation is complete click “Finish” to close the wizard.
Step 2: Create SQL report user
Create a new local user on the DPM server that will be used to access the DPM Database for the custom report.
Next, open SQL Server Management Studio. Expand Security > Logins and right click for “New Login” option.
For the Login name, enter the new user you created. Then click Server Roles.
Under Server Roles put a check in the sysadmin box. Then Click User Mapping.
You can now close SQL Server Management Studio.
3: Create Custom Data Source.
Open Reporting Services Configuration Manager, click the Report Manager URL tab and click the Report Manager site URL. To access the report manager URL you need to log in with the user you used to install DPM. Normally this is the Local administrator password.
Once the Report Manager site is up we will need to give your new SQL user access to the web site. Click on “Site Setting” in the top right.
Now click on the “Security” tab.
Click on “New Role Assignment” to add your new SQL User.
Add your new SQL user as a “System Administrator” and Click “OK.”
Click “Home” once your new user is added.
Click “Folder Setting”
From the home page, click “New Role Assignment” to add in your SQL user just like you did in the previous security tab.
Once your SQL user is added and has the Browser, Content Manager, My Reports, Publisher and Report Builder roles, click “Home.”
Once you are back at the Home page click on the DPMReports folder again.
Now we will create a new Data Source for your user to access the DPM Database. Click “New Data Source.”
Now create the connection to the DPM Database and test the connection.
Note: If the connection fails you may need to log out of the SQL Reporting Services webpage and log back in as your new SQL user and the connection should work. If the connection still errors out, open up SQL Management Studio and double check your SQL user has the proper permissions to the DPMDB database.
Once the Custom Data Source is created you can close out of SQL Reporting Services web page.
- Import and Schedule Custom Report
Open SQL Server Report Builder and open your custom DPM Report. Be sure you log into the report builder as your new SQL user. Right click on Data Sources and click “Add Data Source.”
Type in the name of the Data Source and click “Browse” at the bottom of the window.
Select your “CustomDataSource” and click “Open.”
Your Data Source should now be listed under Data Sources.
Expand Datasets. Right click one of your datasets and click “Dataset Properties.”
For more information on SQL Report Datasets click Here.
Select your Custom Data Source and click “OK.” You must do this for each data set you are using.
You can now click “Run” to test your report and verify that it is working correctly.
Now save your report. Click File > Save As. Click “Recent Sites and Servers.”
Open the DPMReports folder.
I always use the file name “Status” to save the report as the Status report. Click “Save.” If you save the report as something other than one of the built-in DPM Reports, the report will not show up in the DPM console under reporting.
You can now close out of Report Builder.
- Schedule the DPM Report
Open the DPM console and click on the reporting tab. Select the “Status” report and click “Edit” under Schedule.
Create the schedule on which you want the report to be run.
Click on the E-mail tab and enter the email addresses you want the report sent to. Select the Report Format and click “OK.”
Note: If you have not already set the SMTP server you will need to go to the management tab and click “Options.” Then select the “SMTP Server” tab and enter your SMTP server information.
Your custom report should now run according to the schedule you configured. If the report does not run according to the schedule you created, you may need to log into the report manager URL and configure the schedule there. I have had issues in the past when creating the schedule in the DPM console and had to use the report manager site to have the correct schedule applied. To do this, first open reporting services configuration manager. Click “Report Manager URL” and then click on the URL. Log in with your DPM report user. Once the webpage is up click on the DPMReports folder.
Click the drop down arrow next to the Status report and click “Manage.”
Click on “Subscriptions.”
From the Subscriptions tab you can either create a New Subscription or Edit an existing subscription.
To create a new subscription, all you need to do is enter an email address in the “To:” field, select the format you want the report delivered to you in, and select the schedule.
The finished report will now be emailed to you. Here is a picture of our custom report. It lists the number of jobs in the critical and warning state, current alert details such as the protection group name of the failed job and affected server, and also jobs with recovery points that have failed more than five times.
By: Kyle VanDyke