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