I am trying to use PowerShell and Windows Search to get some statistics of my disk usage. This is what I came up for so now:
$sql = "SELECT System.FileExtension, System.Size FROM SYSTEMINDEX WHERE DIRECTORY = 'C:\Users\...\Documents' AND System.FileExtension = '.docx'"
$provider = "provider=search.collatordso;extended properties=’application=windows’;"
$connector = New-Object System.Data.OleDb.OleDbDataAdapter -Argument $sql, $provider
$dataset = New-Object System.Data.DataSet
if ($connector.fill($dataset)) {
$res = $dataset.Tables[0]
$res | Group-Object System.FileExtension |
Select-Object System.FileExtension,
@{Name='Total';Expression={($_.Group | Measure-Object SYSTEM.Size -Sum).Sum}} |
Format-Table
}
This works, but it looks kind of ugly. I also found and tried this:
$sql = "SELECT System.Size FROM SYSTEMINDEX WHERE DIRECTORY = 'C:\Users\...\Documents' AND System.FileExtension = '.docx'"
$provider = "provider=search.collatordso;extended properties=’application=windows’;"
$connector = New-Object System.Data.OleDb.OleDbDataAdapter -Argument $sql, $provider
$dataset = New-Object System.Data.DataSet
if ($connector.fill($dataset)) {
$dataset.Tables[0].Compute("Sum(SYSTEM.Size)")
}
which looks a lot nicer, but throws an error:
Cannot find an overload for "Compute" and the argument count: "1"
: Exception calling "Fill" with "1" argument(s): one or more errors [...]
and "FullyQualifiedErrorId : OleDbException", which made me believe any aggregate SQL functions where not allowed when querying Windows Search database. – mokum Nov 05 '15 at 19:50