Safari の Web 閲覧履歴を CSV ファイルで出力する方法

#内容
新型コロナの影響で、在宅ワークが増え、社外からVPN接続により業務される方が多い状況かと思います。

VPNの接続を切れば、社内ネットワークに設定されたWebフィルタリングの影響を受けなくなり、普段許可していないサイトの閲覧が発生しがちです。すると、その利用端末からさらには他の業務端末までセキュリティ上の危険に晒される可能性があります。

そこで、普段から業務用コンピュータの監査を強化が求められます。今回は、監査の材料として、下記環境でのWeb閲覧履歴からWebフィルタリングのホワイトリストにないURLがないか確認し、あればそれをCSVファイルで出力する方法を掲載いたします。

#環境
- 業務機： Mac
- macOS: 10.15.x Catalina
- ブラウザ： Safari
- 使用言語: AppleScript, SQLite3


#必要な作業
###・ホワイトリストのテーブルをCSVで用意
あとでデータベース形式のWeb閲覧履歴と比較するためのテーブルをCSVファイルで用意します。CSVなら、SQLite3 でテーブルとしてインポートすることができます。

###・History.dbをFinderから別の場所へコピー
Safari のWeb閲覧履歴は、macOS Catalina だとデータベース形式のファイルに保存されています。

ファイルパス：
_/Users/(user)/Library/Safari/History.db_

このファイルは直接開ません。アクセス権を変更しても不可です。このファイルを別の場所にコピーすれば開けるようになります。シェルの cp コマンドではコピー不可のため、Finderでコピーする必要があります。スクリプトとしてそれを実現するには Apple Scriptを利用する必要がります。

###・SQLite3 で History.db とホワイトリストを比較して結果をCSVファイルで出力
SQLite3 は一般的な SQL のコマンドがだいたい使えます。ホワイトリストのURLを含まない閲覧履歴のみに絞って、結果をCSVファイルとして書き出します。


#具体的手順
###ホワイトリストのテーブルをCSVファイルで用意
テーブルは2カラム、それぞれ _name_ と _allowedURL_ にします。

| name | allowedURL |
|:----:|:----------:|
| Google  | https://www.google.co.jp/ |
| Qiita  |  https://qiita.com/ |
| Stack Overflow  | https://stackoverflow.com/  |

ファイル名を _urlWhiteList.csv_ とし、保存場所は _/var/tmp/_ （不可視フォルダ）にしました。


###AppleScript でメインのスクリプト作成

※SQLファイルの保存場所は、ホワイトリストのファイル同様、 _/var/tmp/_ （不可視フォルダ）にしました。

```AppleScript
# このスクリプトを実行するユーザのユーザ名を取得
set userName to do shell script "whoami"

# ホームフォルダのPOSIX Pathを取得
set homeFolderPOSIXPath to "/Users/" & userName & "/"

# AppleScript用に「:」区切りのパスに変換
set homeFolderPath to POSIX file homeFolderPOSIXPath

# Safari の Web 閲覧履歴のファイル（History.db）パスを取得
set historyDBPOSIXPath to homeFolderPOSIXPath & "Library/Safari/History.db"

# AppleScript用に「:」区切りのパスに変換
set historyDBPath to POSIX file historyDBPOSIXPath

# 前回コピーしたHistory.dbとそれに関連するファイルを削除
do shell script "rm -f " & homeFolderPOSIXPath & "History*"

# Finder で History.db ファイルをホームフォルダ直下にコピー
tell application "Finder" to duplicate historyDBPath to homeFolderPath

--当初 One Liner コマンドでやろうとしたが難しかった
--do shell script "sqlite3 -header -csv ~/History.db 'select datetime(v.visit_time + 978307200, \"unixepoch\", \"localtime\") as date, i.domain_expansion, v.title,  i.url from history_items i left join history_visits v on i.id = v.history_item order by date desc;' > ~/browsingHistory.csv"

#スクリプトファイルの保存先フォルダパス
set scriptFolderPath to "/var/tmp/"

# SQLite3をスクリプト化したsqlファイルを読み込む方法でWeb閲覧履歴とホワイトリストとの差分をcsvでホームフォルダ直下に出力
do shell script "sqlite3 ~/History.db '.read " & scriptFolderPath & "sqlite_browssing_h.sql' > ~/blackurl.csv"
```

###SQLite3 で実行するスクリプトをSQLファイルで作成

```SQL
.mode csv
.import /var/tmp/urlWhiteList.csv whitelist
.headers on
.separator ,
SELECT datetime(v.visit_time + 978307200, 'unixepoch', 'localtime') as date, i.domain_expansion, v.title, i.url, w.url 
FROM history_items i 
LEFT JOIN history_visits v 
ON i.id = v.history_item 
LEFT JOIN whitelist w 
ON i.url 
LIKE (w.url || '%') 
WHERE w.url IS NULL 
ORDER BY date desc;
.quit

```


スクリプトの内容について、より詳細に編集していく予定です。

#参考URL
[SQLite Tutorial](https://www.sqlitetutorial.net/)
[DBOnline](https://www.dbonline.jp/sqlite/)
[Sqlite: select, join using like for the join fields](https://stackoverflow.com/questions/31947692/sqlite-select-join-using-like-for-the-join-fields)
[SQLite3 Command Line Shell dot-commands マニュアル(完全版)](https://qiita.com/kanegoon/items/fc1e4bfea0984dbe4b90)