How to: Create custom SQL- based Reports in System Center 2012 R2: Configuration Manager

A customer asked me  for a “how-to” on how to Create reports in SCCM 2012, so why not share with everyone. This post is not intended to show how to write or design queries, but show you have to create  a report based on a SQL query you might already have. Even-though we are skipping how to write SQL Queries for know, this post is still going to be a bit long. Time for that cup of coffee! First some requirements: You need to have an SQL Server instance for SCCM with SQL Reporting Services running You need to have [...]

Error when running Application reports in ConfigMgr 2012

During the SQL Reporting Services site system role, SCCM automatically create a login in SQL and assign the required permissions to the account that you have specified. However I have seen several times that the account do not have the permissions required to run some of the Application reports.   When running the Software Distribution - Application Monitoring > All application deployments (advanced) report I get the following error An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'FirstMatchingAppOrCollection'. (rsErrorExecutingCommand) The EXECUTE permission was denied on the object 'fnGetSiteNumber', database 'CM_PS1', schema 'dbo'. To solve the [...]

State of the Empire: How to use E-mail Notifications in ConfigMgr 2012 SP1

In the old days you found out something was wrong when phones started to ring and senior management was at you "doorstep" with an angry look. In Configuration Manager 2012 SP1 we have many ways of being proactive with the state of our environment and the state of our precious clients. In this blog post ill cover: Client Status alerts (Check, Remediation & Activity) Endpoint Protection alerts (Malware detection & outbreak) Site Server status alerts (Site backup, Software Update sync, Database free space & replication) Reports Note: E-mail Notification component has to be configured in the Configuration Manager environment. Client [...]

Configure E-mail Notification component in ConfigMgr 2012 SP1

In order to send Alert notifications or Reports via E-mail we need to make some configurations in the Console and in SQL Reporting Services Configuration Manager. In the Administration section select the Site Configuration folder and click Sites. In the Ribbon click Configure Site Components and select E-mail Notification. Enter the details for the SMTP connection, and test that it works. Click Ok, and you are done in the Configuration Manager Console. Onwards to glory! Almost… Open SQL Reporting Services Configuration Manager. Click E-mail Settings and enter the SMTP details in here. Click Ok, and you are done in the [...]

Role Based Administration change in ConfigMgr SP1

RBA or Role Based Administration is one of the many new features in ConfigMgr 2012. It’s a very powerful feature and has already helped lots of customers minimizing the need for having multiple primary sites. One annoying fact in RTM is that all collections (users and devices) shows up when running a report. This has changed in Service Pack 1 which makes the RBA feature even more powerful. In this example I have a user (DskAdmin) who is member of the Desktop Admins group in Active Directory. Desktop admins must all be granted Application Administrator permissions and allowed to work [...]

Error installing SQL Reporting Builder 3.0 in ConfigMgr. 2012

It has happen to me before, and it will happen again. I ran my ConfigMgr. 2012 administrator console without starting it as Administrator. When trying to edit a report I got the error message: Report Builder 2.0  is not installed as a click-once application on report server The error message is kind of funny, as I had already configured ConfigMgr. 2012 to launch Reporting Builder 3.0 and not the default 2.0 version. When I finally managed to start the Configuration Manager Administrator console as administrator I successfully launched Reporting Builder 3.0 for information about tweeking ConfigMgr. 2012 to launch report [...]

Timeout Expired on Report Server

One question I often get from System Center folks is about the SQL Reporting Services and report timeout. Unfortunately Microsoft have an default value on many report dataset which is to low for an production environment. The easy solution would be to edit the RSReportServer.Config XML file, I mean - in this file we have a Tag called SQLCommandTimeoutSeconds and would it be great if we could use this one. But in fact your Report server dosn't use the SQL timeout value specified in this file... Symptoms: If you see the error message: "An error occurred during client rendering" Like [...]

SQL Reporting Services and antivirus settings

Just installed and configured SQL Reporting Services 2008 with ConfigMgr. 2007 R2 SP2 this morning. Everything went smoothly except for one minor detail; I got an error in my mail subscription: Failure sending mail: The message could not be sent to the SMTP server. The transport error code was 0x800ccc15. The server response was not available After a little investigation it turned out that the McAfee firewall was causing the problem. From the local firewall log file we could see these entries: 3/22/2010 9:57:12 AM Blocked by port blocking rule C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe Anti-virus Standard Protection:Prevent mass mailing [...]

Creating a basic Config mgr. report with SQL reporting services

Converting the Configuration manager ASP reports to SRS reports (SQL Reporting Services) is a really easy task. Once you want to modify or create new the reports you need to a little more work. I create reports using these applications. SQL Server Report Builder 1.0 SQL Server Report Builder 2.0 (requires SQL Server 2008) Business Intelligence Development Studio – BIDS (Visual Studio) BIDS is the favorit choice as it gives the developer the most choices (creation of datasets etc.). However SQL Server Report Builder 2.0 is the easiest tool learn and doesn’t require a whole lot of SQL knowledge. In [...]

Configuring SQL Reportings Services mail subscriptions with Configuration Manager

In this post I’ll describe how you configure mail subscriptions with Configuration Manager 2007 R2. I assume that SQL Reporting Services is installed and configured on a remote database. I also assume that the Reporting Services Site system is configured in the Configuration Manager Administrator console. Prerequisites SQL Reporting Services requirements Execution account. Windows user account with read only access to all reports. The Execution account is specified in the Reporting Services Configuration Manager SMTP configured in the Reporting Services Configuration Manager. You also need to make sure that the SMTP port is enabled for relaying and anonymous access. If [...]