====== ログ検索ツール ======
[[:powershell|一つ上へ]]

<code powershell>
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

# sqlite3.dll を読み込む
Add-Type -Path "path\to\dll\System.Data.SQLite.dll"
# DBのファイルパス(無ければ新規作成)
$dbFile = "path\to\database\database.db"
$connStr = "Data Source=$dbFile;Version=3;"

function Convert-SearchKeyWord {
    param ([string]$input_str)
    # ログの性質上、ワイルドカードは使えない仕様とする。
    # バックスラッシュをエスケープ
    $p = $input_str -replace "\\", "\\\\"

    # % と _ をエスケープ
    $p = $p -replace "%", "\%" -replace "_", "\_"

    # 部分一致処理
    $p = "%$p%"

    return $p
}

function Search-Execution {
    param(
        [System.Data.SQLite.SQLiteConnection]$conn,
        [string[]]$keywords,
        [System.Data.DataTable]$dt
    )

    # データテーブルをクリア
    $dt.Clear()

    # コマンドオブジェクト
    $cmd = $conn.CreateCommand()
    $whereParts = New-Object System.Collections.Generic.List[string]

    for ($i = 0; $i -lt $keywords.Count; $i++) {
        $pattern = Convert-SearchKeyWord -input_str $keywords[$i]

        $whereParts.Add("message LIKE @p$i ESCAPE '\' COLLATE NOCASE")

        $param = $cmd.CreateParameter()
        $param.ParameterName = "@p$i"
        $param.Value = $pattern
        $cmd.Parameters.Add($param)
    }

    $whereClause = $whereParts -join ' AND '

#    $cmd.CommandText = "SELECT * FROM system_log WHERE time_generated > '$($begin_date.Value.ToString('yyyy-MM-dd'))' LIMIT 1000;"
    $cmd.CommandText = @"
    SELECT *
    FROM system_log
    WHERE $whereClause
    LIMIT 1000;
"@

    $reader = $cmd.ExecuteReader() # Readの時はOut-Nullの必要なし

    # DataTable を使う
    $dt.Load($reader)   # ここで reader から一括読み込みされる

    $reader.Close()
}

# SQLite 接続オブジェクトを作成
$conn = New-Object System.Data.SQLite.SQLiteConnection($connStr)
$conn.Open()

# フォーム作成
$form = New-Object System.Windows.Forms.Form
$form.Text = "マイツール"
$form.MinimumSize = New-Object System.Drawing.Size(900,600)
$form.Size = New-Object System.Drawing.Size(1200,800)
$form.MinimumSize = New-Object System.Drawing.Size(1200,800)
$form.MaximumSize = New-Object System.Drawing.Size(1500, 1000)

# 検索欄
## 検索ラベル
$searchLabel = New-Object System.Windows.Forms.Label
$searchLabel.Text = "検索ワード入力"
$searchLabel.Location = New-Object System.Drawing.Point(20,20)
$searchLabel.BorderStyle = [System.Windows.Forms.BorderStyle]::FixedSingle
## 注意書き
$searchNotification = New-Object System.Windows.Forms.Label
$searchNotification.Text = "*や?は通常の文字として処理しています。"
$searchNotification.Location = New-Object System.Drawing.Point(20,50)
$searchNotification.Width = 600
$searchNotification.BorderStyle = [System.Windows.Forms.BorderStyle]::FixedSingle

## 検索パネル BEGIN
$searchPanel = New-Object System.Windows.Forms.TableLayoutPanel
$searchPanel.Size = New-Object System.Drawing.Size(1150,30)
$searchPanel.Location = New-Object System.Drawing.Point(20,80)
$searchPanel.ColumnCount = 2
$searchPanel.RowCount = 1
$searchLabel.Padding = 5
$searchPanel.ColumnStyles.Add((New-Object System.Windows.Forms.ColumnStyle 'Percent', 100))
$searchPanel.ColumnStyles.Add((New-Object System.Windows.Forms.ColumnStyle 'Autosize'))

### 検索ワード入力欄
$searchBox = New-Object System.Windows.Forms.TextBox
$searchBox.Dock = 'Fill'
$searchBox.BorderStyle = [System.Windows.Forms.BorderStyle]::FixedSingle

### 検索ボタン
$button = New-Object System.Windows.Forms.Button
$button.Text = "検索"
$button.AutoSize = $true

$button.Add_Click({
    $searchBoxVal = $searchBox.Text
    if ([string]::IsNullOrWhiteSpace($searchBoxVal)) {
        return
    }
    else {
        $keywords = $searchBox.Text.Trim() -split '[\s　]+'
        $dt = New-Object System.Data.DataTable
        Search-Execution -keywords $keywords -conn $conn -dt $dt

        # データグリッドに描画
        $dataGridArea.DataSource = $null # 再描画前に初期化
        $dataGridArea.DataSource = $dt
        $dataGridArea.Columns["message"].DefaultCellStyle.WrapMode = [System.Windows.Forms.DataGridViewTriState]::True
        $dataGridArea.Columns["message"].AutoSizeMode = [System.Windows.Forms.DataGridViewAutoSizeColumnMode]::Fill
    }
})
## 検索パネル END

# 日付を選択させる
$begin_date = New-Object System.Windows.Forms.DateTimePicker
$begin_date.Location = New-Object System.Drawing.Point(20,200)
$begin_date.Width = 200


# データの表示
$dataGridArea = New-Object System.Windows.Forms.DataGridView
$dataGridArea.Location = New-Object System.Drawing.Point(250,120)
$dataGridArea.Size = New-Object System.Drawing.Size(900,600)
#$dataGridArea.AutoSizeColumnsMode = "Fill"
$dataGridArea.AutoSizeRowsMode = [System.Windows.Forms.DataGridViewAutoSizeRowsMode]::DisplayedCells
$dataGridArea.ReadOnly = $true
$dataGridArea.Anchor = [System.Windows.Forms.AnchorStyles]::Top `
    -bor [System.Windows.Forms.AnchorStyles]::Bottom `
    -bor [System.Windows.Forms.AnchorStyles]::Left `
    -bor [System.Windows.Forms.AnchorStyles]::Right


# コントロールを追加
$form.Controls.Add($searchLabel) # 検索ラベル
$form.Controls.Add($searchNotification) # 検索における注意書き
$form.Controls.Add($searchPanel)
$form.Controls.Add($begin_date)
$form.Controls.Add($dataGridArea)

$searchPanel.Controls.Add($searchBox, 0, 0)
$searchPanel.Controls.Add($button, 1, 0)

$form.Add_FormClosed({ $conn.Close() })
# フォームを表示
[void]$form.ShowDialog()
</code>