Configure-DedupeForMe – SCCM

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

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

Implement HWI extension in production

HWI Definitions

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

  • Import definitions.

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

Review the classes and click on Import.

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

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 are 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 to be 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

The Error Message That Wasted Part Of My Day – or – I Am Not A Smart Man…

, , , , , , ,

I’ve run into an issue that I couldn’t find documented anywhere, so I am hoping this post can help someone else in the future.

I recently stood up a new environment for a school district and it’s running ConfigMgr 1902 with ADK 1903. I prefer to have a custom boot image that is separate from the default boot images that are created with setup, because I don’t want to risk breaking a boot image during an upgrade of ConfigMgr/ADK in the future. So, I open the Deployment and Imaging Tools Environment cmd prompt and run:

copype.cmd amd64 c:\bootimagex64

I take the boot.wim file from c:\bootimagex64\media\sources and place it in my site server sources folder for import. I go to Software Library -> Operating Systems -> Boot Images, select Add Boot Image, type out the UNC path to my site server sources folder and the new boot.wim file. Easy Peasy … but then I’m presented with the following error message:

The specified UNC path does not contain a valid boot image file or you do not have permission to access it. Specify a valid path.

The specified UNC path does not contain a valid boot image file or you do not have permission to access it. Specify a valid path.

The path IS valid! The Add Boot Image Wizard even completes my UNC path as I type it …

y u no like my path?!

Yet it still gives me that error!

liar!

Welp – Time to troubleshoot.

typey, typey, type

First thing I checked is permissions. I verified that the site server computer account had full permissions for NTFS on that drive and within sharing for that particular share. I also verified that the service account had full permissions to NTFS and sharing, just in case. I even checked the permissions on the boot.wim file thinking maybe they didn’t inherit properly. None of that seemed to matter.

Next thing I checked is if the boot.wim is a valid boot image. I started up psexec in the context of system so I could run dism to verify that I could mount the boot.wim with:

psexec -i -s cmd

and then:

dism /mount-image /imagefile:”c:\bootimagex64\media\sources\boot.wim” /index:1 /mountdir:”C:\bootimagex64mount”

Then I go browse over to c:\bootimagex64mount and I can see the boot image successfully mounted so I know I can unmount it with:

dism /unmount-image /mountdir:”c:\bootimagex64mount” /discard

I was still unable to add this boot image to ConfigMgr.

So, I did what any self-respecting sysadmin would do and I went grovelling to my community for assistance. I tried the winadmins slack first, but the ideas presented didn’t get me anywhere.

[If you aren’t in the winadmins slack yet, then head on over and join us]

  • I tried using an alternate boot.wim, like the ones found at <ConfigMgrInstallDirectory>\OSD\boot\x64, but was met with the same error message as before.
  • I really didn’t want to rollback the ADK, even just for testing purposes. I was willing to try it, as a last recourse, since the support matrix shows it is supported, but I’m stubborn.
Windows 10 ADK & ConfigMgr Support Matrix

https://docs.microsoft.com/en-us/sccm/core/plan-design/configs/support-for-windows-10#windows-10-adk

Next I went to reddit and posted on /r/sccm to see if I could get any help there.

reddit post

https://www.reddit.com/r/SCCM/comments/c1qdiq/issues_importing_boot_image/

I was fully prepared for Jason Sandys to jump in and comment as he seems to be on the most popular Google results for this error message and he’s present on technet and reddit comments that I was looking at during my research, but I think I got an even better response…

I love you /u/vsoro00

https://www.reddit.com/r/SCCM/comments/c1qdiq/issues_importing_boot_image/erf7apl

I blame Jeffrey Snover for making me not want to click buttons, but really … I’m not a smart man. I cannot believe I never thought to browse to the damn wim file.

Windows 10 ADK & ConfigMgr Support Matrix

https://twitter.com/jsnover/status/386104326000627713

I came into the office today and decided to listen to /u/vsoro00 and click that browse button:

Windows 10 ADK & ConfigMgr Support Matrixand wouldn’t you know it … it worked!

I still don’t understand how this is functionally different from my typing it … but hey!

SUCCESS!

Woot! Now let’s make sure it will fully import…

There we have it folks … a new boot image added successfully!

I want to say a heartfelt thank you to /u/vsoro00, who I assume is Vladimir Sorokin over on the ConfigMgr team at Microsoft, for taking time to answer my post in reddit with exactly what I needed to hear. I don’t understand the technical limitations they are working with or what he means about how expensive the process can be to validate everything I type in, but if this is truly an issue for others than I look forward to them removing the typing functionality altogether! I love this community!

Chris Thomas
@AutomateMyStuff