Scripting Windows Desktop Search 3.0

  • Article

If there’s a drawback to being a Scripting Guy it’s this: after a few years of doing nothing but scripting you begin to think that you’ve seen it all. As a result, it becomes harder and harder to get excited about new innovations in the scripting world. You say there are some new WMI classes in Windows Vista? That’s nice. You say the IIS team is working on Windows PowerShell cmdlets for IIS 7.0? Good for them. A host of new properties and methods have been added to the Office 2007 object model? Say, are you going to eat that last cookie or can we have it?

The fault, of course, has nothing to do with these new scripting technologies: all of them are incredibly useful and very welcome. Like we said, though, after several years in the business it’s only natural that the Scripting Guys would become a little jaded; you can only stop and start services on remote computers so many times before the thrill begins to wear off. So what can be done to get the Scripting Guys excited about scripting once again? Sadly, there’s nothing that can be done, nothing at all.

Well, unless someone introduces a fast, efficient, and extremely cool way to search your computer. But what are the odds of something like that happening?

Introducing Windows Desktop Search 3.0

Yes, we said “Windows Desktop Search 3.0.” You might actually be familiar with this recently-release product: it’s the new technology that adds a search box to your Windows taskbar, and provides a surprisingly-fast way to search your hard disk, your Microsoft Outlook/Outlook Express mail folders, your Instant Messenger message, etc.. But Windows Desktop Search 3.0 is more than just fast: it’s also extremely versatile. You can search by file name; that’s a given. Likewise, you can search by file extension or file size. But that’s just the beginning. For example, you can search for music files by artist name, or search for photos by the horizontal and vertical resolutions. You can search for text within a file (including PowerPoint presentations). You can search for an Outlook contact by his or her birthday, or search for a meeting request by meeting organizer. You can do Boolean-type searches (where FileName = something and CreationDate >= something else). You can – well, you get the idea.

And here’s the best part: the whole thing is scriptable. Just like we said: very cool.

In a minute or two we’ll show you some sample code that will help you add Desktop Search to your system administration scripts. Before we do that, however, we need to take care of a little business. First of all, none of this will do you any good until you install Desktop Search 3.0 on your computers. If you’re running Windows Vista then you’re already done: that’s because Desktop Search is built into Windows Vista. If you’re running Windows XP or Windows Server 2003 check out the following links for information on how to download the proper set of files (and don’t worry: installation is fast and easy):

  • Windows Desktop Search 3.0 RTW for Windows XP (KB917013)
  • Windows Desktop Search 3.0 RTW for Windows XP x64 Edition (KB917013)
  • Windows Desktop Search 3.0 RTW for Windows Server 2003 (KB917013)
  • Windows Desktop Search 3.0 RTW for Windows Server 2003 x64 Edition (KB917013)

Note. What if you’re running Windows 2000 or, say, Windows 98? Well, sorry: Desktop Search doesn’t exist for those platforms and probably never will. But maybe that gives you a reason to upgrade; after all, you can’t continue running MS-DOS 5.0 forever, can you?

Second, you’ll probably have lots of questions about Desktop Search that we won’t be addressed in this introductory article. Who can answer these questions for you? Not the Scripting Guys, that’s for sure. The truth is, at this point in time we don’t know much more about this than you do. For example, our understanding is that – with the proper setup – you can perform these searches on remote computers running Windows Vista. So what is the proper setup? At the moment, we don’t know. When we get a chance we’ll look into this in more detail. In the meantime, this article will give you a head start on scripting Desktop Search, but don’t consider it the final word on everything you can do with this new technology.

Last, but surely not least, by default Desktop Search 3.0 is preconfigured to search your Outlook/Outlook Express Inbox and only a few selected folders on your hard disk. One thing you might want to do immediately after installation is change these options, perhaps to allow Desktop Search to search all the folders on all your hard drives. To modify the search locations just click the Desktop Search icon in the Notification Tray, click Windows Desktop Search Options and then click Modify. Of course, you don’t have to change these options, but if you don’t you might get unexpected results: for example, if all your music files are on drive D a search for music files will likely come back with the terse message “0 files found.” Why? Because by default drive D is not included in the search scope. To fix that problem, add drive D to the search location.

Note. Is there a way to programmatically modify the search scope? File that inquiry under “Questions About Desktop Search That the Scripting Guys Can’t Answer.” Like we said, we’ll look into this further.

Oh, one other note: Desktop Search 3.0 works by indexing all the items found in your search locations. Depending on the size of your hard disk and what-not it could take several minutes (or more) before everything has been fully indexed. But look at it this way: you’ve waited years for a really fast, really cool way to search your computer. Having to wait a few more minutes probably won’t kill you.

Well, we did say “probably.”

Uh, What about That Sample Code?

Oh, right: we almost forgot about that. Let’s start with a very simple (if only marginally useful) search, one that returns a list of all your files, folders, and mail items:

On Error Resume Next

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")



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

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX", _

    objConnection

objRecordSet.MoveFirst

Do Until objRecordset.EOF

    Wscript.Echo objRecordset.Fields.Item("System.FileName")

    objRecordset.MoveNext

Loop

Ah, very observant of you: yes, you do use ADO (ActiveX Data Objects) to programmatically perform searches. Is that good? You bet it is; after all, if you’ve used ADO to work with Active Directory or with a SQL Server or Microsoft Access database, then you’ve got a big head start when it comes to doing desktop searches.

Let’s take a closer look at our basic script. (Which, by the way, works just fine: it will not only echo back the names of all your files, folders, and mail items, but it will do so in a matter of seconds and without bringing your computer to a standstill.) As you can see, we start out by creating the two fundamental ADO objects: the Connection object (which, as the name implies, helps us connect to our data source) and the Recordset object (which we use to specify the information to be retrieved from the data source). That’s what these two lines of code are for:

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")

After we’ve created the Connection and Recordset objects we then call the Open method to connect to the Desktop Search data source:

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

What does all of that code do? We don’t really know, and it doesn’t really matter: it’s all just boilerplate. The parameter we pass to the Open method is simply the connection string that binds us to Desktop Search. All you have to do is type everything in exactly as shown above; given the proper connection string ADO can take it from there. The long and short of it? Just make sure each and every one of your desktop search scripts start with this block of code and you’ll be in business:

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")



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

After we connect to the data source our next step, logically enough, is to retrieve some data. This gives us an opportunity to point out that Desktop Search is a read-only technology: you can retrieve data using Desktop Search but you can’t change data using Desktop Search. (For example, you can retrieve the Title of a Word document using Desktop Search, but you can’t change the Title of a Word document using Desktop Search.) For you database aficionados, that means you can only use Select queries; other query types – like Delete, Update, or Insert – are doomed to fail. Fortunately, though, you can hand the results of a search over to a technology that can change data. We’ll show you an example of that later on.

For now, though, let’s refresh our memories and take another peek at the line of code that retrieves data for us:

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX", _

    objConnection

Again, this should look familiar to anyone who’s done ADO scripting in the past: we’re just calling the Recordset object’s Open method followed by two parameters: our SQL query and the reference to the Connection object (objConnection), which simply tells the script which connection to retrieve this data from. Experienced ADO script writers might have noticed that we didn’t specify a cursor type or lock type when calling the Open method. Why not? Well, as near as we can tell, we don’t need to do that. The default values seem to work just fine.

Now, what about the query itself? Two things are important here. First, notice that we’re selecting data from the “table” SYSTEMINDEX. When you perform a search using Desktop Search 3.0 you don’t actually search your hard drives. Instead, after you install Desktop Search it begins indexing all your files, folders, and mail items. When you run a search you actually search that index file. In other words, all your scripts will search only SYSTEMINDEX.

Second, you’ll also note that we specified the property we want the search to return: System.FileName. This is important: you can get back data for only the properties specified in your search query. Can you use a shortcut method, like Select * From? No; a query like that will fail. You must specify the property names in the query, no shortcuts or wildcards allowed. Want to get back both the file name and the file size (System.Size)? Then you must include both those properties in your query, using commas to separate the property names:

objRecordSet.Open "SELECT System.FileName, System.Size FROM SYSTEMINDEX", _

    objConnection

What’s that? You have more questions?!? Let’s see if we can answer them. First of all, no, we aren’t 100% sure which property values you can search for and which ones you can’t. In theory, you can search for any of the Shell object properties; however, some of those don’t seem to work. We’ve posted a list of properties, organized by file type, that do seem to work; that should get you started.

As for your second question, you don’t necessarily have to type System.FileName; in this case you can get away with just typing FileName. Likewise, you can seemingly-truncate a property name like System.Music.Artist to Artist. However, we haven’t had a chance to try each and every property, which means we can’t guarantee that each and every name can be shortened in this fashion. Therefore, in this article we’ll use the full property name. If you’re feeling adventurous you can try the shortened names on your own. (After all, we don’t want to hog all the fun for ourselves.)

After setting up and executing the query the rest is easy. We use the MoveFirst method to position ourselves at the beginning of the recordset, then we set up a Do Until loop that loops through all the records in that recordset. (That is, we keep looping around until the recordset’s EOF – end-of-file – property is True.) Inside that loop we do two things: we echo back the value of the System.FileName property, and we use the MoveNext method to then move on to the next record in the recordset. That’s what this block of code is for:

 

objRecordSet.MoveFirst



Do Until objRecordset.EOF

    Wscript.Echo objRecordset.Fields.Item("System.FileName")

    objRecordset.MoveNext

Loop

That’s all we have to do. Which even we Scripting Guys have to admit is at least a little bit exciting.

Can’t We Narrow This Down a Little?

Of course, excitement isn’t everything. For example, roller coasters are exciting but they have very limited practical value: unless you both live and work at an amusement park you probably can’t commute back and forth via roller coaster. (Though that would be cool, wouldn’t it?) Likewise the fact that, within seconds, you can retrieve a list of all the files on your computer is pretty exciting. Whether it has much practical value is another story.

As it is, it’s probably safe to say that most of the time you search for files or folders you aren’t interested in all your files and folders; instead, you’re usually interested in a particular subset of information. So can we do a better job of zeroing in on a particular file or set of files? Let’s put it this way: would we be this excited about Desktop Search if you couldn’t?

In other words, yes, we can easily zero in on a particular file or set of files: because we’re using ADO all we have to do is add a Where clause to our query. For example, suppose we’d like to get back a list of all the .WMA files on our computer. No problem; we just have to search for all the items that have a .WMA file extension:

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX WHERE System.FileExtension = '.wma'", _

    objConnection

Is that going to work? Of course it is; here is a small portion of the data we got back (literally in seconds) when running this query on a test computer:

City Of New Orleans.wma

Blue Bayou.wma

House Of The Rising Sun.wma

I Wanna Be Sedated.wma

Never Been To Spain.wma

Anything That's Rock 'N' Roll.wma

If you go back to the query itself, you’ll see that we added the following WHERE clause:

WHERE System.FileExtension = '.wma'

Nothing special about that: we simply specify the property we want to filter on (System.FileExtension) followed by our criteria: = '.wma' (equal to .wma). Notice that - as usual - the string value .wma is enclosed within single quotes; if we were using numbers or a Boolean value (like True or False) we would leave out the single quotes. For example, what if we wanted to find all the files that have a file size greater than one gigabyte (1,073,741,824 bytes)? Then we'd use this query:

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX WHERE System.Size > 1073741824", _

    objConnection

Note. Before we go much further we should point out that, with Desktop Search (and unlike WMI), the period is considered part of the file extension.

All in all that’s not bad, not bad at all: with one simple little query we got back a list of all the .WMA files on our computer. However, although this is a good query, it’s not necessarily the perfect query. Why not? Well, for one thing, our file names came back in seemingly-random fashion; that makes it hard to locate a particular music file. It would be nice if the file names came back sorted alphabetically, but that’s probably too much to ask, isn’t it?

Yes, it is. But go ahead and ask anyway:

objRecordSet.Open _

    "SELECT System.FileName FROM SYSTEMINDEX WHERE System.FileExtension = '.wma' ORDER BY System.FileName", _

        objConnection

In other words, of course we can sort data within our recordset; after all, this is an ADO recordset. All we have to do is add an ORDER BY clause, followed by the name of the property we want to sort on:

ORDER BY System.FileName

Now look at the data we get back (or at least the first few records):

All Shook Up.wma

American Pie.wma

Anything That's Rock 'N' Roll.wma

Apeman.wma

Because The Night.wma

Berkeley Mews.wma

Would you rather see the data sorted in descending order (that is, from Z to A rather than from A to Z)? Then just add the DESC keyword to the ORDER BY clause:

objRecordSet.Open _

    "SELECT System.FileName FROM SYSTEMINDEX WHERE System.FileExtension = '.wma' ORDER By System.FileName DESC", _

        objConnection

Here’s what we get back when we execute the preceding query:

Why Me Lord.wma

Why Don't We Do It In The Road.wma

White Rabbit.wma

Where Did You Sleep Last Night.wma

What is Life.wma

Two Of Us.wma

Tusk.wma

Cool.

But you know what they say: you ain’t seen nothin’ yet. Being able to sort returned data is nice, but, other than that, everything we’ve seen so far is old hat: we’ve always been able to use WMI to return a list of all the .WMA files on a computer. Why would you want to go to all the trouble of downloading and installing Desktop Search 3.0 just to carry out the same kind of tasks you’re already able to carry out?

Beats us. So let’s see if we can do something here that you haven’t been able to do up until now. As it turns out, music files have an Artist property; if this property has been filled out (which is inevitably the case for music files purchased online, and typically the case for songs you “rip” from a CD) then you can easily identify the artist responsible for a given song. Which leads to a burning question: do we have any John Lennon songs on our computer?

Well, let’s see. We’ll just haul out WMI and – oh, that’s right: WMI doesn’t have access to “extended” properties like Artist and AlbumTitle. Neither does the FileSystemObject. Dang; looks like we’re out of luck.

Oh, hey, good idea: why don’t we try this new Desktop Search thing. Here’s a query that looks for all the files where the System.Music.Artist property is equal to John Lennon:

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX WHERE System.Music.Artist = 'John Lennon'", _

    objConnection

And guess what? Looks we have a few of John’s songs after all:

Mother.wma

Mind Games.wma

(Just Like) Starting Over.wma

Watching the Wheels.wma

What do you mean that’s not good enough? Turns out you’d like to know about songs by John Lennon or by George Harrison? Well, let’s give it a try. Here’s a new query, one that looks for files where the Artist is equal to John Lennon or equal to George Harrison:

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX " & _

"WHERE System.Music.Artist = 'John Lennon' or System.Music.Artist = 'George Harrison'", _

    objConnection

And what do you know:

Mother.wma

Mind Games.wma

(Just Like) Starting Over.wma

Watching the Wheels.wma

If Not For You.wma

What is Life.wma

As you probably guessed, you can write even more-sophisticated queries by using an AND clause or an OR clause. Very nice, huh?

Here’s another cool little trick. It’s not unusual in this day and age for a family to have several digital cameras. Wondering if you still have some pictures on the computer that were taken using that one camera? Yeah, right that camera, the Kodak, the one that we can never remember the model number for, the –

Relax; Desktop Search has you covered. Give this query a try:

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX WHERE System.Photo.CameraModel Like 'Kodak%'", _

    objConnection

You guessed it: Desktop Search supports wildcard queries. To search for a specific camera model we add the following WHERE clause:

WHERE System.Photo.CameraModel Like 'Kodak%'

As you can see, we specify the Like operator, then use the percent sign (%) as the wildcard character; with ADO a string like Kodak% is equivalent to the string Kodak* when using wildcards with, say, the Dir command. With the preceding query we’re looking for all the files where the value of the CameraModel property begin with the string Kodak. What if we wanted to find files where the value of CameraModel ended with Kodak? That’s easy; we’d just put the % wildcard character in front of the target string:

WHERE System.Photo.CameraModel Like '%Kodak'

Logically enough then, this query searches for files where the string Kodak appears anywhere within the value of the CameraModel property:

WHERE System.Photo.CameraModel Like '%Kodak%'

Why does this find files that have the string Kodak anywhere with the CameraModel property? We know the answer to that one: because we use a wildcard character both before and after the target string Kodak. Essentially we’re saying, “Listen, we don’t care what comes before the word Kodak (if anything) or what comes after the word Kodak (if anything). As long as Kodak appears somewhere in the value we want to know about it.”

But What If I Don’t Know the Values of My Extended Properties?

Ha-ha; that’s pretty funny. Anyway, as we were saying – oh, wait: you’re serious, aren’t you? You mean you haven’t memorized the Title of each of your Microsoft Word documents? Well, then what about the RevisionNumber or the WordCount properties? Really? Wow; that is a dilly of a pickle, isn’t it?

OK, so maybe we are being a bit facetious. The truth is, the Scripting Guys (or at least one of them) are notorious for giving their files names like X.doc, for filing them away in folders named Stuff or Junk, and for never, ever filling out extended property data. Then how do the Scripting Guys (or at least one them) ever find a document when they need one? Typically they rely on one of two approaches:

  • Prayer
  • Searching for specific text within that document

If you need help with the first approach (prayer), well, there are probably better places to turn than the Scripting Guys. But if you need help with the second method (searching for specific text within a document), well, that we can handle:

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX WHERE Contains('hippopotamus')", _

    objConnection

Take a look at the WHERE clause in this query:

WHERE Contains('hippopotamus')

As you can see, we’re looking for all the items where the text Contains the word hippopotamus (with the target word enclosed in single quotes).

Note. Yes, the Scripting Guys actually have work-related documents that include the word hippopotamus.

As you can see, finding a single word is easy. However, things get a tiny bit more complicated if you want to search for more than one word (that is, if you want to search for a phrase that includes one or more blank spaces). Why is that more complicated? Because the target phrase must be enclosed in double quote marks, which then must be enclosed in single quote marks. If you know anything about VBScript, quote marks inside of quote marks is a catastrophe waiting to happen.

Here’s an easy way to work around that problem: assign your target string to a variable, making sure to bracket the string with double quotes (something you can do using the VBScript function Chr(34)). Then all you have to do is use that variable in your query. For example, here’s a query that searches for items where the text includes the phrase Windows PowerShell Scripting Sweepstakes:

strText = Chr(34) & "Windows PowerShell Scripting Sweepstakes" & Chr(34)



objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX WHERE Contains('" & strText & "')", _

    objConnection

Sure, it’s a little weird. But that’s OK: just use the preceding code as a template and you’ll be fine.

Now, What about Folders and File Paths?

OK, what about folders and file paths? We can think of at least two reasons why this might be of interest to you. First, you might want to know the complete path for each item returned by a search: if your search returns only a file name – like test.vbs –then you haven’t really gained much by doing this search, have you? Along the same lines, Desktop Search is a read-only technology: all it can do is locate items for you. But what if you’d like to copy or delete the items that you find? Well, you can do that, provided that you hand each of these items off to another technology (such as WMI or the FileSystemObject). To do that, however, you need to know the complete path to the item.

Second, there will undoubtedly be times when you want to target your search to a specific folder or set of folders (for example, retrieving a list of files in C:\Scripts, or maybe C:\Scripts and any of its subfolders). Can you do that with Desktop Search?

We’ll find out in a minute. Before we do that, let’s take a look at a query that returns the complete path for all the .WMA files on a computer:

objRecordSet.Open "SELECT System.ItemPathDisplay FROM SYSTEMINDEX WHERE System.FileExtension = '.wma'", _

    objConnection

What’s the difference between this query and our original show-me-all-the-.WMA-files query? Just one thing: this time around we’re asking to get back the value of the System.ItemPathDisplay property instead of the System.FileName property. In turn, that gives us output that looks like this:

d:\Music\Rip\Badfinger\Best Of Badfinger\01. No Matter What(128).wma

d:\Music\Rip\Badfinger\Best Of Badfinger\02. Day After Day(128).wma

d:\Music\Rip\Badfinger\Best Of Badfinger\06. Come And Get It(128).wma

d:\Music\Rip\Badfinger\Best Of Badfinger\08. Carry On Til Tomorrow(128).wma

d:\Music\Rip\Blondie\Best Of Blondie\06. Hanging On The Telephone(128).wma

d:\Music\Rip\Cowboy Junkies\The Trinity Session\02. Misguided Angel(128).wma

In other words, now we can actually find those files on the hard drive. In addition, we can also do something with these files. For example, suppose we want to copy all the songs by the Cowboy Junkies to the folder D:\Test. No problemo:

On Error Resume Next



Set objFSO = CreateObject("Scripting.FileSystemObject")



Set objConnection = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")



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



objRecordSet.Open "SELECT System.ItemPathDisplay FROM SYSTEMINDEX WHERE System.Music.Artist ='Cowboy Junkies'", _

    objConnection



objRecordSet.MoveFirst



Do Until objRecordset.EOF

    strFile = objRecordset.Fields.Item("System.ItemPathDisplay")

    objFSO.CopyFile strFile, "D:\Test\"

    objRecordset.MoveNext

Loop

How does this script work? Well, we begin by creating an instance of the Scripting.FileSystemObject, the object we’ll use to copy the files. We then do a regular search, returning the value of the System.ItemPathDisplay property for all the music files that have Cowboy Junkies as the Artist. That part is pretty straightforward.

Now, take a look at what happens inside the Do Until loop:

strFile = objRecordset.Fields.Item("System.ItemPathDisplay")

objFSO.CopyFile strFile, "D:\Test\"

objRecordset.MoveNext

As you can see, the first thing we do is assign the file path to a variable named strFile. Once we know the path to the file we can then call the CopyFile method and copy the file to the folder D:\Test:

objFSO.CopyFile strFile, "D:\Test\"

And then, as usual, we call the MoveNext method to loop around and repeat the process with the next file in the collection.

OK, then what about getting a list of all the files in a folder? Again, that’s pretty easy, thanks to the System.ItemFolderPathDisplay property, which returns the path to the folder that a file lives in. For example, here’s a query that returns all the items in the C:\Scripts folder:

objRecordSet.Open "SELECT System.ItemPathDisplay FROM SYSTEMINDEX Where System.ItemFolderPathDisplay = 'C:\Scripts'", _

    objConnection

You can then echo the output with this line:

Wscript.Echo objRecordset.Fields.Item("System.ItemPathDisplay")

If you look closely at the output, you’ll notice that this query returns folders as well as files. Want to get back just files, leaving out any folders? Sure; all we have to do is tack on an AND clause that weeds out anything where the System.ItemType property is equal to folder:

objRecordSet.Open "SELECT System.ItemPathDisplay FROM SYSTEMINDEX Where System.ItemFolderPathDisplay = 'C:\Scripts' " & _

    "AND System.ItemType <> 'folder'", objConnection

Getting a list of all the items in C:\Scripts plus all the items in any subfolder of C:\Scripts is a little trickier. However, here’s one approach that seems to work just fine: use a wildcard query to return a list of items where the ItemPathDisplay starts with the string C:\Scripts\. In other words, try this query and see what you get back:

objRecordSet.Open "SELECT System.ItemPathDisplay FROM SYSTEMINDEX Where System.ItemPathDisplay LIKE 'C:\Scripts\%'", _

    objConnection

One (or Two) for the Road

Given our druthers, we could sit here all day and write sample scripts that use Desktop Search 3.0. (Of course, as long as it keeps us from having to work we Scripting Guys could sit here all day and pretty much do anything.) For better or worse, though, duty calls, which means we need to bring this article to a close. (Besides, it’s almost lunch time.) Before we go, however, we’d like to show you two more things.

First, we know that a lot of people love to search the file system by date (all the files created since last Tuesday, all the files modified in the past hour, etc.). If you happen to be one of those people you’ll be happy to hear that Desktop Search makes it easy to search for items by date. For example, here are just some of the date-related properties available to you:

  • System.DateAccessed
  • System.DateAcquired
  • System.DateArchived
  • System.DateCompleted
  • System.DateCreated
  • System.DateImported
  • System.DateModified
  • System.Document.DateCreated
  • System.Document.DatePrinted
  • System.Document.DateSaved
  • System.Message.DateReceived
  • System.Message.DateSent
  • System.Photo.DateTaken

The one thing you need to keep in mind when working with dates is that Desktop Search works with date and time values. What difference does that make? Well, suppose we have a file that was created at 11:01:23 AM on December 1, 2006. Now, suppose we execute the following query:

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX Where System.DateCreated = '2006-12-1' ", _

    objConnection

Is that going to return our target file? No; in fact, this query probably won’t return any file. That’s because the value of DateCreated is actually equal to the entire date-time value (2006-12-1 11:01:23, using a 24-hour clock). If we want to find all the files created on December 1st (and yes, we do have to use the format year-month-day in our queries) we need to look for all the files with a creation date greater than or equal to the “0 hour” on December 1st but less than the 0 hour on December 2nd. In other words:

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX Where System.DateCreated >= '2006-12-1'" & _

    " and  System.DateCreated < '2006-12-2'", objConnection

What about files created between 8:00 AM and 3:00 PM (15:00:00 on a 24-hour clock) on December 1? That requires a query similar to this:

objRecordSet.Open "SELECT System.FileName FROM SYSTEMINDEX Where System.DateCreated >= '2006-12-1 8:00:00'" & _

    " and  System.DateCreated <= '2006-12-1 15:00:00'", objConnection

Yes, we know. But you’ll get used to it in no time. Again, use the preceding code as a template and your query-writing life should be a little easier.

And one last little tip: because this is ADO you can do some clever little things using standard SQL queries. For example, here’s a simple little query that returns the five largest files on your computer:

objRecordSet.Open "SELECT Top 5 System.FileName FROM SYSTEMINDEX ORDER BY System.Size DESC", _

    objConnection

Just a little something you SQL gurus might have fun playing with.

For More Information …

… well, to be honest, you’re probably out of luck for the time being; like we said, right now it’s hard to find information about Desktop Search 3.0, especially when it comes to scripting. However, because we do find this technology kind of fun we’re hoping to explore it in more detail in the future. If and when we stumble upon more information you’ll be the first to know.

Well, OK: Peter will probably tell his mom first. But after that you’ll be the first to know.

Until then, are you going to eat that last cookie or can we have it?