Recently I noticed that the available disk space on my ConfigMgr distribution points was getting low. Since they are mostly VMs using SAN storage it’s fairly easy to increase the disk space, but it got me thinking about how I could monitor the DPs for available free space, as well as the sizes of the key directories that ConfigMgr is using to store large amounts of data, including the SCCMContentLib, the legacy package share (SMSPKGx$), and in my case, the WSUSUpdates folder, as I also use our DPs as WSUS servers for patching during OSD. These directories will certainly increase in size over time, as new content is distributed and new patches are downloaded.
There are, of course, many ways to monitor the overall free disk space, including WMI, SNMP, various 3rd party software, your corporate monitoring tool and even ConfigMgr itself. But monitoring the ConfigMgr/WSUS directory sizes can be a little trickier. Rather than purchase some new software to do it, I wanted to build a ‘free’ solution using tools already available to me in the Enterprise. I also wanted to have all this data for all our distributions points displayed graphically in a report.
Since Microsoft Excel is my favourite reporting tool, I decided to build a workbook that has a data connection to a SQL database where I will store the disk and directory information from the DPs. Then I run a PowerShell script on each of the DPs as a scheduled task that will get the information I need and insert it into the SQL database.
The result is a nice graphical report that displays all the data I want and keeps itself up-to-date automatically. You can download a sample report here.
Here is a step-by-step guide to create such a report.
Create a SQL Database and Table
First, we will create an SQL database where we will store this data. You could, of course, use an existing database as we only need a single table to store this data, but just for good organisation I will create a new one for this purpose. Here is a sql script that will create the database, create a table, and create records for each of my distribution points with blank data values for now. Edit the script with the hostnames of your distribution points, and run the script in your SQL instance.
CREATE DATABASE SCCM_Server_Data
GO
USE SCCM_Server_Data;
CREATE TABLE DiskSpace (
ID INT NOT NULL,
Server VARCHAR (20),
WSUS_Updates DECIMAL(6,2),
SMSPKGx DECIMAL(6,2),
SCCMContentLib DECIMAL(6,2),
DiskCapacity DECIMAL(6,2),
AvailableSpace DECIMAL(6,2),
PRIMARY KEY (ID)
);
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (1, 'SCCMDP-01', 0, 0, 0, 0, 0 );
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (2, 'SCCMDP-02', 0, 0, 0, 0, 0 );
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (3, 'SCCMDP-03', 0, 0, 0, 0, 0 );
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (4, 'SCCMDP-04', 0, 0, 0, 0, 0 );
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (5, 'SCCMDP-05', 0, 0, 0, 0, 0 );
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (6, 'SCCMDP-06', 0, 0, 0, 0, 0 );
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (7, 'SCCMDP-07', 0, 0, 0, 0, 0 );
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (8, 'SCCMDP-08', 0, 0, 0, 0, 0 );
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (9, 'SCCMDP-09', 0, 0, 0, 0, 0 );
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (10, 'SCCMDP-10', 0, 0, 0, 0, 0 );
INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (11, 'SCCMDP-11', 0, 0, 0, 0, 0 );
SQL Permissions
I recommend to create an SQL login to the database you created that has only ‘db_datareader’ permission. This way you can include the credentials required to get data from the database in the report, and not have to give SQL database permissions to everyone you want to share the report with. If your SQL Instance is not using Mixed-mode authentication, you can easily change this. I created a SQL login called ‘ConfigMgrDB_Read’ for this purpose (I use the same login to access my ConfigMgr database for reporting purposes).
Note: The SQL credentials will be stored in clear text in the Excel data connection file, but since they are read-only permission, it shouldn’t be a security concern.
Create a PowerShell Script and Scheduled Task on each DP
Next, we need to create a PowerShell script that will get the directory sizes of the following locations:
- SCCMContentLib (SCCM Content Library)
- SMSPKGx$ (SCCM Legacy Package Share)
- WSUS_Updates (WSUS Update files location)
We will also get the disk capacity and amount of free space remaining.
We will put this data into a simple formatted text table that can be sent in an email. We will then create a connection to the SQL database where we will store this data, and update the records with the new data values.
I recommend to run this script as a scheduled task on each DP, rather than running it against multiple DPs from one location, especially if your DPs are across a WAN, as the Get-ChildItem commands run much quicker locally than remotely.
The account running this script must have permission to input data to the SQL database.
You will need to change some things in the script for your environment:
- Line 4: Enter the hostname of the server you are running the script on and for
- Lines 7-11: Enter the email details
- Lines 16,19,22: Enter the locations of the directories you want to get the size for
- Line 25: Change the drive letter for your DP data drive
- Lines 47,48: Enter the SQL Instance and database name
## Variables
# Enter ComputerName
$ComputerName = "SCCMDP-01"
# Enter email details
$SendEmail = "Yes"
$smtpServer = "mysmtpserver@mydomain.com"
$smtpFrom = "$ComputerName@mydomain.com"
$smtpTo = "MyRecipients@mydomain.com"
$messageSubject = "Drive Space Report for $ComputerName"
## Get and email the data
# Get drive and directory data
$colItems = (Get-ChildItem G:\WSUS_Updates -recurse | Measure-Object -property length -sum)
$WSUS_Updates = "{0:N2}" -f ($colItems.sum / 1GB)
$colItems = (Get-ChildItem G:\SMSPKGG$ -recurse | Measure-Object -property length -sum)
$SMSPKGG = "{0:N2}" -f ($colItems.sum / 1GB)
$colItems = (Get-ChildItem G:\SCCMContentLib -recurse | Measure-Object -property length -sum)
$SCCMContentLib = "{0:N2}" -f ($colItems.sum / 1GB)
$data = Get-WmiObject -Class Win32_Volume -ComputerName $ComputerName | Where-Object {$_.DriveLetter -eq 'G:'} | Select Capacity,FreeSpace,SystemName,DriveLetter
$capacity = "{0:N2}" -f ($data.capacity / 1GB)
$freespace = "{0:N2}" -f ($data.freespace / 1GB)
$systemname = $data.SystemName
$driveletter = $data.DriveLetter
# Add data to a simple text 'table'
$table = "All values in GB`nServer:`t`t`t$systemname`nWSUS_Updates:`t$WSUS_Updates`nSMSPKGx:`t`t$SMSPKGG`nSCCMContentLib:`t$SCCMContentLib`nDiskCapacity:`t`t$capacity`nAvailableSpace:`t`t$freespace"
# Send data as an email
if ($SendEmail -eq "Yes")
{
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.Body = $table
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
}
# SQL Stuff
# Database info
$dataSource = “MySQLServer\INST_SCCM”
$database = “SCCM_Server_Data”
# Open a connection
cls
Write-host "Opening a connection to '$database' on '$dataSource'"
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
# Set and run the SQL Command
$update = "UPDATE DiskSpace SET WSUS_Updates = '$WSUS_Updates', SMSPKGx = '$SMSPKGG', SCCMContentLib = '$SCCMContentLib', DiskCapacity = '$capacity', AvailableSpace = '$freespace' WHERE SERVER = '$ComputerName'"
$command = $connection.CreateCommand()
$command.CommandText = $update
$command.ExecuteNonQuery()
# Close the connection
$connection.Close()
Note: if you don’t want to receive the email from each DP, simply put ‘No’ in the $SendEmail variable, although it’s a useful way to verify that the Scheduled Task has run.
Now create a scheduled task on the DP with the schedule of your choice and point to the location of your Powershell script:
- Action: Start a program
- Program/script: Powershell
- Add arguments: -Command “C:\LocalScripts\DP-DiskSpaceScript.ps1” -ExecutionPolicy Bypass
You can manually start the task to verify that it works, you should receive an email like below, and data should be entered into the SQL table in the record for that server. In this case, I now have lots of free space. Nice 🙂
Create an Excel Workbook
Create the Data Connection
Open a new blank workbook in Microsoft Excel (I’m using Office 2013), right-click the tab for Sheet1 and rename it Data.
On the Data ribbon, in the Get External Data section, click From Other Sources and choose From SQL Server.
In the Data Connection Wizard, enter the name of the Instance that contains your database, and enter the SQL account credentials. Click Next.
Select the SCCM_Server_Data database from the drop-down list, then simply click next. The database only contains one table, and we will pull the entire table into the workbook, so no need for a specific SQL query.
Rename the File Name and Friendly Name of the data connection file if you wish, then click Finish.
In the Import Data window, make sure to choose Table and the cell range =$A$1 in the existing worksheet, then click Ok.
You will now see the data from the database in the worksheet:
Finally, go to the Data ribbon again and click Connections. Make sure your data connection is selected, the click Properties. Check the option to Refresh data when opening the file. This will make sure that you always get the current data from the database every time the file is opened.
On the Definition tab, check the box Save password, click Yes at the prompt to save without encryption, then click OK to save and close the Connection Properties.
Close the Workbook Connections window.
Create a Summary Chart
Now that we have the data, let’s create some nice charts to display it.
First, select all the populated cells in the Data sheet, except for the ‘ID’ column, then right-click the Data tab and choose Insert… Choose Chart and click Ok.
You should see a chart automatically added for you. Let’s make it look a bit nicer. (These customizations are just my own suggestions, of course. Do what you like :-))
Rename the Chart tab to Disk Space Summary.
Click on the Chart, then choose Change Chart Type from the Chart ribbon. In the Column charts, select 3D Column and click Ok.
Double-click the chart title and change it to SCCM DP Disk Space Summary
Cool, looking better to my eye.
Create Additional Charts
Now let’s create some additional charts so we can compare the data on their own scales.
Go back to the Data sheet and select all the populated cells in the Server column, and all the populated cells in the WSUSUpdates column, including the headers.
Right-click on the Data tab and choose Insert… Choose Chart and click Ok. Change the chart type again, this time choosing 3D Clustered Column.
Rename the chart tab to WSUSUpdates.
Job done.
Now do the same for each of the remaining data columns in the Data sheet, selecting the populated cells in the Server column, and the next data column, and adding charts.
There you have it. Now you can keep your eye on those SCCM DPs, know the sizes of the key content directories, observe how they are growing over time, and make sure there is plenty of free disk space available!
Note: you could also create an SSRS report, if you prefer, with the SQL data and add the report to your existing ConfigMgr reports folder.
!Tip
If you want the most up-to-date data in your report, then you can manually trigger all the scheduled tasks to run immediately using PowerShell. If you have PowerShell remoting available, you can run the following script which will trigger the Task on all your distribution points.
You need to enter your DP names in the $server variable, and it assumes that your scheduled tasks all have the same name on each DP.
<#
This script manually triggers the 'DriveSpaceReport' Scheduled Task on each of the DPs listed in the $servers variable
#>
$servers = "sccmsrvdp-01", `
"sccmsrvdp-02, `
"sccmsrvdp-03", `
"sccmsrvdp-04", `
"sccmsrvdp-05", `
"sccmsrvdp-06", `
"sccmsrvdp-07", `
"sccmsrvdp-08", `
"sccmsrvdp-09", `
"sccmsrvdp-10", `
"sccmsrvdp-11", `
"sccmsrvdp-12"
cls
write-host "######################################################"
write-host "Trigger the DriveSpaceReport Scheduled Task on all DPs"
write-host "######################################################"
write-host ""
# Trigger the Scheduled tasks
write-host "Get task status and trigger the task for each server" -ForegroundColor Green
write-host ""
foreach ($Server in $servers)
{
write-host "Processing $server" -ForegroundColor Yellow
Write-Host "Getting Scheduled Task Details for 'DriveSpaceReport'" -ForegroundColor Cyan
schtasks /query /S $server /TN DriveSpaceReport /FO List
Write-Host ""
Write-Host "Run the task..." -ForegroundColor Cyan
schtasks /run /S $server /TN DriveSpaceReport /I
Write-Host ""
}
# Report task status
Write-Host ""
write-host "Get updated task status for each server" -ForegroundColor Green
foreach ($Server in $servers)
{
Write-Host ""
$Task = schtasks /query /S $server /TN DriveSpaceReport /V /FO CSV | ConvertFrom-Csv
$Name = $Task.TaskName
$Status = $Task.Status
$LastRunTime = $Task.'Last Run Time'
$Result = $Task.'Last Result'
Write-host $server -ForegroundColor Cyan
Write-host "TaskName: $Name"
Write-host "Status: $Status"
write-host "Last Run Time: $LastRunTime"
Write-host "Last Result: $Result"
}
