Find-File.psm1
<#
.SYNOPSIS Finding files including OneDrive by querying the windows search index database over SQL Statements .DESCRIPTION This function offers a method for querying the Windows Search Index Database over simple command line arguments, displayed in an interactive GUI for easy use. Alternatively it offers a legacy mode if used without the recommended external module. Results can also be displayed in Raw mode which does not offer any interactive interface but due to it beeing directly handled by the Dataset Adapter itself, it is able to Display Large amounts of Data instantly. .PARAMETER Query Specify search term to be used, Allows for Wildcard (or wildcard only) and does accept Arrays .PARAMETER Path Specify Path to search .PARAMETER Exclude Excludes a specific path, exclude can be applied to single Path inside whole search Path .PARAMETER Type Defines what File type should be searched for .PARAMETER Size Specify a Size or Size Range for Items to be searched, sizes must be specified with operators: < = > (Supports MB, GB etc.) .PARAMETER Order Specify Order in which results should be sorted by .PARAMETER View Selects the Mode how Results should be Displayed, Single/Multi select Interactive Shell, Legacy for external GUI, Raw for fast but not interactable GUI .PARAMETER Mode Selects the Execution Mode in regard to what should happen after a file has been selected Shell: Execute file with Windows Explorer equally to what happens by opening a file in File Explorer Console: Changes directory to the Path where selected result resides None: Does not execute a file after it has been selected Files always get stored inside $Item variable .PARAMETER FreeText Switch that causes query to query inside documents and raw text files instead of searching for files themself .PARAMETER Force Switch forces to always use Interactive shell GUI even if there are more than 1000 Results (Depending on amount may slow down session considerably) .PARAMETER Ascending Displays the selected order in Ascending direction .PARAMETER Descending Displays the selected order in Descending direction .PARAMETER SQL Use an SQL Query as Search Query .INPUTS String to be used as Query, with additional options for extensive filters .OUTPUTS Matching results, displayed inside interactive shell GUI for easy selection .EXAMPLE Find-File *test* Searches for Files matching query *test* .EXAMPLE Find-File * -Size ">1GB" -Order Size -Ascending Searches for Files over 1GB in size, sorted by Size and displayed in Ascending Direction .EXAMPLE Find-File Konfiguration -FreeText -Path '$home\OneDrive\' Searches Text documents inside specified Path for string "Konfiguration" .EXAMPLE Find-File *chrome*,*firefox* -Path '$home\Desktop\' -Type link Searches for any shortcuts residing on the Desktop, matching *chrome* or *firefox* .EXAMPLE Find-File -SQL -Querry "SELECT System.FileName, System.KindText, system.size, System.ItemPathDisplay FROM SYSTEMINDEX WHERE System.FileName LIKE 'test%' AND SYSTEM.SEARCH.STORE LIKE 'File' ORDER BY System.ItemPathDisplay DESC" Query the windows search Index directly with SQL .NOTES Full Script and Function by CZ Interactive Shell GUI by PowerShell Team .LINK PowerShell 7.1.4: https://github.com/PowerShell/PowerShell/releases/tag/v7.1.4 Interactive Shell GUI: https://www.powershellgallery.com/packages/Microsoft.PowerShell.ConsoleGuiTools/0.6.2 #> #Find File v2.4 (SQL) function Find-File { #Parameter Param( [CmdletBinding()] #Search Query [Parameter(Mandatory=$true, Position=0, ValueFromPipeline=$true, HelpMessage="Searchterm")] [SupportsWildcards()] [Alias("Q","Search")] [string[]]$Query, #Search Path [parameter(Mandatory=$false, Position=2, HelpMessage="Path to search")] [ValidateScript({$_ | ForEach-Object {Test-Path $_}})] [Alias("P")] [string[]]$Path, #Content Type [parameter(Mandatory=$false, Position=1, HelpMessage="Specify content type to search for")] [ValidateSet("folder", "link", "program", "document", "picture", "video", "music", "playlist", "contact", "communication")] [Alias("T")] [string[]]$Type, #File Size [parameter(Mandatory=$false, Position=4, HelpMessage="Specify filesize(bytes) Use following Operators < = > (File size only works with single query)")] [Alias("S","FileSize")] [string[]]$Size, #Application Modes #Sort By [parameter(Mandatory=$false, Position=3, HelpMessage="By which row to sort by (Default is Path)")] [ValidateSet("FileName", "Type", "Size", "Path", "Date")] [Alias("SB")] [string]$SortBy, #Output View Mode [parameter(Mandatory=$false, Position=6, HelpMessage="Output View Type (only Single and Multi Supports direct execution)")] [ValidateSet("Single", "Multi", "Raw", "Legacy")] [Alias("V")] [string]$View, #Execution Mode [parameter(Mandatory=$false, Position=7, HelpMessage="Execution mode after Item has been selected")] [ValidateSet("Shell", "Console", "None")] [Alias("M","EX")] [string]$Mode, #Switches #FreeText Switch [parameter(Mandatory=$false, Position=5, HelpMessage="Search Inside Document Text Instead")] [Alias("FT","text")] [switch]$FreeText, #SQL Query Switch [parameter(Mandatory=$false, Position=9, HelpMessage="Write SQL Query as Search Query instead")] [switch]$SQL, #Force Switch [parameter(Mandatory=$false, Position=8, HelpMessage="Always Display Inside Interactive GUI, ignore too many results")] [Alias("F")] [switch]$force ) #Dynamic Parameters DynamicParam{ #Dynamic Parameter for Sort IF ($PSBoundParameters.ContainsKey('SortBy')){ $SortParamAttrib = [System.Management.Automation.ParameterAttribute]@{ Mandatory = $false HelpMessage = "Sort Order" ParameterSetName = "Direction" } #Attribute Collection $SortAttribCollection = [System.Collections.ObjectModel.Collection[System.Attribute]]::new() $SortAttribCollection.Add($SortParamAttrib) #Dynamic Parameters $dynParamAscending = [System.Management.Automation.RuntimeDefinedParameter]::new('Ascending', [switch], $SortAttribCollection) $dynParamDescending = [System.Management.Automation.RuntimeDefinedParameter]::new('Descending', [switch], $SortAttribCollection) #Dynamic Parameter Build Dictionary $SortParamDictionary = [System.Management.Automation.RuntimeDefinedParameterDictionary]::new() $SortParamDictionary.Add('Ascending', $dynParamAscending) $SortParamDictionary.Add('Descending', $dynParamDescending) #Return dynamic Parameters to $PSBoundParameters return $SortParamDictionary } #Dynamic Parameter for Path IF ($PSBoundParameters.ContainsKey('Path')){ $PathParamAttrib = [System.Management.Automation.ParameterAttribute]@{ Mandatory = $false HelpMessage = "Search Location" ParameterSetName = "Location" } #Attribute Collection $PathAttribCollection = [System.Collections.ObjectModel.Collection[System.Attribute]]::new() $PathAttribCollection.Add($PathParamAttrib) #Dynamic Parameters $dynParamExclude = [System.Management.Automation.RuntimeDefinedParameter]::new('Exclude', [string[]], $PathAttribCollection) #Dynamic Parameter Build Dictionary $PathParamDictionary = [System.Management.Automation.RuntimeDefinedParameterDictionary]::new() $PathParamDictionary.Add('Exclude', $dynParamExclude) #Return dynamic Parameters to $PSBoundParameters return $PathParamDictionary } } #Begin Process Block Begin { #Check if Search Service is Running IF ((Get-Service -Name "WSearch").Status -ne "Running") { Write-Host "Windows Search Service not Running" -ForegroundColor Red break } #Import Dependency Import-Module Microsoft.PowerShell.ConsoleGuiTools -MinimumVersion 0.6.2 IF ($? -eq $False) { $View = "Legacy" } #Init $SQLQuery=""; $WHERE =""; $Filters = $null; $Filters = @(); $IncludePaths = @(); $ExcludePaths = @() $QueryTerm = $Query -replace "\*","%" } #Main Process block Process { #Search Query IF ($null -ne $PSBoundParameters.Query){ IF ($FreeText) { $Filters += $QueryTerm | Join-String -OutputPrefix "FREETEXT " -Separator " OR FREETEXT " -FormatString "('{0}')" } else { $Filters += $QueryTerm | Join-String -OutputPrefix "System.FileName LIKE " -Separator " OR System.FileName LIKE " -FormatString "'{0}'" } } #File Type IF ($null -ne $PSBoundParameters.Type){ $Filters += $PSBoundParameters.Type | Join-String -OutputPrefix "System.kind LIKE " -Separator " OR System.kind LIKE " -FormatString "'{0}'" } #Path IF ($null -ne $PSBoundParameters.Path){ $IncludePaths += $PSBoundParameters.Path | ForEach-Object {Join-Path $_ -ChildPath "\%"} $Filters += $IncludePaths | Join-String -OutputPrefix "System.ItemPathDisplay LIKE " -Separator " OR System.ItemPathDisplay LIKE " -FormatString "'{0}'" } #Path to exclude IF ($null -ne $PSBoundParameters.Exclude){ $ExcludePaths += $PSBoundParameters.Exclude | ForEach-Object {Join-Path $_ -ChildPath "\%"} $Filters += $ExcludePaths | Join-String -OutputPrefix "System.ItemPathDisplay NOT LIKE " -Separator " AND System.ItemPathDisplay NOT LIKE " -FormatString "'{0}'" } #File Size IF ($null -ne $PSBoundParameters.Size){ #File Size not working with multible queries ??? $lt=$null;$gt=$null;$eq=$null;$FileSize=$null $PSBoundParameters.Size | ForEach-Object { IF ($_ -like "<*"){[long]$lt = $_ -replace "<",""} IF ($_ -like ">*"){[long]$gt = $_ -replace ">",""} IF ($_ -like "=*"){[long]$eq = $_ -replace "=",""} } IF ($eq){ $FileSize = "=$eq" } elseif ($lt -and $gt) { IF ($lt - $gt -lt 0) { Write-Host "Specified FileSize Range invalid" #$FileSize = "<$lt" break } else { $FileSize = "<$lt",">$gt" } } elseif ($lt){ $FileSize = "<$lt" } elseif ($gt){ $FileSize = ">$gt" } $Filters += $FileSize | Join-String -OutputPrefix "System.size " -Separator " AND System.size " } #Order results by Property IF ($null -ne $PSBoundParameters.SortBy){ IF ($PSBoundParameters.SortBy -eq "FileName") {$OrderBy = "System.FileName"} IF ($PSBoundParameters.SortBy -eq "Type") {$OrderBy = "System.KindText"} IF ($PSBoundParameters.SortBy -eq "Size") {$OrderBy = "System.size"} IF ($PSBoundParameters.SortBy -eq "Path") {$OrderBy = "System.ItemPathDisplay"} IF ($PSBoundParameters.SortBy -eq "Date") {$OrderBy = "System.DateAccessed"} } else {$OrderBy = "System.ItemPathDisplay"} #Results order direction IF ($PSBoundParameters.Ascending) {$Direction = "DESC"} elseif ($PSBoundParameters.Descending) {$Direction = "ASC"} else {$Direction = ""} #Build Filter IF ($null -ne $Filters){ $WHERE = $Filters | Join-String -Separator " AND " } #Build Select Statement IF ($SortBy -eq "Date") { $Select = "System.FileName, System.KindText, system.size, System.DateAccessed, System.ItemPathDisplay" } else { $Select = "System.FileName, System.KindText, system.size, System.ItemPathDisplay" } #Build SQL Statement IF ($PSBoundParameters.ContainsKey('SQL')){ $SQLQuery = $Query } else { $SQLQuery = "SELECT $Select FROM SYSTEMINDEX WHERE $WHERE AND SYSTEM.SEARCH.STORE LIKE 'File' ORDER BY $OrderBy $Direction" Write-Debug $SQLQuery } #Search Index DB Connector $Connector = [System.Data.OleDb.OleDbConnection]::new("Provider=Search.CollatorDSO;Extended Properties='Application=Windows';") $Command = [System.Data.OleDb.OleDbCommand]::new($SQLQuery,$Connector) $Adapter = [System.Data.OleDb.OleDbDataAdapter]::new($Command) $DS = [System.Data.DataSet]::new("SearchQuery") $ResultCount = $Adapter.Fill($DS) #Catch Too many Results IF ($force) { Write-Host "This might take a moment..." } elseif ($ResultCount -gt 750){ [int]$DefaultChoice = 1 $Yes = [System.Management.Automation.Host.ChoiceDescription]::new("&Yes", "Continue with raw output") $No = [System.Management.Automation.Host.ChoiceDescription]::new("&No", "Stop") $options = [System.Management.Automation.Host.ChoiceDescription[]]($yes, $no) $choice = $host.ui.PromptForChoice("Too many matches ($ResultCount Results)","Continue with Raw View?", $options,$DefaultChoice) if ($choice -eq 0) { $View = "Raw" } else { break } } #Display Error if no Results returned IF ($ResultCount -eq 0){ Write-Host "No matching Items found inside IndexDB" -ForegroundColor Red } } #End Process Block end { #Display Output $selection = "" IF ($View -eq "Single"){ #View Single $DS.Tables[0].Rows | Select-Object -Property ($Select -split ", ") | Out-ConsoleGridView -Title "Windows Search Query: $Query ($ResultCount Results)" -OutputMode Single -OutVariable selection | Out-Null } elseif ($View -eq "Multi") { #View Multi $DS.Tables[0].Rows | Select-Object -Property ($Select -split ", ") | Out-ConsoleGridView -Title "Windows Search Query: $Query ($ResultCount Results)" -OutputMode Multiple -OutVariable selection | Out-Null } elseif ($View -eq "Raw") { #View Raw $DS.Tables[0] | Select-Object -ExpandProperty SYSTEM.ITEMPATHDISPLAY break } elseif ($View -eq "Legacy") { #View Legacy $DS.Tables[0].Rows | Select-Object -Property ($Select -split ", ") | Out-GridView -Title "Windows Search Query: $Query ($ResultCount Results)" -OutputMode Single -OutVariable selection | Out-Null } else { #View Default (Single) if nothing specified $DS.Tables[0].Rows | Select-Object -Property ($Select -split ", ") | Out-ConsoleGridView -Title "Windows Search Query: $Query ($ResultCount Results)" -OutputMode Single -OutVariable selection | Out-Null } #Wait for Selection While ($null -eq $selection){} #Selection IF ($null -ne $selection.'SYSTEM.ITEMPATHDISPLAY'){ $Global:Item = $selection.'SYSTEM.ITEMPATHDISPLAY' Write-Host "Selected item stored inside `$Item var" -ForegroundColor Green $item; "`n" IF ($Mode -eq "Shell") { #Selection Shell $selection | ForEach-Object {Start-Process explorer.exe $_.'SYSTEM.ITEMPATHDISPLAY'} } elseif ($Mode -eq "Console") { #Selection Console Set-Location -Path (Split-Path $Item) } elseif ($Mode -eq "None") { #Selection None } else { #Selection Default (Shell) $selection | ForEach-Object {Start-Process explorer.exe $_.'SYSTEM.ITEMPATHDISPLAY'} } } } } Export-ModuleMember -Function Find-File |