Unable to search directory subtree with WQL query

I am attempting to find all .PST files for the logged on user. However, I seem unable to add a path to 'Drive'. I would like to search all files under C:\users\ + $env:UserName

This command seems to find all PST files on C:\

$Res = Get-CimInstance CIM_DataFile -filter "Drive='c:' AND extension='pst'" -OutVariable m | Select-Object -Property Name

However when I change this to:

$Res = Get-CimInstance CIM_DataFile -filter "Drive='C:\users\' + $env:UserName AND extension='pst'" -OutVariable m | Select-Object -Property Name

or even just:

$Res = Get-CimInstance CIM_DataFile -filter "Drive='C:\users\' AND extension='pst'" -OutVariable m | Select-Object -Property Name

I get the following error:

Get-CimInstance : Invalid query At C:\Users\mntle2\Documents\PSTMig.ps1:2 char:8 + $Res = Get-CimInstance CIM_DataFile -filter "Drive='c:\users\' AND ex ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (:) [Get-CimInstance], CimException + FullyQualifiedErrorId : HRESULT 0x80041017,Microsoft.Management.Infrastructure.CimCmdlets.GetCimInstanceCommand

Can somebody please assist me in resolving this issue?

tl;dr

• In order to perform a CIM-based file search in a directory subtree (as opposed to a single directory), you must compare the Path property with a drive-relative path wildcard expression using the LIKE operator.

  "Drive='C:' AND Extension='pst' AND Path LIKE '\\Users\\$env:USERNAME\\%'"
  

  • However this is INEFFICIENT AND SLOW, because this kind of operation still searches ALL files.

    • By contrast, looking for files in a single directory (and not also its subdirectories) works efficiently; e.g.:
      "Drive='C:' AND Extension='pst' AND Path='\\Users\\$env:USERNAME\\'"
      would search efficiently in the user's home directory, but only directly in it (note the required trailing \\)

  • Additionally, this functionality was temporarily broken in some versions of Windows 10 (observed on at least one build in the 1703 servicing channel).

• Thus, in order to efficiently locate files anywhere in a directory's subtree, use Get-ChildItem instead (with Invoke-Command -ComputerName for remoting, if needed) - see below.

Preferable, much faster alternative based on Get-ChildItem:

$res = (Get-ChildItem -Recurse $HOME -File -Filter *.pst -OutVariable m).FullName

^{Note: Strictly speaking Select-Object -Property Name in your original command doesn't return the full file paths directly, the way the above command does, but wraps them in custom objects whose .Name property contains them.}

In order to run this command on remote machines, pass it to Invoke-Command -ComputerName (from an elevated session, with PS remoting set up):

$res = Invoke-Command -ComputerName $targetComputer { 
         (Get-ChildItem -Recurse $HOME -File -Filter *.pst -OutVariable m).FullName 
       }

Suboptimal CIM solution and general tips for CIM / WMI-based file searches:

Here's the complete solution to the OP's problem, but, as stated:

• it is inefficient and slow

• it is broken on at least one Windows 10 build in the 1703 update channel.

$filter = "Drive='C:' AND Extension='pst' AND Path LIKE '\\Users\\$env:USERNAME\\%'"
$Res = Get-CimInstance CIM_DataFile -Filter $filter -OutVariable m |
  Select-Object -Property Name

The -Filter argument accepts an WQL expression, which can be tricky to construct:

• Drive can only accept a drive specification, so in order to search in specific directory you must use Path to target a - drive-relative - directory path.

  • Generally, be sure to narrow your search by drive(s), because by default all drives are searched, including network drives, which can be very slow.

• The Path argument must have its \ chars. doubled (\\), because \ acts as an escape character in string operands.

  • If you use operator = to specify a path, only the specified directory itself is searched, which, however, is efficient; note that the path must then end in \\.

  • By contrast, in order to search the specified path and its entire subtree :

    • As stated, this is generally inefficient and slow, and broken in some W10 builds.
    • use operator LIKE instead of = and
    • append WQL wildcard character % to the path; (in WQL (and SQL, which WQL is based on), % is the equivalent of the usual * wildcard character,and _ corresponds to ?)