All posts by MikeL

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

CM 2012 – Reporting – Create a Table Report Using SSRS

One of the best features of Configuration Manager is its ability to collect immense amounts of data.  What a CM Administrator does with that data can make the difference between a supervisor being impressed with the capabilities of CM, or believing it has a database full of useless information.  That’s where the reporting feature comes in.  I’ve met many CM Administrators who are still exporting the results of SQL queries into spreadsheets and sending those to their supervisors.

Hopefully, the series of videos I’ll be releasing will help those folks create some very useful reports and get them excited about using Report Builder.

This is a short video demonstrating how to create a basic table report using Configuration Manager 2012 and SQL Report Builder.

The SQL query used in this video is shown below:

SELECT
v_R_System.Name0,
V_R_System.Operating_System_Name_and0,
v_R_System.Client0,
v_R_System.Active0

FROM
v_R_System

ORDER BY
V_R_System.Operating_System_Name_and0,
v_R_System.Name0

CM 2012–Troubleshooting – MP has rejected a message from GUID:XXXXX

For those not familiar with the CM database, it can be confusing at times to locate the specific client to which an error message within SMS_MP_CONTROL_MANAGER is referring.

Let’s take the following error as an example:

Message ID:  5447
Description:  MP has rejected a message from GUID:F36C6053-12D7-404C-AAF6-E406F84DAF50 because the signature could not be validated. If this is a valid client,  it will attempt to re-register automatically so its signature can be correctly validated.

When looking at the CM database, specifically at the view, v_R_System, one would notice that there are two columns which have names explicitly containing the label “GUID” – Object_GUID0 and SMBIOS_GUID0, neither of which match up to the GUID referred to in the error message shown above.  Looking at the properties of these two columns, we find that Object_GUID0 is related to the Active Directory object of the resource and SMBIOS_GUID0 is the BIOS GUID of the resource.

So, where can one find the information needed to track down the resource to which the above error message is referring?  The answer is the v_R_System column named SMS_Unique_Indentifier0 – intuitive, isn’t it?  Reviewing the properties of this column, we find that this refers to the Unique ID of the resource, which is exactly what the error is returning, the source identifier.  I’ve included a couple methods for identifying the resource below.

SQL Method:  (Query against the CM database)

SELECT
Name0,
SMS_Unique_Identifier0

FROM
v_R_System

WHERE
SMS_Unique_Identifier0 = ‘GUID:F36C6053-12D7-404C-AAF6-E406F84DAF50’

PowerShell Method:
Enter your data where the [] are, but omit the brackets.

$params = @{Namespace=”ROOT\SMS\site_[YOURSITECODE]”;
Class=”SMS_R_System”;
ComputerName=”[YOURSITESERVER]”;
Filter=’SMSUniqueIdentifier = “GUID:F36C6053-12D7-404C-AAF6-E406F84DAF50″‘}
Get-WmiObject @params | ForEach-Object {
$Name = $_.Name
$Name = ” Computer Name:  $Name”
$ResourceID = $_.ResourceID
$ResourceID = ”   Resource ID:  $ResourceID”
$ClientVersion = $_.ClientVersion
$ClientVersion = “Client Version:  $ClientVersion”
Return $Name, $ResourceID, $ClientVersion
}

SCCM 2007 – End of Life

Just in case some of you are still running SCCM 2007 in your environment and have not been keeping tabs on this, mainstream support of this product for all versions ends tomorrow, 7/8/2014.

SCCM 2007 Support Schedule

Hopefully you have developed a migration strategy and are moving to CM 2012 R2 in the near future.

For more information, see Microsoft’s lifecycle report for SCCM 2007 here:

Microsoft Lifecycle Report for SCCM 2007