Windows 10 Compatibility Pack for SCCM 2007

MS has released a compatibility pack for Windows 10 for SCCM 2007.  This will add Software Distribution, Software Updates, and Desired Configuration Manager functions from the client side as well as few others, additional information can be found in the link below.

https://support.microsoft.com/en-us/kb/3044077

Software Distribution worked prior to the hotfix but the Software Updates Agent did not.  If you have installed the current SCCM client before hotfix, this is the error that shows up in the WUAHandler.log.

WU Agent Version too low (6.2.10586.420), needs to be at least: 7.1.6001.65

After installing the hotfix on the site servers and deploying to the Windows 10 client, it will update  the client to version, 4.00.6487.2239.   

The only component to show being upgraded was the Software Updates Agent.

Once that is installed, trigger the Software Updates scans and it should start applying the patches after the detection.  The hotfix should be deployed to all clients regardless of OS.

Back a few months before this was release, we were already planning on a Windows 10 image and used Enterprise edition.  That is not officially supported because SCCM 2007 cannot deploy the feature updates.  That would have to happen from the system with WUA enabled.

SCCM 2007 does not seem to pull in new OS software updates newer than 2015 versions like Server 2016 as mentioned in the article only supports LTSB 2015 so you're pretty much forced to upgrade at some point.

Plink and Citrix Netscalers

Our global Citrix infrastructure contains multiple Netscalers load balancers situated worldwide.  However not all regions run the same apps.  The issue is our US call center does a shift change and cannot access a published application due to being directed to the wrong application portal page from the load balancer.  The resource they are trying to use doesn't have access to it from our EU and AS NS.

First, I'm not experienced with Citrix and Netscalers but been tasked to perform the documented steps when escalations come in.  There's a script already to run on a schedule that our EU team has done but I didn't ask to see their script and some times it still slips through.

I wrote this script to be used in Powershell ISE and you must have Plink downloaded. 

The first command is loaded into a variable and the Do While loop is to run the command again until the variable is null.  Most instances it runs the commands 3 to 4  times.  I've tested it on all the NS that this works right after the last deletion a sh command via Putty to verify doesn't show anything. 


$route =./plink.exe -ssh nsroot@host -pw PASSWORD "sh persistentSessions ""
Do {./plink.exe -ssh nsroot@host -pw PASSWORD "clear persistentSessions ""} while (!$route)

SCCM 2007: Software Updates Not Replicating to Child Site

The issue from my previous post did not resolve itself easily using supported methods to re-sync of pre-inst and creating the SHA file.  Some reason a few items was holding things up because it wasn't able to insert them into the DB.

The engineer provided instructions to delete the entire table of the items and to re-sync the SUP items.

The idea behind renaming the objreplmgr.dl in Step 4 is to have all the items copied to the child site to process everything already local instead of as it is received, some items may have dependencies but in my case it didn't seem like it and they never saw the error listed. 

Make a backup of the DB and use the instructions at your own risk.  Please contact MS Support if you're unsure as you may end up causing more issues than trying to fix modifying the DB directly.

Perform the steps below

1. In child Primary site, delete all files inside objmgr.box, objmgr.box\INCOMING, objmgr.box\INCOMING\retry & objmgr.box\INCOMING\retry\Bad.
2. Run below SQL query to clean the updates information from child site DB.
  
SQL script in blue text

BEGIN TRANSACTION

DECLARE @SiteCode varchar(8)
DECLARE @RowCount int, @Err int, @TotalRowCount int
SELECT @SiteCode=ThisSiteCode from SMSData

PRINT N'Step 1 - Deleting the update sources'

delete from CI_UpdateSources where UpdateSourceName = 'WSUS Enterprise Server'

PRINT N'Step 2 - Deleting rows of type 1 and 6'

delete CIR from CI_ConfigurationItemRelations CIR 
left join CI_ConfigurationItems cci on cci.CI_ID = CIR.FromCI_ID 
where cci.CIType_ID in (1, 8)

delete CIR from CI_ConfigurationItemRelations CIR 
left join CI_ConfigurationItems cci on cci.CI_ID = CIR.ToCI_ID 
where cci.CIType_ID in (1, 8)

set rowcount 1000

delete from CI_ConfigurationItems Where CIType_ID in (1) and SourceSite <> 
@SiteCode
SET @TotalRowCount=@@RowCount

IF @@ERROR!=0 GOTO exiterror

while (1=1)
begin
Delete from CI_ConfigurationItems Where CIType_ID in (1) and SourceSite <> 
@SiteCode
SELECT @RowCount=@@ROWCOUNT, @Err=@@ERROR
SET @TotalRowCount=@TotalRowCount + @RowCount

IF @Err!=0 GOTO exiterror 
IF @RowCount = 0 break 

PRINT N'Looping... Rows deleted so far:'
PRINT @TotalRowCount
end

PRINT N'Step 3 - Deleting rows of type 8'

set rowcount 1000

delete from CI_ConfigurationItems Where CIType_ID = 8
SET @TotalRowCount=@@RowCount

IF @@ERROR!=0 GOTO exiterror

while (1=1)
begin
Delete from CI_ConfigurationItems Where CIType_ID = 8
SELECT @RowCount=@@ROWCOUNT, @Err=@@ERROR
SET @TotalRowCount=@TotalRowCount + @RowCount

IF @Err!=0 GOTO exiterror 
IF @RowCount = 0 break 

PRINT N'Looping... Rows deleted so far:'
PRINT @TotalRowCount
end

PRINT N'Step 4 - Running the SP...'

set rowcount 0
update CI_SDMPackages set IsDeleted = 1
where SourceSite is not NULL and SDMPackageName not like 'ScopeID%' and 
SDMPackageName not like '%GLOBAL%'
IF @Err!=0 GOTO exiterror

exec sp_DeleteOldSDMPackageData 0
IF @Err!=0 GOTO exiterror

GOTO exitok

exiterror:

PRINT N'Error:'
PRINT @@ERROR

exitok:
set rowcount 0

if @@ERROR = 0 COMMIT TRANSACTION else ROLLBACK TRANSACTION

3. Stop SMS Executive and Site component manager service on Child Primary site.
4. On the child Primary site navigate to \Bin\i386 rename the DLL -> objreplmgr.dll to objreplmgr.dll.old
5. Start the Site component manager service on Child Primary site, SMS Executive service will be started automatically by site comp service.

6. Force a resync of the updates data objects by dropping a .sha file into the Central site's ..\inboxes\objmgr.box. eg. CH1.SHA
7. Once the SHA file disappear, wait for 4-5hours and proceed with next step.

8. stop SMS Executive and Site component manager service on Child site.
9. On the child site navigate to \Bin\i386 rename the DLL -> objreplmgr.dll.old to objreplmgr.dll
10. Start the Site component manager service on Child site, SMS Executive service will be started automatically by site comp service.

Deleting Updates from WSUS

After we migrated our first SCCM server to Windows 2008 R2, we were dealing with a problem with the WSUS components.  It had over 40k updates which clients were having issues described here in the link below. I'd definitely recommend pruning the upstream server if your other site servers are half way around the world before re-installing WSUS.

https://blogs.technet.microsoft.com/sus/2008/09/18/wsus-clients-fail-with-warning-syncserverupdatesinternal-failed-0x80244010/

The workaround was to test the component to see if it was working was to set the value for the item below, of course this caused issue for sites in slow links the next morning so it was reverted back to the default size of 5MB.

USE SUSDB
GO
UPDATE tbConfigurationC SET MaxXMLPerRequest = 0

https://social.technet.microsoft.com/Forums/en-US/29c7952e-6eb3-41e7-87f0-bc1162953179/wsus-30-sp1-client-update-failure?forum=winserverwsus

To prevent the next WSUS sites from inheriting unnecessary updates, there's a script to delete the updates that the Server Wizard is not removing after declining.

Using the script from the link below, we used it to remove the Drivers.

http://runesk.blogspot.com/2012/09/delete-oldunwanted-updates-from-wsus.html

For other updates, I modified it some to read it from a text file so I can review the updates and add it into one txt file and let it run overnight to remove all unneeded updates for OS we no longer use or never used like XP, Vista, Windows 8, Server 2003, etc.  When I first ran it, it was faster than the query method but on subsequent it took just as long.  Or if decline the updates, you can just change the script to delete declined updates.

Here's a good reference link on the updates you can decline from the WSUS console.

https://blogs.technet.microsoft.com/configurationmgr/2015/04/15/support-tip-configmgr-2012-update-scan-fails-and-causes-incorrect-compliance-status/


Delete Updates from list - Powershell

$wsusserver = "localhost"
$ids = get-content 'c:\Vista-guid.txt'
[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($wsusserver,$False,"8530")
ForEach ($id in $ids){$wsus.DeleteUpdate($id); Write-Host $id removed}

or replace the red text with the below to delete the recently declined updates.

$wsus.getupdates() | Where {$_.IsDeclined -eq 'True'} | ForEach-Object { $wsus.DeleteUpdate($_.Id.UpdateID); Write-Host $_.Title removed }

To get the updates, you'll need to open up the SQL console and connect to the WSUS DB, this is a sample query for declined items.  Copy the UpdateID column into the txt file of updates you want to delete.   Depending on your server resources, using the script to delete a criteria may be slower than from a list.

Get Declined Updates SQL

select UpdateID from PUBLIC_VIEWS.vUpdate
where isDeclined = '1'

Once you have cleaned it up, here's another good reference below on maintaining it.  If you are in a multi-hierarchy site you may want to configure it as a downstream replicas if the software update deployments are centrally managed on the parent site and no SUP deployments are managed from the child primaries.

https://blogs.technet.microsoft.com/configurationmgr/2016/01/26/the-complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maintenance/

Refresh All Packages on a DP

Having a recent DP suddenly die and no way to bring it back up to properly decommission, you can spend some MS hours for a SQL script to delete everything or rebuild a computer with the same name and decommission it properly by "adding" the packages back to it using a script. 

The script refreshes all packages and what's interesting to note is that it doesn't matter that the drive on the old server wasn't the same location.  Of course, if you are rebuilding for production use and have a specific drive to use, put the NO_SMS_ON_DRIVE file where you don't want it to create it and be mindful of the bandwidth to whatever site because it will transmit all the packages again.

This isn't the same as adding packages to a DP that was never on it and will only refresh the packages  SCCM shows up with the server.

The script below reads from a list of packageIDs that I exported into a txt file.

Check the distmgr.log on the site server where the DP is to see the progress.

A note that if your site is  multi-site hierarchy, you'll need to enter the site code where the DP is in not the parent site where the package was distributed from.

Dim StrSMSServer, DPs, ObjFS, StrFile, ts, strPackageID, DP

'On Error Resume Next

strSMSServer = ""
strDPserver = ""

strSMSSiteCode = ""

Set objLoc =  CreateObject("WbemScripting.SWbemLocator")
Set objSMS= objLoc.ConnectServer(strSMSServer, "root\sms")

Set Results = objSMS.ExecQuery _
  ("SELECT * From SMS_ProviderLocation WHERE ProviderForLocalSite = true")
For each Loc in Results
  If Loc.ProviderForLocalSite = True Then
    Set objSMS = objLoc.ConnectServer(Loc.Machine, "root\sms\site_" & _ 
        Loc.SiteCode)
    'strSiteCode = Loc.SiteCode
  end if
Next

Set objFS = CreateObject("Scripting.FileSystemObject")
strFile = "c:\dp1.txt"

Set objFile = objFS.GetFile(strFile)
Set ts = objFile.OpenAsTextStream(1,-2)

Do Until ts.AtEndOfStream

    strPackageID = ts.Readline()


Set DPs = objSMS.ExecQuery ("Select * From SMS_DistributionPoint WHERE ServerNALPath like '%" & strDPserver & "%' and siteCode='" & strSMSSiteCode & "' and PackageID='" & strPackageID & "'")
For Each DP In DPs
    wscript.echo strPackageID
    DP.RefreshNow = True
    DP.Put_
Next

Loop

SCCM 2007 Backup Failing - Hangs Indefinitely At SCOM Maintenace Mode

For the last 2 days, I've been on the phone with MS engineers and they haven't been able to figure out the issue to unstuck the MaintenancemodeSCOM.  We don't use SCOM in our environment and on working servers, it goes right by this error in a matter of seconds.   This didn't continue after the 1 hour timeout but shouldn't have to wait that long.  We are working with a partner for a project to upgrade our server OS and they were able to escalate this and on the 2nd day get the best of the best SCCM engineers, they tried their best but still weren't able to get any further than the first day going in with deeper dives and traces.

Still continuing working on day 2, I tried a different approach that we didn't try earlier with MS.  We already tried putting the services into a stopped state or stopping it when it gets stuck but it doesn't work but had before but what I always noticed is that after VSS was stopped then SMS_SITE_BACKUP was able to continue to stop the services.  At that point the backup would be logged as failed.  The logs however doesn't indicate anything in an unstable state or errors so that appears to be fine.

So what was tried differently was putting those 2 services in a paused state first and stopped VSS.  Let the backup or the writer start the VSS service and tehn start the backup.  This worked correctly 4 out of 5 times with the logs showing both writers are in a stable state on a successful completion with the correct event ID of 6833.  I think the 4th time VSS was already running so it should be stopped before a backup is enabled.  

Writers are Stable

It was a repeatable process and hopefully it will allow us to progress with upgrading these servers off 2003 R2 to 2008 R2 which I hope have more stable back up.  Not quite sure if the smsbkup.exe or the VSS is the issue but I believe somewhere with the 2 services. 

On Monday, we'll have MS validate this is a workable solution that can be used to get working backups.

Event ID 6833 indicates succesful backup

Update 3/7/2016: The Microsoft engineer validated that the method used to produce the backups is good in the sense the manual method is essentially the same as sms backup performing it and going by the official documentation that as long as the Event ID is produced the backup is successful.  To really confirm however the backups are valid, is to do restore it on the new server.

Internet Explorer 11 Crashing Randomly

After deploying IE 11 to about 11k machines we have been encountering the occassional ticket request regarding random crashing on websites.  It doesn't happen across the board but for some users the same websites would constantly crash on them while other users would be different sites.

Having come across the article below which mentions to check for under the Internet Options in the Advanced tab and enabling "Use software rendering instead of GPU rendering".  It seems to fix a few users we are testing so worth checking out if you have the same types of random crashes or with certain sites.  On one of the computers that had the problem, we updated the latest video drivers from HP and didn't fix the issue until the setting was enabled.  Forgot the model but it's a few years old at most.

https://technet.microsoft.com/en-us/library/dn338138.aspx

SCCM 2007: Status Filter Rules to Delete Duplicate IDs

With the recent issue we have regarding duplicate IDs, we decided to go with the deleting the objects "manually" because we can't change the interval the discovery happens or as I've suggested to use a unique naming convention which wasn't approved using serial instead of username.

To accomplish this task, it could have been done a few ways that I could think of but I decided to look into Status Filter Rules to do this in an automated fashion as opposed to using Task Scheduler which could be run every a few hours but may involve creating a network service account.

Create a script to look for the same named object but only select the ones with Obsolete0 as NULL or any column that has a NULL value.  I ran the script to retrieve duplicates a few times to validate then modified it to select only NULL.  If delete the "and Obsolete0 is NULL" it will retrieve both but for the deletion, we only want to delete the ones with NULL.   When done save the sql file to a location on the system.

Set NOCount ON
USE SMS_YOURSITECODE;
Select b.resourceID
from (SELECT Name0, COUNT(*) as rec_cnt
FROM dbo.v_R_System AS a
group by name0
having count(*)>1) A
join v_R_System AS b ON
b.name0 = a.name0 and Obsolete0 is NULL
SET NOCOUNT OFF

I created a batch file to use SQLCMD to run the sql file and export it to the text file that contains on the resource IDs.  I created another that would also export the Name and SMS Unique Identifier, the change date, and creation date so I can monitor what gets exported.  My delete script exports the items delete with the time so I can see when they are done.

Using the command lines as below will export with correct formatting w/o the white space and header information.

sqlcmd -m 1 -h -1 -W -S SERVERNAME -i c:\sql\nullid.sql > c:\dupid\dupids.txt


The batch file runs both queries and exports them to 2 separate text file.  One is the ResourceID only and the other has more info and appends to a file with the extra data.

Before creating the Status Filter Rule, you may want to review what message IDs you want to use a the trigger.  I'm looking at the SMS_AD_Group_Discovery_Agent message logs and decided to use 500 and 1105 because of the time gap.

Select the Component and Message ID to match and click the Action tab.

In the Actions tab, I have it run the batch file to export the duplicate IDs.  Then repeat it again with the other script that runs against the txt file to delete.

The upside of this method is that it doesn't require additional permission settings or using a service account to run it.

Duplicate SCCM (2007) Records Not Showing The Correct AD Discovery Objects After a Data Discovery Cycle

Recently, we changed vendors for client computer purchases and have them imaged before shipping to our locations.  They are applying our custom wim using MDT with our SCCM client installed and joined to the domain using a generic name.  Once received it goes through the new provisioning process that includes renaming it while on the domain, adding security groups for non-standard app deployment and then is moved into a production OU from the staging OU.

What we are encountering with new process is that when a computer is renamed, the new name is discovered and has a record created.  After a DDR to update the record, the original one reflects the new name change and then NULLs the new one.  The issue is that the new one contains the AD discovery objects for security groups and OU information which the original active one doesn't have.

This was tested extensively prior to writing the documentation but upon my return this did not appear to work anymore.  I created a case with MS support and they were able to reproduce the problem that I showed him.  I think he was under the assumption that SCCM 2007 would be able to recognize by the GUID which is the active one and correct itself.   Our AD Discovery cycles runs every 2 hours and Heartbeat is set daily and even when the service is restarted it doesn't send a DDR that the engineer said it would.  So if it was left to SCCM under the current settings that would not correct itself and require our manual intervention to correct.

So his suggestion was to turn off the AD discovery on the staging OU and to test it out. 

It didn't seem to work w/o manual intervention.

Based on this, it seems it is a known issue.

https://blogs.technet.microsoft.com/configmgrteam/2011/09/09/known-issue-and-workaround-duplicate-records-when-you-use-unknown-computer-support-with-active-directory-delta-discovery/


On a side note this is a script I created for the tech/supervisors to only export the SCCM groups to copy to the new computer account.  This writes to a CSV file named by the computer name you enter.

Export Specific Security Groups from AD Computer Object

$computer = Read-Host -Prompt 'Input computer name'

$PC =  $computer + "$"

Get-ADPrincipalGroupMembership -Identity $PC | Where-Object {$_.Name -like '*-SCCM*'} |select Name | export-csv c:\$pc.csv -NoTypeInformation

Same for the bulk computer account creation, I was told someone was creating hundreds manually.

The below is a modified script I found online that was used to create bulk VMs in VMWare.  I replaced the commands to create the VMs and made it so you can create the objects to start at a certain number because we in certain amounts each time.

http://vinf.net/2010/02/25/quick-and-dirty-powershell-to-create-a-large-number-of-test-vms-with-sequential-names/

Bulk Computer Creation Script

$erroraction = "Silently-Continue"

$Nameconvention = 'USPC'

$HOW_MANY_TO_CREATE = Read-Host -Prompt 'Enter # of accounts to create'

$start = Read-Host -Prompt 'Enter starting #'

$NumArray = (1..$HOW_MANY_TO_CREATE)

foreach ($number in $numArray )

{

$seqn=$number + $start

$name =  $seqn | % {"{0:0##}" -f $_}

$string = $Nameconvention + $name

echo Creating $string

new-adcomputer $string -Path '' -Enabled $true

}

Please modify accordingly to your environment and test it.

Deploy Windows 8.1 to Windows 8 via SCCM

We have some Windows 8 devices that needs to be upgraded to 8.1.  There is no installer for IE 11 and support has ended on Jan 12, 2016.  I did some research regarding upgrades for enterprise deployment tools but came across only a few articles that said the same thing.  It would have to be refreshed through OSD or downloaded through the Microsoft App Store.

I came across a Technet article of all things that didn't even come up as a top results for this and had a short mention for enterprise customers using SCCM 2012 to deploy the upgrade using the Windows 8.1 install media.  They must have added that upgrade switch later on because of the limited choices enterprise customers to upgrade and it wasn't easily found on any other tech sites.

Here's the article for reference:

https://technet.microsoft.com/en-us/library/dn744271.aspx

The only applicable part for my scenario.

For organizations running Windows 8 Enterprise RTM, the Windows 8.1 update process is simply running the Windows 8.1 setup.exe with a few command-line switches. The switches used in this update scenario are commonly the following:

• /auto:upgrade. Required switch that runs the update process with no user interaction.
• /noautoexit. Optional switch. If something goes wrong during setup, wait for user input.

This means that if you have only a few machines to upgrade, or if you want to test upgrading a few machines, you can simply download Windows 8.1 Enterprise ISO and run the setup.exe /auto:upgrade command. For larger numbers, you probably want to automate the setup using System Center 2012 R2 Configuration Manager.

We're still on CM 2007 so I figured why not give this a try and if it works, we just saved a lot of time and resources, if not then it would have to refreshed or upgraded through some other means.

I have tested it successfully on an HP Elitebook 8440p and it upgraded w/o a hitch, I thought something went wrong when I couldn't connect to it 2 hours later but the next morning I tried again and it worked.  So even if they don't officially list CM 2007, I can verify that it works.  I'll have to re-run the upgrade to see what goes on and how long it takes.  The tablets are powered by Atom processors and have SSD so I'm not sure how long they will take.  The only issue with them besides their sites having low bandwidth is their drive is only 32GB and near full.  Deploying a 3GB install might not work if it requires a lot of space during the upgrade.

Looking at the execmgr.log it appears that the SCCM client was still working as it periodically re-ran the advertisements and provided a 0 status every few hours and stopped at 2AM with occassionally lines of system restarted in between.  I'm thinking CM 2007 could not accurately track the install from the command line.

An interesting "side effect" of the upgrade was that it generated 15 more resourceID entries in the DB.  It's all obsolete but didn't expect to see that.

SCCM Reporting: All Succeeded or Reboot Pending Status from advertisements using a specific package

We are progressing well with the IE 11 upgrade globally and all kinks are fixed for the most part.

Here's a query I use have used often when looking at the overall deployments success status for a large deployment project such as this.   We have over 12000+ machines to deploy to and for this project it was done with care from all the incompatibility and other environmental things that's not set for IE 11 until after IE 11 is installed on the system.   So this came in handy again for advertisements from testing, pilot, and production deployments and there are still more to be created!

This is a sub-query that pulls the advertisements status of 'Succeeded' and 'Reboot Pending' from the advertisements IDs tied to the package you're using.  In this instance it's both IE 11 packages.


Code below:

SELECT     v_R_System.Name0, vSMS_ClientAdvertisementStatus.LastStateName, vSMS_ClientAdvertisementStatus.LastStatusTime,
                      vSMS_ClientAdvertisementStatus.AdvertisementID, v_Advertisement.AdvertisementName, v_Advertisement.CollectionID, v_Collection.Name,
                      vSMS_ClientAdvertisementStatus.LastStatusMessageID
FROM         vSMS_ClientAdvertisementStatus INNER JOIN
                      v_R_System ON vSMS_ClientAdvertisementStatus.ResourceID = v_R_System.ResourceID AND
                      ((vSMS_ClientAdvertisementStatus.LastStateName = 'Succeeded' OR
                      vSMS_ClientAdvertisementStatus.LastStateName = 'Reboot Pending') and vSMS_ClientAdvertisementStatus.LastStatusMessageID != '10040') INNER JOIN
                      v_Advertisement ON vSMS_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID INNER JOIN
                      v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID
WHERE     (vSMS_ClientAdvertisementStatus.AdvertisementID IN
                          (SELECT     adv.AdvertisementID
                            FROM          v_Advertisement AS adv INNER JOIN
                                                   v_Package AS pkg ON adv.PackageID = pkg.PackageID INNER JOIN
                                                   v_Collection AS v_Collection_1 ON adv.CollectionID = v_Collection_1.CollectionID
                            WHERE      (pkg.PackageID = 'XXX00853') OR
                                                   (pkg.PackageID = 'XXX00854')))

Create Collections from CSV file in SCCM 2007

Here's a script I use often for when we do targeted global deployments, I've combined a create collection script with one that reads from a CSV from the MSDN sample here: https://msdn.microsoft.com/en-us/library/ms974559.aspx .

Saves a lot of time from not using the console.  It reads from a CSV file with Headers defined as "Name" and "CollectionID" saved to c:\csv folder with the file named collections.csv which you can change to your liking.

The collectionID is the parent collection that will be created under.  Why 2 columns?  I like to separate EU, APAC, North America into their own given.  You may need to have parent collection created a head of time to retrieve the CollectionID.

Chances are you are running this on a x64 machine, if so, execute using cscript from c:\windows\syswow64\ instead of c:\windows\system32 or you might get an ADODB error.  You should not need any MDAC components, I'm running this on a Windows 2003 server.

Script contents:

'On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

Set objLoc =  CreateObject("WbemScripting.SWbemLocator")
Set objSMS= objLoc.ConnectServer(strSMSServer, "root\sms")
Set Results = objSMS.ExecQuery     ("SELECT * From SMS_ProviderLocation WHERE ProviderForLocalSite = true")
For each Loc in Results
    If Loc.ProviderForLocalSite = True Then
        Set objSMS = objLoc.ConnectServer(Loc.Machine, "root\sms\site_" & Loc.SiteCode)
    end if
Next
 

strPathtoTextFile = "C:\csv\"

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""

objRecordset.Open "SELECT * FROM collections.csv", _
          objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    strColName=objRecordset.Fields.Item("Name")
    strParentID=objRecordset.Fields.Item("CollectionID")
    wscript.echo strColName & " " & strParentID
    CreateSCCMCollection
    objRecordset.MoveNext
Loop

Sub CreateSCCMCollection
Set newCollection = objSMS.Get("SMS_Collection").SpawnInstance_()

newCollection.Name = strColName
newCollection.OwnedByThisSite = True
path=newCollection.Put_

'the following two lines are used to obtain the CollectionID
'of the collection we just created
Set Collection=objSMS.Get(path)
strCollID= Collection.CollectionID
'now we create a relationship betwen the new collection
'and it's parent.

Set newCollectionRelation = objSMS.Get ( "SMS_CollectToSubCollect" ).SpawnInstance_()
newCollectionRelation.parentCollectionID = strParentID
newCollectionRelation.subCollectionID = strCollID
newCollectionRelation.Put_
End Sub

SCCM 2007 DCM using VBscript

I assume anyone reading this would already know about but may not have looked at other ways to set up a compliance item.

I have tried using Powershell which is much even easier to write and shorter but you will need to ensure your environment has the proper PS policies in place.   Since they will not budge on the PS policies I used VBScript.

In the script below, it is looking for the Encase agent service name and querying for 2 names depending on what OS it was installed on.  I also use a modified version to check on the service state for running or stopped.  I use this for all security applications for our retail environment to reduce any non-PCI compliant systems.

Script contents:

strComputer = "."

   Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
   Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Service where Name = 'enstart' or Name = 'enstart64' ")

   For Each objItem In colItems
      encasename=objItem.Name
      'encasestate=objItem.State
        Next

Select Case encasename
Case "enstart"
wscript.echo "Compliant"
Case "enstart64"
wscript.echo "Compliant"
Case Else
wscript.echo "Non-Compliant"
End Select

Remove packages from DP from SCCM 2007 using Powershell

Here's a script to remove all packages from a DP in 2007 scheduled to be decommissioned.  I'm aware of the cmd-lets that are available for this but unfortunately Windows 2003 R2 can only have PS v2 and internal firewall rules prevents me from running this from a remote 2008 R2 servers, this is what I have come up with a variation of what I found of adding a package to a DP but instead to delete.

$ErrorActionPreference = "SilentlyContinue"
$SCCMServer = ''YourSCCMSERVER"
$SMSSitecode = "YourSiteCode"
$DP = "YourDP"

$pkgs = Get-WMIObject -ComputerName $SCCMServer -Namespace "root\sms\site_$SMSSitecode"  -Query "Select * From SMS_DistributionPoint WHERE ServerNalPath like '%$DP%' and SourceSite = '$smssidecode'"
foreach ($pkg in $pkgs){write-host $pkg.PackageID;$ID.Delete()}

If you want to find out the ServerNalPath of the servers replace the code with the below.  If you have PXE shares, you may end up accidentally deleting something you don't want but those shares shouldn't be holding software packages.  Enter a packageID to limit the results or it will pull the ServerNALPath from all packageIDs.

$pkgs = Get-WMIObject -ComputerName $SCCMServer -Namespace "root\sms\site_$SMSSitecode"  -Query "Select * From SMS_DistributionPOint WHERE PackageID = '$packageid' and SourceSite = 'XXX'"

foreach ($pkg in $pkgs){write-host $pkg.ServerNalPath}

Replace XXX with your site code above.

Good resources for scripting in general and SCCM related.

https://technet.microsoft.com/en-us/scriptcenter/bb410849.aspx

https://msdn.microsoft.com/en-us/library/cc145334.aspx