発表_EXCEL_ACCESS_Microsoft365連携

https://connpass.com/event/191325/presentation/
発表資料です。

Transcript

1. EXCEL,ACCESS, Microsoft365連携勉強会 発表 RYU.CYBER（冴場 竜） TWITTER: @CYBERWINTELLIG1
2. 今回の仮想職場の実態？？ 半沢直樹のドラマの舞台である銀行のように、いくつかの組織が統廃合によって1つになった組織 旧組織の派閥など壁がある 現場第一主義のため、会社でいうと本社にあたる部門の権力が弱い 勝手にサブ組織が独自ルールで運用 システムの勝手に作って運用 ファイルサーバーから（共有アカウントのオンパレードetc） ちょっと前にトップのボスが声がでかい人になり、本社にあたる部門が強化されてきて、やっと当たり 前になってきたかな？
3. でも ◦◦申請書はEXCEL 手書きを清書しただけとかわらん 申請書→目視→システム投入 自分たちで作業が嫌なので、単価の安いところに丸投げ やっと全組織共通のADができたくらい それまではシステムごとにアカウント管理
4. セキュリティー面から アカウント管理はセキュリティー管理のイロハのイの字の一画目 追加申請 → ないと困るので、申請はなされます 削除申請 → 無視。真面目にやるやつはほぼ皆無。 ISMS上の削除整理しました書面だけ作成（書類の偽造）
5. ADの管理はどうなってるの？ 会社でいうと本社にあたる部門の権力が「まだ」弱い 勝手にサブ組織が独自ルールで運用したがる 本社相当部門で完全管理したいが・・・ 各サブ組織にOU割り当て、管理権限を委任
6. サブ組織 稼ぐ部門 面倒くさいことはやらない OU管理権限の役割与えられてる人は兼任で、評価につながらないので真面目にやらない 削除申請 → 無視。真面目にやるやつはほぼ皆無。 ISMS上の削除整理しました書面だけ作成（書類の偽造） 監査のときにあわててそろえる
7. 問題点 あるべき状態 OU管理権限者が、定期的に棚卸しする 現状 OU管理権限者は管理画面にログインすらしない でも、一括管理は嫌がる
8. だったら おまえの部署はこんだけひどい状況だぞ！！！と晒し者にするしかあるめ 晒し者にして組織評価減点材料を作ってやろう ボーナス査定下がるってならやるでしょ
9. 現状の見える化 これまでもそれなりにはやってました でも、ADから情報取り出し、手作業でEXCEL化 「ファイルサーバーの所定フォルダーに最新版おいてるから、該当者見てね」って状態 EXCEL化から配置まで、作業は単価の安い人に丸投げ
10. ここ1、2年で 組織内システムのクラウド化 ◦ Microsoft365導入 ◦ VDI導入
11. そしてコロナ禍 テンポラリーな要員は削減対象 特に、稼いでない本社相当組織は予算大幅削減 投資は０（ゼーロー) 自分たちで全部やれ
12. やったろうじゃん自動化 APTの名に懸けて
13. ADの状況 情シス管理のOU ◦ ユーザーは意識せず人事異動で自動的に更新 ◦ 組織構造、配属 サブ組織管理のOU ◦ 各サブ組織で管理 ◦ 案件毎などファイルサーバーアクセス権グループに
14. ADの状況 サブ組織は自分管理のアクセス権グループ実態を把握してない、しようとしてない サブ組織を横断したアクセス権グループはどちらのサブ組織（例えば、総務部、財務部）が主 管なのか？を理解しておらずアンマネージド
15. 情シス管理のOU
16. サブ組織管理のOU
17. サブ組織管理のOU
18. 表の結合（左外結合） サブ組織管理のOU ◦ アクセス権グループ ◦ そのアクセス権に 所属するユーザー一覧 • 情シス管理のOU • あるユーザーの所属組織 • 兼任も含めて 左外結合
19. 結合した表でわかること 例：コンサル部が管理する案件Aのアクセス権グループに誰が所属しており、かつ その誰がどこ の組織に居るか？ 人事異動があっても「その誰がどこの組織に居るか？」が児童反映 • 案件Aにはコンサル部の人間しか所属していけないのであれば、ア ラートが出せる
20. 全体図 組織内NW 組織AD ユーザーPC ファイルサーバー List アプリ アプリ データー利用 ユーザーPC 1 2 3 4 5 7 6 SQL
21. ①AD→EXCEL PowerQuery 【AD管理】Active Directoryの特定グ ループのユーザー設定を常に最新の状 態でExcelに一覧で表示(抽出)させる方 法 http://lifekeynotes.com/how-to-get-the- value-in-the-bottom-row-when-there-is- more-than-one-same-value-when-you- want-to-match-another-value-in-the- current-sheet-and-another-sheet-in- excel-to-get-another-specific-column-in- a-r/
22. ②ファイル保存 PowerQueryでデーター取り出した EXCELファイルの保存です。
23. ③EXCEL→ACCESS ACCESSの機能、EXCELからデーター連携機能 を利用する
24. ④SQL PowerQueryのEXCELから取り込んだデーター 4．1 情シス管理OU内のデーターから組織図をつくるSQL 4．2 サブ組織管理ごとのアクス権グループとグループ所属ユーザー一覧を作るSQL 4．3 4．2の集合 と 4．1の集合 をユーザーIDで左外結合するSQL
25. ⑤Sharepointリストへのアップロード ACCESSの機能、Sharepointリストからデー ター連携機能を利用する
26. ⑤Sharepointリストへのアップロード ACCESSと連携したSharepointリストは ACCESSのテーブルの1つと同様に扱える 参照、削除、更新、追加のSQLでデーター 操作可能
27. ⑥ファイル保存 ACCESSファイルの保存
28. ⑦Sharepointリストのユーザー利用 Excelにエクスポートアイコンを クリックするとiqyファイルがダ ウンロードされ、EXCELと Sharepointリストが連携した EXCELファイルが出来上がりま す Sharepointリストを利用して PowerAppsを作成することも可 能です。 ユーザー毎にそのユーザーが 所属しているアクセス権グルー プ一覧PowerApps
29. まとめ Microsoft365とofficeアプリは親和性が高いです ノンコードでデーター連係が可能です ◦ PowerQuery ◦ 外部データー連携機能
30. まとめ PowerQueryはさまざまなデーターとの連係ができます 1つのカラムの分解（ピボット解除）の機能があり、第一正規形への変換もGUIで構築できます ただメモリーを食う ユーザー領域ディスクの空き容量に注意
31. さいごに 調べて、３営業日くらいで作れました できたと思ったら、ACCESSとSharepointリストの連携できなくなったりトラブルはありましたが GUIだけでここまで簡単にできるとは驚きです 見える化して けしからん奴等には 怨送り 見える化して 性善説が成り立つ（情シスに協力的な）ユーザーには 恩送り