Powershell–Schedule a SCCM Advertisement

One of the business units I work with needed to deploy an application using SCCM.  So far so good right?  Then they said that they only wanted it to run between 23:00 and 06:00.  So now you are thinking about putting a Maintenance Window on the collection to accomplish the time restriction requirement right?  Well, what about all of the other distributions (like Software Updates)?  They wouldn’t run on the machines that are in the collection with the Maintenance Window on it.  I guess we could go into each and every deployment and flag it to ignore maintenance windows, but that would cause even more headaches.

With a little help from PowerShell and SQL we can change the advertisement properties.  Specifically we need to change the following:

  • Advertisement Start Time
  • Advertisement Expires
  • Mandatory Assignments

Example of what we need to accomplish:

  • Today the Advertisement has the following settings
      • Advertisement Start Time – 4/20/2012  23:00.
      • Advertisement Expires – 4/21/2012 06:00
      • Mandatory Assignments – 4/20/2012 23:00
  • Tomorrow the Advertisement will have the following settings
      • Advertisement Start Time – 4/21/2012 23:00.
      • Advertisement Expires – 4/22/2012 06:00
      • Mandatory Assignments – 4/21/2012 23:00

Well, I could go into the properties of the advertisement and change them, but that isn’t my style.

What I did was write a PowerShell script to change the above times and then created a SQL Job to run the PowerShell script on a schedule.

PowerShell Script:

Param([String[]] $AdvertisementID)

$SCCM_SERVER = "[server name without the brackets]"
$SCCM_SITECODE = "[three letter site code without brackets]"
#$AdvertisementID = "[AdvertisementID without the brackets]"

#Foreach ($AdvertisementID in $AdvertisementIDs)
#	{
	$AdvertisementSettings = ([WMIClass] "\\$SCCM_SERVER\root\SMS\site_$($SCCM_SITECODE):SMS_Advertisement").CreateInstance()
	$AdvertisementSettings.AdvertisementID = $AdvertisementID
	#Get lazy properties
	$AdvertisementSettings.Get()
	#Build the Scheduled Time
#	$NewDate = (Get-Date).adddays(1)
	$year = [string](Get-Date).Year
	$month = [String](Get-Date).Month
	if ($month.Length -eq 1) {$month = "0" + $month}
	$day = [string](Get-Date).Day
	if ($day.Length -eq 1) {$day = "0" + $day}
	$ScheduledTime = $year + $month + $day + "230000.000000+***"
	#Build the Expiration Time
	$ExpireDate = (Get-Date).AddDays(1)
	$day = [string]$ExpireDate.Day
	if ($day.Length -eq 1) {$day = "0" + $day}
	$ExpirationTime = $year + $month + $day + "060000.000000+***"	
	#Apply the settings to the advertisement
	$AdvertisementSettings.PresentTime = $ScheduledTime
	$AdvertisementSettings.ExpirationTime = $ExpirationTime
	$AdvertisementSettings.ExpirationTimeEnabled = $true
	$AdvertisementSettings.put()

	$AssignedSchedule = ([WMIClass] "\\$SCCM_SERVER\root\SMS\site_$($SCCM_SITECODE):SMS_ST_NonRecurring").CreateInstance() 
  $AssignedSchedule.StartTime = $ScheduledTime
  $AdvertisementSettings.AssignedSchedule = $AssignedSchedule
  $AdvertisementSettings.AssignedScheduleEnabled = $true
  $AdvertisementSettings.put() 
#	}

Then we need to create a SQL Job to run this PowerShell script nightly:

-- USE [msdb]
GO

/****** Object:  Job [Job Name Here.  Keep the brackets]    Script Date: 04/20/2012 14:34:08 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/20/2012 14:34:08 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Job name inside the ticks', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'[Domain\UserName without the brackets]', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CWT - Express 1.2 Advertisement]    Script Date: 04/20/2012 14:34:09 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job step name inside the ticks', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe -command "& ''I:\SCCMScripts\SCCM Powershell Production Scripts\Set-Advertisement.ps1'' -AdvertisementID ''[AdvertisementID without the brackets]''"', 
		@flags=0, 
		@proxy_name=N'[proxy name so that the job can execute]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Job name inside the ticks', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20120419, 
		@active_end_date=20120503, 
		@active_start_time=220000, 
		@active_end_time=235959, 
		@schedule_uid=N'2fd7b8b0-f749-4022-a806-3410372c6f4e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Moving the SMSSIG$ and SMSPKGSIG folders in SCCM

Just by reading the title of this post you can rest assured that the s**t hit the fan on one of my SCCM Primary Sites today!  One of our drives was almost full and it turned out that the SMSSIG$ folder was the culprit.

First you should ask yourself, how did this happen?  How did I get here?  Same as it ever was.  Ahh….I digress. 

Well, for starters you should have the No_SMS_on_drive.sms file in the root of all drives that you don’t want SCCM to use.  For Distribution Points SCCM uses the drive with the most amount of free space.  Once that drive fills up it will use the next drive with the most freespace.  I am not sure that was my issue since I did have that file in the root of the drive that was filling up.  But, I certainly could have put it there after the SMSSIG$ folder was created.

The next step is to ensure that the Site Settings > Component Configuration > Software Distribution > Location of stored packages has the drive letter of the drive you want the SMSSIG$ folder on.  In my case it was set to Z:\.  I don’t have a Z: drive.  At this point you are probably asking yourself “Hey Matthew, what kind of shop are you running there?”.  I don’t know really!  But, I do know how to fix the issue.

First I Googled “moving SMSSIG$ folder” and came across this article:  http://www.myitforum.com/articles/1/view.asp?id=12833

You will notice that the article states that the SQL Update command has to be run for each packageID.  Well, I had over 300, so like, um….yeah…that wasn’t happening.

Enter our best friend PowerShell.  Here is the PowerShell script based on the article above.  You will see that I am changing the drive from I$ to H$.  Note:  read the article above, don’t just execute the PowerShell code.  The article states that this should be done on all of the parents of the site in question.

Add-PSSnapin SqlServerCmdletSnapin100
$SQLCMD = Invoke-Sqlcmd "SELECT * FROM PkgStatus WHERE Sitecode = 'p01' and Type = 1 and location like '%i$%'"
foreach ($i in $SQLCMD)
	{
	$NewLocation = $i.location.replace("I$", "H$")
	$NewLocation = "'" + $NewLocation + "'"
	$i.ID = "'" + $i.ID + "'"
#	$i.ID
#	$NewLocation
	$NewSQLCMD = "UPDATE PkgStatus SET Location = $NewLocation WHERE Sitecode = 'P01' and Type = 1 and ID = " + $i.ID
	$NewSQLCMD
	Invoke-Sqlcmd $NewSQLCMD
	}

Then I figured I better move the SMSPKGSIG folder as well.  Instructions here:  http://www.myitforum.com/articles/1/view.asp?id=11981

I followed all of this up by creating a package and sending it to the DP in question and all is well.