Powershell – "Search and Count All File Extensions on Multiple Machines using Powershell"

The Issue

Recently I was at a customer that had a very strange and specific requirement. They wanted to query All File Type Extensions with a count on all machines in the environment. This was an environment with 30 000+ machines and they wanted it to run quickly and once off.

The Investigation

System Center Configuration Manager does a great job of file inventory IF you have an exact list of file types supplied. In our trials when you are searching for EVERYTHING or All Extensions(*) the file inventory takes anywhere from 2 ā€“ 4 hours PER MACHINE and sometimes times out. This is because of the generation of an XML file that queries the Windows Management Instrumentation (WMI). It is quite resource intensive on the machines/endpoints and not even to mention the Management Point having to process all this as well as SQL.

This wastes a lot of System Center Configuration Manager system resources that could have been used to keep clients healthy.

A custom PowerShell script was written and deployed via the package deployment option in System Center Config Manager. This will run anywhere from 2 ā€“ 10 minutes PER MACHINE (As per our testing) and then copy a CSV file to a remote share directory. This runs in the background and the user is not affected at all.
The CSV files seem to be 500KB – 1Mb in size and does a count of all file extensions in the specific machine. (See screenshot below)

The Powershell Script

$Stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
$compname = hostname
$exlcudedPaths = @("Windows");
$searchPaths = Get-ChildItem -Path C:\ -Recurse | Where-Object {$exlcudedPaths -notcontains $_.Name} | group extension -NoElement | sort count -Desc
$searchPaths |Select Count, Name |Export-Csv "\\*YOURSHARE*\$compname.csv" -NoClobber -NoTypeInformation
$Stopwatch.Elapsed | Select TotalSeconds

The Solution

Deploy this via System Center Configuration Manager

  1. Create a share accessible via all clients on the network. They will read the .ps1 file from there and I used that same share to dump the CSV files

2. Create a package that contains no source files

3. Create a program in that package that contains a command line to execute the script Hidden

Powershell.exe -executionpolicy bypass -file "\\*YOURSHARE*\CollectCountAllFileExtensionsV3.ps1"

4. Deploy this to your required collection of machines

5. The CSV files will start to get created in the share as soon as the script is run.

Now you can either take those files and write them into a database or query specific machines for the count of file extensions. This is by no means the easiest or most recommended way to count file extensions but it is fast and met our requirements.

I hope this information is valuable to you and feel free to correct me in any steps.