Summary<\/b>: Guest blogger, James O’Neill, discusses using Windows PowerShell to build a query to search the Windows Index.\nMicrosoft Scripting Guy, Ed Wilson, is here. Today is Part One of three blogs written by guest blogger, James O’Neill.\nJames O’Neill was born in the 1960s and used his first Microsoft product in the 1970s (and has been trying to stop ever since.) He obtained a degree in Computer
Science in the 1980s and spent most of the 1990s running an IT training company. From 2000 to 2010 he worked for Microsoft in his native England,
finishing as the evangelist for Windows platform, where he discovered PowerShell. He’s probably best known in the PowerShell community for his
library to manage Hyper-V on Windows Server 2008\/2008-R2.\n.<\/p>\n
Note<\/b> I have two Weekend Scripter blogs where I discuss querying the Windows Search Index. The first is Querying the Windows Search Index<\/a>, and the second is Using the Windows Search Index to Find Specific Files<\/a>. Both of these blogs use COM-based ADO to do the query instead of using the .NET Framework classes that are used by James. The blogs provide a good background for James’ series.\nTake it away, James…\nI have spent some time developing and honing a Windows PowerShell function that gets information from the Windows Index, which is the technology behind the search that is integrated into Windows Explorer in Windows 7 and Windows Vista. The Windows Index can be queried by using SQL, and my function builds the SQL query from user input, executes it, and receives rows of data for all the matching items.\nToday, I’m going to explore the query process. Part Two will look at making user input easier (I don’t want to make understanding SQL a prerequisite for using the function). In Part Three, I will look at why rows of data are not the best thing for the function to return and what the alternatives might be.\nWe will look at how at how the query is built in a moment. For now, please accept a ready-to-run query that is stored in the variable $SQL<\/b>. Then it only takes a few lines of Windows PowerShell to prepare and run the query as shown here.<\/p>\n $Provider=”Provider=Search.CollatorDSO;Extended Properties=’Application=Windows’;”<\/p>\n $adapter = new-object system.data.oledb.oleDBDataadapter -argument $sql, $Provider<\/p>\n $ds = new-object system.data.dataset<\/p>\n if ($adapter.Fill($ds)) { $ds.Tables[0] }\nThe data is fetched by using the oleDBDataAdapter<\/b> and DataSet<\/b> objects. The adapter is created by specifying a “provider” (which says where<\/i> the data will come from) and a SQL statement (which says what<\/i> is being requested). The query is run when the adapter is told to fill <\/i>the dataset. The .fill()<\/b> method returns a number that indicates how many data rows were returned by the query. If this is non-zero, my function returns the first table in the dataset. Windows PowerShell sees each data row in the table as a separate object, and these objects have a property for each of the table’s columns. So a search might return something like this:<\/p>\n SYSTEM.ITEMNAME : DIVE_1771+.JPG<\/p>\n SYSTEM.ITEMURL : file:C:\/Users\/James\/pictures\/DIVE_1771+.JPG<\/p>\n SYSTEM.FILEEXTENSION : .JPG<\/p>\n SYSTEM.FILENAME : DIVE_1771+.JPG<\/p>\n SYSTEM.FILEATTRIBUTES : 32<\/p>\n SYSTEM.FILEOWNER : InspironJames<\/p>\n SYSTEM.ITEMTYPE : .JPG<\/p>\n SYSTEM.ITEMTYPETEXT : JPEG Image<\/p>\n SYSTEM.KINDTEXT : Picture<\/p>\n SYSTEM.KIND : {picture}<\/p>\n SYSTEM.MIMETYPE : image\/jpeg<\/p>\n SYSTEM.SIZE : 971413\nThere are lots of fields to choose from, so the list might be longer. The SQL query to produce it looks something like this:<\/p>\n SELECT System.ItemName, System.ItemUrl, System.FileExtension, System.FileName, System.FileAttributes, System.FileOwner, System.ItemType, System.ItemTypeText , System.KindText, System.Kind, System.MIMEType, System.Size <\/p>\n FROM SYSTEMINDEX<\/p>\n WHERE System.Keywords = ‘portfolio’ AND Contains(*,’stingray’)\nIn the finished version of the function, the SELECT clause has 60 or so fields. The FROM and WHERE clauses might be more complicated than in the example, and an ORDER BY clause might be used to sort the data. The clauses are built by using parameters that are declared in my function like this:<\/p>\n Param ( [Alias(“Where”,”Include”)][String[]]$Filter ,<\/p>\n [Alias(“Sort”)][String[]]$orderby,<\/p>\n [Alias(“Top”)][String[]]$First,<\/p>\n [String]$Path,<\/p>\n [Switch]$Recurse<\/p>\n )\nIn my functions, I try to use names that are already used in Windows PowerShell. So here I use -Filter<\/b> and -First<\/b>, but I also define aliases for SQL terms like WHERE and TOP. These parameters build into the complete SQL statement, starting with the SELECT clause which uses –First<\/b>.<\/p>\n if ($First) {$SQL = “SELECT TOP $First “}<\/p>\n else {$SQL = “SELECT “}<\/p>\n $SQL += ” System.ItemName, System.ItemUrl ” # and the other 58 fields\nIf the user specifies –First 1<\/b>, $SQL<\/b> will be “SELECT TOP 1 fields<\/i>“; otherwise, it’s just “SELECT fields.<\/i>” After the fields are added to $SQL<\/b>, the function adds a FROM clause. Windows Search can interrogate remote computers, so if the -Path<\/b> parameter is a UNC name in the form \\computerNameshareName, <\/i>the SQL FROM clause becomes FROM computerName<\/i>.SYSTEMINDEX; otherwise, it is FROM SYSTEMINDEX to search the local computer. if ($Path -match “\\\\([^\\]+)\\.”) {<\/p>\n $sql += “FROM $($matches[1]).SYSTEMINDEX WHERE “<\/p>\n }<\/p>\n else {$sql += ” FROM SYSTEMINDEX WHERE “}\nThe regular expression in the first line of the example breaks down as follows:<\/p>\n Meaning<\/strong><\/p>\n<\/td>\n Application<\/strong><\/p>\n<\/td>\n<\/tr>\n \\\\<\/span><\/b>([^\\]+)\\.<\/p>\n<\/td>\n 2 characters: “” is the escape character, so each one needs to be written as \\<\/p>\n<\/td>\n \\<\/span><\/b>computerNameshareName<\/p>\n <\/p>\n<\/td>\n<\/tr>\n \\\\([^\\]+<\/span><\/b>)\\.<\/p>\n<\/td>\n Any non- character, repeated at least once<\/p>\n<\/td>\n \\computerName<\/span><\/b>shareName<\/p>\n <\/p>\n<\/td>\n<\/tr>\n “\\\\([^\\]+)\\.<\/span><\/b>“<\/p>\n<\/td>\n A ,followed by any<\/i> character<\/p>\n<\/td>\n \\computerNames<\/span><\/b>hareName <\/p>\n<\/td>\n<\/tr>\n “\\\\(<\/span><\/b>[^\\]+)<\/span><\/b>\\.”<\/p>\n<\/td>\n Capture the section that is enclosed by the brackets as a match<\/p>\n<\/td>\n $matches[0] =\\computerNames<\/p>\n $matches[1] =computerName<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n I allow the function to take different parts of the WHERE clause as a comma separated list, so that<\/p>\n -filter “System.Keywords = ‘portfolio'”,”Contains(*,’stingray’)”\nis equivalent to <\/p>\n -filter “System.Keywords = ‘portfolio’ AND Contains(*,’stingray’)”\nTo add the filter, we simply need this:<\/p>\n if ($Filter) { $SQL += $Filter -join ” AND “}\nThe folders searched can be restricted. A “SCOPE” term limits the query to a folder and all of its subfolders, and a “DIRECTORY” term limits it to a folder without subfolders. If the request is going to a remote server, the index is smart enough to recognize a UNC path and return only the files that are accessible via that path. If a -Path<\/b> parameter is specified, the function extends the WHERE clause, and the –Recurse <\/b>switch determines whether to use SCOPE or DIRECTORY, like this:<\/p>\n if ($Path){<\/p>\n if ($Path -notmatch “w{4}:”) {<\/p>\n $Path = “file:” + (resolve-path -path $Path).providerPath<\/p>\n }<\/p>\n if ($sql -notmatch “WHEREs*$”) {$sql += ” AND ” }<\/p>\n if ($Recurse) {$sql += ” SCOPE = ‘$Path’ ” }<\/p>\n else {$sql += ” DIRECTORY = ‘$Path’ “}<\/p>\n }\nIn these SQL statements, paths are specified in the form file:c:\/users\/james, which isn’t how we normally write them (and the way I recognize UNC names won’t work if they are written as file:\/\/ComputerName\/shareName). This is rectified by the first line inside the If ($Path) {}<\/b> block, which checks for 4 “word” characters, followed by a colon.\nDoing this prevents ‘File:’ being inserted if any protocol has been specified. The same search syntax works against HTTP:\/\/ (although, not usually when searching on your workstation), MAPI:\/\/ (for Outlook items), and OneIndex14:\/\/ (for OneNote items). If a file path has been given, I ensure it is an absolute one. The need to support UNC paths forces the use of .ProviderPath<\/b> here. It turns out that there is no need to convert <\/b>characters in the path to \/<\/b>, provided file: <\/b>is included.\nAfter taking care of that, the operation -notmatch “WHEREs*$” sees to it that an “AND” is added if there is anything other than spaces between WHERE and the end of the line (that is, if any conditions specified by –Filter<\/b> have been inserted).\nIf neither -Path<\/b> nor –Filter<\/b> was specified, there will be a dangling WHERE at the end of the SQL statement. Initially I removed this with –Replace<\/b>. Then I decided that I didn’t want the function to respond to a lack of input by returning the whole index, so I changed it to write a warning and exit.\nWith the WHERE clause completed, the final clause in the SQL statement is ORDER BY, which, like WHERE, joins a multipart condition.<\/p>\n if ($sql -match “WHEREs*$”) {<\/p>\n Write-warning “You need to specify either a path, or a filter.”<\/p>\n Return<\/p>\n }<\/p>\n if ($orderby) { $sql += ” ORDER BY ” + ($OrderBy -join ” , “) }\nWhen the whole function is put together, it takes three dozen lines of Windows PowerShell to handle the parameters, build and run the query, and return the result. Put together, it looks like this:<\/p>\n Function Get-IndexedItem{<\/strong><\/p>\n Param ( [Alias(“Where”,”Include”)][String[]]$Filter ,<\/p>\n [Alias(“Sort”)][String[]]$OrderBy,<\/p>\n [Alias(“Top”)][String[]]$First,<\/p>\n [String]$Path,<\/p>\n [Switch]$Recurse )<\/p>\n <\/p>\n if ($First) {$SQL = “SELECT TOP $First “}<\/p>\n else {$SQL = “SELECT “}<\/p>\n $SQL += ” System.ItemName, System.ItemUrl ” # and the other 58 fields<\/p>\n <\/p>\n if ($Path -match “\\\\([^\\]+)\\.”) {<\/p>\n $SQL += “FROM $($matches[1]).SYSTEMINDEX WHERE “<\/p>\n }<\/p>\n else {$SQL += ” FROM SYSTEMINDEX WHERE “}<\/p>\n <\/p>\n if ($Filter) { $SQL += $Filter -join ” AND “}<\/p>\n <\/p>\n if ($Path) {<\/p>\n if ($Path -notmatch “w{4}:”) {$Path = “file:” + $Path}<\/p>\n $Path = $Path -replace “\\”,”\/”<\/p>\n if ($SQL -notmatch “WHEREs*$”) {$SQL += ” AND ” }<\/p>\n if ($Recurse) {$SQL += ” SCOPE = ‘$Path’ ” }<\/p>\n else {$SQL += ” DIRECTORY = ‘$Path’ “}<\/p>\n }<\/p>\n <\/p>\n if ($SQL -match “WHEREs*$”) {<\/p>\n Write-Warning “You need to specify either a path or a filter.”<\/p>\n Return<\/p>\n }<\/p>\n if ($OrderBy) { $SQL += ” ORDER BY ” + ($OrderBy -join ” , ” ) }<\/p>\n <\/p>\n $Provider=”Provider=Search.CollatorDSO;Extended Properties=’Application=Windows’;”<\/p>\n $Adapter = New-Object system.data.oledb.oleDBDataadapter -argument $SQL, $Provider<\/p>\n $DS = New-Object system.data.dataset<\/p>\n if ($Adapter.Fill($DS)) { $DS.Tables[0] }<\/p>\n }\nThe -Path<\/b> parameter is more user-friendly as a result of the way I handle it. But I’ve made it a general rule that you shouldn’t expect the user to know too much about the underlying syntax; and at the moment, the function requires too much knowledge of SQL. I don’t want to type this:<\/p>\n Get-Indexed-Item –Filter “Contains(*,’Stingray’)”, “System.Photo.CameraManufacturer Like ‘Can%'”\nAnd it seems unreasonable to expect anyone else to do so. I came up with this list that I want the function to do for me:<\/p>\n In Part Two, I’ll look at how I accomplish these things.\n~James\nThank you, James, for a great blog.\nI invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n Ed Wilson, Microsoft Scripting Guy<\/b> <\/p>\n","protected":false},"excerpt":{"rendered":" Summary: Guest blogger, James O’Neill, discusses using Windows PowerShell to build a query to search the Windows Index. Microsoft Scripting Guy, Ed Wilson, is here. Today is Part One of three blogs written by guest blogger, James O’Neill. James O’Neill was born in the 1960s and used his first Microsoft product in the 1970s (and […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[56,353,31,3,4,147,45],"class_list":["post-8941","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-guest-blogger","tag-james-oneill","tag-operating-system","tag-scripting-guy","tag-scripting-techniques","tag-search","tag-windows-powershell"],"acf":[],"blog_post_summary":" Summary: Guest blogger, James O’Neill, discusses using Windows PowerShell to build a query to search the Windows Index. Microsoft Scripting Guy, Ed Wilson, is here. Today is Part One of three blogs written by guest blogger, James O’Neill. James O’Neill was born in the 1960s and used his first Microsoft product in the 1970s (and […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/8941","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=8941"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/8941\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=8941"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=8941"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=8941"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
A regular expression can recognize a UNC name and pick out the computer name, like this:<\/p>\n\n\n
\n Regular expression<\/strong><\/p>\n<\/td>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n