display top 6 recent blogpost

Posts

Configure-DedupeForMe – SCCM

, , , , , , , , , , , ,

SQL Server Product and Version Reporting with SCCM

, , , , , , , , , ,

SQL Server Product and Version Reporting with SCCM

In this artcile, we will cover SQL Server Product and Version Reporting with SCCM. SQL licensing is always a pain but this report should make it a little easier…

Report release history

Previous report version

Also published on my blog

This is the second iteration of my SQL version report. When I look back on my previous work I always cringe and this was no exception. A while back, I received a request to add the SQL key to the report. So I began examining the old code. Horrified by the things that I found laying dormant there, I scrapped everything and started anew.

The report is brand new with a lot more info, smaller database footprint, and much better coding.

Recommendations

  • Do not modify or revemove the previous version until you verify that this version has all the data you need!
  • Use a test environment for validation!
  • Back-up your configuration.mof file before any changes!
  • Test the configuration.mof using mofcomp.exe on a test machine first!

Notes
This version is compatible with the previous version, they can live side by side.

Hardware inventory extension needs to be done on the top of your hierarchy.

Installation

Prerequisites

  • Test environment
  • Downloads (Right click →Download linked file)
# HWI Extension
HWI EXT SQL Server Products.mof
# HWI Definitions
HWI DEF SQL Server Products.mof
# SSRS Report
SW SQL Server Products.rdl
  • SQL Stored Procedure →‘Create the SQL Stored Procedure’ section.

HWI Extension

The extension needs to be added to the configuration.mof file in \Inboxes\clifiles.src\hinv\

  • Look for the section below at the end of the configuration.mof file. The extension needs to be added between the “Added extensions start/end” headers.
  • Uncomment the “Old SQL extension cleanup” section to remove the old extension classes from the clients repository if needed.
  • Use a test environment for validation as described in the ‘Test and Validation’ header after the Installation section.
//========================
// Added extensions start 
//========================
//========================
// Added extensions end 
//========================

Notes
Always use a test environment before any changes in production!

Never create any extensions outside of the “Added extensions start/end” headers.
Try to have consistent formatting inside these headers.
Never modify anything outside these headers.
Watch for other previous extensions. And use clear delimitation between them.

  • Apply changes in production
## Compile file on the CAS/PSS
/* 
Compiling the configuration.mof file in the hinv folder on the CAS/PSS, will trigger the distribution and compilation on all machines in your environment on the next machine policy evaluation.
*/
mofcomp.exe \Inboxes\clifiles.src\hinv\Configuration.mof
SQL Server Product and Version Reporting with SCCM

Implement HWI extension in production

HWI Definitions

You need to add the new class definitions to the Default Client Settings

  • Import definitions.
SQL Server Product and Version Reporting with SCCM

Click on Import and select the HWI DEF SQL Server Products.mof file

SQL Server Product and Version Reporting with SCCM

Review the classes and click on Import.

SQL Server Product and Version Reporting with SCCM

Make sure the new extension classes are enabled and click OK.

Notes
DO NOT DELETE the old extension definitions if you still want to use the old report!

Test and Validation

Configuration.mof

Use mofcomp.exe to check. If configuration.mof was correctly modified, and implement the changes.

## Check syntax
mofcomp.exe -check \Configuration.mof
## Compile file 
/* 
Compiling the configuration.mof file in the hinv folder on the CAS/PSS. Will trigger the distribution and compilation on all machines in your environment on the next machine policy evaluation.
*/
mofcomp.exe \Configuration.mof
SQL Server Product and Version Reporting with SCCM

Compling the configuration.mof is done on a test environment here!

Notes
Saving and compiling the configuration.mof file in the hinv folder on the CAS/PSS. Will trigger the distribution and compilation on all machines in your environment on the next machine policy evaluation.

WMI

Use PowerShell to check if the new classes have been created in WMI

## Check if the new classes are present in WMI
/* The machine must have at least one version of SQL installed in order for these classes to be created */
#  Get SQL 2017 class
Get-CimClass -ClassName SQL_2017_Property
#  Get SQL 2014 class
Get-CimClass -ClassName SQL_2014_Property
#  Get SQL 2012 class
Get-CimClass -ClassName SQL_2012_Property
#  Get SQL 2008 class
Get-CimClass -ClassName SQL_2008_Property
#  Get SQL Legacy class
Get-CimClass -ClassName SQL_Legacy_Property
#  Get SQL ProductID class
Get-CimClass -ClassName SQL_ProductID

Database

Use SSMS (SQL Server Management Studio) to check if the views created in the CM database

Import the SSRS Report

Upload Report to SSRS

  • Start Internet Explorer and navigate to http:///Reports
  • Choose a path and upload the previously downloaded report file.

Configure Imported Report

Create the SQL Stored Procedure

The usp_PivotWithDynamicColumns is needed in order to maximize code reuse and have a more sane and sanitized data source.

  • Copy-paste the code below in SSMS
  • Change the in the USE statement to match your Site Code.
  • Click Execute to add the usp_PivotWithDynamicColumns stored procedure to your database.

Notes
You might need additional DB access to install the support function!
Allow some time for the policy to be downloaded or force a policy refresh.

Allow some time for the data gathered or force a HWI collection.
This report was created with SQL 2017 Reporting Services. You might need to remove some report elements. If you use an older version.

Preview

Report preview

Code

HWI Extension

For reference only, you can download the file in the ‘Prerequisites’section.

HWI Definitions

For reference only, you can download the file in the ‘Prerequisites’section.

SQL Query

For reference only, the report includes this query.

VB Support Function

For reference only, the report includes this function.

Notes
Credit to Jakob Bindslet and Chrissy LeMaire.


Use Github for 🐛 reporting, or 🌈 and🦄 requests

Troubleshooting and Upgrading AD FS Farms

, ,

Troubleshooting and Upgrading an AD FS farms is usually a straightforward and easy task. There are many blogs detailing the process from Server 2012R2 to Server 2016/2019. Here are the general steps for upgrading a farm.

  1. Setup up a new server and install the AD FS role.
  2. Add the server to the existing farm.
  3. Set the new server as the primary server.
  4. Point the other servers to the new primary server.
  5. Install additional servers to the AD FS farm.
  6. Uninstall AD FS on the old servers to remove them from the farm.

What are your options when this process doesn’t work? In my case, I could not add a new server to the farm. PowerShell and the GUI both returned errors when attempting to add a new Windows Server 2019 to the farm. I started looking at troubleshooting options and eventually decided to proceed with an attempt to in-place upgrade the Server 2012R2 farm to Server 2019.

Troubleshooting

Microsoft provides some powerful tools for troubleshooting AD FS issues. AD FS Help provides several troubleshooting guides and diagnostic tools that can help resolve issues with your AD FS farm. The tools are located here: https://adfshelp.microsoft.com/.

The AD FS Diagnostic Analyzer tool can provide a health check for you AD FS farm. To use the AD FS Diagnostic Analyzer, you need to install the AD FS Toolbox PowerShell Module.

Install-Module -Name ADFSToolbox -Force

Import-Module ADFSToolbox -Force

Once the AD FS Toolbox is installed, run the Export-AdfsDiagnosticsFile command which will generate a JSON file for upload. The command will run against the local AD FS server unless the farm is Windows Server 2016 or higher. You can also list the servers with the -adfsServers parameter. The -adfsServers parameter is required for 2012R2 farms. Upload the JSON file to the https://adfshelp.microsoft.com/ site and the site will display the Health Test Results. The site will detail any problems and offer step by step guides or links to documentation to remediate the issues.

Backup and Restore

There is also the AD FS Rapid Restore Tool found here: https://docs.microsoft.com/en-us/windows-server/identity/ad-fs/operations/ad-fs-rapid-restore-tool. The tool backs up the following AD FS configuration data:

  • AD FS database
  • Configuration file
  • Automatically generated token signing and decrypting certificates and private keys
  • SSL certificate and any externally enrolled certificates and corresponding private keys
  • The custom authentication providers, attribute stores, and local claims provider trusts that are installed

Unfortunately, the Restore Tool only supports a restore to the same version as the backup. This means that you cannot use this method to restore AD FS to a newer version of AD FS. You could restore the AD FS farm to a new set of servers and attempt to add upgraded servers to the farm as outlined above.

Other Tools

The https://adfshelp.microsoft.com/ site provides a number of other tools and support options. There is a full list of AD FS event items for 2012R2/2016/2019 with ID, Name, and Description. Claims X-Ray assists with debugging claims issues in your applications. The AD FS Event Module provides tools to gather and review the events from multiple servers. There are several more tools available as well.

In-Place Upgrade

Even after running the diagnostic tools several times and making the recommended changes, I still was unable to add a new server to the existing farm. The other option is to attempt an in-place upgrade of the servers in the AD FS farm. Technically, this is not supported as upgrading Windows Server with AD FS installed will uninstall the AD FS role. Prior to attempting this method, I made a snapshot (Hyper-V virtual Machines) of the AD FS servers and a backup of the AD FS farm’s current state. I started by upgrading the secondary AD FS server first. If I was unable to add the server back into the farm and promote it to the primary server, then my plan was to use the AD FS Restore Tool and rebuild the farm. These are the steps I took for the in-place upgrade.

  1. Upgrade the secondary AD FS server to Server 2019.
  2. Install the AD FS role.
  3. Add the upgraded server back into the farm.
  4. Set the server as the primary AD FS server in the farm.
  5. Verified that AD FS was still working for our services.
  6. Upgrade the former primary server, reinstall the AD FS role and set it as the primary server.

I also did an in-place upgrade of the Web Application Proxy server and added it to the farm with the Install-WebApplicationProxy cmdlet. The final step is raising the farm functional level with the Invoke-AdfsFarmBehaviorLevelRaise cmdlet. This will enable the new features to Verify the update completed with the Get-AdfsProperties | Select CurrentFarmBehavior cmdlet.

While it is not a supported option, the in-place upgrade of my AD FS farm worked perfectly. Hopefully, it is not an option you will need.

read more Troubleshooting and Upgrading AD FS Farms