You are browsing the archive for Reporting.

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

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 R3 : Power Management In Practice (how-to) Part 1

9:33 am in ConfigMgr, ConfigMgr 2007, ConfigMgr SP2, configmgr2007, ConfigMgr2007 R3, Power Management, R3, Reporting, sccm, SCCM 2007, SCCM 2007 R3, SCCM 2007 SP2, sccm2007 by Kenny Buntinx [MVP]

Hi there ,

I have recently deployed Configuration Manager 2007 R3 beta (refresh) in production at my TAP customer.In my previous blog post I highlight the R3 the beta installation on your SCCM 2007 SP2 lab environment .

See “http://scug.be/blogs/sccm/archive/2010/05/14/sccm-2007-r3-beta-refresh-installation-howto.aspx

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 the R3 Powermgmt feature ; how to use it in your environment and start using reporting on your powerconsumption.

 

Prerequisites :

First you need to start rolling out the R3 Client mgmt hotfix to your clients.When done the SCCM client will show up with a newer version build.The current version is 4.00.6487.2125.

image 

Enable your Power management client under “Client Agents”:

image

Configuration Manager 2007 R3 beta Client upgrade Validation Report

After you have deployed the R3 beta Client hotfix, you can use the below query to create a web or SQL SRS report for tracking the R3 beta Client upgrade status

SELECT  CASE sis.Client_Version0
             WHEN ‘4.00.6487.2000’ THEN ‘ConfigMgr SP2 RTM’
             WHEN ‘4.00.6487.2125’ THEN ‘ConfigMgr SP2 R3 beta’
        END AS [ClientVersion]
       ,COUNT(1) AS [Total]
  FROM v_R_System sis where
         sis.Client0 = 1
         AND sis.Obsolete0 = 0
         AND sis.Client_Version0 IN (‘4.00.6487.2000′,’4.00.6487.2125′)
GROUP BY CASE sis.Client_Version0
               WHEN ‘4.00.6487.2000’ THEN ‘ConfigMgr SP2 RTM’
               WHEN ‘4.00.6487.2125’ THEN ‘ConfigMgr SP2 R3 beta’
          END

Collections :

You basically need 3 collections :

    • A Baseline collection : This collection contains the members that have will be in scope for Powermgmt.
    • An Enforcement collection : This collection are going to have a Powermgmt plan applied.
    • An Opt-Out collection : This collection will never have a Powermgmt plan applied.

Some people have different requirements for implementing power plan: some people will have longer work hours (eg. From 6 am – 11 pm), some branch office may have different working hours, etc. To define different power policy, people need to communicate with different teams and make different power settings for different requirements. So you will define different PowerMgmt collections to meet different needs.
In this blog post example, we will only enforce power policy for one set of regular working hours.

This is how my collection structure looks like :

image 

Like you see here , I have made the difference between laptops/Desktops . This has been done to monitor more closely of what the consumption/savings would be , but also with in the back of my mind for later enabling different powerplans between desktops/laptops.

[PC-DESKTOP-POWERMGMT-SITE] PowerPlan Baseline Collection query :

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_POWER_MANAGEMENT_CAPABILITIES on SMS_G_System_POWER_MANAGEMENT_CAPABILITIES.ResourceId = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation %" and SMS_R_System.Name not like "OCHP%" and SMS_R_System.Client is not null  and SMS_R_System.ADSiteName = "CDM-Hoofdzetel" and SMS_G_System_POWER_MANAGEMENT_CAPABILITIES.PreferredPMProfile = 1

It looks like this :

image 

As you can see I use the “Power Capabilities.PreferedPMProfile” .

This value is been returned from your HW inventory that reads out the value from the BIOS that have the ACPI v2.0 Specification  The set of values is currently:

    • 0–Unspecified
    • 1–Desktop
    • 2–Mobile
    • 3–Workstation
    • 4–Enterprise Server
    • 5–SOHO Server
    • and more

 

[PC-DESKTOP-POWERMGMT-SITE] PowerPlan Disabled Collection query :

This is right now a static collection membership as I want to add different computers quickly

[PC-DESKTOP-POWERMGMT-SITE] PowerPlan Enabled Collection query :

I had the need to make a collection that toke all the members from my baseline “[PC-DESKTOP-POWERMGMT-SITE] PowerPlan Baseline Collection” collection and exclude members from my “[PC-DESKTOP-POWERMGMT-SITE] PowerPlan Disabled” collection.I needed to seperate several custom machines that I don’t want to be treated by my powermgt plan . I had a really hard time trying to find and/or build a query that actually worked.

The query to use :

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Client = 1

and SMS_R_System.ClientType = 1

and SMS_R_System.ResourceId in (select ResourceID from SMS_CM_RES_COLL_xxxxxxx)

and SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_yyyyyyy)

Replace the item in red with the Collection ID of the baseline collection : “[PC-DESKTOP-POWERMGMT-SITE] PowerPlan Baseline Collection” and replace the item in Blue with the Collection ID of the collection : “[PC-DESKTOP-POWERMGMT-SITE] PowerPlan Disabled” you are trying to exclude, which is located under the General tab of the properties window.

It looks like this :

image

Power Plans :

Before enabling Powerplans to your collections , you need to know about which clients are capable to apply any of your powerplan settings and eventually at that moment take corrective measures.

To do that we will use SRS reporting  and I will explain that in my other blog post next week . So stay tuned to see my next blog post on how to indentify machines that will need different powerplans and actions taken to resolve some of the issues found.

 

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 Reporting : How to change the Maximum Rows Returned in the Values List that is default set to 1000 rows

1:34 pm in AdminUi, ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, Reporting, sccm, SCCM 2007, SCCM 2007 R2 by Kenny Buntinx [MVP]

Report Viewer in Configuration Manager 2007 limits the number of rows returned to 1,000 rows when you click Values and the values list displays for a prompt. The maximum number of rows returned by the query for the prompt that populates the values list can be modified by creating a registry key and setting a value on site system computers that have the reporting point role.

IMPORTANT : Increasing the maximum number of rows for queries that return a large amount of data or for queries that are inefficiently written might cause performance issues when the values are displayed.

To do this and configure the number of rows returned in a values list

  1. Open the Registry Editor on the reporting point computer.

  2. On reporting points that run on a 32-bit operating system, navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Reporting.

    On reporting points that run on a 64-bit operating system, navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\SMS\Reporting.

  3. Create a DWORD value named Values Rowcount, and then set its value to the number of rows that you want returned in the report query. If you want to return all rows, set the value to 0xffffffff, which is the hexadecimal equivalent of –1.

    The configured number of rows is returned by any prompt query that is run from this reporting point.

 

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