◆グループ集計（ブレーク集計）

PowerShell: ◆グループ化して最終データを生かすでGroup-Objectコマンドレットを使ったときにグループ集計もやってみようと思い、そのままになっていたので試してみようと思う。

そもそも、かつてはプログラムといえば「ソート、マッチング、ブレーク集計」するための物。
なのでグループ集計なんて簡単にできるのかと思ったのだが、意外と「これ」といったコマンドが見つからない。
仕方なく、ごねごねとやって見る。

ファイルサーバーで容量を食っているファイルはどんな種類のファイルかを見てみたかったので、まずはある拡張子のファイルを抽出して容量の合計を求める。

PS>(dir d:\documents -Filter *.xlsx -r | measure length -sum).sum /1kb 86.802734375

あとは、これを特定の拡張子だけではなく全てのファイルに対して行えば良い。

001 002 003 004 005 006 007 008 009 010 011

Dir d:\documents -r | ?{!$_.psiscontainer} | Group-Object Extension | %{   $len = $_ | select -ExpandProperty Group | measure -Sum -Property length   $len = $len.sum / 1kb   Add-Member -Type NoteProperty -Name Length -Value $len -Input $_ -PassThru } |      sort length -Descending |      ft name,count,@{                     name="length(kb)"                     expression={$_.length.ToString("#,##0.0")}                     alignment="Right"                     } -auto

もう少しすっきり書けても良いような気はするのだが・・・。

◆パイプライン入力のプロパティを使う

誰でも使っていると思うが、パイプライン入力を参照するには「$_」を使う。

PS>dir | %{$_.fullname} C:\Users\minminnana\Desktop C:\Users\minminnana\Documents C:\Users\minminnana\Favorites C:\Users\minminnana\Links C:\Users\minminnana\test

これは次のような局面でも同様に使える。例えば、ファイルの最終アクセス年でグルーピング。

PS>dir d:\documents -r | Group {($_.LastAccessTime).Year} Count Name                      Group ----- ----                      -----   640 2010                      {Downloads, Fax, Integration S    26 2011                      {SQL Server Management Studio,

Selectでも同様。

この場合、項目名のタイトルが($_.LastAccessTime).Yearとなるが、このタイトル名を指定するのが、これまでにも何度か出てきた集計プロパティという事になる。

PS>dir d:\documents -r | select name,@{name="Year";expression={($_.LastAccessTime).Year}}

◆Getコマンドレットの隠れたエイリアスを使う

Get-ChildItemコマンドレットのエイリアスを調べるには、次のようにすれば良い。

PS>Get-Alias -Definition Get-ChildItem CommandType     Name                                                          Definition -----------           ----                                                          ---------- Alias                   dir                                                           Get-ChildItem Alias                   gci                                                           Get-ChildItem Alias                   ls                                                             Get-ChildItem

エイリアスが定義されていない場合は以下のようになる。

PS>Get-Alias -Definition Get-Acl Get-Alias : definition 'Get-Acl' を含むエイリアスは存在しないため、このコマンドは一致するエイリアスを見つけられません 発生場所 行:1 文字:10 + Get-Alias <<<<  -Definition Get-Acl     + CategoryInfo          : ObjectNotFound: (Get-Acl:String) [Get-Alias]、ItemNotFoundException     + FullyQualifiedErrorId : ItemNotFoundException,Microsoft.PowerShell.Commands.GetAliasCommand

しかし、このGet-Aclコマンドレットには実はエイリアスが存在する。
「Acl」である。

どうも、Get-XXXXコマンドレットは全てGetを省略したXXXXがエイリアスになっているようだ。
なので、Get-ChildItemもGet-Aliasコマンドでは表示されてこないが、「ChildItem」だけで実行可能だ。
Get-ChildItemなどはdirとかlsを使ったほうが便利なのでChildItemを使うことはないだろうが、Get-Aclの様にエイリアスが定義されていないコマンドレットの場合は覚えておくと便利だろう。

◆SQL Server Powershell リソース

 

SQL Server PowerShell の概要

SQL Server PowerShell を使用した管理手法 第 1 回 概要編

SQL Server PowerShell を使用した管理手法 第 2 回 実践編

Server クラス (Microsoft.SqlServer.Management.Smo)

◆SQL Server PowerShellの環境を作る４

SQL Server PowerShellの環境を調査してきたが、リモート接続に難があったりしてあまり有用とは思えない。
たぶん、サーバー自身にタスクでスクリプトを仕掛けたりって使い方がメインになるのかな？

おそらくSQL Server PowerShellもSMOのラッパーだったりするのだろうから、今のところSMOを直接弄ったほうが使い勝手がよさげ。（環境的にもSMOをロードしておくだけだし）

というわけでSMOを使ったサンプルを。

001 002 003 004 005 006 007 008 009

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  $server =  New-Object Microsoft.SqlServer.Management.Smo.Server("Servername\instancename") $server.ConnectionContext.LoginSecure = $false #Windows認証の時はTrue $server.ConnectionContext.Login = "username"  $server.ConnectionContext.Password = "password"  $db = $server.databases["pubs"] $result = $db.ExecuteWithResults("select * from jobs")  $result.tables | select -ExpandProperty rows

SQLを投げるだけであればPowerShell: ◆PowershellでDBアクセス3あたりと大差ないと思うのだが、Serverオブジェクトを使えば管理タスクは何でもできそう。

ちなみに、PowerShell: ◆SQL Server PowerShellの環境を作るで作ったDBの容量一覧のスクリプトも、このServerオブジェクトを使って同様に取得できる。（結局中身は同じだろうし）

001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  $server =  New-Object Microsoft.SqlServer.Management.Smo.Server("Servername\instancename") $server.ConnectionContext.LoginSecure = $false  $server.ConnectionContext.Login = "username"  $server.ConnectionContext.Password = "password"  $dbinfos = @() $fmt = "#,##0.0"   #データベースの数だけ繰り返し foreach ( $db in $server.Databases ) {   $dbinfo = New-Object PsObject |        select データベース名,DB,空き, データ,インデクス   #データベース名   $dbinfo.データベース名　= $db.Name   #DBの容量   $dbinfo.DB = $db.Size.ToString($fmt) + "MB"   #DBの空き容量   $dbinfo.空き = ($db.SpaceAvailable / 1024).ToString($fmt) + "MB"   #データの容量   $dbinfo.データ = ($db.DataSpaceUsage / 1024).ToString($fmt) + "MB"   #インデックスの容量   $dbinfo.インデクス = ($db.IndexSpaceUsage / 1024).ToString($fmt) + "MB"   $dbinfos += $dbinfo } $dbinfos | ft –Auto

なお、SMOのロードにAdd-Typeを使うとエラーになる（環境にもよりそうだが）。
Add-Typeはストロングネームを指定する必要があるとの事。

try { Add-Type -Assembly Microsoft.SqlServer.Smo } catch { Add-Type -Assembly 'Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' }

詳しくは以下を参照。
CTP3/V2b - Add-Type -a "Microsoft.SqlServer.Smo" won't load SMO assemblies | Microsoft Connect

◆SQL Server PowerShellの環境を作る３

sqlps環境はPowershellのスパーセットなのかと思っていたら、どうも違うようだ。
色々と使えないコマンドがあったりして使い勝手が違う。
スーパーセットではなくサブセットなのね・・・。

そこで、通常のPowershell環境で以下のスクリプトを実行するとPowershell+SQLServer用Snapin環境となるようだ。
（MS資料からの転載）SQL Server PowerShell の実行

001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037

# # Add the SQL Server Provider. # $ErrorActionPreference = "Stop" $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps" if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue") {     throw "SQL Server Provider for Windows PowerShell is not installed." } else {     $item = Get-ItemProperty $sqlpsreg     $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path) } # # Set mandatory variables for the SQL Server provider # Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0 Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30 Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000 # # Load the snapins, type data, format data # Push-Location cd $sqlpsPath Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 Update-TypeData -PrependPath SQLProvider.Types.ps1xml  update-FormatData -prependpath SQLProvider.Format.ps1xml  Pop-Location

こいつをドットソースで取り込むなり、Profileに入れておくなりしておけば以下のような感じで簡単にSQLを発行できるようになる。

PS>invoke-sqlcmd -Database pubs -Query "select * from jobs" | ft -auto job_id job_desc                     min_lvl max_lvl ------ --------                     ------- -------      1 New Hire - Job not specified      10      10      2 Chief Executive Officer          200     250      3 Business Operations Manager      175     225      4 Chief Financial Officier         175     250      5 Publisher                        150     250      6 Managing Editor                  140     225      7 Marketing Manager                120     200      8 Public Relations Manager         100     175      9 Acquisitions Manager              75     175     10 Productions Manager               75     165     11 Operations Manager                75     150     12 Editor                            25     100     13 Sales Representative              25     100     14 Designer                          25     100

◆SQL Server PowerShellの環境を作る２

PowerShell: ◆SQL Server PowerShellの環境を作るではローカルのSQLServerに接続した。

SQLServerの管理となれば当然リモートからなのでリモート接続を試して見た。
のだが、これがうまくいかない・・・・。

MSの資料によるとSQLServer認証の場合は仮想ドライブを作ってそこに資格情報を結び付けろとある。

001 002

New-PSDrive mysql -PSProvider SqlServer ` -Root "SQLSERVER:\SQL\ServerName\Default" -Credential (Get-Credential)

ちなみにデフォルトインスタンスでもインスタンス名は省略できず、「Default」と指定する必要があるらしい。

ここで表示された資格情報ダイアログに、 sa/hoge とか入れてやれば良いはずなのだが、以下のようなエラーが出る。

警告: SQL Server サービスの情報を取得できませんでした。'ServerName' の WMI への接続が次のエラーで失敗しました: アクセスが拒否されました。 (HRESULT からの例外: 0x80070005 (E_ACCESSDENIED))

メッセージからするとWMIレベルでエラーになっているようだ。
確かに、自PCからサーバーへはWMIで接続はできない。（まぁ、普通そうでしょ）
WMIを使ってSqlServerに認証を投げるには２種類の認証を指定しなければダメっぽ。

いろいろ調べてみたがそんな方法はなさげ・・・。
やっと以下の情報にたどり着いた。
New-PSDrive with SQL Authentication internally is using Integrated Authentication | Microsoft Connect

ん～、結局バグかい・・・。
っていうか仕様自体が破綻していない？

試しにWMIでアクセス可能なPCに対して上記スクリプトを実行したところ接続に成功した。
とはいえ、そもそもWMIでアクセス可能だったらWindows認証で良いわけなんでほとんど意味が無い。

というわけでSQLServerプロバイダーは使う環境が限られてきそうだ。

なお、テストで他PCのExpressを使う場合はリモート接続を有効にする必要がある。
SQL Server 2008 Express にリモート接続