Tag Archives: Configuration Manager 2012

CM 2012 – Reporting – Summarizing Results with COUNT Statements

This will be the second half of the series I’m creating on using SQL Report Builder with Configuration Manager 2012. In this part of the series I will show how to build a dashboard using various features.

This first video will demonstrate how to use a COUNT statement in order to summarize query results.

The SQL query used in this video is shown below

SELECT
Operating_System_Name_and0, COUNT(*) AS Qty

FROM
V_R_System

GROUP BY
Operating_System_Name_and0

CM 2012 – Reporting – Using Status Indicators as Visual Queues

I’m continuing the series on SSRS Reporting within Configuration Manager 2012 and building on the previously create report outlined in

CM 2012 – REPORTING – CREATE A TABLE REPORT USING SSRS

CM 2012 – REPORTING – CREATING MEANINGFUL RESULTS WITH SQL CASE STATEMENTS

CM 2012 – REPORTING – NARROWING RESULTS WITH PARAMETERS

We will add an indicator “light” which has the three conditions of green, yellow, and red, to serve as visual queues so that a person reviewing the report can quickly identify troublesome systems.  This will complete the work on this specific report and it will be used as a drill-down destination for a future exercise.

The SQL query used in this video is shown below:

SELECT
v_R_System.Name0,
V_R_System.Operating_System_Name_and0,
CASE
WHEN v_R_System.Client0=1 THEN ‘YES’
ELSE
‘NO’
END AS Client0,
CASE
WHEN v_R_System.Active0=1 THEN ‘YES’
ELSE
‘NO’
END AS Active0,
CASE
WHEN v_R_System.Client0+v_R_System.Active0 IS NULL THEN 0
ELSE Client0 + Active0
END AS Status

FROM
v_R_System

WHERE
V_R_System.Operating_System_Name_and0 = @OSName

ORDER BY
V_R_System.Operating_System_Name_and0,
v_R_System.Name0

CM 2012 – Reporting – Narrowing Results with Parameters

I’m continuing the series on SSRS Reporting within Configuration Manager 2012 and building on the previously create report outlined in

CM 2012 – REPORTING – CREATE A TABLE REPORT USING SSRS

CM 2012 – REPORTING – CREATING MEANINGFUL RESULTS WITH SQL CASE STATEMENTS

We will create and use a parameter which will display as a drop-down menu using values from a new dataset.

The SQL queries used in this video is shown below:

Second Dataset for Parameter

SELECT DISTINCT
Operating_System_Name_and0

FROM
V_R_System

ORDER BY
Operating_System_Name_and0

Original Dataset with Added Parameter in WHERE statement

SELECT
v_R_System.Name0,
V_R_System.Operating_System_Name_and0,
CASE
WHEN
v_R_System.Client0=1 THEN ‘YES’
ELSE
‘NO’
END AS Client0,
CASE
WHEN
v_R_System.Active0=1 THEN ‘YES’
ELSE
‘NO’
END AS Active0

FROM
v_R_System

WHERE
V_R_System.Operating_System_Name_and0 = @OSName

ORDER BY
V_R_System.Operating_System_Name_and0,
v_R_System.Name0

CM 2012 – Reporting – Creating Meaningful Results with SQL CASE Statements

Building upon our previous reporting outlined in

CM 2012 – REPORTING – CREATE A TABLE REPORT USING SSRS

we will use a SQL CASE statement to create more meaningful results from the values presented previously.

The SQL query used in this video is shown below:

SELECT
v_R_System.Name0,
V_R_System.Operating_System_Name_and0,
CASE
WHEN
v_R_System.Client0=1 THEN ‘YES’
ELSE
‘NO’
END AS Client0,
CASE
WHEN
v_R_System.Active0=1 THEN ‘YES’
ELSE
‘NO’
END AS Active0

FROM
v_R_System

ORDER BY
V_R_System.Operating_System_Name_and0,
v_R_System.Name0

Adaptiva OneSite – Configuration – Setting Preferred Clients

I’m sure that at one point in most technical careers, people have run into those networks which are similar to a spider’s web, spun by a drunk black widow. The illustration below is tame compared to what I’ve encountered, but it will serve a purpose for this article. The Main Office symbol represents a Configuration Manager Primary Site, the Office ### symbols represent locations housing Distribution Points, and the lettered symbols represent smaller sites and branch offices.

clip_image002[4]

When designing a Configuration Manager infrastructure, the network topology is very important in determining where to put additional Primary Sites, Secondary Sites, and Distribution Points, based on network layout, link speeds, endpoint populations, and other factors. You can leverage your Configuration Manager infrastructure for OneSite and using preferred client settings is a way to achieve a certain amount of network traffic isolation with respect to content pre-staging and downloads. Referring to the diagram above, the Office ### sites have been made parent sites to those smaller down-line sites for the purposes of this exercise.

Keep in mind that OneSite content download requests may flow up a topology but stop at the top of the hierarchy (Central Office). A OneSite preferred client is one that will handle all content requests from down-line clients, provided the content is present, prior to the request being sent to the Central Office site. That being said, looking at the diagram below (which is a portion of the first diagram presented), a case could be made for setting the preferred client key on Office 003 and Office 004 for their child sites.

clip_image004[4]

Setting a preferred client within OneSite will force down-line clients to look to the preferred client first before traversing up-line in the network topology when requesting content. Here is how to create a client setting which will set this variable within the environment.

From within Adaptiva Workbench, starting at the Home perspective, expand Workbench perspectives, expand Misc and then select the System Configuration Perspective from the result choices.

clip_image005[4]

After the System Configuration Perspective screen loads, create a new client settings policy by selecting “Create New Client Settings Policy.” The Custom Client Settings Policy Editor panel will load to the right.

clip_image006[4]

Within the Custom Client Settings Policy Editor, set an intuitive name for your new policy setting, provide a short description and priority, and set a settings override priority. To the right, click the Add Collection. A drop-down menu will appear, showing collections from your Configuration Manager environment. Select the collection in which you have placed those OneSite clients you wish to be set as preferred clients for specific down-line branches of your OneSite network topology. (This section is located in the top panel of the Policy Editor screen.)

clip_image008[4]

Within the System config settings panel (located at the bottom of the Policy Editor screen), expand SystemConfig, expand Contentsystem, then drag Client type from the left, into and over the SystemConfig setting in section labeled “Overridden client settings by this policy” until you see a “+” sign appear, then release the mouse button to add the attribute. Within the “Overridden client settings by this policy,” expand Contentsystem and select Client type. In the far right section, change the “New value” to 1. Click the Apply button at the top of this panel.

clip_image010[4]

Click the Save icon in the top left of the Custom Client Settings Policy Editor section and your policy will be saved and applied to the CM collection you designated earlier. In the future, if additional preferred clients are needed, simply add them to the same CM collection.