Hey, Scripting Guy! Weekend Scripter: Querying the Windows Search Index

Hey, Scripting Guy! Weekend Scripter: Querying the Windows Search Index

  • Comments 1
  • Likes

Bookmark and Share

 

Microsoft Scripting Guy Ed Wilson here. I finally had time to play around with querying Windows Search from within Windows PowerShell. I tried, back during the beta of Windows Vista, to do that, but I was unable to get it to work. It has been on my mind and on the back burner for quite some time. Today, being a nice relaxed day—due in part to the fact that I have been unable to receive any e-mail for the last 36 hours—I finally had time to play around with it.

A quick word of warning: This script and the concept of querying the Windows Search index can quickly become addictive. After you begin experimenting with the query syntax, you can easily begin to think of other applications and additional items to look for, and as a result you can eat up vast amounts of time. My first effort produced the SearchEveryThingFromWindowsSearch.ps1 script that is seen here.

SearchEveryThingFromWindowsSearch.ps1

$objConnection = New-Object -ComObject adodb.connection
$objrecordset = New-Object -ComObject adodb.recordset
$objconnection.open("Provider=Search.CollatorDSO;Extended Properties='Application=Windows';")
$objrecordset.open("SELECT System.ItemName, System.ItemTypeText, System.Size FROM SystemIndex", $objConnection)
$objrecordset.MoveFirst()
do
{
 $objrecordset.Fields.Item("System.ItemName")
 $objrecordset.Fields.Item("System.ITemTypeText")
 $objrecordset.Fields.Item("System.SIze")
 $objrecordset.MoveNext()
} Until ($objrecordset.EOF)

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


Just like any other script that uses the COM version of ADO, you begin by creating the connection object, the recordset object, and opening the connection. The key thing is to know which provider to use. The provider used here is the Search.CollatorDSO provider, but I must also specify extended properties as well. Therefore, the provider string that is used to open the connection is shown here:

"Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"


The first three lines of the script are shown here:

$objConnection = New-Object -ComObject adodb.connection
$objrecordset = New-Object -ComObject adodb.recordset
$objconnection.open("Provider=Search.CollatorDSO;Extended Properties='Application=Windows';")


After the connection is open, it is time to open the recordset. Here I also type in my select statement that will be used to retrieve information from the index. Note that I do not have a where clause; this query will therefore return the ItemName, the ItemTypeText and the Size from the SystemIndex. The SystemIndex is the only table that I can query with this provider. In addition to the SQL statement, the open method also needs a connection object. This command is shown here:

$objrecordset.open("SELECT System.ItemName, System.ItemTypeText, System.Size FROM SystemIndex", $objConnection)


The next thing I do is move to the beginning of the recordset, and print out each of the property items I had selected. After displaying the three property items for the current record, the script moves to the next item in the recordset. It will continue in this fashion until it reaches the EOF (end of the file) property of the recordset. A Do…Until loop is used to walk through the recordset. This section of the script is shown here:

$objrecordset.MoveFirst()
do
{
 $objrecordset.Fields.Item("System.ItemName")
 $objrecordset.Fields.Item("System.ITemTypeText")
 $objrecordset.Fields.Item("System.SIze")
 $objrecordset.MoveNext()
} Until ($objrecordset.EOF)


A little bit of cleanup code is all that I needed to add to the script. I therefore close the recordset and the connection, set the objects to $null, and call garbage collection. When working with COM-based objects, it is always a good idea to call garbage collection at the end of your script. The cleanup code is shown here:

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


When the script runs, the output shown in the following image scrolls by in the output pane. To stop the script from running, click the red square in the Windows PowerShell ISE. If you run it in the Windows PowerShell console, press CTRL+C to halt execution. If you do not do this, the script will run for quite some time.

Image of output from script

 

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Is there a way to rename the field (column)?  something like select System.ItemName AS Name