Report My Documents Size in Configuration Manager

Configuration Manager has the ability to roll out the OneDrive for Business Managed Folder Backup (formally known as Known folder move) feature. This feature redirects the users Desktop, Documents, and Pictures folders to OneDrive for Business. For my company this was great news. It allows our users to work as normal and in the background the data is being sent to the Cloud.

I am not going to write on how to perform this redirection. This website, https://docs.microsoft.com/en-us/configmgr/compliance/deploy-use/onedrive-profile, has detailed information on using the feature. Instead, I am going to write about what for my company was a significant concern: bandwidth. If you have sites with small bandwidth, you need to plan the deployment of OneDrive Managed Folder Backup carefully. Configuration Manager is an excellent tool for rolling out this feature. You can target as many or as few computers as your bandwidth can handle to get the deployment done.

The first step is to gather data. How much data needs to be uploaded? Since this information is all user-based, using hardware inventory would be difficult. Microsoft provides a script to monitor the deployment, https://aka.ms/kfmStatus. I had initially tried to modify this PowerShell script to collect the information and report in Hardware Inventory. First, I wrote to the registry; however, this required administrator permissions. Next, I modified to write directly to the WMI; but again, this required administrator permissions. Our users are not local administrators on the computer, so using hardware inventory was not possible. The solution I found fits perfectly with user settings: Configuration Items and Configuration Baselines.

In this case, I was not looking for if the baseline was compliant or non-compliant; I just wanted the size of the Documents folder for each user on the computer. The running of the configuration item records the value returned from the script. That “v_CIComplianceStatusDetail” view in SQL stores the value. Once it is in SQL, I can report on it.

Step 1 Create the Configuration Item

• Open the Configuration Manager Console
• Open the Assets and Compliance Workspace
• Select Compliance Settings / Configuration items
• Select Create Configuration Item from the ribbon
• Provide a name, i.e, CI_MyDocumentsSize
• Click on the Next button

• Select the operating system this CI will run on.
• Click on the Next button.

• Click on the New button to create the settings
• Provide the name, i.e, Settings_MyDocumentsSize
• Settings Type: Script
• Data Type: Floating point
• Click on Add Script in the Discovery Script section
• Script Language is PowerShell
• Paste the following script in the script box

Function InventoryDirectory {
     param (
         # Report folder
         [parameter(Mandatory=$true)][string]$DirectoryToInventory
     )

     # Check if directory exists
     If (Test-Path $DirectoryToInventory) {
         # Find the size
         $DirectorySize = (Get-ChildItem $DirectoryToInventory -Recurse | Measure-Object -Property length -Sum).Sum
         if ($DirectorySize) { # ensure size is not zero
             $DirectorySizeMB = ($DirectorySize / 1MB)
             $DirectorySizeMB = [math]::Round($DirectorySizeMB,2)
         } else {
             $DirectorySizeMB = "0"
         }
         Return $DirectorySizeMB
     }
}
# ~~~~~~~~~~~~~~~~
# Begin
# ~~~~~~~~~~~~~~~~
# Set user directory
# Don't use $env:USERPROFILE or $env:USERNAME since script needs to run as admin

$Username = ([Security.Principal.WindowsIdentity]::GetCurrent()).Name.Replace("$env:USERDOMAIN\","")
$UserDir = "C:\Users\$Username"
$DirSize = InventoryDirectory -DirectoryToInventory "$UserDir\Documents"
$DirSize

• Check the checkbox for “Run scripts by using the logged on user credentials”
• Click on the Compliance Rules tab

• Click on the New button to create the compliance rule
• Provide the Name, i.e, Rule_MyDocuments
• Set the rule to be Greater than 0.
• for Non Compliance severity for reports leave at the default of None
• Click on the OK button

• Click on the OK button again at the Create Settings dialog box
• Click on the Next button at the Settings dialog box
• Click on the Next button at the Compliance Rules dialog box
• Review the summary and click the Next button.
• Click the Close button at the Completion dialog box

Step 2 Create the Baseline

• Select the Configuration Baselines node
• Select “Create Configuration Baseline” from the ribbon.
• Provide a name, i.e, My Documents Size CB
• Click on the Add button
• Select Configuration Items
• Click the configuration Item created above and click on the Add button
• Click on the OK button
• Click on the OK button

Step 3 Deploy the Baseline

• Select the baseline created above
• Click on the “Deploy” option on the ribbon
• Select a device collection to pilot the deployment
• You can select either a simple schedule or a custom schedule. Since I have to follow a change management process, I use a custom schedule to start on the date that is approved in the change form
• Click on the OK button

Step 4 Create the SQL Query for the report

Once the baseline has completed on several computers, I created a SQL script to return the results. Below is the SQL script I created. Please note I am not a SQL scripting expert. I know enough to Google and find the functions I need. The first issue I ran into was that computers with the Romance Languages (such as French, German, and Spanish) installed format numbers differently than we do in the United States. In the United States, the decimal indicator between numbers is a period. In these other countries, it is a comma. The query needed to account for that and display all numbers in the same format. I included the IP Subnet location for the computer in the report to allow the network team to identify the amount of data at each site.

SELECT DISTINCT Netbios_Name0 'Barcode',UserName 'User'
,CASE WHEN substring(ci.currentvalue,LEN(ci.currentvalue)-2,1) = ','
	THEN cast((replace(ci.currentvalue,',','.')) as decimal(12,2))
    WHEN substring(ci.currentvalue,LEN(ci.currentvalue)-1,1) = ','
	THEN cast((replace(ci.currentvalue,',','.')) as decimal(12,2))
    WHEN ci.CurrentValue like '%[a-z]%' THEN NULL
    ELSE cast(ci.CurrentValue as decimal (12,2))
    END as 'Documents Size (MB)'
, LastComplianceMessageTime
,CASE WHEN charindex(',',adapter.ipaddress0,1) > 1
    THEN substring(adapter.IPAddress0,1,(charindex(',',adapter.ipaddress0,1)-1))
    ELSE adapter.ipaddress0
    END AS IPAddress
,CASE WHEN charindex(',',adapter.DefaultIPGateway0,1) > 1
    THEN substring(adapter.DefaultIPGateway0,1,(charindex(',',adapter.DefaultIPGateway0,1)-1))
    ELSE adapter.DefaultIPGateway0
    END AS 'Default Gateway'
,adapter.IPSubnet0 'Subnet'

FROM v_CIComplianceStatusDetail ci
LEFT JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION adapter on adapter.ResourceID = ci.ResourceID and adapter.IPAddress0 is not null and adapter.ServiceName0 not in ('jnprva','VMnetAdapter','VMSNPXYMP')
where ConfigurationItemName like 'CI_My%

Step 5 Create an SSRS Report

The details of writing the SSRS report is outside the scope of this blog. I created an elementary report that people could open and then export the data to Excel for more formatting if needed. I am attaching the SSRS file to the blog, to be available for download.