Get the Total Size of All Application Content Files in an SCCM Console Folder

Today I was installing a new ConfigMgr distribution point on a remote site and wanted to start distributing packages to it.  But since the network bandwidth is only 4Mbps to that site, rather than distribute all the required packages in one go I wanted to distribute groups of packages out of working hours when the network is more free and no-one is affected. Yes you can use bandwidth throttling on the distribution point, but I prefer to schedule my distributions out of hours so I don’t need to.

But to find out what time-frame is required to distribute my packages, I need to get an idea of how long a package distribution will take.  To do this, I use a script I published in an earlier blog (actually an updated version I haven’t published yet!) that will allow me to schedule and monitor a package distribution in SCCM, and will send me an email when the distribution is complete telling me how big the package is and how long the distribution took.  This will enable me to get a rough calculation of the amount of time needed to distribute packages, assuming I know the volume of data I need to distribute.

CaptureSo based on the time taken by this boot image, I can distribute 300MB in 20 minutes, so just less than 1GB per hour.  Of course, several factors can affect that time, but it gives a rough guide which is useful enough.

But now how do I find out the volume of data I need to distribute?  Well, lets start with the Application content packages.  I like to organise my Applications into subfolders in the SCCM console, and I have some scripts that will distribute all the packages in the console folder I choose.

Capture

Let’s choose the ‘Non-Default Apps’ folder.  This contains Applications that are not part of our default deployment image.  How do I find out the total size of all the content files in each application in that folder?

Ok, well let’s use some PowerShell 🙂  First, I need to get the ID of that console folder.

$server = "sccmsrv-01"
$SiteCode = "ABC"
$FolderName = "Non-Default Apps" # Applications\Non-Default Apps

# Get FolderID
Write-Host "Getting FolderID of Console Folder '$FolderName'"
$FolderID = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_ObjectContainerNode where Name='$FolderName'" | Select ContainerNodeID
$FolderID = $FolderID.ContainerNodeID
Write-host "  $FolderID"

I have entered my SCCM site server name, site code, and the name of the console folder as variables so the script is portable.  Then I query the WMI on the site server to get the ContainerNodeID of the console folder.

Capture

Now I can query for all Applications in that folder using the FolderID, and get the unique ID, or ‘InstanceKey’ of each one, which equates to the CI Unique ID value for the Application in the SCCM console.  You can add that value in the console from the available fields.

# Get InstanceKey of Folder Members
Write-Host "Getting Members of Folder"
$FolderMembers = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_ObjectContainerItem where ContainerNodeID='$FolderID'" | Select InstanceKey
$FolderMembers = $FolderMembers.InstanceKey
write-host "  Found $($FolderMembers.Count) applications"

Capture

Cool, it’s found 21 Applications in that folder.  Now we need to translate the Unique ID of each Application to its friendly name.  There’s more than one way to do that, but I chose to use the SMS_ObjectName class:

# Get Application name of each Folder member
write-host "Getting Application Names"
$NameList = @()
foreach ($foldermember in $foldermembers)
    {
        $Name = Get-wmiobject -Namespace "ROOT\SMS\Site_$SiteCode" -Query "select Name from SMS_ObjectName where ObjectKey='$foldermember'" | Select -ExpandProperty Name
        $NameList += $Name
    }
$namelist = $NameList | sort

Now that we have the list of Applications we need to find out the size of the content files in the deployment types for each Application.  The slightly annoying thing is that this information is serialized into xml format and stored in a property called SDMPackageXML, so we need to deserialize that property.

First we will import a .Net assembly and add an accelerator to perform deserialization.

# import assemblies
[System.Reflection.Assembly]::LoadFrom(“C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\Microsoft.ConfigurationManagement.ApplicationManagement.dll”) | Out-Null

# Creating Type Accelerators
$accelerators = [PSObject].Assembly.GetType('System.Management.Automation.TypeAccelerators')
$accelerators::Add('SccmSerializer',[type]'Microsoft.ConfigurationManagement.ApplicationManagement.Serialization.SccmSerializer')

Now we can loop through each Application, deserialize the SDMPackageXML property, loop through each deployment type that might be present, and retrieve the size of each file in the contents of each deployment type, adding the size values together as we go and storing them in a variable.

# Deserialize each SDMPackageXML property, and get the file size of each file in the contents for each deployment type
$totalsize = 0
foreach ($name in $namelist)
    {
        write-host "  Deserializing $name"
        $app = [wmi](gwmi -ComputerName $server -Namespace root\sms\site_$code -class sms_application | ?{$_.LocalizedDisplayName -eq $Name -and $_.IsLatest -eq $true}).__Path
        $appXML = [SccmSerializer]::DeserializeFromString($app.SDMPackageXML,$true)
        $DTs = $appxml.DeploymentTypes
        foreach ($DT in $DTs)
            {
                $sizes = $dt.Installer.Contents.Files.Size
                foreach ($size in $sizes)
                    {$totalsize = $totalsize + $size}
            }
    }

Now let’s output the results:

write-host "Total Size of all content files for every application in the '$FolderName' folder is:" -ForegroundColor Green
write-host "$(($totalsize / 1GB).ToString(".00")) GB" -ForegroundColor Green

Capture

So based on my initial test, it could take around 15 hours to distribute 13.4 GB of data!  Wow, better schedule it for the weekend!

Here is the full script:

<#

This script gets the total size of all the content files for each deployment type for each application in the console folder you specify.

#>

$server = "sccmsrv-01"
$SiteCode = "ABC"
$FolderName = "Non-Default Apps" # Applications\Default Apps

# import assemblies
[System.Reflection.Assembly]::LoadFrom(“C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\Microsoft.ConfigurationManagement.ApplicationManagement.dll”) | Out-Null

# Creating Type Accelerators
$accelerators = [PSObject].Assembly.GetType('System.Management.Automation.TypeAccelerators')
$accelerators::Add('SccmSerializer',[type]'Microsoft.ConfigurationManagement.ApplicationManagement.Serialization.SccmSerializer')

# Get FolderID
Write-Host "Getting FolderID of Console Folder '$FolderName'"
$FolderID = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_ObjectContainerNode where Name='$FolderName'" | Select ContainerNodeID
$FolderID = $FolderID.ContainerNodeID
Write-host "  $FolderID"

# Get InstanceKey of Folder Members
Write-Host "Getting Members of Folder"
$FolderMembers = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_ObjectContainerItem where ContainerNodeID='$FolderID'" | Select * | Select InstanceKey
$FolderMembers = $FolderMembers.InstanceKey
write-host "  Found $($FolderMembers.Count) applications"

# Get Application name of each Folder member
write-host "Getting Application Names"
$NameList = @()
foreach ($foldermember in $foldermembers)
    {
        $Name = Get-wmiobject -Namespace "ROOT\SMS\Site_$SiteCode" -Query "select Name from SMS_ObjectName where ObjectKey='$foldermember'" | Select -ExpandProperty Name
        $NameList += $Name
    }
$namelist = $NameList | sort

# Deserialize each SDMPackageXML property, and get the file size of each file in the contents for each deployment type
$totalsize = 0
foreach ($name in $namelist)
    {
        write-host "  Deserializing $name"
        $app = [wmi](gwmi -ComputerName $server -Namespace root\sms\site_$code -class sms_application | ?{$_.LocalizedDisplayName -eq $Name -and $_.IsLatest -eq $true}).__Path
        $appXML = [SccmSerializer]::DeserializeFromString($app.SDMPackageXML,$true)
        $DTs = $appxml.DeploymentTypes
        foreach ($DT in $DTs)
            {
                $sizes = $dt.Installer.Contents.Files.Size
                foreach ($size in $sizes)
                    {$totalsize = $totalsize + $size}
            }
    }

write-host "Total Size of all content files for every application in the '$FolderName' folder is:" -ForegroundColor Green
write-host "$(($totalsize / 1GB).ToString(".00")) GB" -ForegroundColor Green

And here is a script I use to distribute all the packages in a console folder to a single distribution point:

$DP = "sccmsrvdp-02v.contoso.com"
$SiteCode = "ABC"
$FolderName = "Default Apps" # Applications\Default Apps

Write-Host "Getting FolderID of Console Folder '$FolderName'"
$FolderID = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_ObjectContainerNode where Name='$FolderName'" | Select ContainerNodeID
$FolderID = $FolderID.ContainerNodeID
Write-host "  $FolderID"

Write-Host "Getting Members of Folder"
$FolderMembers = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_ObjectContainerItem where ContainerNodeID='$FolderID'" | Select InstanceKey
$FolderMembers = $FolderMembers.InstanceKey

write-host "Getting App Names"
foreach ($Folder in $FolderMembers)
{
$App = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_Application where ModelName='$Folder'" | Select LocalizedDisplayName
$App = $App.LocalizedDisplayName
Write-host "Distributing content for $App to $DP"
Start-CMContentDistribution -ApplicationName $App -DistributionPointName $DP
}

You can distribute any kind of package this way, for example to distribute driver packages instead, query the SMS_DriverPackage class. Replace the last section of the script with:

write-host "Getting DriverPack Names"
foreach ($Folder in $FolderMembers)
{
$DriverPack = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_DriverPackage where PackageID='$Folder'" | Select Name
$DriverPack = $DriverPack.Name
Write-host "Distributing content for $DriverPack to $DP"
Start-CMContentDistribution -DriverPackageName $DriverPack -DistributionPointName $DP
}

WSUS Database Maintenance for SQL Express

To perform database maintenance (defragmentation, re-indexing) on a WSUS database that is installed using SQL Express, I use the following solution.  Because SQL Express has no SQL Server Agent, you cannot create jobs or maintenance plans, but we can use Powershell with a Scheduled Task to perform regular maintenance.  I am using SQL Express 2012.

Download the WSUSDBMaintenance script from the Technet Gallery, and save it to a local folder on your WSUS server/s.

Then use the following Powershell script as a scheduled task to call the sql maintenance script and send an email to yourself with the output of the sql script as a log file.


$WSUSServers = @(
    "wsussrv-01v",
    "wsussrv-02v",
    "wsussrv-03v",
    "wsussrv-04",
    "wsussrv-05v",
    "wsussrv-06v",
    "wsussrv-07v"
    "wsussrv-08",
    "wsussrv-09v",
    "wsussrv-10v",
    "wsussrv-11v"
    )

foreach ($server in $WSUSServers)
    {
        $S = New-PSSession -ComputerName $server
        Invoke-Command -Session $S -ArgumentList $server -ScriptBlock {
        param ($server)
        Invoke-SQLCmd -InputFile C:\LocalScripts\SUSDB_Maintenance.sql -ServerInstance $env:COMPUTERNAME -OutputSqlErrors $True -Verbose *>$env:TEMP\SUSDB_Maintenance_$Server.log
        Send-MailMessage -To Trevor.jones@contoso.com -From Powershell@contoso.com -SmtpServer mysmtpserver -Subject "WSUS DB Maintenance log for $server" -Attachments $env:TEMP\SUSDB_Maintenance_$Server.log
        }
        Remove-PSSession $S
    }


Note that I am performing this maintenance on multiple WSUS servers in succession using an array.

In the Invoke-SQLCmd command, change the inputfile to the location of your sql script.

In the Send-MailMessage command, use the particulars for your environment

Powershell remoting must be available as the command will be run on the WSUS server itself.

If you are using an older version of SQL Express, you may need to add the SQL snapins first as described here.  In SQL Express 2012, the cmdlet is a part of a module that will be imported when you call it (Powershell 3.0 +).

WSUS Server Cleanup Report

There are a few scripts out there that will perform a cleanup of your WSUS server/s, but here’s my contribution 🙂  It uses the Invoke-WSUSServerCleanup cmdlet only available in Windows Server 2012 / Windows 8 onwards, so for previous versions try something like Kaido Järvemets’ script.  This script will perform the WSUS cleanup for any number of WSUS servers, then send you a summary html email report.  It will also give you the time taken for each WSUS server to perform the cleanup, convert the ‘Freed diskpace’ value into a more useful GB value, and report any errors that occurred trying to perform the cleanup.  An error can sometimes occur when maintenance has not been performed for a long time on the server, and the cleanup can timeout.

Simply add your WSUS server names to the $WSUSservers variable, and add the mail settings for your environment. If you are using upstream/downstream WSUS servers, perform the cleanup on the lowermost downstream server first, and work up the heirarchy, as recommended by Microsoft.

You can then run the script as a scheduled task to perform regular maintenance.

Capture


$WSUSServers = @(
    "wsussrv-01v",
    "wsussrv-02v",
    "wsussrv-03v",
    "wsussrv-04v",
    "wsussrv-05v",
    "wsussrv-06v",
    "wsussrv-07",
    "wsussrv-08v",
    "wsussrv-09",
    "wsussrv-10v",
    "wsussrv-11v",
    "wsussrv-12v"
    )

# Mail settings
$smtpserver =  "mysmtpserver"
$MailSubject = "WSUS Cleanup Report"
$MailRecipients = "trevor.jones@contoso.com"
$FromAddress = "WSUS@contoso.com"

# Location of temp file for email message body (will be removed after)
$msgfile = "$env:Temp\mailmessage.txt"

$ErrorActionPreference = "Stop"

#region Functions
function New-Table (
$Title,
$Topic1,
$Topic2,
$Topic3,
$Topic4,
$Topic5,
$Topic6,
$Topic7

)
{ 
       Add-Content $msgfile "<style>table {border-collapse: collapse;font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;}"
       Add-Content $msgfile "h2 {font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;}"
       Add-Content $msgfile "th, td {font-size: 1em;border: 1px solid #87ceeb;padding: 3px 7px 2px 7px;}"
       Add-Content $msgfile "th {font-size: 1.2em;text-align: left;padding-top: 5px;padding-bottom: 4px;background-color: #87ceeb;color: #ffffff;}</style>"
       Add-Content $msgfile "<h2>$Title</h2>"
       Add-Content $msgfile "<p><table>"
       Add-Content $msgfile "<tr><th>$Topic1</th><th>$Topic2</th><th>$Topic3</th><th>$Topic4</th><th>$Topic5</th><th>$Topic6</th><th>$Topic7</th></tr>"
}

function New-TableRow (
$col1, 
$col2,
$col3,
$col4,
$col5,
$col6,
$col7

)
{
Add-Content $msgfile "<tr><td>$col1</td><td>$col2</td><td>$col3</td><td>$col4</td><td>$col5</td><td>$col6</td><td>$col7</td></tr>"
}

function New-TableEnd {
Add-Content $msgfile "</table></p>"}
#endregion


# Create file
New-Item $msgfile -ItemType file -Force | Out-Null

# Add html header
Add-Content $msgfile "<style>h2 {font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;}</style>"
Add-Content $msgfile "<p></p>"
        
# Create a new html table
New-Table -Title "WSUS Cleanup Report $(Get-Date -Format f)" -Topic1 "WSUS Server" -Topic2 "Declined Expired Updates" -Topic3 "Declined Superseded Updates" -Topic4 "Cleaned Obsolete Updates" -Topic5 "Compressed Updates" -Topic6 "CleanedUp Unneeded Content Files" -Topic7 "Time Taken"
        

# Run the cleanup on each server
foreach ($WsusServer in $WSUSServers)
    {
        try
            {
            $startDTM = (Get-Date)
            write-host "Doing cleanup on $WSUSServer"
            $Cleanup = Get-WsusServer -Name $WsusServer -PortNumber 8530 | Invoke-WsusServerCleanup -DeclineExpiredUpdates -DeclineSupersededUpdates -CleanupObsoleteUpdates -CompressUpdates -CleanupUnneededContentFiles
            $endDTM = (Get-Date)
            $Time = $endDTM-$startDTM
            $TimeTaken = $Time.Hours.ToString() + " hours, " + $Time.Minutes.ToString() + " minutes, " + $Time.Seconds.ToString() + " seconds"
            $DiskspaceFreed = $cleanup[4].Split(":")[0] + ":" + ([math]::round(($cleanup[4].Split(":")[1] / 1GB),2)).ToString() + " GB" 
            New-TableRow -col1 $WSUSServer -col2 $Cleanup[1] -col3 $Cleanup[0].Replace("Obsolete Updates Deleted", "Superseded Updates Declined") -col4 $Cleanup[2] -col5 $Cleanup[3] -col6 $DiskspaceFreed -col7 $TimeTaken
            }
        catch
            {
            New-TableRow -col1 $WSUSServer -col2 "Failed to perform cleanup." -col3 $($_.Exception.Message)
            }
    }

# Add html table to file
New-TableEnd

# Set email body content
$mailbody = Get-Content $msgfile

Send-MailMessage -Body "$mailbody" -From $FromAddress -to $MailRecipients -SmtpServer $smtpserver -Subject $MailSubject -BodyAsHtml 

# Delete tempfile 
Remove-Item $msgfile

You can follow the cleanup process on each server from the SoftwareDistribution.log located at %ProgramFiles%\Update Services\LogFiles.