You are browsing the archive for SRS.

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