ConfigMgr: Reporting Services

Showing posts with label Reporting Services. Show all posts

Migrate reports from SCCM 2007 to SCCM 2012

sync SSRS report between two report servers:

The built-in migration wizard in ConfigMgr 2012 can migrate most objects but not reports. In ConfigMgr 2012 classical ASP reports (as we know them from earlier versions) are no longer supported.
Migrate the classical reports to SQL Reporting Services in SCCM 2007
Below is a walk thru of how you migrate your existing classical reports to SQL reporting Services in SCCM 2007. Notice that you will still be able to run all classical reports after the migration.

On you SCCM 2007 site server install either SCCM 2007 R2 or SCCM 2007 R3, both will add SQL Reporting Services support for SCCM 2007.
In the ConfigMgr. console, navigate to Site Database, Site Management, A01 (where A01 is the sitecode), Site Systems. Right click the Site system server that will become a Reporting Services Point and click New Roles. Select Reporting Services Point and finish the wizard using the default values.
In the ConfigMgr. console, navigate to Site Database, Computer Management, Reporting, Reporting Services, and expand the node.
Right-click SCCM1 (where SCCM1 is the name of your server), and then click Properties.
On the Data Source Settings tab, specify SCCM1 as the database server and SMS_A01as the database name (where SMS_A01 is the name of your SCCM 2007 database. ClickTest.
On the Data Source Authentication tab, select Windows integrated security.
Click OK.

Copy reports to SQL Reporting Services

Right-click SCCM1, and select Copy Reports to Reporting Services.
On the Data Source Settings page, accept the default settings, and click Next.
On the Data Source Authentication page, accept the default settings, and click Next.
On the Select Reports page, accept the default settings, and click Next.
On the Security page, accept the default settings, and click Next.
On the Summary page, click Next.
On the Progress page - once all of the reports have copied - click Next and then clickClose.

Migrate the SCCM 2007 reports to SCCM 2012
You can’t use the built-in migration tools to migrate reports from SCCM 2007 to SCCM 2012. This leaves you with two choices; 1) Download each RDL file one by one from SCCM 2007 and upload them to SCCM 2012 or 2) Use one of the free community tools that can download multiple folders at the time. In my last project I used this tool – ReportSync - to assist me.

Launch ReportSync
In Source server type in the name of the SCCM 2007 Report server in form of http://CM01/ReportServer. Provide credentials and click Load
In Destination server type in the name of the SCCM 2012 Report server in form of http://CM02/ReportServer. Provide credentials and click Load
Select the folders or individual reports you want to migrate and click Sync
Once the process is finished click OK and your reports are now ready in SCCM 2012.
This tool keeps the old Data source which will generate an error the first time you run the report.
Open the report properties (by clicking on the report link)
Select Data Sources, click Browse and select the shared SCCM 2012 data source. The data source is found in Home\ConfigMgr_Sitecode. The data source is the object with long GUID. Select the data source click OK and Apply.
Run the report and verify that it works. There is no guarantee the all SCCM 2007 reports work out of the box in SCCM 2012.

Download link : http://code.google.com/p/reportsync/downloads/list

All credits goes to Kent.

SCCM collection to list all the Laptop computers only

SELECT Caption0 as 'Operating System',CSDVersion0 as 'Service Pack', COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM,v_R_System
WHERE v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
GROUP BY Caption0,CSDVersion0
ORDER BY Caption0,CSDVersion0

SQL Reporting Services: SRS

SQL Reporting services have some benefits over the current reporting solution:

Provide best in class reporting capability by integrating SQL Reporting Services, with the leading change and configuration management product: SCCM 2007
Enable Ad-hoc reporting - Make it easy for both SCCM administrators, and non administrators to find the information they need to make the right decisions for their business
Support alternative databases as the reporting database, such as a replicated or backup database
Allow custom report creation via a report authoring wizard.
Enable report browsing and viewing via the SRS Report Viewer.
Take advantage of rendering in all supported SRS formats, report caching, and subscriptions.

This article will explain setting up SRS with Configuration Manager based on SQL 2008. I’ve taken most of the steps from this article (many thanks to Michael Wiles). I’ve added some screenshots and applied it to SQL 2008, while the original article discusses SQL 2005. If you need more background info on SRS check out that link.

Install and configure SQL Reporting Services in SQL 2008

Be sure to check Reporting Services as part of your SQL installation

At step 21 of this part of this guide the setup provides you with three choices:

Native mode default configuration
SharePoint mode default configuration
Unconfigured Reporting Services installation

You should select Unconfigured Reporting Services installation at this point.

After the installation finishes you should configure SRS for the use with Configuration Manager 2007.

1. In the start menu select Microsoft SQL Server 2008 / Configuration Tools, then click Reporting Services Configuration Manager.

2. In the Reporting Services Configuration Connection select the Server Name and the Report Server Instance, then select Connect.

3. In the Report Server Status verify that the Service Status is set to Running. If it is not, click Start, and then click Apply.

4. Select Web Service URL and make sure you specify the name you want to call the virtual directory created by Reporting Services (or use the default ReportServer). Optionally you can select the IP-address you want to use, the port and SSL setting. When done click Apply.

5. Select Database, select Create a new report server database in the Report Server Database Configuration Wizard and click Next

6. Select the Database Server Name and Authentication Type that matches your SQL server and credentials.

7. Supply the Database Name and Language. Be sure to select Native Mode for Report Server Mode. Click Next.

8. Select the proper credentials from the drop list for Authentication Type

9. In the Summary screen review your settings and Click Next.

10. Review your database in the Report Server Database screen.

11. Select Report Manager URL and make sure you specify the name you want to call the virtual directory created by Reporting Services (or use the default Reports). Optionally you can select the IP-address you want to use, the port and SSL setting. When done click Apply.

12. Email settings are not mandatory to setup, but can be configured appropiately if necessary.

13. Execution Account is necessary to configure if you will be running unattended reports. Optionally apply credentials and Click Apply.

20. Click Exit to close Reporting Services Configuration Manager.

Configure Configuration Manager 2007

SQL Reporting Services need the configuration of a Reporting Services Point role.

1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Site Management / <site code>-<site name> / Site Settings / Site Systems.

2. In this guide I will add the reporting services point site role to the existing site system. Right-click the site system name, and click New Roles. On the General page of the New Site Role Wizard, configure the general settings for this site system, then click Next.

3. On the System Role Selection page of the wizard, select Reporting Services point (don’t select Reporting Point by mistake, which is the traditional reporting functionality), then click Next.

5. On the Reporting Services Point page, specify the folder that will be created on the report server to contain the SQL Reporting Services reports used in Configuration Manager and then click Next.

6. Review the information shown on the Summary page, then click Next.

7. Click Close to exit the wizard.

8. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reporting Services, and expand the node.

9. Right-click the reporting services point server you want to configure, then click Properties.

10. On the Data Source Settings tab of the Report Server Properties dialog box, specify the Configuration Manager 2007 database server and database name (not the one create earlier for reporting!) to be used as the data source for SQL Reporting Services reports. Click the test button to verify that you have correct data entered.

11. On the Data Source Authentication tab of the Report Server Properties dialog box, specify the credentials used to access reports on the report server. I’ve use Windows Integrated Security here.

12. On the Data Source Security tab of the Report Server Properties dialog box, specify permissions for the users who have access to the data source specified in the Data Source Settings tab.

13. On the Security tab of the Report Server Properties dialog box, specify the users who have access to the selected report server.

14. Click OK to close the Report Server Properties dialog box.

Import standard reports in Reporting Services

The last step in configuring SRS for Configuration Manager 2007 is to convert the standard reports to Reporting Services reports.

1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reporting Services

2. Right-click the Reporting Services server that the standard reports will be copied to, then click Copy Reports to Reporting Services.

3. On the Data Source Settings page of the Copy Reports Wizard, specify the Configuration Manager 2007 database server and database name from which to copy the reports. Click Next.

4. On the Data Source Authentication page of the Copy Reports Wizard, choose the authentication method required to run the copied reports on the reporting point server. Click Next.

5. On the Select Reports page of the Copy Reports Wizard, select the reports you want to copy to the reporting services point. Select Overwrite existing reports if you want to overwrite any reports that already exist on the reporting services point. Click Next.

6. On the Security page of the Copy Reports Wizard, specify which users can access the copied reports and the roles they must belong to. For more information about the options on this page, see Report Server Properties: Security Tab. Click Next