-- 一時テーブル 
CREATE TABLE #temp_index_info (a text, b text, c text)
CREATE TABLE #index_info (server_name text, db_name text, table_name text, index_name text, index_description text, index_keys text)

-- データベースは ! を使い、テーブルは ? を使うことで全DB全テーブルに対して処理をする。
EXEC sp_MSforeachdb @replacechar ='!', @command1='
    USE [!];
    EXEC sp_MSforeachtable ''
        INSERT #temp_index_info EXEC sp_helpindex ''''?'''';
        INSERT #index_info SELECT @@SERVERNAME, ''''!'''', ''''?'''', * FROM #temp_index_info;
        TRUNCATE TABLE #temp_index_info;
    ''
';
DROP TABLE #temp_index_info;

SELECT * FROM #index_info;
DROP TABLE #index_info;

学び

今回は sp_MSforeachdb , sp_MSforeachtableを初めて活用した。

めっちゃ便利。これからも使っていく。

あと、弊社内でもこういうインデックスの情報とかをまとめて簡単に参照できるウェブページが作れるといいね。

設定情報に加えて意図や目的をメモする欄とか入れたりして、DB自体の情報に対して説明を入れ込むことがしたい。

属人化も減るし、社内メンバーの理解度の底上げにもなると思う。

以上！

2019-09-21

仮想マシンのWindows Server 2012 R2にSQL Server 2014 Expressをインストールするために必要な.NET Framework 3.5のインストールで苦しんだ結果「これで大丈夫？」って方法になった話。

SQL Server Windows Server .Net Framework

タイトル長すぎる。

結論

仮想マシンでインストールメディアのないWindows Server 2012 R2 に SQL Server 2014 Expressをインストールする時に必要な .Net Framework をインストールするには、Windows Server 2012 R2 の評価版のISOからSxSフォルダを抽出してそこを指定してインストールする。

実際この方法で解決したんですけれど、大丈夫なんすかこの方法。問題あれば誰かご指摘ください・・・。

経緯

自社テスト環境のために以下の環境を構築することになりました。

Windows Server 2012 R2
SQL Server 2014 Express

マシンの調達

そのため、まず仮想マシンを調達。

弊社はKDDIの閉域網WVSを使っており、その中で仮想マシンを調達できるKCPSを利用しています。

KCPSから仮想マシンを調達。

SQL Server 2014 Express の調達

以下からダウンロード

www.microsoft.com

SQL Server 2014 Expressのインストール

インストールを試したところ失敗。 .Net Framework 3.5 が必要なようです。

スクショ忘れた。

.Net Framework 3.5のインストール

.Net Framework 3.5 のインストール方法を調べたところ、以下の方法が見つかりました。

blogs.osdn.jp

が、それが失敗。原因がわからずググることに。

.NET Framework 3.5 インストール時のエラー: 0x800F0906、0x800F081F、0x800F0907 https://support.microsoft.com/ja-jp/help/2734782/net-framework-3-5-installation-error-0x800f0906-0x800f081f-0x800f0907

で、これも失敗。

Windows Updateなんども当てて最新にしようと、パッチを当てようとインストールはできませんでした。

評価版からのインストール

他の方法は「インストールメディアを指定してそれをソースとしてインストール」しかなく、インストール済の仮想マシンを調達したこの場合はインストールメディアもないので試せない。

インストールメディアが必要な理由はその中のSxSフォルダを参照するからなので、極端な話SxSフォルダさえあればいい。

Windows Server の評価版のISOから抜き出してSxSフォルダを使えばいい。

という発想のもと評価版をインストールしマウント。 SxSフォルダのみ抽出し、役割と機能の追加からやっと.Net Framework 3.5 をインストールすることができました。

SQL Serverのインストールも無事でき、環境構築が完了しました。

懸念

評価版のISOから抜き出してSxSフォルダを参照することに関して、ライセンス面での問題はあるのだろうか・・・

こういう懸念が出たとき、どうやって調べていくのがよいのか。

以上！

2019-09-18

SQL Serverのセキュリティ保護可能なリソース、全部出す。

SQL Server

2019/09/18 追記

取得ということに焦点を当てて検索してコピペで作ったけれど、今後は sys.database_permissions をきっちり見ていく必要があるなと思った。今回は許可のみ（現在のシステムがそうなっている）で考えていたけれど、拒否もあれば permission stateも表示すると正確に把握できる。

docs.microsoft.com

引き継いだシステムの検証用データベースの構築をしたのですが、セキュリティ保護可能なリソースの設定漏れが発生したので再発防止のため、表題のクエリを作成した。

SSMSで一つづつポチポチプロパティを変更して設定していったため作業漏れが発生したので、まず一覧がほしいと思ったのでそこまで。将来的には権限を設定するクエリも作成しておいて、検証用DBの作成が楽になるといいな。

日英表現違いに注意
クエリ
参考
- sp_MSforeachdb
- Securables の取得

日英表現違いに注意

URLとか英語で読むにしたら分かるんですが、セキュリティ保護可能なリソースは英語で securables です。

docs.microsoft.com

securables でググるだけでもかなり情報へのアクセスしやすさが変わりました。

クエリ

一時テーブルはめっちゃ適当です。

CREATE TABLE #secuarables_alldb (
    server_name text,
    database_name text,
    object_name text,
    user_name text,
    permission_name text
);

EXEC sp_MSforeachdb '
USE ?;

INSERT INTO #secuarables_alldb
SELECT
   @@servername as server_name, ''?'' AS database_name, OBJECT_NAME(major_id) as object_name, USER_NAME(grantee_principal_id) as user_name, permission_name
FROM
    sys.database_permissions p
WHERE
    p.class = 1 AND
    OBJECTPROPERTY(major_id, ''IsMSSHipped'') = 0
ORDER BY
    OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name
';


select * FROM #secuarables_alldb;
DROP table #secuarables_alldb;

参考

sp_MSforeachdb

めっちゃ便利だった。こんなものがあるとは。。。
勉強の過程でこういうのってどうやってたどり着くんでしょうね。

blog.engineer-memo.com

Securables の取得

stackoverflow.com

2019-08-15

Wi-Fi Arubaのshow clientsを取得してDBに入れて見える化するまで

VBScript PowerShell Aruba

無線アクセスポイントが場所により遅いなどトラブルがあり、手作業でログを取得して解析しているチームがあったので、以下の点で手伝ってみました。

自動取得からDB取り込み
見える化（Power BI）

目的

アクセスポイントへのアクセスを見える化して、障害時や問合せ対応のときに即座に参照できるようにする。

やったこと

自動取得からDB取り込み

データの取得

arubaにtelnetで接続し、 show clients というコマンドを実行することでデータが取れるもよう。

www.arubanetworks.com

結果はこうなる

f:id:NotShown:20190815142202p:plain — show clients 結果

データの出力

どうにかCSVにしたかったけれど方法が見つからず、しょうがないのでtelnetのログファイル出力をし、それをどうにかして読み込むことにしました。

f:id:NotShown:20190815142458p:plain — telnet /?

無事、ログファイルはできましたが、コマンドプロンプト上の改行も加味されて出力されてしまうので、コマンドプロンプトの設定を以下のように設定

フォント:8
ウインドウのサイズ: 幅250 ※環境依存かと思います。

出力データの整形

PowerShellで無理やり組みました。以上。

SignalやSpeedについては速度と評価が併記されているので、分解して数値として取り込みやすくしました。＃現在では活用できず。。。

<#
 Aruba のログ解析（クライアント）
 telnetから標準出力をテキストとして出力したものを元にCSVとして吐き出す。
#>


# 設定値
$csvPath = "show_clients.csv"
$csvText = ""
$hasHeader = $true
$headers = "Name","IP Address","MAC Address","OS","ESSID","Access Point","Channel","Type","Role","IPv6 Address","Signal","Speed (mbps)"
$TableTitle = "Client List"
$TableTail = "Number of Clients"

# 事前処理
Remove-Item $csvPath -Force -ErrorAction Ignore
$now = (Get-Date)
$text = Get-Content .\show clients.log
$columnIndex = @()

$isSkip = $true

for($i=0; $i -lt $text.Count;$i++){
    $line = $text[$i]
    
    # ヘッダーの取得
    if($line -eq $TableTitle){
        $i += 2;
        $headerText =  $text[$i]
        # ヘッダーテーブルのフォーマット取得
        for($j = 0; $j -lt $headers.Count;$j++){
            $columnIndex += $headerText.IndexOf($headers[$j])
        }
        if($hasHeader){
            $csvText += (("DateTime,"+($headers -join ",")) + "`r`n")
        }
        $i++
        $isSkip = $false
        continue;
    }
    if($text[$i].StartsWith($TableTail)){ $isSkip = $true }

    # スキップ
    if($isSkip){ continue; }

    $csvLine = $line
    for($ci = $columnIndex.Length -1; $ci -gt 0; $ci--){
        $csvLine = $csvLine.Insert($columnIndex[$ci],",")
    }
    $csvText += (("$($now.ToString('yyyy-MM-dd HH:mm:ss.fff')),"+$csvLine) + "`r`n")
}

# Signal とSpeed を分解してわかりやすくする

ConvertFrom-Csv $csvText | 
    select DateTime,
    "Name","IP Address","MAC Address","OS","ESSID","Access Point","Channel","Type","Role","IPv6 Address",
    @{Name="Signal Value";Expression={$_.Signal -replace "\(.*","" }},
    @{Name="Signal Grade";Expression={$_.Signal -replace "[\d\(\)]",""}},
    @{Name="Speed (mbps) Value";Expression={$_."Speed (mbps)" -replace "\(.*","" }},
    @{Name="Speed (mbps) Grade";Expression={$_."Speed (mbps)" -replace  "[\d\(\)]",""}} |
    ConvertTo-Csv -NoTypeInformation |
    %{ $_.Replace('"','') -replace " +,",',' } | 
    Set-Content -Path $csvPath

データ取得処理の自動化

単純なのでVBScriptでオートメーション化しました。

command="show clients"
path="C:\Aruba\"& command &".log"
ip=""
userId=""
password =""

set oShell = CreateObject("WScript.Shell")
oShell.run "Telnet -f " & path
WScript.Sleep 1000
oShell.SendKeys("Open " & ip & "{Enter}")
WScript.Sleep 5000
oShell.SendKeys(userId & "{ENTER}")
WScript.Sleep 1000
oShell.SendKeys( password & "{ENTER}")
WScript.Sleep 1000
oShell.SendKeys(command & "{ENTER}")
WScript.Sleep 10000
oShell.SendKeys("exit{ENTER}")
WScript.Sleep 500
oShell.SendKeys("{Enter}")
WScript.Sleep 1000
oShell.SendKeys("quit{Enter}")
WScript.Sleep 1000

データの取り込み

bcp コマンドで取り込み

docs.microsoft.com

見える化（Power BI）

アクセス数

情報取得した時間のアクセス数はMACアドレスのユニークカウントとしました。また、下記の観点でアクセス数を絞り込みます。 - アクセスポイント名 - SSID名

フロアマップで見たくない？

BI製品の宣伝によくある「地図上に円がプロットされて、円のサイズでその数字を表す！」なグラフを作りたい、と思ったので散布図を活用して試してみました。

散布図に必要なデータは以下のようにしています。

日時：取得日時
X座標：フロアマップのX座標
Y座標：フロアマップのY座標
値：アクセスしているクライアント数

座標のとり方

Excelにフロアマップ画像を貼り付けて、背景色を透過することで力技で座標を設定。アクセスポイント名をリレーションのキーとして紐付けます。

アクセスポイント名	X座標	Y座標
AP_FUGA	2	9
AP_HOGE	8	7
AP_FOO	7	2

出来上がり

f:id:NotShown:20190815164329p:plain — PowerBI フロアマップ散布図

中心はずれているけれど大体あえばいい。とりあえず、なんとなく形になったので完成。

大体合計1.5日くらいかかりましたが、満足の出来です。取り込み処理も定期実行に載せたのでこれからデータがじわじわ貯まるのが楽しみです。これで集中しているAPなどが一目で分かり、次のアクションにつながるとよいなと思っています。

2019-07-18

弊社現場レベルで使えるであろう簡単な社内FAQページを作った

Vue.js PowerShell VBA Excel

経緯

新しい施策を社内で行うと問い合わせが増える。その問い合わせがある程度蓄積してきたのでFAQを立てようと思い作りました。あくまで弊社現場レベルなのでレベルは高くありません。

経緯
環境
作ったもの

環境

Windows Server + IIS
社内イントラのサーバーに間借りして設置
社内イントラ関連ファイル(html/css/jsなど)のフォルダは共有されており、許可された者のみ編集可能
なんでもExcelで管理しているからメンテはExcelでできるとよい。
- 弊社現場レベルで使えるのはExcelくらい

作ったもの

FAQページ(Bootstrap4 + jQuery 3.3 + Vue.js)
ExcelのFAQ台帳(CSVを吐くマクロ付き)
PowerShell スクリプト(CSVをjson形式に変換)

まずはFAQページを作り、Vue.jsでうまいことデータを表示し、
メンテに気を使いつつ、Vue.jsにわたすjsonを作れるExcelマクロとPowerShellを組みました。

全体像

こんな感じのExcelにFAQを追加して更新すると。。。 f:id:NotShown:20180820221646p:plain

FAQページがこうなります。 f:id:NotShown:20180820222047p:plain

こんな環境を作りました。

ExcelのFAQ台帳

FAQの源流
保存前タイミングでCSVを出力、そのCSVをjsonに変換するPowerShell起動
- マクロボタンとかを用意するとメンテする人が押し忘れる可能性があるので保存前

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim sheet As Worksheet
    Dim name As String

    Dim csv As String: csv = ThisWorkbook.Path & "\qna.csv"
    Dim ps As String: ps = ThisWorkbook.Path & "\convertQnACSV2JSON.ps1"
    
    
    Set sheet = Worksheets("Q&A")
        
        ' ファイル名
        name = ThisWorkbook.Path & "\qna.csv"

        ' シートを別のワークブックにコピーする
        sheet.Copy

        ' コピーしたワークブックを上書き保存
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=name, FileFormat:=xlCSV
        Application.DisplayAlerts = True

        ' コピーしたワークブックを閉じる
        ActiveWorkbook.Close SaveChanges:=False

    strCommand = "Powershell -File """ & ps & """"
    Set WshShell = CreateObject("WScript.Shell")
    WshShell.Exec (strCommand)
End Sub

PowerShell スクリプト

Excelブックマクロから出力されたCSVをjson形式に変換します。

FAQデータは以下の構造として捉えています（わかりにくい）

ジャンル別Q&Aのリスト
- 単一Q&A
  - Question
  - Answer
  - 参考リンクのリスト
    - タイトル
    - URL

<#
 # Q&AコンテンツのCSVをJson形式に変換する
 
 CSVレイアウトは以下
 | genre | id | q | a | title1 | url1 | title2 | url2 | ～ | url5 |
 
 #> 
Push-Location -Path (Split-Path $MyInvocation.MyCommand.Path)

$csv = Import-Csv ".\qna.csv" -Encoding Default

# スコープはjson全体
$json = @{content=(New-Object System.Collections.ArrayList)}

# スコープはジャンル別のグループ
foreach($gen in ($csv | group "genre")){

    # スコープは各Q&A
    $jgen=@{genre=$gen.Name;qnas=(New-Object System.Collections.ArrayList)}
    foreach($qna in $gen.Group){

        # Q&A参考リンクは列でタイトルとURLを表現している。
        #　5つまでのタイトルとURLの組み合わせを配列に変換する。
        $tmpLinks = @(
            @{title=$qna.title1; url=$qna.url1},
            @{title=$qna.title2; url=$qna.url2},
            @{title=$qna.title3; url=$qna.url3},
            @{title=$qna.title4; url=$qna.url4},
            @{title=$qna.title5; url=$qna.url5}
            )

        # リンクは自由入力欄のため、ある場合のみリストに加える
        $jqna = @{id=$qna.id;q=$qna.q;a=$qna.a;keywords=$qna.keywords;links=(New-Object System.Collections.ArrayList)}
        foreach($link in $tmpLinks){
            if($link.url -ne ""){
                # タイトルなしの場合URLにする
                if($link.title -eq ""){
                    $link.title = $link.url
                }
                $jqna.links.Add($link)
            }
        }
        $jgen.qnas.Add($jqna)
    }
    $json.content.Add($jgen)
}

# 出力する
Set-Content qna.json ( ConvertTo-Json $json -Depth 6 ) -Encoding UTF8

FAQページ

見た目は全体像の通り、FAQはアコーディオンで開きます。

なお、こちらはBootstrapのフリーのFAQテンプレートを流用して作っており、参考URLと検索欄の他はほぼそのまま。 www.prepbootstrap.com

このページに変換機能もあったので気まぐれでBootstrap4にアップデートしています。

テンプレからの変更点１：検索機能

検索欄に入力すると、FAQが絞れます。検索というよりは絞り込みですね。既存の環境はいじれないのでバックエンドは考えずフロントでどうにかしています。

function search() {
    // 検索条件を取得、スペースで区切られた各条件で検索する。
    var input = document.querySelector("#search-criteria");
    var conditions = input.value.split(/ |　/).filter(function(elem) {
        return elem != "";
    });

    // 正規表現の作成
    var tmpExp = "";
    conditions.forEach(function(c, i, a) {
        tmpExp += ("(?=.*" + c + ")");
    });
    var exp = "^" + tmpExp;
    var regexp = new RegExp(exp, "i");

    // Q&Aの各要素を取得。条件にヒットしないものを非表示にする。
    var divTmp = document.querySelectorAll(".qna")
    divs = Array.prototype.slice.call(divTmp, 0)
    divs.forEach(function(div, index, ar) {
        div.style.display = regexp.test(div.innerHTML) ? "" : "none";
    });
}

テンプレからの変更点２：Vue.js

以下の理由からVue.jsに触れることにしました。

最近流行っている技術に触れたい
メンテナンスを楽にしたい
- jsonを更新するだけで反映される構造にしたい
とはいえシンプルなものでさっさと作りたい
- Reactも気になっていたんですが、環境作るのが難しそうで今回は断念

Web開発の業務経験がないので、こんな理由で使っていいのかアレですが、いろいろ興味のあるものは触れてみると楽しいので。

活用したのは「FAQのjsonデータを元にDOMを作る」目的です（言葉の使い方が正しいか不安）以前にも riotjsを使ったことがあったので、スムーズに取り入れることができました。

具体的には以下のようなコードを書きました。

<div class="container">

    <br />
    <div class="form-group">
        <label for="usr">検索</label>
        <input type="text" class="form-control" id="search-criteria" placeholder="検索" onkeyup="search()">
    </div>
    <div class="" id="accordion">
        <div id="qnaAll">
            <div v-for="qnaByGenre in content">
                <div class="faqHeader">{{ qnaByGenre.genre }}</div>
                <div class="card qna" v-for="qna in qnaByGenre.qnas">
                    <div class="card-header">
                        <h4 class=""><a class="accordion-toggle collapsed" data-toggle="collapse" data-parent="#accordion" v-bind:href="'#' + qna.id">{{ qna.q }}</a></h4>
                    </div>
                    <div v-bind:id="qna.id" class="panel-collapse collapse">
                        <div class="card-block">
                            <div class="answer">{{ qna.a }}</div>
                            <div style="display:none">検索用キーワード {{ qna.keywords }}</div>
                            <div class="links" v-if="qna.links.length > 0">
                                <hr />
                                <h5>参考リンク</h5>
                                <ul v-for="link in qna.links">
                                    <li><a v-bind:href="link.url" target="_blank"> {{ link.title }}</a></li>
                                </ul>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

要件

概要

前提

クエリ

学び

結論

経緯

マシンの調達

SQL Server 2014 Express の調達

SQL Server 2014 Expressのインストール

.Net Framework 3.5のインストール

評価版からのインストール

懸念

日英表現違いに注意

クエリ

参考

sp_MSforeachdb

Securables の取得

目次

目的

やったこと

自動取得からDB取り込み

データの取得

データの出力

出力データの整形

データ取得処理の自動化

データの取り込み

見える化（Power BI）

アクセス数

フロアマップで見たくない？

座標のとり方

出来上がり

経緯

環境

作ったもの

全体像

ExcelのFAQ台帳

FAQページ

テンプレからの変更点１：検索機能

テンプレからの変更点２：Vue.js