SCCM Query for Empty Collections

I just came across the need to query SCCM for collections that are empty as digging through queries in the console I could not find a suitable solution. Luck would have it that a quick Google search away was the following:

http://smsug.ca/blogs/garth_jones/archive/2010/07/01/find-the-collections-which-does-nt-have-members.aspx

Here’s the actual query statement
select
C.Collectionid,
C.Name
from
dbo.v_Collection C
Where
C.Collectionid not in (Select Collectionid from dbo.v_FullCollectionMembership)

Pretty basic however if your even remotely familiar with SQL you can add additional critera to weed out collections you know will be empty and only look at the ones you truly care about.

Example: In our SCCM we have multiple layers of collections with the parent collections being empty (with no “update this collection” schedule of course!)
Lets pick on Adobe. For Acrobat Reader we have a parent collection called Adobe, a sub collection called Adobe_AcrobatReader and the finally a 3rd level collection called Adobe_AcrobatReader_9.3_R01-Install.
The query above returns all 3 collections when really all I care about is the 3rd level collection. The other two collections are empty by design.

Quite simply I add another criteria to my query to only pull back the “Install” collections like so:

select
C.Collectionid,
C.Name
from
dbo.v_Collection C
Where
C.Collectionid not in (Select Collectionid from dbo.v_FullCollectionMembership) and c.name like '%-Install'

Easy peasy lemon squeezy.

SCCM: Migrating to a new source code server

I have run across this several time in the past where the source code location for your packages (and now drivers as well) needs to be moved to a new server. I have 3 scripts I use (Could be combined into one if you like) that will allow you to change the location for each object.

Script 1: remapPkgs.vbs
This script will modify the source path for all (non Driver related) packages.

Script 2: remapDrivers.vbs
This script will remap all the source locations for the drivers that have been imported.

Script 3: remapDriverPkgs.vbs
This script will remap all the source locations for your driver packages.

VERY IMPORTANT – When you run the remapPkgs.vbs and the remapDriverPkgs.vbs it will trigger a Distribution Point update for all your packages. You will likely want to run this after hours or over the weekend depending on how many packages you have and what your bandwidth is to your DP’s.

*UPDATE* – Couple of things I noticed in my environment after running these scripts.
1. We use Nomad Branch from 1E. This adds a tab to your packages where you specify if you want to enable Nomad. After running the scripts I had to go back and reset this on all my packages.
2. Much more importantly we also discovered that these scripts re-enabled the setting for all of our App-V packages to “remove when no longer advertised”. Under normal day to day operations this setting is desired for us however as part of our re-assigning the clients to a new site code all local policy is deleted and re-downloaded from the new server. Well with that option still enabled it would remove all App-V apps from all machines as the policy would get deleted and it would not come back automatically. This is why we removed that option. Long story short we had to go back and remove the option again from all App-V packages after migrating the source code.

Another important note regarding App-V packages:
Although Microsoft does not give you the option to modify the source directory of your Virtual Applications via the Admin Console the script above will remap your App-V packages as well. In my environment however I was required to touch each Virtual App after the script and “re-import” the package if you will. This was achieved by clicking the Properties of the Virtual App, Click Data Source, then click “Set”. In the “Virtual Application package source directory” click “Browse” and specify the original location of the xml file used to create the package. I found that in most circumstances I needed to delete the contents of the “ConfigMgr data source directory” prior to browsing to the xml file. Once you import the package again the contents of the source directory get re-created and the package resent to the DP’s.

The syntax for the scripts are included in the script header however its pretty simple (must be run from the SCCM server itself):

cscript scriptname.vbs NEWSERVERNAME

So example: My current source path that I want to change is:
\\SERVER01\SOURCE$\SoftwareInstalls\Adobe_AcrobatReader_9.0_R01

I would run the script like so:
cscript remapPkgs.vbs SERVER02

This will change the source directory to:
\\SERVER02\SOURCE$\SoftwareInstalls\Adobe_AcrobatReader_9.0_R01
(And trigger an update to the DP)

Again these scripts are setup to do all packages in one fell swoop and designed to only need to replace the server name. You could easily modify the scripts to change the share name, etc as needed.

I would also recommend commenting out the “put_” statement and run the script to verify it works prior to actually making the changes.
_________________________________________________
Script 1 (Mind word wrapping)
'///////////////////////////////////////////////////
'
' SYNTAX: cscript remapPkgs.vbs NEWSERVERNAME
'
'///////////////////////////////////////////////////////////
Dim oWbemServices
If UCase(Right(Wscript.FullName, 11)) = "WSCRIPT.EXE" Then
Wscript.Echo "This script must be run under CScript."
Wscript.Quit
End If
If WScript.Arguments.Count 1 Then
WScript.Echo "Wrong number of arguments were passed."
WScript.Quit
End If
'====================================================
'== Execute SUB to connect to SCCM Provider
'====================================================
CONNSCCM()
For Each oPackage In oWbemServices.execquery("select * from sms_package")
sOldPathString = oPackage.pkgsourcepath
If InStr(sOldPathString,"\\") Then
iStart = InStr(3,sOldPathString,"\")
sNewPathString = Right(sOldPathString,(LEN(sOldPathString)-iStart))
sNewPathString = "\\" & WScript.Arguments(0) & "\" & sNewPathString
WScript.Echo "Setting Package Path for " & oPackage.Name & " from " & sOldPathString & " to " & sNewPathString
oPackage.pkgsourcepath = sNewPathString
oPackage.put_
End If
Next
WScript.Echo "Done"
'====================================================
' SUB: Connects to the SMS Provider
'====================================================
Sub CONNSCCM()
Set oWbemLocator = CreateObject("WbemScripting.SWbemLocator")
Set oWbemServices = oWbemLocator.ConnectServer(".", "root\sms")
Set oSCCMProvLoc = oWbemServices.InstancesOf("SMS_ProviderLocation")
For Each oLoc In oSCCMProvLoc
If oLoc.ProviderForLocalSite = True Then
Set oWbemServices = oWbemLocator.ConnectServer(oLoc.Machine, "root\sms\site_" + oLoc.SiteCode)
End If
Next
End Sub

Script 2 (Mind word wrapping)
'///////////////////////////////////////////////////
'
' SYNTAX: cscript remapDrivers.vbs NEWSERVERNAME
'
'///////////////////////////////////////////////////////////
Dim oWbemServices
If UCase(Right(Wscript.FullName, 11)) = "WSCRIPT.EXE" Then
Wscript.Echo "This script must be run under CScript."
Wscript.Quit
End If
If WScript.Arguments.Count 1 Then
WScript.Echo "Wrong number of arguments were passed."
WScript.Quit
End If
'====================================================
'== Execute SUB to connect to SCCM Provider
'====================================================
CONNSCCM()
For Each oPackage In oWbemServices.execquery("select * from sms_Driver")
sOldPathString = oPackage.contentsourcepath
If InStr(sOldPathString,"Latitude 13") Then
iStart = InStr(3,sOldPathString,"\")
sNewPathString = Right(sOldPathString,(LEN(sOldPathString)-iStart))
sNewPathString = "\\" & WScript.Arguments(0) & "\" & sNewPathString
WScript.Echo "Setting Package Path for " & oPackage.LocalizedDisplayName & " from " & sOldPathString & " to " & sNewPathString
'WScript.Echo sOldPathString
oPackage.contentsourcepath = sNewPathString
oPackage.put_
End If
Next
WScript.Echo "Done"
'====================================================
' SUB: Connects to the SMS Provider
'====================================================
Sub CONNSCCM()
Set oWbemLocator = CreateObject("WbemScripting.SWbemLocator")
Set oWbemServices = oWbemLocator.ConnectServer(".", "root\sms")
Set oSCCMProvLoc = oWbemServices.InstancesOf("SMS_ProviderLocation")
For Each oLoc In oSCCMProvLoc
If oLoc.ProviderForLocalSite = True Then
Set oWbemServices = oWbemLocator.ConnectServer(oLoc.Machine, "root\sms\site_" + oLoc.SiteCode)
End If
Next
End Sub


Script 3 (Mind word wrapping)
'///////////////////////////////////////////////////
'
' SYNTAX: cscript remapDriverPkgs.vbs NEWSERVERNAME
'
'///////////////////////////////////////////////////////////
Dim oWbemServices
If UCase(Right(Wscript.FullName, 11)) = "WSCRIPT.EXE" Then
Wscript.Echo "This script must be run under CScript."
Wscript.Quit
End If
If WScript.Arguments.Count 1 Then
WScript.Echo "Wrong number of arguments were passed."
WScript.Quit
End If
'====================================================
'== Execute SUB to connect to SCCM Provider
'====================================================
CONNSCCM()
For Each oPackage In oWbemServices.execquery("select * from sms_DriverPackage")
sOldPathString = oPackage.PkgSourcePath
If InStr(sOldPathString,"\\") Then
iStart = InStr(3,sOldPathString,"\")
sNewPathString = Right(sOldPathString,(LEN(sOldPathString)-iStart))
sNewPathString = "\\" & WScript.Arguments(0) & "\" & sNewPathString
WScript.Echo "Setting Package Path for " & oPackage.Name & " from " & sOldPathString & " to " & sNewPathString
'WScript.Echo sOldPathString
oPackage.PkgSourcePath = sNewPathString
oPackage.put_
End If
Next
WScript.Echo "Done"
'====================================================
' SUB: Connects to the SMS Provider
'====================================================
Sub CONNSCCM()
Set oWbemLocator = CreateObject("WbemScripting.SWbemLocator")
Set oWbemServices = oWbemLocator.ConnectServer(".", "root\sms")
Set oSCCMProvLoc = oWbemServices.InstancesOf("SMS_ProviderLocation")
For Each oLoc In oSCCMProvLoc
If oLoc.ProviderForLocalSite = True Then
Set oWbemServices = oWbemLocator.ConnectServer(oLoc.Machine, "root\sms\site_" + oLoc.SiteCode)
End If
Next
End Sub


VBScript: Run Optional SCCM Advertisements

Here’s a fairly simple script that will check for and execute all optional advertisments on a client machine. This could be modified to include checks for adverts that have already ran and not rerun or put a GUI around it to choose which adverts to run selectively.
If the code has not been downloaded to the client yet a content retrieval will be made automatically prior to running.

CAREFUL OF WORD WRAPPING

'==========================================================================
'
' VBScript Source File -- Created with SAPIEN Technologies PrimalScript 2009
'
' NAME: RunOptionalAdvertisements.vbs
'
' AUTHOR: William Bracken
' DATE : 10/12/2009
'
' COMMENT: Run optional advertisments
'
'==========================================================================
Dim oUIResource, programArray(), fs, f
Dim oPrograms, x, i, strLogFile, strFileName, UserTempPath, WindowsTempPath
Dim oProgram, programId, packageId, myProgram, WinTempLog, UsrTempLog
Const ForReading = 1, TemporaryFolder = 2, ForAppending = 8
Set fs = CreateObject("Scripting.FileSystemObject")
Set WSHShell = CreateObject("WScript.Shell")
windir = WSHShell.ExpandEnvironmentStrings("%WINDIR%")
UserTempPath = WSHShell.ExpandEnvironmentStrings("%TEMP%")
WindowsTempPath = windir & "\temp"
strFileName = "RunOptionalAdverts.log"
WinTempLog = WindowsTempPath & "\" & strFileName
UsrTempLog = UserTempPath & "\"& strFileName
strLogFile = UsrTempLog

CLEANLOG strLogFile
COLLECTMSG "Begin Logging", ""

' Validate connection to SCCM Client
Set oUIResource = CreateObject ("UIResource.UIResourceMgr")
If oUIResource Is Nothing Then
COLLECTMSG "CONNECT TO SCCM CLIENT", "Could not create Resource Object - quitting"
wscript.echo "Could not create Resource Object - quitting"
WScript.Quit
End If

'==========================================================================
' Begin Process
'==========================================================================
' Call ShowPrograms routine to get array of programs available
ShowPrograms

' Split Array And Call Sub to install
For Each program In programArray
strStrings = Split (program,";")
programId = strStrings(0)
packageId = strStrings(1)
' Call RunProgam routine to invoke installation
'RunProgram programId,packageId
Next

' Cleanup
Set oProgram=Nothing
Set oUIResource=Nothing

'==========================================================================
' Routines
'==========================================================================
Sub ShowPrograms
On Error Resume Next

Set oPrograms = oUIResource.GetAvailableApplications

If oPrograms Is Nothing Then
COLLECTMSG "SHOWPROGRAMS", "Failed to get programs object - quitting"
wscript.echo "Failed to get programs object - quitting"
Set oUIResource=Nothing
Exit Sub
End If
strCount = oPrograms.Count
COLLECTMSG "SHOWPROGRAMS", "There are " & strCount & " programs"
wscript.echo "There are " & strCount & " programs"
wscript.echo
strCount = strCount - 1

ReDim programArray(strCount)
x = 0
For Each oProgram In oPrograms
COLLECTMSG "SHOWPROGRAMS", "ProgramName:" & oProgram.Name & ";" & "PackageID:" & oProgram.PackageId
WScript.Echo "Program Name: " & oProgram.Name
WScript.Echo " Package ID: " & oProgram.PackageId
' build array
programArray(x) = oProgram.Name & ";" & oProgram.packageId
x = x + 1
Next
End Sub

Sub RunProgram(programId,packageId)
On Error Resume Next
' #region Disabled code
' if oUIResourceMgr.IsMandatoryProgramPending = 1 Then
' Wscript.Echo "Mandatory program pending. Try again later."
' Set oUIResource=Nothing
' Exit Sub
' End If
' #endregion

' Get Program object
Set oProgram = oUIResource.GetProgram(programId,packageId)
'WScript.Echo "oProgram: " & oProgram.Name
if oProgram is Nothing Then
COLLECTMSG "RUNPROGRAMS", "Couldn't get the program"
WScript.Echo "Couldn't get the program"
Set oUIResource=Nothing
Exit Sub
End If

'When was the program last ran?
Set myProgram = oUIResource.GetProgram(programId, packageId)
oldTime = myProgram.LastRunTime
COLLECTMSG "RUNPROGRAMS", "Last Run Time: " & oldTime
WScript.Echo oldTime

' Execute program
COLLECTMSG "RUNPROGRAMS", "Running program: " & programId & " " & packageId
Wscript.Echo "Running program: " & programId & " " & packageId
oUIResource.ExecuteProgram programId, packageId, True

' Wait for last ran to change...
Set myProgram = oUIResource.GetProgram(programId, packageId)
Do While myProgram.LastRunTime = oldTime
Wscript.Sleep(2500)
Set myProgram = oUIResource.GetProgram(programId, packageId)
Loop
COLLECTMSG "RUNPROGRAMS", "Return Code: " & Err
WScript.Echo "Return Code: " & Err
End Sub

'*************************************************************
'*** Method: COLLECTMSG
'*** Description: Collections Errors for logging to log file
'*************************************************************
Sub COLLECTMSG(szLocation, errmsg)
'on error resume next
'Dim f
strmsg=errmsg
Set f = fs.OpenTextFile(strLogFile, ForAppending, True)
strmsg = Now & " | " & szLocation & " | "& errmsg
if err0 then
strmsg=strmsg & vbCrLf _
& "Error | " & Hex(Err.Number) & " h (" & CStr(Err.Number) & ") | Description |" & Err.Description
end if
f.Writeline strmsg
f.Close
strmsg=""
Err.Clear
End Sub

Sub CLEANLOG(strLogFile)
If fs.FileExists(strLogFile) Then
fs.DeleteFile strLogFile
End If
End Sub

Adding Duplicate Drivers to SCCM SP1

Another one of the most glaring deficiencies in my opinion with OS deployment using SCCM is the inability to import the same drivers for different models.  I personally prefer to import ALL drivers for every model of machine I have versus trying to dig through other imported drivers and piece together what each model actually needs.  It can be quite frustrating to say the least.  I break up my drivers by model and would love to out of the box (I believe this may be addressed in SP2) simply import the entire driver set in each subfolder, then create a package per model and assign the drivers from the subfolders to their respective package.  Well the good news is you CAN!  You simply have to trick SCCM into allowing you to do this.  Its quite simple actually. SCCM bases whether drivers are the “same” based on the calculated hash value of the driver files.  By simply adding a unique text file to the driver folder it creates a different hash value and allows you to import them even if the actual drivers have already been imported.  How I do it:

Break your drivers down by model in their own subfolders:
Example:
\\Server\share$\Drivers\Latitude E6500\Audio
\\Server\share$\Drivers\Latitude E6500\Video
Etc..

\\Server\share$\Drivers\Latitude E4300\Audio
\\Server\share$\Drivers\Latitude E4300\Chipset
etc..

Now open the root folder and create a new txt file based on the model.
Example:
\\Server\share$\Drivers\Latitude E4300\Latitude E4300.txt

Now copy this file to your clipboard, then do a windows search within the root directory for “*”.  Sort by type to gather all the folders at the top of the search.  Right click on EACH folder within the search and past the model specific txt file.

Do this for each of your driver sets and when you import them into their own sub-folder under the drivers node they will import even if the actual driver has already been imported.    you can now make corresponding packages for each model all with their OWN version of their drivers.

I know this has made my life SO much better with managing drivers in SCCM.  I hope it helps someone else.

*UPDATE* – A MUCH quicker and simpler way to create the txt file in your driver folders can be found here:
http://myitforum.com/cs2/blogs/jsandys/archive/2010/04/05/duplicate-drivers-helper-script.aspx

How to prevent optional PXE Adv from popping on clients

Once of the glaring deficiencies of SCCM OSD deployment (in my opinion of course) is the default behavior of an optional PXE advertisement.  Effectively if you want to have more than one pxe advertised task sequence the one that you DIDN’T execute will eventually pop up on every machine that has not run it before as an optional advertisement.  If the user clicks it and tries to run it (assuming your task sequence is set to do so) it will fail as it should be designed to only run within Windows PE.  Not a concern from a user accidentally rebuilding their own pc however not a viable option to have this popup on everyone and thus freak out the organization.  Well fortunately I have discovered a workaround for this issue.  It’s quite simple actually. 

This is assuming you have your “PXE” TS adverts and your “Push” TS Adverts separated.
*ALWAYS test any new changes in a lab environment prior to production implementation*
Right-Click your PXE advertisement and go to Properties.
Click the Advanced tab.
Select the radio button “This task sequence can only run on the specified client platforms”
Now find a platform that you will not have in your environment.  In my case I have selected “x64 Windows XP Professional SP1”.  I should not have this OS version on my network.  If I do it’s unsupported and the machine needs to be rebuilt as we currently do not support ANY x64 OS’s. 
Give that several minutes to propagate the changes.
You can then turn off the mandatory advertisement.  Your client will receive the advertisement however they will reject it based on the platform restriction.  This has no effect on your PXE building since the platform is not evaluated as part of the PXE build process.

You can now have multiple PXE adverts.  This is invaluable for testing build updates, etc.

One thing to keep in mind is that when you make your adverts optional the auto PXE boot (after selecting the network card as your boot device) goes away and you must press F12 (again) to initiate the connection.

I have found a workaround for this in my environment however the big disclaimer here is that this is NOT a supported solution from Microsoft.

F12 workaround:
On your WDS server(s) navigate to
D:\RemoteInstall\SMSBoot\x86
Make a copy of your pxeboot.com (the version that prompts you to press F12)
Delete the original pxeboot.com
Make a copy of pxeboot.n12 (the version that bypasses the additional F12 prompt)
Rename this copy to pxeboot.com
Flag this file as READ-ONLY.  If you do not do this the next reboot of your server will revert this file back.
Restart your WDS service and away you go.

Hope this helps someone else out there!!

Windows 7 Beta – Impressions

Hello, It has been quite some time since I have updated any entries on this blog.  Seems like everyday I get more and more to do so less time to post blogs! 

Anyway, I currently typing this post from a Windows 7 Beta machine (ie8).  I rolled my production Dell Latitude E6500 to Windows 7 the day the public Beta was released and so far (barring one Explorer.exe crash) I have to say I am fairly jazzed.  All the Vista drivers loaded no problem and pound for pound it is using less resources than Vista did.  The performance in general really just feels much more solid than Vista and this is still Beta!  Some of the new functionality that is built into W7′s Areo is actually quite functional.   The new “Show Desktop” is pretty freakin cool. 

I am running this Beta in production right now joined to our domain so I will be putting it to the coals so to speak.  With this being Beta, barring any major screwups this could be the new generation OS I have been waiting for.  Its nice be excited again about a new desktop OS.  I was very disappointed with Vista sadly (as were many) so it appears that MS may be back on track.  Keeping my fingers crossed!

I’ll post back with more info after I give it a proper go.

Export Computer list from AD

I find myself using this quite often so I figured I would post here for a nice copy and paste for future uses. 
*Requires Windows 2003 Admin tools (adminpak.msi) to be installed

Export computer objects from a specified OU and all sub OU’s(careful of wordwrap):

dsquery computer “ou=laptops,ou=devices,ou=site,dc=subdomain,dc=domain,dc=com” -limit 0 -o samid >>Laptops.txt

-limit 0 tells the script to return ALL matching values (Default is 100)
-o samid tells the script to return the “friendly” name. (Default is the full DN)

Obviously replace the values with ones specific to your environment. 

Image Capture: VPC vs VMWare

Just thought I may be able to save someone else out there a headache..

Although I am typically a much bigger proponent of VMWare products in terms of performance I found out the hard way that building and capturing an image from VMWare (Server 2.0) works great in most cases for newer hardware. I also discovered that the “IDE” mass storage driver in VMWare Server 2.0 would not allow the image to boot on older hardware (think Latitude D800, 600, etc). I spent a couple of days trying to find a set of driver that I could inject to get the machines working but to no avail. I had used Virtual PC 2007 in the past to build and capture but the performance is so lacking compared to VMWare I figured I would start there first. Well a long story short, after I went back to using Virtual PC 2007 (SP1) as my base machine for build and capture my “older” hardware is now back to booting properly and my newer hardware continues to function by injecting their mass storage drives as needed.

The moral of the story? Use Virtual PC 2007 to build and capture your image! Then you can turn it off and use VMWare for everything else.

PXEFilter.vbs update to auto clear last PXE Advert

** This script has been deprecated with the release of SCCM 2007 R2.  It supports “Unknown Machines” inherently

I have modified the PXEFilter.vbs that ships with MDT 2008 to automatically clear the last PXE advertisement of the machine that is PXE booting. This allows me to set a mandatory assignment for my OS Deployment and removes the requirement for field techs to have to open the admin console to re-image a machine via PXE.

The section I added was:

‘//————————————————————-
‘// Clear Last PXE Advertisement
‘//————————————————————-
On Error Resume Next
Dim resources
Dim InParams

‘ Set up the Resource array parameter.
resources = Array(1)
resources(0) = iResourceID

Set InParams = oSMS.Get(“SMS_Collection”).Methods_(“ClearLastNBSAdvForMachines”).InParameters.SpawnInstance_
InParams.ResourceIDs = resources

oSMS.ExecMethod “SMS_Collection”,”ClearLastNBSAdvForMachines”, InParams

if Err.number <> 0 Then
PXE.LogTrace “Failed to clear PXE advertisement for resource: ” & iResourceID
Else
PXE.LogTrace “Clear PXE Advertisement Completed successfully for resource: ” & iResourceID
End If

Cleanup WSUS 3.0 Sync History

As any WSUS admin knows, the sync history for some unknown reason is not an option for cleanup using the built in cleanup wizard. This can result in excruciating load times when trying to view the sync history. Hopefully Microsoft will correct this in future releases however until then you can execute the following query against your WSUS DB to cleanup the Sync events.

DELETE FROM [SUSDB].[dbo].[tbEventInstance]
WHERE [EventID]=381 or [EventID]=382 or [EventID]=384 or [EventID]=386 or [EventID]=387

You may consider running this as a part of your monthly maintenance.