You are browsing the archive for SQL Reporting services.

KB2688247: SQL Server cumulative updates must be manually installed on secondary System Center 2012 Configuration Manager sites that use SQL Server Express

10:21 am in ConfigMgr, ConfigMgr 2012, ConfigMgr 2012 SP1, ConfigMgr V.next, SCCM 2012, SCCM 2012 SP1, SQL, SQL Cluster, SQL Reporting services by Kenny Buntinx [MVP]

 

System Center 2012 Configuration Manager: The original RTM version of Microsoft System Center 2012 Configuration Manager installs Microsoft SQL Server Express 2008 R2 Service Pack 1 (SP1) when you deploy a new secondary site. The minimum supported SQL Server version is Cumulative Update 4 for SQL Server 2008 R2 Service Pack 1 (SP1).

System Center 2012 Configuration Manager Service Pack 1:  System Center 2012 Configuration Manager SP1 requires Cumulative Update 6 for SQL Server 2008 R2 Service Pack 1 (or SQL Server 2008 R2 SP2).

You must manually install the Cumulative Update for SQL Server 2008 R2 SP1 on the new secondary site after the site is installed. More information here : http://support.microsoft.com/kb/2688247

Hope it Helps ,

Kenny Buntinx

Configmgr 2012 Reporting throws an error at you when trying to run a report

6:29 am in ConfigMgr, ConfigMgr 2012, ConfigMgr 2012 SP1, ConfigMgr Reporting, Report, Reporting, SCCM 2012, SCCM 2012 SP1, SQL Reporting services, System Center by Kenny Buntinx [MVP]

 

Did you ever configured reporting in Configmgr 2012 and specified a reporting service account like below ?

image

Did you get an error thrown at you like this when you try to run a report ?

SNAG-0085

If we look a little closer , we see the following line that is very interesting :”Logon failure: the user has not been granted the requested logon type at this computer “ as shown below in the log file

——————————-

System.Web.Services.Protocols.SoapException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot impersonate user for data source ‘AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_’. —> Microsoft.ReportingServices.Diagnostics.Utilities.LogonFailedException: Log on failed. Ensure the user name and password are correct. —> System.ComponentModel.Win32Exception: Logon failure: the user has not been granted the requested logon type at this computer
   at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.GetReportParameters(String Report, String HistoryID, Boolean ForRendering, ParameterValue[] Values, DataSourceCredentials[] Credentials, ReportParameter[]& Parameters)
   at Microsoft.ReportingServices.WebServer.ReportingService2005.GetReportParameters(String Report, String HistoryID, Boolean ForRendering, ParameterValue[] Values, DataSourceCredentials[] Credentials, ReportParameter[]& Parameters)

——————————-

Solution :  When you request a service account , make sure that people creating this service account grant you the right "Allow log on locally" thru Local Policy or Group Policy. For more information about configuring this group policy on Windows Server 2003, see the Microsoft TechNet article, "Allow log on locally".  To solve this you will need to change the Local Security Policy of the server.

As a best practice , most service accounts don’t get per default the "Allow log on locally" for security best practices .However in this case it is needed to run the reports .

Hope it Helps ,

Kenny Buntinx

SQL 2008 R2 failover cluster for system center: Lessons Learned (the hard way)

12:22 pm in SQL, SQL Reporting services, System Center by Kenny Buntinx [MVP]

I had assigned a task to install 5 SQL 2008 R2 cluster instances and on one of them had Analysis Services installed on a two node SQL Server 2008 R2 cluster (along with db engine).

Now I discovered that Analysis Services was not needed on one of the instances.

I thought : “No Problem , let’s remove” .

How do I remove this service from the cluster?

I tried the add/remove programs and command line option and both end up with the message: "The selected instance is clustered and cannot be removed as specified. To remove the selected instance, select "Remove Node" on the Installation Center or specify /Action=RemoveNode from the command line."

I tried removing one node hoping I could then uninstall from the only remaining node but still could not remove Analysis Services.

I’ve searched Google  Bing and discovered this article that explained that “You cannot add or remove features to a SQL Server 2008 or SQL Server 2008 R2 failover cluster”: Http://support.microsoft.com/kb/2547273

So I learned it the hard way. Think before you install a feature on a SQL Cluster , because once it is installed , there is no way back !!

Hope it Helps ,

Kenny Buntinx

How to retrieve the Lenovo Modelname (Full name) instead of TypeNumber within Configmgr

2:56 pm in ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr Dashboards, ConfigMgr Reporting, ConfigMgr SP2, configmgr2007, ConfigMgr2007 R3, Reporting, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 R3, SCCM 2007 SP2, SCCM Dashboards, sccm2007, SQL Reporting services by Kenny Buntinx [MVP]

A customer of mine wanted a readable report on the different machine that existed in his country.

Right now it apears as the typenumber , witch frankly a manager does not care about and I can totally understand it.

You will see what I mean . This is what you get if you use the default report :

image

This is what management wants to see :

image

To get to this result , please follow the guidelines below :

Now to tried and  find out where it was stored  , we used wmiexplorer . We show you where to find it below :

wmi

Lenovo’s store their “ModelName” in a different WMI class that others (Win32_ComputerSystemProduct as opposed to Win32_ComputerSystem ).  By default that class is not enabled in the SMS_DEF.MOF file.

Go to your SMS_DEF.MOF file and enable it as followed

<div>
<div>
[ SMS_Report 
SMS_Group_Name ("Computer System Product")
<!--CRLF-->
<pre><span style="color: #606060;">   3:</span>   SMS_Class_ID   (<span style="color: #006080;">"MICROSOFT|COMPUTER_SYSTEM_PRODUCT|1.0"</span>) ]</pre>
<pre><span style="color: #606060;">   4:</span></pre>
<pre><span style="color: #606060;">   5:</span> <span style="color: #0000ff;">class</span> Win32_ComputerSystemProduct : SMS_Class_Template</pre>
{
[SMS_Report (TRUE)     ]
string</span>     Caption;
<pre><span style="color: #606060;">   9:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>)     ]</pre>
<pre><span style="color: #606060;">  10:</span>         <span style="color: #0000ff;">string</span>     Description;</pre>
<pre><span style="color: #606060;">  11:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>), key]</pre>
<pre><span style="color: #606060;">  12:</span>         <span style="color: #0000ff;">string</span>     IdentifyingNumber;
<pre><span style="color: #606060;">  13:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>), key]</pre>
<pre><span style="color: #606060;">  14:</span>         <span style="color: #0000ff;">string</span>     Name;</pre>
<pre><span style="color: #606060;">  15:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>)     ]</pre>
<pre><span style="color: #606060;">  16:</span>         <span style="color: #0000ff;">string</span>     SKUNumber;</pre>
<pre><span style="color: #606060;">  17:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>)     ]</pre>
<pre><span style="color: #606060;">  18:</span>         <span style="color: #0000ff;">string</span>     UUID;</pre>
<pre><span style="color: #606060;">  19:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>)     ]</pre>
<pre><span style="color: #606060;">  20:</span>         <span style="color: #0000ff;">string</span>     Vendor;</pre>
<pre><span style="color: #606060;">  21:</span>     [SMS_Report (<span style="color: #0000ff;">TRUE</span>), key]</pre>
<pre><span style="color: #606060;">  22:</span>         <span style="color: #0000ff;">string</span>     Version;</pre>
<pre><span style="color: #606060;">  23:</span> };</pre>

Now that the HW inventory is modified , we still need to update our reporting .Below you will find the code for the report :

Here’s the fun part… win32_computersystemproduct is somehow hard coded into inventory to be resolved into v_gs_client0 , so go look there for the results (once you’ve made the changes)  . Thanks Sherry Kissinger for the tip !!!

<pre><span style="color: #606060;">   1:</span> <span style="color: #0000ff;">select</span>  distinct</pre>
<!--CRLF-->
<pre><span style="color: #606060;">   2:</span>  v_R_System_Valid.ResourceID,</pre>
<!--CRLF-->
<pre><span style="color: #606060;">   3:</span>  v_R_System_Valid.Netbios_Name0 <span style="color: #0000ff;">AS</span> [Computer Name],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">   4:</span>  v_R_System_Valid.Resource_Domain_OR_Workgr0 <span style="color: #0000ff;">AS</span> [Domain/Workgroup],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">   5:</span>  [Top Console User] = <span style="color: #0000ff;">CASE</span></pre>
<!--CRLF-->
<pre><span style="color: #606060;">   6:</span>  <span style="color: #0000ff;">when</span> (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 <span style="color: #0000ff;">is</span> NULL <span style="color: #0000ff;">or</span> v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = <span style="color: #008000;">'-1') </span></pre>
<!--CRLF-->
<pre><span style="color: #606060;">   7:</span>  <span style="color: #0000ff;">then</span> <span style="color: #008000;">'Unknown' </span></pre>
<!--CRLF-->
<pre><span style="color: #606060;">   8:</span>  <span style="color: #0000ff;">Else</span> v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0</pre>
<!--CRLF-->
<pre><span style="color: #606060;">   9:</span>  <span style="color: #0000ff;">End</span>,</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  10:</span>  v_GS_OPERATING_SYSTEM.Caption0 <span style="color: #0000ff;">AS</span> [Operating System],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  11:</span>  v_GS_OPERATING_SYSTEM.CSDVersion0 <span style="color: #0000ff;">AS</span> [Service Pack Level],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  12:</span>  v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 <span style="color: #0000ff;">AS</span> [Serial Number],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  13:</span>  v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 <span style="color: #0000ff;">AS</span> [Asset Tag],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  14:</span>  v_GS_COMPUTER_SYSTEM.Manufacturer0 <span style="color: #0000ff;">AS</span> [Manufacturer],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  15:</span>  v_GS_COMPUTER_SYSTEM.Model0 <span style="color: #0000ff;">AS</span> [Model],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  16:</span>  v_GS_Client0.Version0 <span style="color: #0000ff;">AS</span> [ModelName],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  17:</span>  v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 <span style="color: #0000ff;">AS</span> [Memory (KBytes)],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  18:</span>  v_GS_PROCESSOR.NormSpeed0 <span style="color: #0000ff;">AS</span> [Processor (GHz)],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  19:</span>  (<span style="color: #0000ff;">Select</span> sum(Size0)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  20:</span>  from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership <span style="color: #0000ff;">on</span> (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  21:</span>   where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID <span style="color: #0000ff;">and</span></pre>
<!--CRLF-->
<pre><span style="color: #606060;">  22:</span>   v_FullCollectionMembership.CollectionID = @CollectionID) <span style="color: #0000ff;">As</span> [Disk Space (MB)],</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  23:</span>  (<span style="color: #0000ff;">Select</span> sum(v_GS_LOGICAL_DISK.FreeSpace0)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  24:</span>  from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership <span style="color: #0000ff;">on</span> (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  25:</span>  where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID <span style="color: #0000ff;">and</span> v_FullCollectionMembership.CollectionID = @CollectionID) <span style="color: #0000ff;">As</span> [Free Disk Space (MB)]</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  26:</span>  from v_R_System_Valid</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  27:</span>  inner join v_GS_OPERATING_SYSTEM <span style="color: #0000ff;">on</span> (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  28:</span>  left join v_GS_SYSTEM_ENCLOSURE_UNIQUE <span style="color: #0000ff;">on</span> (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  29:</span>  inner join v_GS_COMPUTER_SYSTEM <span style="color: #0000ff;">on</span> (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  30:</span>  inner join v_GS_Client0 <span style="color: #0000ff;">on</span></pre>
<!--CRLF-->
<pre><span style="color: #606060;">  31:</span> ( v_GS_Client0.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  32:</span>  inner join v_GS_X86_PC_MEMORY <span style="color: #0000ff;">on</span> (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  33:</span>  inner join v_GS_PROCESSOR <span style="color: #0000ff;">on</span> (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  34:</span>  inner join v_FullCollectionMembership <span style="color: #0000ff;">on</span> (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  35:</span>  left  join v_Site <span style="color: #0000ff;">on</span> (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  36:</span>  inner join v_GS_LOGICAL_DISK <span style="color: #0000ff;">on</span> (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) <span style="color: #0000ff;">and</span> v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  37:</span>  left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP <span style="color: #0000ff;">on</span> (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  38:</span>  Where v_FullCollectionMembership.CollectionID = @CollectionID</pre>
<!--CRLF-->
<pre><span style="color: #606060;">  39:</span>  Order by v_R_System_Valid.Netbios_Name0</pre>
<!--CRLF-->

Hope it Helps ,

Kenny Buntinx

ConfigMgr Dashboards and Software Update General Compliance Gauge

11:25 am in AdminUi, ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr Dashboards, ConfigMgr SP2, configmgr2007, Dashboards, Reporting, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 R3, SCCM 2007 SP2, SCCM Dashboards, sccm2007, SQL, SQL Reporting services, SRS by Kenny Buntinx [MVP]

Microsoft released last year the Configuration Manager 2007 dashboards.A dashboard is basically a webpage containing multiple reports with graphs. The dashboard is a WSS 3.0 / MOSS 2007 solution that tries to give an high level graphical representation of the data contained in ConfigMgr. This enables ConfigMgr administrators to have multiple information in reports on a single page or in this case to help IT managers what is happening in their environment without all the report data .

A manager at a customer asked me if it would be possible to have a general percentage of Software Update Percent Compliance spread over all the Software deployment Management advertisements that are targeted to Pre-Production and Production.

To accomplish this written a SQL query to to this . As I don’t take all the credit for doing this , I will refer to them during my blog post .

I started with getting my initial report SQL code from Kent Agerlund (MVP configMgr) on http://blog.coretech.dk/confmgr07/config-mgr-inventory-and-reporting/status-report-for-software-update-deployments/

As soon as I used this SQL Query in My SCCM Dashboards , I quickly saw that I could not use the value “% Compliance” on the Y axis in combination with my “Deployment” name. It is shown below in the picture :

 

image

 

As I needed to have the Percentage of my total compliance spread over my SDM Lists ,  I needed to modify the query . I want to special thank Raghu Kethineni for his help understanding this query .This is how the SQL query should look like to use the value “% Compliance” on the Y axis in combination with my “Deployment” name :

 

Select Deploymentname, Available, Deadline,

cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) AS ‘% Compliant’,

  [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)]

From

(select

a.AssignmentName as DeploymentName,

a.StartTime as Available,

a.EnforcementDeadline as Deadline,

sn.StateName as LastEnforcementState,

count(*) as NumberOfComputers

from v_CIAssignment a

join v_AssignmentState_Combined assc

on a.AssignmentID=assc.AssignmentID

join v_StateNames sn

on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,

      sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)

FOR LastEnforcementState IN

( [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)])

) AS pvt

****** SQL Query end ******

After modifying the SQL query , the value appeared in the list as shown below in the attached picture :

image

 

If you need to select specific SDM advertisements , like I had to split up “Pre-Production” from “Production” , I had to modify my SQL query once again by adding a filter as show in the code below . In this case we want to only have the Production .

****** SQL Query ******

Select Deploymentname, Available, Deadline,
cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) AS ‘% Compliant’,
  [Compliant],
  [Enforcement state unknown],
  [Successfully installed update(s)],
  [Failed to install update(s)],
  [Installing update(s)],
  [Waiting for another installation to complete],
  [Pending system restart],
  [Downloading update(s)]
From
(select
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,
count(*) as NumberOfComputers
from v_CIAssignment a
join v_AssignmentState_Combined assc
on a.AssignmentID=assc.AssignmentID
join v_StateNames sn
on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
where AssignmentName NOT like ‘%_PREPROD_%’
group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,
      sn.StateName) as PivotData
PIVOT
(
SUM (NumberOfComputers)
FOR LastEnforcementState IN
( [Compliant],
  [Enforcement state unknown],
  [Successfully installed update(s)],
  [Failed to install update(s)],
  [Installing update(s)],
  [Waiting for another installation to complete],
  [Pending system restart],
  [Downloading update(s)])
) AS pvt

****** SQL Query end ******

 

If done and you saved your dashboard  query , It will look like this :

 

image

 

Hope It Helps  ,

 

Kenny Buntinx

ConfigMgr SRS Web Reporting : Issue with report: "Computers that have run a specific metered software program"

9:00 pm in ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr SP2, configmgr2007, Reporting, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 SP2, sccm2007, SQL Reporting services, SRS by Kenny Buntinx [MVP]

Issue :

When running the report "Computers that have run a specific metered software program" thru the SRS reporting web page & If you want to select the month in the report , you will see years instead of months.

Just look at the screenshot below  :

clip_image002

Solution :

Easiest way would be to just edit the report directly in the AdminUI.

Go to Site Database -> Computer Management –> Reporting –> Reporting Services –> Server –> Report Folders –> Software Metering in your ConfigMgr Console .

 

image

 

Right-click “Computer that have run a specific metered software program”, select Properties.

 

  1. Change to the “Datasets” tab.
  2. Change the “Dataset Name” dropdown to DataSet2.
  3. Click the 3rd icon, “Report Parameters”.

 

As shown below in the picture :

image

 

When the “Report Parameters” section opens , Change the selection to “Month” in the top box.

 

image

 

Change the Label field to the correct value (which should already be in the drop-down selection anyway).

 

image

 

Now your report should look OK ….

 

Hope it Helps ,

 

Kenny Buntinx

ConfigMgr 2007 R2 & R3 : How to back up SCCM 2007 SQL Reporting Services

1:22 pm in ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr SP2, configmgr2007, ConfigMgr2007 R3, R3, Reporting, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 R3, SCCM 2007 SP2, sccm2007, SQL Reporting services, SRS by Kenny Buntinx [MVP]

A recent implementation at my ConfigMgr 2007 R3 TAP customer of R3 and SQL reporting services  put me thinking ahead for our disaster recovery procedure .

SRS Reporting role needs to be installed in the DEFAULT Instance ! Named instances are not supported !!

The standard ConfigMgr 2007 backup task does not back up the SSRS reports or the SRS configuration.You need to take this into consideration if you :

  • you have created any custom reports
  • you have created any custom report models
  • Set some custom security
  • etc

I am not going to claim any credit for this , as I found one of my fellow MVP’s Steve Thompson ( in fact a great SRS report Guru ) has created an awesome blog post about it right here : http://myitforum.com/cs2/blogs/sthompson/archive/2009/06/16/how-to-back-up-sccm-2007-r2-reporting-services.aspx

 

Hope it Helps ,

 

Kenny Buntinx

SCCM 2007 R3 Beta (refresh) Installation (Howto)

6:45 pm in ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr SP2, configmgr2007, ConfigMgr2007 R3, Installation, R3, Reporting, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 R3, SCCM 2007 SP2, sccm2007, SQL Reporting services, SRS by Kenny Buntinx [MVP]

I have recently deployed Configuration Manager 2007 R3 beta (refresh) in production at my TAP customer. Below I will outline the steps for deploying Configuration Manager 2007 R3 beta.

Disclaimer: You are not allowed to install any beta products in your production environment!!! This is only allowed for selected TAP Customers !!! Always install beta products in lab environments !!!

This blog post highlights R3 the beta upgrade preparation on your SCCM 2007 SP2 environment . You need SP2 in order to be allowed the installation of R3, so if you did not upgraded your environment , this is the first step !

We talk further about the actual server upgrade and validation tasks.

 

1. Prerequisites :

SCCM 2007 SP2 only environment

Below a table with the site roles where this R3 upgrade is applicable if you have SCCM 2007 SP2 installed :

Role

Needed Installs Comments Specific Comments
Site Server (Central Site) Hotfix KB977384 & R3 Installation Always install This site needs to be upgraded first.
Site Server (Primary Site) Hotfix KB977384 & R3 Installation Always install These sites needs to be upgraded secondly.
Site Server (Secondary Site) Hotfix KB977384 & R3 Installation * Always Install These sites needs to be upgraded after the primary sites because you will need certain included R2 features.
Admin Console R3 Installation to make sure that all functionality is present
DP, SUP, SQL DB Server (if remote) Not applicable none
 

SCCM 2007 SP2 R2 environment

Below a table with the site roles where this R3 upgrade is applicable if you have SCCM 2007 R2 already installed :

Role Needed Installs Comments Specific Comments
Site Server (Central Site) Hotfix KB977384 & R3 Installation none This site needs to be upgraded first.
Site Server (Primary Site) Hotfix KB977384 & R3 Installation none These sites needs to be upgraded secondly.
Site Server (Secondary Site) Hotfix KB977384 & R3 Installation * Hotfix must always be installed ! Look below for additional information if you really need to install R3 on your secondary’s
Admin Console R3 Installation to make sure that all functionality is present
DP, SUP, SQL DB Server (if remote) Not applicable none

* Site Server (Secondary Site) :

If you have SCCM 2007 R2 installed on your secondary sites and then :

1. You don’t need to install R3 on secondary sites if you do not use any "Proxy MP" or "AD discovery" feature at your secondary sites if you only use the "Power Mgmt" feature through your organization & run "AD discovery" on your Central or Primary sites.
2. You do need R3 on your secondary sites if you do use the "Proxy MP" functionality or "AD discovery" at your secondary sites.

If you are planning to use these features in secondary sites you need to install R3. Since most customers do use proxy MP role on secondary ,you need to install R3 there as well.

2. Pre-Flight Checks for R3 Beta (refresh) Upgrade

  • Take a  ConfigMgr Site Backup and verify that it is successful.
  • Make sure that ConfigMgr 2007 SP2 is installed correctly and that all site server components are healthy

 

3. Configuration Manager 2007 R3 Beta (refresh) Server Upgrade

 

  • Install the server side hotfix (KB977384) (included in the dowload of the R3 media) .This hotfix, which comes with the R3 of SCCM 2007, is a prerequisite for SCCM 2007 R3. During the installation it also creates a SCCM Package/Program containing a MSP file allowing to update SCCM Advanced Clients Components. This client hotfix package has to be deployed to all ConfigMgr 2007 SP2 clients before power policies can be managed.

image

Click “Next”to continue.

TRUVO-0083

Click “I accept …”to continue and select “Next”to continue.

image

Hit the Ïnstall” button.

TRUVO-0085

The Hotfix starts to install.

image

It will prompt you to create a Package & Program for later deployment to your Configmgr 2007 SP2 clients.

image

During the installation process, when prompted to create a software distribution package for client hotfix deployment, Provide a name for the ConfigMgr package & Program. However your package & program needs to be done thru conformity of your production naming convention & deployment standards. This client hotfix package has to be deployed to all ConfigMgr SP2 clients in the environment before their power policies can be managed

image

Specify the package source & click “Next” to continue.

image

Click “Next” to continue.

image

Click “Finish” to exit.

  • During the hotfix KB977384 installation, the source bits for the client hotfix package will be copied into the client\i386\hotfix\KB977384Beta folder. Look if the bits exists in that folder.

image

  • After successful installation of hotfix KB977384 pre-requisites, execute R3 beta (refresh) installation from the installation source location using SPLASH.HTA. Follow the screenshots below to complete the installation.

image

Click “Next” to continue.

image

Accept the license agreement and Click “Next” to continue.

image

Click “Next” to continue.

image

Click “Next” to continue.

image

image

Click “Finish” to exit.

 

4. Post Configuration Manager 2007 R3 Beta (refresh) Server tasks

  • Open SCCM Console & navigate to Site Database – Site Management – <Site Code> – <Site Name> and view properties to confirm that R3 Installed is “Yes” as shown below

image

  • Verify that the SMS_Def.mof has been appended with R3 specific WMI classes, without any changes to the pre-existing class definitions.

image

image

  • Go to the ConfigMgr Console, navigate to [Site Database] – [Site Management] – [Your site code] – [Your site name] – [Site Settings] – [Client Agents].You will see a new item called “Power Management Client Agent”. Go to the “Power Management Client Agent” properties and check the box “Enable Power Management on Clients”.

image 

  • Install SCCM Reporting Services Point. Power Management in SCCM 2007 R3 contains a number of reports to help you to analyze power consumption and computer power settings in your organization. These Reports require SQL Reporting Services which was introduced in SCCM 2007 R2.If you never worked or used SRS reporting , get used to it , because it will be the only reporting functionality left in Configmgr V.next.

Note : I am not going to explain how to set-up SRS reporting . You can find guidance on Technet or the online help .

image 

Copy SCCM Reports to Reporting Services. Power Management in SCCM 2007 R3 gives you 17 new reports.

image

Click “Next” to Continue.

image

Fill in your credentials and Click “Next” to Continue.

image

Select “Import Report Definition Language Files From Microsoft Signed Cabinet File”.

image

Browse to the "%SCCM installation folder%\Reports\Power Management" folder and select the MicrosoftReportPack.cab file. Click "Open” to Continue.

image

Look if all reports are selected and Click “Next” to Continue.

image

Click “Next” to Continue.

image

Look at the status and see that all reports are imported successfully. Click “Next” to Continue.

image

Look in the SCCM console if the reports exists.You can run all Reports from the SCCM Console now.

 

5. Deploy the MSP file contained into the SCCM Package created by installing the hotfix onto you SCCM 2007 SP2 Clients

After the R3 installation is completed on the site server, the next step is to deploy R3 hotfix to all SP2 clients to use all R3 features.Look for the Client hotfix package & program that where created during installation and must be available under ‘Software Distribution’ and then look for the Packages Node.

  • Validate your Package & Program Properties

TRUVO-0092

  • Deploy your package to all your Distribution points, before deploying the package to all clients.

TRUVO-0093

  • Create your deployment collection(s).Deployments should be done in multiple phases.Start with a Test/Pilot group and the second wave should be per site/region.You could use “Link to Collection” for avoiding creating to much collections.

TRUVO-0094

  • Creation your Advertisements.There are no special requirements in creating advertisements for this deployment.

TRUVO-0095

 

Hope it Helps ,

 

Kenny Buntinx

SCCM 2007 R2 – SQL Reporting services, Named instances and greyed out config option

10:52 am in ConfigMgr 2007, ConfigMgr 2007 R2, Known Issue, Reporting, SCCM 2007, SCCM 2007 R2, SQL Reporting services, SRS by Kenny Buntinx [MVP]

Hi All,

 

ConfigMgr 2007 R2 introduced a new feature for reporting. SQL reporting services based reporting instead of the original web based reports was added as a new feature. This makes sure that all system center products now use a consistent reporting product. Although both reporting methods can be used in conjunction with each other in the current release, as previously mentioned on this blog, this will not be the case in Sccm v.Next.

In this respect it makes sense to learn the ropes on this SRS technology now, one of the issues I have seen reporting, and which I ran into myself, is a greyed out admin ui interface if you try to start using the ConfigMGr 2007 R2 reporting services feature. After reporting the issue, the ConfigMgr team figured out what the issue was and they updated question 6 on their SRS faq:

http://blogs.technet.com/configmgrteam/archive/2009/05/14/faq-sql-reporting-services-integration-with-system-center-configuration-manager-2007-r2.aspx

For those of you who already have installed SRS, and want to use it in a named instance, you should be able to add another SRS default instance to the same box, and this should clear up the greyed out configuration options in the admin ui when you try to use the “copy reports to reporting services” wizard, or when you try to configure the properties of your ConfigMgr reporting services point in reports \ reporting services

Enjoy.

“Everyone is an expert at something”
Kim Oppalfens – Sms Expert for lack of any other expertise
Windows Server System MVP – SMS

http://www.scug.be/blogs/sccm/default.aspx

http://www.linkedin.com/in/kimoppalfens