Your browser (Internet Explorer 6) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.

List of Computers from an Active Directory User Security Group

Garth Jones (fellow ConfigMgr MVP) blogged a really great query for get a list o PC’s by using a user security group.

Read his original blog post here.

   1: select 


   3:   CS.Name0,


   5:   CS.UserName0


   7: from 


   9:   dbo.v_R_User U


  11:   join dbo.v_RA_User_UserGroupName UGN on U.ResourceID = UGN.ResourceID


  13:   join dbo.v_GS_COMPUTER_SYSTEM CS on CS.UserName0 = U.Unique_User_Name0


  15: Where


  17:   UGN.User_Group_Name0 = 'GARTEK\Domain Users'


Editing CM12 reports in the ConfigMgr console with SQL Server 2008 R2

Fellow MVP Steve Thompson has created a nice post for reporting in ConfigMgr 2012.

Read the full post here.

Presently, if you have installed ConfigMgr 2012 with SQL Server 2008 R2, and attempt to edit a report from the console, you will get an error message that states “Report Builder not available”. The problem here, ConfigMgr 2012 is designed to work out of the box with Report Builder 2.0 as explained here:

Since you have installed SQL Server 2008 R2, which ships with Report Builder 3.0, you can indeed use Report Builder 3.0, just open it directly from SSRS Report Manager.

While not supported or endorsed by Microsoft, a workaround is available to integrate RB 3.0 from the console, as follows:

You’ll need to set a registry key to name the RB 3.0 application manifest.  The key in question is “HKLM\Software\Wow6432Node\Microsoft\ConfigMgr10\AdminUI\Reporting\ReportBuilderApplicationManifestName”.  Change its value from “ReportBuilder_2_0_0_0.application” to “ReportBuilder_3_0_0_0.application” on any machine from which you want to launch RB 3.0.


Configuration Manager–Recovering Stock Reports and Bulk Deleting Reports

Recently was working at client, we caused ourselves a slight reporting issue.  We had exported all the reports from a previous site, including custom reports.  When we imported those into the new site, we overwrote the stock reports.  What happens when you do this is that it breaks all the built in reporting links.

You lose the links like this example below.


In order to recreate the stock reports and ensure the appropriate links are there, we simply need to duplicate the process that creates the original reports.  This can be achieved by running a mofcomp against the smsrprt.mof file that is located in \installpath\bin\i386.


One you have done this, you will be able to look at the stock reports and all the links that were previously missing will now be there.  This creates the original stock 332 reports, this doesn’t include the AI, FSP or FEP reports that might be added when additional features are added or installed.

In addition during this process and testing, there isn’t a nice way to delete multiple reports. No way to do this via the ConfigMgr console is provided.  So I wrote a simple script to allow you to delete multiple reports.

The following VBscript will delete multiple reports and you can specify the ID range you want to delete.  if you uncomment out the echo commands, you can be prompted as each report is deleted, but that isn’t very efficient if you are deleting a lot of reports.

   1: Dim objswbemservices

   2: Sub ConnectToSCCM()

   3:   set objSwbemLocator = CreateObject("WbemScripting.SWbemLocator")

   4:   set objSWbemServices= objSWbemLocator.ConnectServer(".", "root\sms")

   5:   Set ProviderLoc = objSWbemServices.InstancesOf("SMS_ProviderLocation")


   7:   For Each Location In ProviderLoc

   8:         If Location.ProviderForLocalSite = True Then

   9:             Set objSWbemServices = objSWbemLocator.ConnectServer(Location.Machine, "root\sms\site_" + Location.SiteCode)

  10:         End If

  11:   Next

  12: End Sub


  14: ConnectToSCCM()


  16: For each objreport in objswbemservices.execquery("select * from sms_report")

  17:     If objreport.reportid >= 1 And objreport.reportid <= 378 Then

  18:         then objreport.delete_

  19:             'Wscript.Echo "Deleted report: " & objreport.ReportID

  20:             'Wscript.Echo "Report name: "    & objreport.Name

  21:     End If


  23: Next


HTTP 500 Error with Large Reports in SCCM 2007

Phil Schwan has a new post up talking about how to raise the ASP buffer limit to show “large” reports.

As an FYI, another solution to this issue is to use SQL Reporting Services as the 10k row limit and the ASP buffer issue don’t apply when using SRS.

Read the full post here.

If you have more than a couple thousand clients in your SCCM 2007 site and have ever tried to run a large report (like “Hardware 01A – Summary of computers in a specific collection” against the All Systems collection, for example), you’ve probably encountered this HTTP 500 error:


This problem and resolution are pretty well documented for Windows Server 2003 and IIS6, but in this particular case we’re running Windows Server 2008 SP2, so we’re using IIS7.  The underlying cause is the same (default ASP Buffering Limit is set too low to handle the size of the report) and the fix is the same (increase the limit), but the steps to fix are just a little different for IIS7



Beginners Guide to Writing SCCM Reports

John Marcum has created a nice blog post for how to get started creating your own SQL based web reports. 

Read his full post here.

There are often questions on the forums about writing reports and SQL queries for SCCM. These are often people who have no SQL experience at all. There’s no easy way to learn to write these reports but I’m going to show you how I learned to do it and how I still do many of my reports today.


Configuration Manager-Reporting Services Access Denied When Running R3 Power Management Reports

Ran into an issue at a client this week that I haven’t run into before.  When trying to access the new R3 power management reports, we were receiving an access denied.  The errors we received were the following:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘DataSet2’. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset ‘DataSet2’. (rsErrorExecutingCommand)
The EXECUTE permission was denied on the object ‘PowerManagementGetPowerCapabilities’, database ‘SMS_XXX’, schema ‘dbo’.  

All other reports in Reporting Services worked fine.  The reason turned out to be that the new R3 reports use some stored procedures in the ConfigMgr database.  Web reports are typically just queries that use tables and views.  The new R3 reports are the first ones for ConfigMgr that are Reporting Services only reports.  Typically when locking down permissions on reports or a reporting service account, you would just assign the account DB_Reader rights in the SQL database.  This works for all the web reports but didn’t work for the R3 reports.  DB_Reader doesn’t have access to run the stored procedures that are required for the power management reports. 
In order for the R3 reports to run successfully, we need to add the service account to the “smsschm_users” database role. Huge thanks to Kent Agerlund and Garth Jones for providing me with the appropriate fix.

First, lets open up the database and drill down to the database roles:


Next we need to go into the properties of the "smsschm_users” role:


Next we need to add our service account to be a Role Member:


Select “Browse” and then select the service account you want to use.


Here we have our service account selected, then click “ok”.


Once you verified the service account is listed in the Role Members pane, then select “ok”.


Another way to add the necessary rights is to go into the login properties of your service account. Then go to “user mapping” and select the SMS/ConfigMgr DB and check the database role membership. This is probably the easier method (less clicks), but it’s good to know there are 2 ways to do it Smile


Kent Agerlund also has a post on this.  I wasn’t able to find his post when I encountered the issue and used Google, however I was told that using Bing would have resolved my issue, but I haven’t tested that.


New KB: Users may be unable to view and execute the System Center Configuration Manager 2007 reports hosted on SQL Reporting Services

Read the full post here.

Users may be unable to view and execute the System Center Configuration Manager 2007 reports hosted on SQL Reporting Services.
Problem 1: Trying to access http://<SQLServerName>/reports returns blank window. 
Problem 2: When trying to execute one of the reports you get the following error: 

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source ‘{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}’. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors.


This can occur due to a lack of permissions on the Reporting Server and the Database.


The basic configuration to allow an end user to view and execute the Configuration Manager Reports hosted via Reporting Services Point are as follows: 
1. Create a Global Security Group for users with Read access to the Reports.
2. Add all of the users to this group who need to access to the Reports. 
3. Open the Reports website with admin privileges (e.g. http://<SQLServerName>/reports).
4. Click on the Properties Tab > Click on New Role Assignment.
5. In the Group or User name box type in the domain\<GlobalSecurityGroup> you created in step 1.
This will allow those users to view the reports but will fail to execute them. They will get the error described in Problem 2.
To resolve Problem 2 follow the below steps: 
1. Add the Global Security Group in SQL using the SQL Server Management Studio Security > Logins > New Login > User Mapping.
2. Check the box for the SCCM DB and Check “db_datareader” role.

For the latest version of the article see the link below:

KB2428400 – Users may be unable to view and execute the System Center Configuration Manager 2007 reports hosted on SQL Reporting Services


Configuration Manager–Installing ConfigMgr R2 Client Status Reporting

Select “Client Status Reporting” from the main installation wizard under the “Additional Content” section. You can also browse to the “Client Status Reporting” folder on the CD as the install will take you there anyways.


Launch the “Clientstatusreporting” MSI.


Select “Next”.


Select “I accept the license agreement” and select “Next”.


Select the destination folder and select “Next”.


Select “Next” to start the installation.


Select “Finish”.


You will have a new menu option in the Start menu. Launch the “Configure Client Status Reporting” to finalize the installation.


On the “Site Settings” tab, you will need to configure the SQL server and instance, along with what account you want the Client Status reporting to run under. I typically will configure it to use the local system account.


On the Options tab, you will want to select the “Update Configuration Manager 2007 site database with inactive client information”, you will also want to configure the client activity periods for your environment.


On the Schedule tab, you will want to enable the Client pulse settings along with the Client ping settings. You will also want to configure the schedules for those settings.



Importing the Client Status reports into Configuration Manager

Select “Import Objects” on the Reporting node.


Select “Next”.


Select “Browse”.


Browse to the path you see below. Then select “Open”.


Select “Next”.


Select “Next”.


Select “Next”.


Select “Finish”.


After the reports have been imported, you can see them listed in the reports folder.



New TechNet Virtual Lab: Installing and Configuring the System Center Configuration Manager Dashboard

After completing this lab, you will be better able to install and configure System Center Configuration Manager 2007 Dashboard, create and Customize Data sets, and control access and configure Data Displays in System Center Configuration Manager Dashboard.

For all the details and to register click here.


ConfigMgr R3: Power Management Reports

I wanted to take some time to show you some of the power management reports that are provided with R3.  There are quite a number of reports, however there are a few key ones that I think most people will want to use.

For all of these reports I will be showing data against my hyper-v environment. So you probably will see different data in your own test environment if you have physical machines, and for that matter, just more data in general.

I think one of the first ones that people will look at is the “Power Capabilities Report”, this report will show you the capabilities of the targeted systems.


“Power Computer Activity” is a neat one because it’ll show you when the computers are on, and when users are actually using the computers.  It will also show you the monitor activity, however I don’t have data to show that from my virtual environment.


“Environmental Impact Report” displays a graph showing carbon dioxide emissions saved by a given collection over a specified time period.


“Power Consumption” displays the total computer power consumptions (in kW) for a specified collection over a specified time period.


“Power Cost” displays the total computer power consumption cost (in dollars) for a specified collection over a specified time period.


“Power Settings” displays an aggregated list of power settings used by computers in the specified collection. You can drill-down into this report to see a list of computers using specific power settings. (Note this is not the full report, there are many more options not in this screenshot)