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