Hey, Scripting Guy! Weekend Scripter: Using the Windows Search Index to Find Specific Files

ScriptingGuy1

Microsoft Scripting Guy Ed Wilson here. One of the cool things about using Windows PowerShell is the different objects with which you can interact. Windows Search is an example of this. By using ADO, you can query the Windows Search index and retrieve information about files. This technique can have some very interesting possibilities. Because querying the Windows Search index is so fast, a function could be written that would search for specific files that might be required in a script. You could also use this in an attempt to locate a missing file.

The SearchSpecificWindowsSearch.ps1 script uses COM-based ADO objects to query the SystemIndex for files that are larger than 10,000,000,000 bytes. Specific properties of the matching files are then displayed in the output window. The complete SearchSpecificWindowsSearch.ps1 script is seen here.

SearchSpecificWindowsSearch.ps1

$query = “SELECT System.ItemName, system.ItemPathDisplay, System.ItemTypeText,
  System.Size FROM SystemIndex where system.size > 10000000000″
$objConnection = New-Object -ComObject adodb.connection
$objrecordset = New-Object -ComObject adodb.recordset
$objconnection.open(
  “Provider=Search.CollatorDSO;Extended Properties=’Application=Windows’;”)
$objrecordset.open($query, $objConnection)
Try { $objrecordset.MoveFirst() }
Catch [system.exception] { “no records returned” }
do
{
 Write-host ($objrecordset.Fields.Item(“System.ItemName”)).value `
 ($objrecordset.Fields.Item(“System.ItemPathDisplay”)).value `
 ($objrecordset.Fields.Item(“System.ITemTypeText”)).value `
 ($objrecordset.Fields.Item(“System.Size”)).value
 if(-not($objrecordset.EOF)) {$objrecordset.MoveNext()}
} Until ($objrecordset.EOF)

$objrecordset.Close()
$objConnection.Close()
$objrecordset = $null
$objConnection = $null
[gc]::collect()

There are two things you need to know about querying the Windows Search index. The first is the query syntax, and the second is the provider name. Beyond those two specifics, the script works just like any other script that queries ADO.

The query string seen here returns the name, path, type, and size of files that meet specific size criteria. A special form of SQL is used to query the search index that is called Windows Search SQL. It is documented on MSDN. The field names are documented as part of the shell schema. On MSDN, these shell schema properties are grouped into categories. The system properties apply to multiple types of files and include properties such as the ones we selected in our script. You do not need to put quotation marks around the property names. Only system properties that are marked isQueryable = true in the schema can be used in a query. This information is documented for each property on MSDN. The query syntax is powerful, and each portion of the query is documented on MSDN.

$query = “SELECT System.ItemName, system.ItemPathDisplay, System.ItemTypeText,
  System.Size FROM SystemIndex where system.size > 10000000000″

The Write-Host cmdlet is used to display the value of the properties that are selected. A single command is continued to multiple lines by using the backtick (line continuation) operator. It is the value of the property that is of interest. The Item method is used to retrieve a specific property from the recordset object. After the property is returned, the value of the property can be retrieved. This is seen here (note that only properties that are selected in the select statement can be retrieved from the recordset):

Write-host ($objrecordset.Fields.Item(“System.ItemName”)).value `
 ($objrecordset.Fields.Item(“System.ItemPathDisplay”)).value `
 ($objrecordset.Fields.Item(“System.ITemTypeText”)).value `
 ($objrecordset.Fields.Item(“System.Size”)).value

The rest of the script is basic ADO. Two Hey, Scripting Guy! Blog posts might be of interest to you in helping you use ADO with Windows PowerShell. The first talks about using Windows PowerShell to query an Access database, and the second article, also talks about querying Access, but features scripts that were submitted for the 2009 Summer Scripting Games. Both articles are worth your time.

When the SearchSpecificWindowsSearch.ps1 script is run, the output appears that is shown in the following image.

If you want to know exactly what we will be looking at tomorrow, follow us on Twitter or Facebook. If you have any questions, send e-mail to us at scripter@microsoft.com or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson and Craig Liebendorfer, Scripting Guys

ScriptingGuy1

Follow