Monthly Archives: November 2013

SQL Query: Find user’s primary device by deparment AD attribute.

We are getting ready to rollout a Windows 7 desktop deployment in order to “clean up” the software license reports.  Part of this project entails creating static collections for the various departments within the company which will have an OSD task sequence deployed to.  In order to take advantage of User Device Affinity in SCCM 2012, I requested an SAP report of all users within the affected departments.  The report I received contained fields within Active Directory such as the “Department” attribute.  I then created the following SQL query to retrieve all users within a deparment and display their primary device.

Use [CM_BSG]
select upm.UserResourceID, upm.MachineID, vru.Name0 as [User Name],
vrs.Name0 as [Machine Name], vru.department0 as [Department]
from v_UsersPrimaryMachines  upm left join v_R_User AS vru
on upm.UserResourceID = vru.ResourceID left join v_R_System vrs
on upm.MachineID = vrs.ResourceID where vru.department0 like '[VALUE OF AD DEPARTMENT FIELD HERE]'

If you are looking for User Device Affinity for a specific username, you can easily modify the query as the following:

Use [CM_BSG]
select upm.UserResourceID, upm.MachineID, vru.Name0 as [User Name],
vrs.Name0 as [Machine Name], vru.department0 as [Department]
from v_UsersPrimaryMachines  upm left join v_R_User AS vru
on upm.UserResourceID = vru.ResourceID left join v_R_System vrs
on upm.MachineID = vrs.ResourceID where vru.name0 like '%[USERNAME HERE]%'

Or all devices for all users in the database:

Use [CM_BSG]
select upm.UserResourceID, upm.MachineID, vru.Name0 as [User Name], 
vrs.Name0 as [Machine Name], vru.department0 as [Department] 
from v_UsersPrimaryMachines upm left join v_R_User AS vru 
on upm.UserResourceID = vru.ResourceID left join v_R_System vrs 
on upm.MachineID = vrs.ResourceID where vru.name0 IS NOT NULL

Hydration Kit for ConfigMgr 2012 R2 is available for download!

Fellow Microsoft MVP, Johan Arwidmark, has released his “Hydration Kit” for the new SCCM 2012 R2.  What in the world is a “Hydration Kit” you ask?  Simple!  It is a series of scripts in Powershell, XML, and VBScript which will build out a complete SCCM 2012 R2 infrastructure, running on Windows Server 2012 R2 and SQL Server 2012 SP1, in either Hyper-V or VMware utilizing MDT 2013.

I typically use this to deploy a quick lab environment but you can also use this in production too.  Please refer to this post and follow the step-by-step procedure.

SQL query to obtain list of SCCM SQL views for report building.

I found this post I thought would be great to share from my fellow colleague’s blog, Eswar Koneti.  It details the SQL query needed in order to obtain a list of SQL views included in the SCCM 2012 database for report building purposes:

Select distinct table_name,COLUMN_NAME From Information_Schema.columns where table_name LIKE ('v_%') Order by table_name

UPDATE:  Here is his post for the SCCM 2012 R2 SQL Views!  A direct link to the spreadsheet is also included in my links sidebar.

Create a WinPE 4.0 USB Flash Drive or ISO using Windows ADK

Now that the Windows ADK for Windows 8 (WADK) superseded the old Windows AIK, the procedure to create a Windows PE image has slightly changed.  In my opinion, it has greatly improved as Microsoft has now included a simple command-line utility that is capable of creating both ISO and USB flash drives.  The name of this utility is MakeWinPEMedia.cmd, and once the WADK is installed, you can find this utility here:

C:\Program Files (x86)\Windows Kits\8.0\Assessment and Deployment Kit\Windows Preinstallation Environment\MakeWinPEMedia.cmd

OK, so how do you create a WinPE 4.0 image?  Let’s read ahead!

  1. You will need to download and install Windows ADK for Windows 8.  The installation is as simple as running the adksetup.exe executable and making sure to check off the Windows Pre-installation Environment option.
    11-2-2013 8-34-35 PM
  2. Once installed, click on Start, All Programs, Windows Kits, Windows ADK, Deployment and Imaging Tools Environment.
  3. Type the following commands depending on what hardware architecture you need:
    WinPE x86
    copype.cmd x86 C:\WinPE_x86
    WinPE x64
    copype.cmd amd64 C:\WinPE_x64
  4. Once the files are copied, you can optionally mount the boot.wim.  You would want to do this in order to integrate mass storage, USB and ethernet hardware drivers and/or scripts.
    ImageX.exe /Mountrw C:\WinPE_x86\media\sources\boot.wim 1 C:\WinPE_x86\mount

    For hardware drivers integration, I like to use DISM GUI, a graphical user interface to the excellent command-line utility, DISM.EXE.  Though it has no documentation yet, the interface is pretty intuitive.  In a nutshell, you choose the WIM file and mount location, then go to the Driver Management tab and choose a directory of drivers to integrate.

    For utility and/ or scripts, I always add GImageX, which is a graphical user interface for IMAGEX.EXE command-line utility.  When adding this utility, the original Microsoft ImageX.exe executable is not needed, only the GImageX.exe executable is required.  Also, please make sure to add this to the “C:\WinPE_x86\mount\Windows\System32” directory.

  5. Once your changes to the WIM are complete, you will need to dismount and commit all changes:
    ImageX.exe /Unmount /Commit C:\WinPE_x86\mount
  6. Now we can build out our WinPE image depending on our target, USB or ISO image:
    ISO Image
    MakeWinPEMedia.cmd /ISO C:\WinPE_x86 C:\WinPE_x86\WinPE_x86.ISO
    USB Flash Drive
    MakeWinPEMedia.cmd /UFD /F C:\WinPE_x86 G:

I hope you have enjoyed this tutorial as much as I have enjoyed making it for you.  If you have any questions, please comment.  Thank you in advance.

Extensive List of SCCM Log Files!

I’m not going to reinvent the wheel here, especially since there’s an extensive post by Liuxiang Chen detailing all the log files in SCCM.

I will say this however, to make it easier to read these files use CMTRACE.EXE in SCCM 2012 or SMSTRACE.EXE in SCCM 2007.

CMTRACE.EXE has been included in the latest ConfigMgr 2012 Toolkit SP1 or on the SCCM server at the following share:


Capturing task sequence log files during OSD deployment.

This idea grew out of the need to troubleshoot certain task sequence errors.  Usually when your task sequence throws an error, the code will be displayed for an X amount of time after which the machine reboots.  Retrieving the SMSTS.LOG file is somewhat cumbersome, as the exact location of the file varies depending on which phase of the OSD the machine is in:

WinPE, Before HD Format x:\windows\temp\smstslog\smsts.log
WinPE, After HD Format x:\smstslog\smsts.log
Windows, No SCCM Client Installed c:\_SMSTaskSequence\Logs\Smstslog\smsts.log
Windows x86, SCCM Client Installed c:\windows\system32\ccm\logs\Smstslog\smsts.log
Windows x64, SCCM Client Installed c:\windows\sysWOW64\ccm\logs\Smstslog\smsts.log
Task Sequence Completed x86 c:\windows\system32\ccm\logs\smsts.log
Task Sequence Completed x64 c:\windows\sysWOW64\ccm\logs\smsts.log

An easier way to gather these logs is to take advantage of a Task Sequence Variable within the OSD deployment.  Whenever the task sequence finishes either successfully or not, the TS variable “_SMSTSLastActionSucceeded” returns a true or false value.  We can then leverage this variable to create a step in the task sequence to run only if the return value is false and then further run some steps to map a network drive and copy the logs over.  I was lucky to find an old post by Steve Rachui, a Microsoft Premier Field Engineer which helped saved me time brainstorming a solution.  However, my implementation makes use of the %OSDComputerName% variable and displays a custom error message in order to prevent WinPE from rebooting after clearing the countdown.

Below are the steps I took:

  1. Created a network share and allowed “Everyone” full access permission.  You can restrict this share to a specified AD account if you wish but you will then need to specify the account in the steps below.  I named my network share “TSLogCapture”.
  2. In the root of your task sequence, create a new group named “Task Sequence” and move all the steps as a subfolder to this new group.  In the Options tab, make sure the “Continue on error” box is checked.  The idea is to have all the steps grouped and if there is an error, pass control to the next group which will copy the logs to a share.10-31-2013 3-25-10 PM
  3. Also in the root of your task sequence, create another group named “Log Capture”.  In the Options tab add a condition to run when the task sequence variable “_SMSTSLastActionSucceeded” equals “False”.11-1-2013 8-25-52 PM
  4. We will need to add the above displayed steps to the “Log Capture” folder.  To add the first step, go to Add, General, Connect to Network Folder.11-1-2013 8-31-56 PM 

    In the Properties tab, you will need to specify a Path, Drive Letter and Account to connect and map the network share.  If you restricted the network share to a specific AD account, input that account here.  Also, please note I chose the “Z:” drive letter, therefore, all my command line entries reflect this drive letter.  If you choose a different drive letter, please update any subsequent command lines with this new drive letter.11-1-2013 8-35-04 PM

  5. Now we will need to add “Run Command Line” steps to the “Log Capture” folder.  To add this step, go to Add, General, Run Command Line.  This step will remove any pre-existing folders for that machine name you are currently using from the network share.  Its purpose is to prevent duplicate log files.11-1-2013 8-43-32 PM 

    In the Properties tab, type the following:11-1-2013 8-45-53 PM

    cmd.exe /c rd /s /q z:\%OSDComputerName%
  6. Add another “Run Command Line” step.  This step will create a folder named after the computer name in the TS variable %OSDComputerName% (a friendly name of your choosing).  You can also use %_SMSTSMachineName% which will name the folder after the random “MiniNT-12345” name if the error occurs in WinPE or after the actual AD computer name if the error happens in Windows.11-1-2013 9-00-26 PM
    cmd.exe /c md z:\%OSDComputerName%
  7. Add another “Run Command Line” step.  This step will copy the log files from the path contained within the %_SMSTSLogPath% variable to machine named folder in the network share.11-1-2013 9-03-58 PM
    cmd.exe /c copy %_SMSTSLogPath%\*.* z:\%OSDComputerName%


This last step is entirely optional.  I use it to display a custom error message using VBScript in order to prevent the error countdown from starting and rebooting the machine.  To add this step, you will need to create a Package out of one file, ErrorPrompt.vbs.  To create this file, open Notepad.exe and copy/ paste the following and update the path to your network share:

WScript.Echo "There was an error in the task sequence." & VbCrLf & VbCrLf & "Please review the log files stored at:  [Your Network Share Path Here]"

Next, save the VBScript file in a folder in your SCCM package repository and create a package using the SCCM Administration Console:

11-1-2013 9-24-09 PM

11-1-2013 9-27-54 PM

11-1-2013 9-34-38 PM

Click on “Next” all the way until the package is created.  Once complete, you will need to distribute it to your distribution points or groups.

11-1-2013 9-38-28 PM

11-1-2013 9-39-38 PM

10-31-2013 4-11-36 PM

10-31-2013 4-12-13 PM

Once again, click “Next” until you finish distributing the package.  You will need to watch the “Content Status” pane in the package information section to verify that the package was distributed successfully.

10-31-2013 4-12-56 PM 10-31-2013 4-13-49 PM

Now that you have created your package and have it distributed to your distribution points.  You may proceed creating that last “Run Command Line” step in your task sequence.  In the Properties tab, insert the following command line and don’t forget to specify the package we created:

11-1-2013 9-08-06 PM

cmd.exe /c wscript.exe errorprompt.vbs

Phew!  We are finally done!  Give yourself a good pat on the back because from now on, whenever your task sequence encounters an error, you will see the following:

11-1-2013 10-01-52 PM