Month: May 2015

Posted on

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)"
            }
        }
Posted on

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

Posted on

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',

[email protected] = 'Y',

@LogToTable = 'Y'
  • Schedule the job
    • Daily at 23:30