May 2015 – The EmptyGarden

May 10, 2015

SCCM Software updates automation

Software Updates synchronize daily at 14:00

Software updates classifications:

Select the Classifications you want SCCM WSUS to synchronize
Select the Products you want SCCM WSUS to synchronize

Create collections for Software Updates

Device Collections > Workstation Clients > Software Updates

All collections are based off of the All Workstation Clients collection

Software Updates Workstation – Monday
- SCCM Resource ID %0 or %1
  - Maintenance Window – Software Updates only – 19:00-07:00
Software Updates Workstation – Tuesday
- SCCM Resource ID %2 or %3
  - Maintenance Window – Software Updates only – 19:00-07:00
Software Updates Workstation – Wednesday
- SCCM Resource ID %4 or %5
  - Maintenance Window – Software Updates only – 19:00-07:00
Software Updates Workstation – Thursday
- SCCM Resource ID %6 or %7
  - Maintenance Window – Software Updates only – 19:00-07:00
Software Updates Workstation – Friday
- SCCM Resource ID %8 or %9
  - Maintenance Window – Software Updates only – 19:00-07:00

Created saved searches in the Software Updates > All Software Updates node

All saved searches have the following rules

Expired = No
Metadata Only = No
Superseded = No
Severity = Critical or Important
Update Classification = Critical or Security
Title = does not contain Itanium
- 2000 – 2010 – Critical and Important
- 2011 – Critical and Important
- 2012 – Critical and Important
- 2013 – Critical and Important
- 2014 – Critical and Important
- 2015 – Critical and Important

Created saved searches in the Software Updates > All Software Updates node

All saved searches have the following rules

Expired = No
Metadata Only = No
Superseded = No
Severity = Critical or Important
Update Classification = Critical or Security
Title = does not contain Itanium
Required >= 1
Deployed = NO
- 2000 – 2010 – Critical and Important – Required Not Deployed
- 2011 – Critical and Important – Required Not Deployed
- 2012 – Critical and Important – Required Not Deployed
- 2013 – Critical and Important – Required Not Deployed
- 2014 – Critical and Important – Required Not Deployed
- 2015 – Critical and Important – Required Not Deployed

Created saved search in Software updates > All Software updates node

Endpoint Protection

Expired = No
Metadata Only = No
Superseded = No
Product = Forefront Endpoint Protection 2010

Created Deployment Packages for Software Updates

2000 – 2010 – Critical or Important
2011 – Critical and Important
2012 – Critical and Important
2013 – Critical and Important
2014 – Critical and Important
2015 – Critical and Important

Created Automatic Update Rule

Workstations – 2015 – Critical or Important – ADR

This rule will create a new software update group each time it is run

At the end of 2015 there should be 12 software update groups for this rule

The rule once on the 2nd Tuesday of each month at 17:00

This rule targets the BETA workstations collection

Date released or revised = Last month
Severity = Critical or Important
Superseded = No
Update Classification = Critical or Security

Created Automatic Update Rule

Endpoint Protection – ADR

This rule will add to an existing software update group each time it is run

The rule runs after every Software Update Synchronization

Product = Forefront Endpoint Protection 2010
Superseded = no

Created Software Update Groups based on the above saved searches

2000-2010 – Critical or Important
2011 – Critical or Important
2012 – Critical or Important
2013 – Critical or Important
2014 – Critical or Important
2015 – Critical or Important 2015-01-13
2015 – Critical or Important 2015-02-10

Created PowerShell script to automate software updates process

The process for Software Updates is as follows:

All Software Updates will be deployed to the Device Collection > Departments > All Workstation Clients – BETA
All Software Updates will be deployed to the Device Collection > Workstation Clients > All Workstation Clients collection for PRODUCTION pushes

The following SQL Agent jobs need to be created

2nd Tuesday of the Month

14:0
SQL Server Agent job “Set-CMSoftwareUpdatesDeploymnet -Beta -EnableMaintenanceWindows” runs as a domain account SQL server agent PowerShell proxie

This sets all of the Software Update Group deployments to the BETA collection and instructs the deployment to respect maintenance windows

14:00

Software Update Point Synchronization runs (daily)
14:00 + [however long it takes the synchronization process to run] – Automatic Deployment Rule “Workstations – 2015 – Critical or Important – ADR” runs

This will create a new Software Update Group deployed to the BETA collection that respect Maintenance Window

3rd Tuesday of the Month

18:00
SQL Server Agent job “Set-CMSoftwareUpdatesDeploymnet -Production -EnableMaintenanceWindows” runs as a domain account SQL server agent PowerShell proxi

This sets all of the Software Updates Group deployments to “All Workstation Clients” collection and instructs to deployment to respect maintenance windows

1st Tuesday of the Month

18:00
- SQL Server Agent job “Set-CMSoftwareUpdatesDeploymnet -Production -DisableMaintenanceWindows” runs as a domain account SQL server agent PowerShell proxie
  - This sets all of the Software Updates Group deployments to “All Workstation Clients” collection and instructs to deployment to ignore maintenance windows

2nd Tuesday of the month

17:00

SQL Server agent job “Start-CleanCMSoftwareUpdateGroup” runs as a domain account SQL server agent PowerShell proxie
This script calls scripts on the SQL server

This scripts cleans out Expired and Superseded updates from all Update Groups and Packages except for Endpoint protection update groups and packages.

NOTE: The script doesn’t work with Endpoint protection updates.

[CmdletBinding()]
Param
    (
    [Switch]$Beta, # This switch will target the software updates to the Beta collection,
    [Switch]$Production, #This switch will target the software updates to the Production collection
    [Switch]$EnableMaintenanceWindows, #This switch will cause the deployment to respect a clients maintenance windows
    [Switch]$DisableMaintenanceWindows, #This switch will cause the deployment to over-ride a clients maintenance windows
    [Parameter(Mandatory=$True, Position=1)]
        [string]$SCCM_Server #This parameter is the NETBios or FQDN of the SCCM Site Server
    )
    
    $BetaCollectionName = "BETA"
    $ProductionCollectionName = "All Workstation Clients"

    #$BetaCollectionName = "Beta"
    #$ProductionCollectionName = "Production"

    $PSVersion = $host.Version.Major
    Write-Verbose "PowerShell version is : $PSVersion"

    Write-Verbose "Determining SiteCode for Site Server: '$($SCCM_Server)'"

    If ($PSVersion -ge 3)
        {
        $SCCM_CimSession = New-CimSession -ComputerName $SCCM_Server
        $SiteCodeObjects = Get-CimInstance -CimSession $SCCM_CimSession -Namespace "root\SMS" -Class SMS_ProviderLocation -ErrorAction Stop
        }
    Else
        {
        $SiteCodeObjects = Get-WmiObject -ComputerName $SCCM_Server -Namespace "root\SMS" -Class SMS_ProviderLocation -ErrorAction Stop
        }

    foreach ($SiteCodeObject in $SiteCodeObjects) 
        {
        if ($SiteCodeObject.ProviderForLocalSite -eq $true) 
            {
            $SCCM_SiteCode = $SiteCodeObject.SiteCode
            Write-Verbose "SiteCode: $($SCCM_SiteCode)"
            }
        }

    If ($EnableMaintenanceWindows)
        {
        $RebootOutsideOfServiceWindows = $FALSE
        $OverrideServiceWindows = $FALSE
        Write-Verbose "RebootOutsideOfServiceWindows = $RebootOutsideOfServiceWindows"
        Write-Verbose "OverrideServiceWindows = $OverrideServiceWindows"
        }
    If ($DisableMaintenanceWindows)
        {
        $RebootOutsideOfServiceWindows = $TRUE
        $OverrideServiceWindows = $TRUE
        Write-Verbose "RebootOutsideOfServiceWindows = $RebootOutsideOfServiceWindows"
        Write-Verbose "OverrideServiceWindows = $OverrideServiceWindows"
        }
    If ($Beta)
        {
        If ($PSVersion -ge 3)
            {
            $BetaCollectionID = Get-CimInstance -CimSession $SCCM_CimSession -Namespace "ROOT\SMS\Site_$($SCCM_SiteCode)" -Query "Select * from SMS_Collection where name = '$($BetaCollectionName)'"
            }
        else
            {
            $BetaCollectionID = Get-WmiObject -computername $SCCM_Server -Namespace "ROOT\SMS\Site_$($SCCM_SiteCode)" -Query "Select * from SMS_Collection where name = '$($BetaCollectionName)'"
            }
        $TargetCollectionID = $BetaCollectionID.CollectionID
        Write-Verbose "Beta collection name = $($BetaCollectionID.name) and collectionID = $($BetaCollectionID.CollectionID)"
        Write-Verbose "TargetCollectionID = $TargetCollectionID"
        }
    If ($Production)
        {
        If ($PSVersion -ge 3)
            {
            $ProductionCollectionID = Get-CimInstance -CimSession $SCCM_CimSession -Namespace "ROOT\SMS\Site_$($SCCM_SiteCode)" -Query "Select * from SMS_Collection where name = '$($ProductionCollectionName)'"
            }
        Else
            {
            $ProductionCollectionID = Get-WmiObject -computername $SCCM_Server -Namespace "ROOT\SMS\Site_$($SCCM_SiteCode)" -Query "Select * from SMS_Collection where name = '$($ProductionCollectionName)'"
            }
        $TargetCollectionID = $ProductionCollectionID.CollectionID
        Write-Verbose "Production collection name = $($ProductionCollectionID.name)  and collectionID = $($ProductionCollectionID.CollectionID)"
        Write-Verbose "TargetCollectionID = $TargetCollectionID"
        }    
    
    If ($PSVersion -ge 3)
        {
        $SMS_UpdateGroupAssignment = Get-CimInstance -CimSession $SCCM_CimSession -Namespace "ROOT\SMS\Site_$($SCCM_SiteCode)" -Query "Select * from SMS_UpdateGroupAssignment"
        }
    Else
        {
        $SMS_UpdateGroupAssignment = Get-WmiObject -ComputerName $SCCM_Server -Namespace "ROOT\SMS\Site_$($SCCM_SiteCode)" -Query "Select * from SMS_UpdateGroupAssignment"
        }
    $SoftwareUpdateDeployents = $SMS_UpdateGroupAssignment | where {$_.AssignmentName -notlike "*Endpoint*" -and $_.AssignmentName -like "*Workstation*"}
    Foreach ($Deployment in $SoftwareUpdateDeployents)
        {
        Write-Verbose $Deployment.AssignmentName
        If ($RebootOutsideOfServiceWindows -ne $NULL -and  $OverrideServiceWindows -ne $NULL)
            {
            $Deployment.RebootOutsideOfServiceWindows = $RebootOutsideOfServiceWindows
            $Deployment.OverrideServiceWindows = $OverrideServiceWindows

            If ($PSVersion -ge 3)
                {
                Set-CimInstance -CimSession $SCCM_CimSession -CimInstance $Deployment -OperationTimeoutSec 60
                }
            Else
                {                      
                $Deployment.put()
                }
            Write-Verbose "$($Deployment.AssignmentName) RebootOutsideOfServiceWindows is set to $($Deployment.RebootOutsideOfServiceWindows)"
            Write-Verbose "$($Deployment.AssignmentName) OverrideServiceWindows is set to $($Deployment.OverrideServiceWindows)"
            }
        If ($ProductionCollectionID -or $BetaCollectionID)
           {
           $Deployment.TargetCollectionID = $TargetCollectionID
           If ($PSVersion -ge 3)
               {
               Set-CimInstance -CimSession $SCCM_CimSession -CimInstance $Deployment -OperationTimeoutSec 60
               }
            Else
                {
                $Deployment.put()
                }
            Write-Verbose "$($Deployment.AssignmentName) target collectionID is set to $($Deployment.TargetCollectionID)"
            }
        }

May 10, 2015

SCCM–WSUS Maintenance

http://blog.coretech.dk/kea/house-of-cardsthe-configmgr-software-update-point-and-wsus/

Increased the Private Memory Limit (KB) to 6291456 (6GB) on the WSUS Server

Open IIS
Select Application Pools
Right click the WSUS app pool and select Advanced Settings
Find the Private Memory Limit (KB) setting and adjust.

Example: Your server has 8GB. You might want to provide a memory limit of 5GB on the WSUS App Pool.

Open Powershell
Type: 5GB / 1KB
Copy the answer into the Private Memory Limit (KB) setting

Right click the App Pool and select recycle

Ensure that the WSUS database is being indexed. See the foloowing post: https://emptygarden.info/2015/05/10/sql-maintenance-with-olas-script/

Create a new SQL Server Agent job to cleanup the WSUS server. This job will be a powershell script. So, we will need to create a SQL server credential to accomplish this.

Create SQL Server Credntial

Open SQL Server Management Studio
Expand Security
Right click on Credentials

Enter in a credential that has rights to run the PowerShell command below

Map the SQL Server Credential to a SQL Server Agent Proxy

In SQL Server Management Studio expand SQL Server Agent
Right click Proxies
Select New Proxie
use the credential you created above and map it to a PowerShell command

Create a new SQL Server Agent job that runs a PowerShell script using the above Proxie.

Enter the following as the PowerShell command

PowerShell.exe -command “invoke-command –computername [SCCM WSUS server] -scriptblock {Get-WsusServer | Invoke-WsusServerCleanup –CleanupObsoleteUpdates -CleanupUnneededContentFiles -CompressUpdates -DeclineExpiredUpdates -DeclineSupersededUpdates}”

Have the SQL Server Agent job save the results to a text file.

Runs Daily at 22:30

May 10, 2015

SQL maintenance with Ola’s script

Steve Thompson (MVP) has an excellent blog describing how to implement SQL maintenance scripts for SCCM. https://stevethompsonmvp.wordpress.com/2013/05/07/optimizing-configmgr-databases/

As you know, there is a a SCCM Site Maintenance job titled Rebuild Indexes. Steve Thompson goes into some detail on his blog about why this doesn’t work…..at all!!

Enter: Ola Hallengren SQL scripts: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Basically:

Create a database and table to track the results of the indexing / statistics process

		-- USE [master]
		GO
		
		IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'CMMonitor')
		DROP DATABASE [CMMonitor]
		GO
		
		-- USE [master]
		GO
		
		-- CREATE DATABASE [CMMonitor] ON  PRIMARY 
		( NAME = N'CMMonitor', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\CMMonitor.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
		 LOG ON 
		( NAME = N'CMMonitor_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\CMMonitor_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 5120KB )
		GO
		
		ALTER DATABASE [CMMonitor] SET COMPATIBILITY_LEVEL = 100
		GO
		
		IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
		begin
		EXEC [CMMonitor].[dbo].[sp_fulltext_database] @action = 'enable'
		end
		GO
		
		ALTER DATABASE [CMMonitor] SET ANSI_NULL_DEFAULT OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET ANSI_NULLS OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET ANSI_PADDING OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET ANSI_WARNINGS OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET ARITHABORT OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET AUTO_CLOSE OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET AUTO_CREATE_STATISTICS ON 
		GO
		
		ALTER DATABASE [CMMonitor] SET AUTO_SHRINK OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET AUTO_UPDATE_STATISTICS ON 
		GO
		
		ALTER DATABASE [CMMonitor] SET CURSOR_CLOSE_ON_COMMIT OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET CURSOR_DEFAULT  GLOBAL 
		GO
		
		ALTER DATABASE [CMMonitor] SET CONCAT_NULL_YIELDS_NULL OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET NUMERIC_ROUNDABORT OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET QUOTED_IDENTIFIER OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET RECURSIVE_TRIGGERS OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET  DISABLE_BROKER 
		GO
		
		ALTER DATABASE [CMMonitor] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET DATE_CORRELATION_OPTIMIZATION OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET TRUSTWORTHY OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET ALLOW_SNAPSHOT_ISOLATION OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET PARAMETERIZATION SIMPLE 
		GO
		
		ALTER DATABASE [CMMonitor] SET READ_COMMITTED_SNAPSHOT OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET HONOR_BROKER_PRIORITY OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET  READ_WRITE 
		GO
		
		ALTER DATABASE [CMMonitor] SET RECOVERY SIMPLE 
		GO
		
		ALTER DATABASE [CMMonitor] SET  MULTI_-- USER 
		GO
		
		ALTER DATABASE [CMMonitor] SET PAGE_VERIFY CHECKSUM  
		GO
		
		ALTER DATABASE [CMMonitor] SET DB_CHAINING OFF 
		GO

Download the ‘MaintenanceSolution.sql’ script from here: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
- Change the — USE [Master] line to — USE [CMMonitor]
Optional: Delete the following SQL Agent Jobs that were created using the above script
- All Backup jobs
- Output File Cleanup job
- SP_Delete_backuphisorty job
- Syspolicy_purge_history job
Schedule the CommandLog Cleanup job
- Weekly – Sunday – 21:00
Schedule the DatabaseIntegrityCheck – System Databases job
- Daily at 22:00
Schedule the DatabaseIntegrityCheck – User databases job
- Daily at 22:00
Edit the IndexOptimize – User_Databases job
- Rename the Step 1 to “IndexOptimize – CM_[SITE CODE] and SUSDB”
- Modify the step to be a T-SQL type
- Edit the comand:

EXECUTE dbo.IndexOptimize

@Databases = "CM_[SCCM SITE CODE], SUSDB",

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 10,

@FragmentationLevel2 = 40,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y',

--@SortInTempdb = 'Y',

@LogToTable = 'Y'

Schedule the job
- Daily at 23:30