0

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"

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
mokum
  • 25
  • 5
  • 2
    _Kind of ugly_ is a subjective phrase. What are you expecting it to look like? Also _does not work_ does not describe what is actually wrong. – Matt Nov 05 '15 at 16:05
  • It might look less ugly to you if you stayed consistent with your capitalization and formatting. (Edited to be less abrasive.) – Benjamin Hubbard Nov 05 '15 at 17:04
  • Can you not do the calculation directly in the SQL? `SELECT SUM(System.Size) FROM ...` – Eris Nov 05 '15 at 18:54
  • @matt: kind of ugly really means: difficult to read for someone with little powershell experience like me. It also looks like doing something easy the hard way. The easy way - for me - would be the SELECT SUM() approch, which I already tried. That fails on the fill($dataset) - will try to post the exact error. The "Compute" way looked nice and clean, but throws an error Cannot find an overload for "Compute" and the argument count: "1" – mokum Nov 05 '15 at 19:46
  • @matt: the error using the SELECT SUM() approch is something link
    : 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
  • sorry about the formatting, trying to figure out how that works too... – mokum Nov 05 '15 at 20:02
  • @mokum If you need to provide additional information: please edit your question. Comments are not the right place. – Ansgar Wiechers Nov 05 '15 at 21:24

0 Answers0