paloma blog

NWエンジニアやってます。技術の備忘など。Pythonもちょっと。タイトルは好きなカクテルから。

Turnkey LinuxにFirefly IIIをインストールする

一人暮らししてると家計簿つけようかなーとか考えるんですが、実際やるとすごいめんどうですよね。

レシート取っといて毎日記帳とかやってられません。
GNU Cashも使ってた時期がありましたが、多機能すぎて挫折しました。

そんなわけで支出は極力クレジットカードを使って記録して、後から集計できるようにしています。
月次の支払い集計ツールを作る スクリプト化する編 - paloma blog

そんな中面白そうなOSSの家計簿ツールを見つけたので作ってみました。

  • Firefly III

www.moongift.jp

FireflyといえばJuniperの仮想FWを思い出しますねw

構築

この手のツールはDockerfileとかovaが提供されていたらそのままデプロイして、出来た出来たw
とやっていたのですが、僕もインフラ屋の端くれなので今回は自分で作ってみようと思います。
(Firefly IIIもDockerfile提供されています)

要件

公式のインストールガイドを見ると

での構築がサポートされているようですね。

OSはCentOSUbuntuですがイチからインストールするのも大変なのでTurnkey Linuxで作ってみようと思います。

ちょうどnginxとphpVMがありました。
Turnkey LinuxDebianなのですが、たぶん大丈夫でしょう。

www.turnkeylinux.org

これをVirtualboxにインポートします。
起動直後にwebコンソールやsshが使えるのは楽ですね。

初期設定

ユーザ作成、IP設定等の初期設定は割愛します。
SElinux、Firewalld、iptablesはインストールされていないし、
家で使うのでこのまま未使用で行きます。

インストールディレクトリは/var/www/html/fireflyにします。

パッケージインストール

php7.3をインストールします。
Debian stretchのリポジトリにはまだ入っていないので下記を参考にインストールします。

def-4.com

masashi@nginx-php-fastcgi:/var/www/firefly$ sudo apt install php7.3 php7.3-fpm php7.3-mysql php7.3-mbstring php7..3-xml php7.3-gd php7.3-curl
composer

composerとはphpのパッケージ管理システムのようですね。
phpは全然使わないのでさっぱりです。

masashi@nginx-php-fastcgi:~$ curl -sS https://getcomposer.org/installer | sudo php -- --install-dir=/usr/local/biin --filename=composer
All settings correct for using Composer
Downloading...

Composer (version 1.9.1) successfully installed to: /usr/local/bin/composer
Use it: php /usr/local/bin/composer

masashi@nginx-php-fastcgi:~$ composer -v
   ______
  / ____/___  ____ ___  ____  ____  ________  _____
 / /   / __ \/ __ `__ \/ __ \/ __ \/ ___/ _ \/ ___/
/ /___/ /_/ / / / / / / /_/ / /_/ (__  )  __/ /
\____/\____/_/ /_/ /_/ .___/\____/____/\___/_/
                    /_/
Composer version 1.9.1 2019-11-01 17:20:17


...

OK!

Firefly III インストール

composerを使ってインストールします。
現時点の最新安定版4.8.1.8にします。

masashi@nginx-php-fastcgi:/var/www/firefly$ composer create-project grumpydictator/firefly-iii --no-dev --prefer-dist firefly-iii 4.8.1.8
Installing grumpydictator/firefly-iii (4.8.1.8)
  - Installing grumpydictator/firefly-iii (4.8.1.8): Downloading (connecting...)Downloading (100%)         
Created project in firefly-iii
> @php -r "file_exists('.env') || copy('.env.example', '.env');"
> @php -r "if (!(getenv('DYNO'))===false){file_exists('.env') || copy('.deploy/heroku/.env.heroku', '.env');copy('.deploy/heroku/.locales', '.locales');}"
Loading composer repositories with package information
Installing dependencies from lock file
Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - The requested PHP extension ext-bcmath * is missing from your system. Install or enable PHP's bcmath extension.
  Problem 2
    - The requested PHP extension ext-intl * is missing from your system. Install or enable PHP's intl extension.
  Problem 3
    - Installation request for adldap2/adldap2 v10.1.1 -> satisfiable by adldap2/adldap2[v10.1.1].
    - adldap2/adldap2 v10.1.1 requires ext-ldap * -> the requested PHP extension ldap is missing from your system.
  Problem 4
    - adldap2/adldap2 v10.1.1 requires ext-ldap * -> the requested PHP extension ldap is missing from your system.
    - adldap2/adldap2-laravel v6.0.8 requires adldap2/adldap2 ^10.1 -> satisfiable by adldap2/adldap2[v10.1.1].
    - Installation request for adldap2/adldap2-laravel v6.0.8 -> satisfiable by adldap2/adldap2-laravel[v6.0.8].


...

なんかエラーが出ました。
phpのパッケージが足りないようです。

  • sudo apt install php7.3-bcmath
  • sudo apt install php7.3-intl
  • sudo apt install php7.3-ldap

の3つを追加。
必要パッケージはガイドにちゃんと書いてありました。

再実行したらインストールできました。

...

+------------------------------------------------------------------------------+
|                                                                              |
| Thank you for installing Firefly III, v4.8.1.8!                              |
| This version of Firefly III requires PHP7.3.                                 |
|                                                                              |
+------------------------------------------------------------------------------+
> @php artisan key:generate
Application key set successfully.

作成されたディレクトリの権限変更します。

masashi@nginx-php-fastcgi:/var/www$ cd firefly/
masashi@nginx-php-fastcgi:/var/www/firefly$ sudo chown -R www-data:www-data firefly-iii
[sudo] password for masashi: 
masashi@nginx-php-fastcgi:/var/www/firefly$ sudo chmod -R 775 firefly-iii/storage
環境設定

.envファイルを自分の環境用に編集します。
ここドキュメント内でもノーヒントなのでちょっときつかったですね。

DB環境周りだけ変更。
HOSTはlocalhostだと後のフェーズでうまくいかなかったので127.0.0.1へ修正。
githubのissueにもありました。

404 "The requested URL /firefly-iii/public/login was not found on this server." · Issue #317 · firefly-iii/firefly-iii · GitHub

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=firefly
DB_USERNAME=firefly
DB_PASSWORD=<password>
DB初期化

3行コマンド打つだけなんですがなかなか通らず苦労しました。

  • php artisan migrate:refresh --seed
  • php artisan firefly-iii:upgrade-database
  • php artisan passport:install
masashi@nginx-php-fastcgi:/var/www/firefly/firefly-iii$ php artisan migrate:refresh --seed

In StreamHandler.php line 108:
                                                                                                               
  The stream or file "/var/www/firefly/firefly-iii/storage/logs/ff3-cli-2019-11-16.log" could not be opened:   
  failed to open stream: Permission denied                                                                     
                                                                                                               

www-dataユーザなので私のユーザでは権限がないというエラーです。
しかしsudoでやろうとまた別のエラーが出ます。

masashi@nginx-php-fastcgi:/var/www/firefly/firefly-iii$ sudo php artisan migrate:refresh --seed 

In Connection.php line 664:
                                                                                                               
  could not find driver (SQL: select * from information_schema.tables where table_schema = public and table_n  
  ame = migrations and table_type = 'BASE TABLE')                                                              
                                                                                                               

In PDOConnection.php line 31:
                         
  could not find driver  
                         

In PDOConnection.php line 27:
                         
  could not find driver  
                         

これもissueがありました。
Initialize the database not working · Issue #2628 · firefly-iii/firefly-iii · GitHub

sudoってこんな使い方も出来たんですね。

また、databaseやDBのユーザ作ってなかったというミスもここで回収済みです。

masashi@nginx-php-fastcgi:/var/www/firefly/firefly-iii$ sudo -u www-data php artisan migrate:refresh --seed

Migration table not found.
Migration table created successfully.
Migrating: 2016_06_16_000000_create_support_tables
Migrated:  2016_06_16_000000_create_support_tables (0.4 seconds)

...

Seeding: AccountTypeSeeder
Seeding: TransactionCurrencySeeder
Seeding: TransactionTypeSeeder
Seeding: PermissionSeeder
Seeding: LinkTypeSeeder
Seeding: ConfigSeeder
Database seeding completed successfully.

残りも同じ。

masashi@nginx-php-fastcgi:/var/www/firefly/firefly-iii$ sudo -u www-data php artisan firefly-iii:upgrade-database

Now seeding the database...
Nothing to migrate.
Seeding: AccountTypeSeeder
Seeding: TransactionCurrencySeeder
Seeding: TransactionTypeSeeder
Seeding: PermissionSeeder
Seeding: LinkTypeSeeder
Seeding: ConfigSeeder
Database seeding completed successfully.

...
+------------------------------------------------------------------------------+
|                                                                              |
| Thank you for updating to Firefly III, v4.8.1.8!                             |
| This version of Firefly III requires PHP7.3.                                 |
|                                                                              |
+------------------------------------------------------------------------------+

最後は何のコマンドかわかりませんが、エラー出たので2回やっときました。

masashi@nginx-php-fastcgi:/var/www/firefly/firefly-iii$ sudo -u www-data php artisan passport:install
Encryption keys already exist. Use the --force option to overwrite them.
Personal access client created successfully.
Client ID: 3
Client secret: fMmYdXV0mk0elxhcAX3MRpSnO1je7V1B82JpdGKd
Password grant client created successfully.
Client ID: 4
Client secret: HUNPYAh26Jab3dNn1myzHlYSZXCcyTQMdFKrZMMY
masashi@nginx-php-fastcgi:/var/www/firefly/firefly-iii$ sudo -u www-data php artisan passport:install --force
Encryption keys generated successfully.
Personal access client created successfully.
Client ID: 5
Client secret: pOkkEnXHYXHBzlSNjyibl2ANtuntUYKy6CXEVaqC
Password grant client created successfully.
Client ID: 6
Client secret: LPC6hwF8rMtbypIcPeMACbdXrXhDGMNutAh3vuiD

アクセス

ここまで終わったらアクセスできるよとなっているのでアクセスしてみます。

アクセスしたら/fifefly-iii/public/にDocument root変えろとあるので一応変更しました。
f:id:paloma69:20191117152942p:plain

もう一度アクセス。
f:id:paloma69:20191117153134p:plain

なにも表示されない…。

nginxのエラーログ確認します。

2019/11/16 11:46:11 [error] 1825#1825: *1 FastCGI sent in stderr: "PHP Parse error:  syntax error, unexpected '?
', expecting variable (T_VARIABLE) in /var/www/firefly/firefly-iii/vendor/zendframework/zend-diactoros/src/functions/marshal_uri_from_sapi.php on line 83" while reading response header from upstream, client: 192.168.0.12, server: , request: "GET / HTTP/1.1", upstream: "fastcgi://unix:/var/run/nginx/php-fastcgi.sock:", host: "192.168.0.17"

fastcgiのモジュールが読み込めていないようです。
nginxのlocation ~ \.php$を修正。

ここの該当部分を丸々貼りました。

firefly-iii/nginx.conf at master · firefly-iii/firefly-iii · GitHub

2019/11/16 11:54:54 [error] 1914#1914: *1 open() "/var/www/firefly/firefly-iii/public/login" failed (2: No such file or directory), client: 192.168.0.12, server: , request: "GET /login HTTP/1.1", host: "192.168.0.17"

今度はloginディレクトリにアクセスできないというエラーです。

これもissueを参考にlocation /を変更しました。

nginx: 404 Not Found · Issue #108 · firefly-iii/firefly-iii · GitHub

たぶんこの辺りの設定が怪しかったのですがクエリを読み込めなかったのかな?

try_files $uri $uri/ /index.php?$query_string;

再アクセス

f:id:paloma69:20191117153256p:plain

!!!

ログインページが表示されました!

ユーザ登録してログイン。

情報を登録します。
日本円はあるんですが現時点では日本語対応はしていないようです。

f:id:paloma69:20191117153724j:plain

balance(預金)は仮で100万で設定します。

ようやくセットアップできました。
ダッシュボードはこんな感じです。

f:id:paloma69:20191117153851j:plain

どこまで使いこなせるかわかりませんが、家計簿つけていこうと思います。

Webサーバのチューニングって難しい

久しぶりにnginx触りました。
Turnkey Linuxのほうでfastphp用にカスタマイズされているので、
ファイルを探すのも少し苦労しました。

locationの設定1つとっても今回のアプリが動く動かないの話になってくるので
webサーバって難しいですね。

でもいろいろ勉強になったので今回やってみてよかったです。
issueを漁る経験もできましたw

長くなるので細かい話はまた別で書いてみようと思います。

pythonでCSVファイルの同ヘッダ列に追記したい

やっとpythonにこなれてきたのでスクレイピングを使って脆弱性情報収集などやろうかなと考えています。

SIerにありがちな、とりあえずcsvに吐いておくというのをやりたいのですが、
ファイルを作成するにあたり

  • headerを付けておきたい
    • Excelに転記できるようにしておくため
  • 同じファイルにタンキングする
    • 最終的に管理ファイルであるExcelに直接書き込む練習

ということを考えています。
この時ヘッダ付きのCSVの動きで戸惑ったのでメモっておきます。

ヘッダ付与

ヘッダ付ける記事は割りと出てきます。
DictWriterでfieldnameにリスト型を渡してwriteheaderでOK。

writer = csv.DictWriter(ファイル名, fieldnames=field名)
writer.writeheader()

同ファイルへ追記

ヘッダー付きCSVファイルを作成するという記事はよくあるんですが。
既にあるファイルに追記するパターンの記事が意外と無いんですよね。
こんな事しないからかもしれませんが。

追記時もヘッダを認識するためにDictWriterが必要の様です。
ヘッダを読んだ後、辞書型でヘッダに追加します。

writer = csv.DictWriter(file名, fieldnames=field名)  
writer.writerow({'a': aヘッダの内容, 'b': bヘッダの内容})  

実際のコードはこんな感じ。
Windows10で試したので文字コードとnewlineを記載してます。
(Versionは3.7.2です)

# coding:utf-8

import csv

Filename = 'sample.csv'

Headers = ['header1', 'header2', 'header3', 'header4', 'header5']

# ヘッダ付きファイル作成しておく
with open(Filename, 'w', newline='') as f:
    w = csv.DictWriter(f, fieldnames=Headers)
    w.writeheader()

# 追記したい内容を辞書側にしておく
Contents = {'header1': 'content1' , 
            'header2': 'content2' , 
            'header3': 'content3' , 
            'header4': 'content4' , 
            'header5': 'content5' 
}

# サンプル的に5行追加する
for fivetime in range(1,6):
    with open(Filename, 'a', newline='') as f:
        w = csv.DictWriter(f, fieldnames=Headers)
        w.writerow(Contents)
  • 結果
PS C:\Users\masashi\Documents> python .\csvheader.py
PS C:\Users\masashi\Documents> cat .\sample.csv
header1,header2,header3,header4,header5
content1,content2,content3,content4,content5
content1,content2,content3,content4,content5
content1,content2,content3,content4,content5
content1,content2,content3,content4,content5
content1,content2,content3,content4,content5

ちゃんとヘッダ名と同じ列に追加されてますね。

DictWriterを使わないとどうなるか

DictWriter無しで追記するとヘッダの値だけがむなしく追加されます。

別ファイル作ってみます。

for fivetime in range(1,6):
    with open(Filename, 'a', newline='') as f:
        w = csv.writer(f) # この行だけ変えてみます
        w.writerow(Contents)
  • 結果
PS C:\Users\masashi\Documents> python .\csvheader2.py
PS C:\Users\masashi\Documents> cat .\sample.csv
header1,header2,header3,header4,header5
header1,header2,header3,header4,header5
header1,header2,header3,header4,header5
header1,header2,header3,header4,header5
header1,header2,header3,header4,header5
header1,header2,header3,header4,header5

DictWriteで追記できました

というわけでDictWriterを使えば同ヘッダ列に行の追加ができました。

Excelを直接いじるプログラムはセルの指定等必要なので結構面倒なのですが、
いったんCSVに吐いておけば割と融通の利くモノになりそうですね。
(とりあえずCSVにするのは綺麗ではないですが)

あと時間があればDictWriteとwriterの書き込み周りのソースコード読んでみたいですね。

参考サイト

UbuntuでConkyを半透明にする

IT業界で働いているからにはウィザード級ハッカーになりたいと思うのが世の常なのですが、
ハッカーのPC環境として以下の要素があります。(個人の見解です)

  • デスクトップのテンプレがカッコいい
  • 常にリソースモニタしてる
  • マイニングしてる
  • キーボード使わない

というわけで第一歩としてサブ機UbuntuにConkyをインストールしました。

メイン機でやってもいいんですが、
Windowsだしゲームする用のPCなので余計なプロセスは動かさないようにします。

環境

  • OS
masashi@PC-ubuntu:~$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.3 LTS"
  • デスクトップ環境

gnome3なのですが、確認方法がわかりませんでした…

  • Conky
    • インストール方法は割愛
masashi@PC-ubuntu:~$ conky -v
conky 1.10.8 compiled Wed Feb 28 17:11:29 UTC 2018 for Linux 4.4.0-101-generic x86_64

…

conkyでやりたいこと

  • リソースモニタ

    • CPU使用率
    • メモリ使用率
    • ディスク使用率
    • NW使用帯域
  • 半透明

というものを目指してセッティングしたいと思います。

リソースモニタ系は事例がたくさんあるので簡単にできます。

「 conky 半透明 」で検索すると色々出てきますが、
完全に透明にするものが多く、半透明という結果が得られません。

私はGTAⅤのロスサントスを背景にしているのですが、
夕焼けのシチュエーションのためConky映えする右上に配置したとき
透明だと文字が読みにくくなってしまいます。

文字の色を変えるでもいいのですが、やはりカッコいい半透明にしたいのでいろいろ探しました。

そんな中以下の内容で半透明にできました。

    own_window_transparent = false,
    own_window_argb_visual = true,
    own_window_argb_value = 100,

own_window_argbが半透明になる設定みたいですね。
valueは透過度です。
低いほうが透明に近づきます。
(transparentの項目はいらないかも)

デスクトップはこんな感じです。
ディスプレイもサブなので4:3のままです。
(メモリ4Gでがんばってるよ)

f:id:paloma69:20191106234554p:plain

conkyrcはこんな感じ。
差分だけ出したので見にくいですが。

masashi@PC-ubuntu:~$ diff -u conky.default .conkyrc  | grep -E '^\+|^\-'
--- conky.default       2019-11-06 23:42:55.806898754 +0900
+++ .conkyrc    2019-11-06 23:27:44.463583126 +0900
-    alignment = 'top_left',
+    alignment = 'top_right',
-    font = 'DejaVu Sans Mono:size=12',
-    gap_x = 5,
-    gap_y = 60,
+    font = 'DejaVu Sans Mono:size=10',
+    gap_x = 0,
+    gap_y = 32,
+    own_window_transparent = false,
+    own_window_argb_visual = true,
+    own_window_argb_value = 100,
-    update_interval = 1.0,
+    update_interval = 5.0,
-${scroll 16 $nodename - $sysname $kernel on $machine | }
+$nodename
+$sysname $kernel on $machine
-${color grey}Frequency (in GHz):$color $freq_g
+${color grey}CPU Usage:$color $cpu%  ${cpubar 4} ${acpitemp}C
+${color grey}${cpugraph}
+${color grey}${memgraph}
-${color grey}CPU Usage:$color $cpu% ${cpubar 4}
-Up:$color ${upspeed eth0} ${color grey} - Down:$color ${downspeed eth0}
+Up:$color ${upspeed enp2s0} ${color grey} - Down:$color ${downspeed enp2s0}
+${color lightgrey} ${top name 5} ${top pid 5} ${top cpu 5} ${top mem 5}

私はconkyを右上にくっつけるのはマストなのですが、タスクバーに被らないように高さ調整してます。

設定後は自動起動にして完了です。

ほとんどデフォルトでそんなにいじっていないのですが、
壁紙のおかげかまあまあカッコいいんじゃないでしょうか。
あとはRSS取得したり文字色を変えたりおしゃれに円グラフ等にしてみたいですね。

また、今はGNOME使っていますが、
あとはタイル型デスクトップを使いこなせればハッカーに近づきますねw

参考サイト

第391回 ConkyでデスクトップPCの情報をモニタリングする:Ubuntu Weekly Recipe|gihyo.jp … 技術評論社

Conky - ArchWiki

PPP CHAPをもう一回勉強する 環境構築編

時系列は逆になりますが前回の環境構築について紹介します。

今回したいのはpppの確認だけでよいのでできるだけ楽に作れる様にしました。

仮想環境

仮想環境ツールが出来てから簡単に作れるようになりましたが、
それでも新規インストールは面倒くさいものです。

仮想マシンを1からインストールする事もできれば避けたいところです。
なのでvagrantを使います。

また、vagrantは1つのVagrantfileで2サーバ立ち上げる事ができます。

1つのboxで2サーバ上げるつもりでしたが、コマンド1発で2マシン作れるなんて
今回の検証に使わない手は無いですよね。

  1. vagranit init
  2. Vagrantfile修正
  3. vagrant up

でokです。

  • Vagrantfile
PS C:\Users\masashi\tools\vagrant\centos> cat .\Vagrantfile | findstr -v "#"

Vagrant.configure("2") do |config|

  config.vm.box = "centos/7"

  config.vm.define "client" do |centos|
        centos.vm.hostname = "ppp-client"
        config.vm.synced_folder ".", "/vagrant", disabled: true
        centos.vm.network :forwarded_port, id: "ssh", guest: 22, host: 2222
        centos.vm.network "private_network", ip: "192.168.33.10"
  end

  config.vm.define "server" do |centos|
        centos.vm.hostname = "ppp-server"
        config.vm.synced_folder ".", "/vagrant", disabled: true
        centos.vm.network :forwarded_port, id: "ssh", guest: 22, host: 2223
        centos.vm.network "private_network", ip: "192.168.33.11"
  end

end
  • 起動
PS C:\Users\masashi\tools\vagrant\centos> vagrant up
==> vagrant: A new version of Vagrant is available: 2.2.6 (installed version: 2.2.5)!
==> vagrant: To upgrade visit: https://www.vagrantup.com/downloads.html

Bringing machine 'client' up with 'virtualbox' provider...
Bringing machine 'server' up with 'virtualbox' provider...
==> client: Checking if box 'centos/7' version '1905.1' is up to date...

()

ログイン

sshしようとしたらできませんでした。
調べたら鍵認証なんですね。
いつも1台のみでvagrant sshしてたのであまり意識してませんでした。

2マシンあるので鍵も2つです。

PS C:\Users\masashi\tools\vagrant\centos> vagrant ssh-config
Host client
  HostName 127.0.0.1
  User vagrant
  Port 2222
  UserKnownHostsFile /dev/null
  StrictHostKeyChecking no
  PasswordAuthentication no
  IdentityFile C:/Users/masashi/tools/vagrant/centos/.vagrant/machines/client/virtualbox/private_key # 秘密鍵
  IdentitiesOnly yes
  LogLevel FATAL

Host server
  HostName 127.0.0.1
  User vagrant
  Port 2223
  UserKnownHostsFile /dev/null
  StrictHostKeyChecking no
  PasswordAuthentication no
  IdentityFile C:/Users/masashi/tools/vagrant/centos/.vagrant/machines/server/virtualbox/private_key # 秘密鍵
  IdentitiesOnly yes
  LogLevel FATAL

ログインは
Client・・・localhost:2222
Server・・・localhost:2223
でok。

今回はpppのみの確認なんでpppパッケージインストール以外はなにもやってません。

今回の環境は初めて使いましたがコマンド1発2サーバってかなり便利です。
なにか試したくなったらこの環境を使おうと思います。

同じ構成なら使い回せるのもいいですねえ。
今回はクライアントサーバ構成でしたがサーバのクラスタ検証とかでも役立ちそうです。

おまけ 図面の描画

前回構成図とフロー図を載せましたが、
これはそれぞれ

  • nwdiag
  • seqdiag

というツールで作成しました。

nwdiagは以前も紹介しましたが、
今回は同シリーズのseqdiagも使って書いてみました。

これらはblockdiagというパッケージの兄弟分です。

構成ファイルからいい感じに整形してくれるので結構気に入ってます。
アウトプットコマンドは割愛します。(公式サイトを確認ください)

  • nwdiag
nwdiag {
    default_fontsize = 16;

    network vagrant_localnw {
        address = "192.168.33.0/24"

        "ppp-client" [address = ".10"];
        "ppp-server" [address = ".11"];
  }
}
  • 図面

f:id:paloma69:20191025232135p:plain

  • seqdiag
seqdiag {
// Settings
default_fontsize = 16;
activation = none;

Peer -> Authenticator [label = "Call"];
Peer <- Authenticator [label = "LCP negotiation"];
Peer -> Authenticator [label = "LCP negotiation"];
=== LCP Open ===
Peer <- Authenticator [label = "Challenge"];
Peer <- Authenticator [note = "Challenge(ID, Value) , name"];
Peer <- Peer [note = "Create hash(ID, Value, secret)"];
Peer -> Authenticator [label = "Response"];
Peer -> Authenticator [note = "hash, name"];
Authenticator <- Authenticator [note = "Chack hash"];
Peer <- Authenticator [label = "Access granted"];
Peer <- Authenticator [label = "NCP negotiation"];
Peer -> Authenticator [label = "NCP negotiation"];
=== NCP Open ===
}
  • 図面

f:id:paloma69:20191025231157p:plain

参考サイト

ブロック図生成ツール blockdiag — blockdiag 1.0 ドキュメント

Vagrantで複数台同時起動するマルチマシン設定 | WEB ARCH LABO

vagrantで仮想環境を同時に2台立ち上げる - 煮卵もんすたー

PPP CHAPをもう一回勉強する

事の始まり

先月の話になります。

今どきというとアレですけどお客さん環境の中にINS接続の機器がいるのですが、
PPPの認証方向(どっちのセンタ起動か)がバラバラでどっちの機器に何の設定がいるんだっけと
混乱してきました。

特にずっとciscoで構築してきたので
どうしてもciscoに寄った知識になってしまいます。

特にNW機器だと単方向認証だの双方向だのの項目があるので
たまにしかやらないと本当に混乱します。

そんなわけでCHAPの動きを理解するため今一度整理します。

CHAPで勘違いしていたこと

お互いCHAPのチャレンジ、レスポンスともにパスワードのlookup用にusernameが必要

Ciscoのppp系のサイトはお互いの機器がusernameを確認するという絵が描いてあるので
てっきり双方向の機器にお互いのユーザ名が要るものだと信じてしまいました。

そういえばCHAP認証はハッシュされた値の突合なのでユーザ名が必須かと言わればそうでもない気がしますよね。
Internet接続とかでも使うし。

認証側はもちろん必要ですが起動側は相手のユーザ名は登録しなくても良さそうです。
もちろん機器の使用によっては必要なものもあると思います。

  • 間違いのフロー図
起動側 受信側
call ->
Challenge受信 <- ユーザ名送信(Challenge)
ユーザ名lookup
パスワード+αをハッシュ化
自ユーザ名+ハッシュ値送信 -> ユーザ名+ハッシュ値受信
受信ユーザ名からパスワード検索
ハッシュ値計算
<- 認証OKを送信
  • きっとこうなんだ
起動側 受信側
call ->
Challenge受信 <- ユーザ名送信(Challenge)
パスワード+αをハッシュ化
自ユーザ名+ハッシュ値送信 -> ユーザ名+ハッシュ値受信
受信ユーザ名からパスワード検索
ハッシュ値計算
<- 認証OKを送信

RFC(1994)をみると「ユーザ名に紐づいたパスワードをハッシュすること」とは書いていません。
パケットのnameフィールドは必須ですが、lookup用に使わなくても大丈夫そうです。

https://www.ietf.org/rfc/rfc1994.txt

検証

PPP、CHAPの動きをログを見て確認しようと思います。
検証は自宅で行いますが、INSの環境が無いのでpppoeを使って確認します。

また、自宅にNW機器もないのでLinuxで立てます。
(NWエンジニアなのに無くてすみません…)

もはやINSでもなく、Ciscoでもないんで元の環境の原型が無くなってしまいましたが、まあいいでしょう。
動作は同じはずです。

環境

サブ機ノートPCの仮想マシンでクライアント、サーバ両方立てちゃいます。

構成はシンプルにこう。
pppoeの動き見るだけですからね。

f:id:paloma69:20191025232135p:plain

両サーバcentos7です。
ホスト名のとおりvagrantを使って構築しています。

[vagrant@ppp-server ~]$ cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core)

[vagrant@ppp-client ~]$ cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core)

iptables、firewlldは無効の状態です。

サーバ設定

PPPoEのインストール、設定等基本的な所はデフォルトにするので割愛します。

  • chap-secrets

clientは「client01」にしました。

[vagrant@ppp-server ~]$ sudo cat /etc/ppp/chap-secrets
# Secrets for authentication using CHAP
# client        server  secret                  IP addresses
client01        *       client01        *
その他検証用にやったところ

デバッグレベルのログまで見たいので追加で設定します。

  • pppoe-server-options

CHAPの確認なのでpapはコメントアウト。 require-chapを追加します。

デバッグレベル出力のためdebugを追加します。
ここの設定内容がpppdのオプションとして反映されるようです。

[vagrant@ppp-server ~]$ cat /etc/ppp/pppoe-server-options
# PPP options for the PPPoE server
# LIC: GPL
#require-pap
require-chap #追加
login
debug # 追加
lcp-echo-interval 10
lcp-echo-failure 2
  • rsyslog.conf

pppdのdebugログはdaemonファシリティでsyslogに吐かれるそうなので
rsyslog.confに追加します。

[vagrant@ppp-server ~]$ cat /etc/rsyslog.conf | grep daemon
daemon.*                                                /var/log/daemon

起動します。
vagrant boxのcentosはensじゃなくてethxになってますね。
aliasかな?

sudo pppoe-server -I eth1

クライアント設定

クライアント側の方法はいくつかありますが簡単にpppoe-setupを使います。
この手順は割愛。

sshが切れると困るのでppp0のデフォゲは無効にします。

[vagrant@ppp-client ~]$ sudo cat /etc/sysconfig/network-scripts/ifcfg-ppp0
USERCTL=yes
BOOTPROTO=dialup
NAME=DSLppp0
DEVICE=ppp0
TYPE=xDSL
ONBOOT=no
PIDFILE=/var/run/pppoe-adsl.pid
FIREWALL=NONE
PING=.
PPPOE_TIMEOUT=80
LCP_FAILURE=3
LCP_INTERVAL=20
CLAMPMSS=1412
CONNECT_POLL=6
CONNECT_TIMEOUT=60
DEFROUTE=no # yes -> noに変更
SYNCHRONOUS=no
ETH=eth1
PROVIDER=DSLppp0
USER=client01
PEERDNS=no
DEMAND=no

pppインターフェース起動

clientのpppインターフェースを起動。

[vagrant@ppp-client ~]$ sudo /sbin/ifup ppp0
[vagrant@ppp-client ~]$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:00:8a:fe:e6 brd ff:ff:ff:ff:ff:ff
    inet 10.0.2.15/24 brd 10.0.2.255 scope global noprefixroute dynamic eth0
       valid_lft 51192sec preferred_lft 51192sec
    inet6 fe80::5054:ff:fe8a:fee6/64 scope link
       valid_lft forever preferred_lft forever
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 08:00:27:c2:6f:6b brd ff:ff:ff:ff:ff:ff
    inet 192.168.33.10/24 brd 192.168.33.255 scope global noprefixroute eth1
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fec2:6f6b/64 scope link
       valid_lft forever preferred_lft forever
6: ppp0: <POINTOPOINT,MULTICAST,NOARP,UP,LOWER_UP> mtu 1492 qdisc pfifo_fast state UNKNOWN group default qlen 3
    link/ppp
    inet 10.67.15.3 peer 10.0.0.1/32 scope global ppp0
       valid_lft forever preferred_lft forever

ppp張れましたね。

※ eth0は昔ホストオンリーアダプターを作った時の設定が反映されて上がってしまったみたいです。
今回は特に使わないので無視します。

サーバ側のログ

Oct 24 15:00:44 ppp-server pppoe-server[4447]: Session 3 created for client 08:00:27:c2:6f:6b (10.67.15.3) on eth1 using Service-Name ''
Oct 24 15:00:44 ppp-server pppd[4447]: pppd 2.4.5 started by vagrant, uid 0
Oct 24 15:00:44 ppp-server pppd[4447]: using channel 3
Oct 24 15:00:44 ppp-server pppd[4447]: Using interface ppp0
Oct 24 15:00:44 ppp-server pppd[4447]: Connect: ppp0 <--> /dev/pts/1
Oct 24 15:00:44 ppp-server NetworkManager[4050]: <info>  [1571929244.9456] manager: (ppp0): new Ppp device (/org/freedesktop/NetworkManager/Devices/6)
Oct 24 15:00:45 ppp-server pppd[4447]: rcvd [LCP ConfReq id=0x1 <mru 1492> <magic 0x3771cff7>]
Oct 24 15:00:45 ppp-server pppd[4447]: sent [LCP ConfReq id=0x1 <mru 1492> <auth chap MD5> <magic 0x80db6786>]
Oct 24 15:00:45 ppp-server pppd[4447]: sent [LCP ConfAck id=0x1 <mru 1492> <magic 0x3771cff7>]
Oct 24 15:00:45 ppp-server pppd[4447]: rcvd [LCP ConfAck id=0x1 <mru 1492> <auth chap MD5> <magic 0x80db6786>]
Oct 24 15:00:45 ppp-server pppd[4447]: sent [LCP EchoReq id=0x0 magic=0x80db6786]
Oct 24 15:00:45 ppp-server pppd[4447]: sent [CHAP Challenge id=0xe8 <d2292ad3129e9b165723fedc864214d23b038cf98335>, name = "ppp-server"]
Oct 24 15:00:45 ppp-server pppd[4447]: rcvd [LCP EchoReq id=0x0 magic=0x3771cff7]
Oct 24 15:00:45 ppp-server pppd[4447]: sent [LCP EchoRep id=0x0 magic=0x80db6786]
Oct 24 15:00:45 ppp-server pppd[4447]: rcvd [LCP EchoRep id=0x0 magic=0x3771cff7]
Oct 24 15:00:45 ppp-server pppd[4447]: rcvd [CHAP Response id=0xe8 <651f68004248139f6bf3281d2cbfa215>, name = "client01"]
Oct 24 15:00:45 ppp-server pppd[4447]: sent [CHAP Success id=0xe8 "Access granted"]
Oct 24 15:00:45 ppp-server pppd[4447]: Initializing PAM (2) for user client01
Oct 24 15:00:45 ppp-server pppd[4447]: ---> PAM INIT Result = 0
Oct 24 15:00:45 ppp-server pppd[4447]: Attempting PAM account checks
Oct 24 15:00:46 ppp-server pppd[4447]: User unknown, ignoring PAM restrictions
Oct 24 15:00:46 ppp-server pppd[4447]: user client01 logged in on tty  intf ppp0
Oct 24 15:00:46 ppp-server pppd[4447]: BSD-Compress (15) compression enabled
Oct 24 15:00:46 ppp-server pppd[4447]: local  IP address 10.0.0.1
Oct 24 15:00:46 ppp-server pppd[4447]: remote IP address 10.67.15.3
Oct 24 15:00:46 ppp-server NetworkManager[4050]: <info>  [1571929246.0245] device (ppp0): state change: unmanaged -> unavailable (reason 'connection-assumed', sys-iface-state: 'external')
Oct 24 15:00:46 ppp-server NetworkManager[4050]: <info>  [1571929246.0276] device (ppp0): state change: unavailable -> disconnected (reason 'none', sys-iface-state: 'external')
Oct 24 15:01:02 ppp-server systemd: Created slice User Slice of root.
Oct 24 15:01:02 ppp-server systemd: Started Session 3 of user root.

着信受けてLCPフェーズ、CHAPと来てますね。
centos6系だとIPCPフェーズのログも見えるのですが、7系だと見えませんね。
NetworkManagerが何か悪さしてるんでしょうか。
ここの原因はわからないままです。

クライアントのログ

こっちのデバッグログの出力方法がわかりませんでした。
別パッケージだけどubuntuだったらできるのになー。

Oct 24 15:00:44 ppp-client pppd[4852]: pppd 2.4.5 started by vagrant, uid 0
Oct 24 15:00:44 ppp-client pppd[4852]: Using interface ppp0
Oct 24 15:00:44 ppp-client pppd[4852]: Connect: ppp0 <--> /dev/pts/1
Oct 24 15:00:44 ppp-client NetworkManager[3991]: <info>  [1571929244.9144] manager: (ppp0): new Ppp device (/org/freedesktop/NetworkManager/Devices/6)
Oct 24 15:00:44 ppp-client pppoe[4853]: PPP session is 3 (0x3)
Oct 24 15:00:45 ppp-client pppd[4852]: CHAP authentication succeeded: Access granted
Oct 24 15:00:45 ppp-client pppd[4852]: CHAP authentication succeeded
Oct 24 15:00:46 ppp-client pppd[4852]: BSD-Compress (15) compression enabled
Oct 24 15:00:46 ppp-client pppd[4852]: local  IP address 10.67.15.3
Oct 24 15:00:46 ppp-client pppd[4852]: remote IP address 10.0.0.1
Oct 24 15:00:46 ppp-client NetworkManager[3991]: <info>  [1571929246.0193] device (ppp0): state change: unmanaged -> unavailable (reason 'connection-assumed', sys-iface-state: 'external')
Oct 24 15:00:46 ppp-client NetworkManager[3991]: <info>  [1571929246.0224] device (ppp0): state change: unavailable -> disconnected (reason 'none', sys-iface-state: 'external')
Oct 24 15:01:02 ppp-client systemd: Created slice User Slice of root.
Oct 24 15:01:02 ppp-client systemd: Started Session 3 of user root.

本題の確認ポイント

今回クライアント側にもサーバのユーザ名が必要かどうかの確認をしたかったのですが
クライアント側には自分の情報(client01)しか設定していません。

Oct 24 15:00:45 ppp-server pppd[4447]: sent [CHAP Challenge id=0xe8 <d2292ad3129e9b165723fedc864214d23b038cf98335>, name = "ppp-server"]

サーバ側のログでは「ppp-server」というnameフィールドが付いたチャレンジを送っていますが、
client側にはそんな設定はしていません!

無視しているのかどうかはわかりませんが、なくても認証できるということは
クライアント側にはサーバのユーザ名はやっぱり要らなかったんだ!
たぶんciscoでも要らないに違いない!
(単方向認証のとき)

改めてフロー整理

ログを清書するとこうなります。

f:id:paloma69:20191025231157p:plain

いやー、すっきりすっきり!

まあいろんなサイトでこういう図が出てくるので、でって言う感じですが、
自分で動かして整理すると入りが違いますね。

もう一回勉強する!と言っておきながら大した検証はできていないかもしれませんが、
こういうのできるのがこの業界の面白い所ですね(^^)

参考サイト

Ubuntu Manpage: pppd - PPP (Point to Point Protocol) を処理するデーモン

pppoe-server - システム管理コマンドの説明 - Linux コマンド集 一覧表

CentOS7.4でPPPoE接続 rp-pppoe編 - Qiita

Fortigateの脆弱性情報RSS取得ツールを自作する

オンプレ環境の話になりますが、システムを運用するとなると保守サポートに入りますよね。
NW機器なんかもそこで脆弱性情報を提供してくれるんですが、
どうにもFortigateの情報が少ない。

よくよく聞くと情報を精査して提示しているそうなので、
Fortiの情報が欲しければ自分で探してくれという流れになりました。

脆弱性情報のRSSも提供されているそうなので、いっちょ収集ツールでも作りましょう。

しかし、RSSでタイトルを確認するレベルならどこかのRSSフィードサイトに登録すれば事足ります。

ここは脆弱性の対象バージョンも取れるように作りたいと思います。

Forti機器のRSS

  • サイト

https://fortiguard.com/rss/ir.xml

ここではタイトル、掲載日、詳細リンクしかないので
さらにリンクを辿って対象のバージョンまで取れるようします。

コード

from bs4 import BeautifulSoup
import requests
import time

url = 'https://fortiguard.com/rss/ir.xml'

r = requests.get(url)

soup = BeautifulSoup(r.text, 'xml') 

items = soup.find_all('item')

n = 1
for content in items:

    time.sleep(3)
    detail = requests.get(content.link.text)
    
    soup = BeautifulSoup(detail.text, 'html.parser')
    
    for affected_ver in soup.find_all('div', class_='detail-item'):
        if 'Affected Products' in affected_ver.text:
        
            print(""" 
    --- No. {} ---
    Titile: {}
    Date: {}
    Link: {}
    Affected Ver: {}
    """.format(n, content.title.text, content.pubDate.text, content.link.text, 
        affected_ver.text.replace("\n", "\n\t")))
        
    time.sleep(3)
    n += 1

よく見るBeautifulSoup、requestsのセットですね。
かじっててよかったスクレイピング

リンクをクローリングすることになるのでsleepは多めにとってます。

動かしてみる

RSSページは25件あってスクリプトも全件取りますが、サンプル的に3件だけ出力させてみます。

サブ機Windows10で動かします。

  • 環境
PS C:\Users\masashi\tools\fortirss> $psversiontable

Name                           Value
----                           -----
PSVersion                      5.1.17763.592
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.592
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
  • 実行
PS C:\Users\masashi\tools\fortirss> python .\fortirss.py

    --- No. 1 ---
    Titile: XSS vulnerability in FortiClientEMS
    Date: Mon, 23 Sep 2019 07:13:10 +0000
    Link: https://fortiguard.com/psirt/FG-IR-19-072
    Affected Ver:
        Affected Products
        FortiClientEMS version 6.2.0 and below.



    --- No. 2 ---
    Titile: IPMI network LAN interface failover operational risk
    Date: Tue, 17 Sep 2019 09:54:20 +0000
    Link: https://fortiguard.com/psirt/FG-IR-17-195
    Affected Ver:
        Affected Products

        FortiAnalyzer models:

        FAZ-400E, FAZ-1000E, FAZ-2000E, FAZ-3000F, FAZ-3500F, FAZ-3700F

        FortiManager models:

        FMG-300E, FMG-400E, FMG-2000E, FMG-3000F

        Other models and Fortinet products are confirmed to not have a default Failover setting.




    --- No. 3 ---
    Titile: HTTP/2 Multiple DoS Attacks (VU#605641)
    Date: Tue, 03 Sep 2019 14:41:11 +0000
    Link: https://fortiguard.com/psirt/FG-IR-19-225
    Affected Ver:
        Affected Products

        The following products have been confirmed to NOT be vulnerable to any of the above:

        FortiOS
        FortiAP
        FortiSwitch
        FortiAnalyzer
        FortiWeb
        FortiManager
        FortiMail


・・・

こんな感じ。
対象バージョンの中身はhtmlをそのまま抜き出してるので出力がバラバラですが、
使っているバージョンの有無は探せますね。

対象バージョンがある場合はリンク先URLへ行けば詳細が分かります。
いちいち全件見なくていいので運用が楽になりますね。

ちょっと大変だったところ

詳細ページのリンクへ行った後、対象バージョン部分のテキストを抜き出すのが苦労しました。
対象タグを探してからその配下を抜き出そうとしたのですがその方法がわかりませんでした。

対象の部分はこんな感じです。
(執筆時の1件目の詳細ページです。)

    <div class="detail-item">
        <h3>Affected Products</h3>
        <p>FortiClientEMS version 6.2.0 and below.<br></p>
    </div>

この部分が内容によってあったりなかったり、
テキストが多かったり少なかったりでどうやったらうまく取れるかを考えました。

文字列を検索して、ヒットしたらその要素を抜き出すという方法にしたら
何とか望みの結果が得られました。

"detail-item"クラスはページ内にいくつかあるのですが、
今回は"detail-item"内に"Affected Products"の文字列があったら
その部分を出力する、という処理で対応できました。

あとは出力の際にいいバランスになるようにタブを挿入してます。

せっかくなのでGithubに置きました

いつもの家で使うようなツールと違って職場でも使えると思いGithubに上げました。

https://github.com/masashi69/fortirss

(READMEのハイライトがなんかおかしい…)


全件取るだけのシンプルツールですが、更新された分だけチェックするようにしたり
他メーカーバージョンも作ってみたいですね。

月次の支払い集計ツールを作る スクリプト化する編

一旦今年前半の集計は完了しましたが、
月次で運用するためにスクリプト化します。

手動でやってた処理はこんなところですね。

  1. 文字コード変換
  2. 定期代、ヘッダ、フッタ削除
  3. INSERT変換ツール実行
  4. INSERT実行
  5. 集計用sql発行

明細はログイン処理とかあるのでまだ手動で落とします。
今回は7月分の支払いを挿入します。
ややこしいですが、CSVファイルは8月のものになります。

処理詳細

1. 文字コード変換

nfkコマンドで一発。
man見たら日本製のツールなんですね。

2. 定期代削除

プラス不要なヘッダ、フッタ削除
shellでは厳しそうなのでツール作ります。

  • shaping.py
import sys

if len(sys.argv) != 3:
    pass
else:
    print(' '.join(['Usage:', 'python', sys.argv[0], 'CSV file']))
    sys.exit()

monthlycsv = sys.argv[1]

with open(monthlycsv, 'r') as f:
    # 行削除のためリスト型で格納
    reader = f.readlines()

with open(monthlycsv, 'w') as f:
    # 不要ヘッダ、フッタ削除
    for line in reader[2:-3]:
      if '21340' in line:
          pass
      else:
          f.write(line)
3. INSERTファイル作成

前回のINSERT変換用スクリプトを使います。
前回はCSVファイルべた書きでしたが、引数から読むように修正しました。
コードは割愛。

4. INSERT実行

前回のコマンドの通り。
3を実行するだけです。

5. 集計SQL発行

前回のコマンドの通り。

それでは各処理をスクリプト化します。

上記をまとめたシェル

  • insertmonth.sh
#!/bin/bash
# $1 は月のcsvファイル

# 文字コード変換
nkf -w --overwrite $1

# 不要ヘッダ、フッタ削除
python3 shaping.py $1

# 固定費挿入
# 先月分最終日に挿入のためー1する
lastmonth=0$(($(echo $1 | cut -c 6-6)-1))

echo $(date +"%Y/%m/%d" -d"`date +"%Y${lastmonth}01"` 1day ago + 1 month"),家賃,61000 >> $1
echo $(date +"%Y/%m/%d" -d"`date +"%Y${lastmonth}01"` 1day ago + 1 month"),光熱費,10000 >> $1

# インサート文作成
python3 insertsql.py $1

# インサート実行
sqlite3 Credit_expenses_2019.db <  $1".sql"

# 集計ファイル作成
sqlfile="syukei"${lastmonth}".sql"

# フォーマットをコピー
cat syukei01.sql | sed "s/2019\/[0-9][0-9]/2019\/${lastmonth}/g" > $sqlfile

# セレクト処理実行
sqlite3 Credit_expenses_2019.db < $sqlfile

引数まみれのスクリプトになってしまいました。
可変でやろうとしているから仕方がないですが、
変数に入れる等してもう少し綺麗になる気もする...

7月分をINSERTしてみましょう

くどいですが、「7月分の支払い結果」なので8月のファイルになります。
(私の使っているカード会社の仕様です)

masashi@DESKTOP-N095TND:~/expenses$ bash insertmonth.sh 201908.csv
INSERT INTO expenses VALUES('2019/06/30','ヤフージャパン','498');
INSERT INTO expenses VALUES('2019/07/01','J:COM サービス利用料','5431');
INSERT INTO expenses VALUES('2019/07/02','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/02','イトーヨーカドー','1780');
INSERT INTO expenses VALUES('2019/07/04','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/05','イトーヨーカドー 食品','2308');
INSERT INTO expenses VALUES('2019/07/06','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/07','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/09','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/11','06月分 au電話利用料','3049');
INSERT INTO expenses VALUES('2019/07/11','ミーナ津田沼','3175');
INSERT INTO expenses VALUES('2019/07/12','イオンリテール','3184');
INSERT INTO expenses VALUES('2019/07/14','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/15','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/17','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/17','イトーヨーカドー 食品','2168');
INSERT INTO expenses VALUES('2019/07/19','JR東日本モバイルSuica','3000');
INSERT INTO expenses VALUES('2019/07/19','JR東日本みどりの窓口(びゅうプラザ)','13210');
INSERT INTO expenses VALUES('2019/07/21','JR東海','13100');
INSERT INTO expenses VALUES('2019/07/21','戸田家','1750');
INSERT INTO expenses VALUES('2019/07/21','NETFLIX.COM','1296');
INSERT INTO expenses VALUES('2019/07/22','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/24','イトーヨーカドー 食品','2199');
INSERT INTO expenses VALUES('2019/07/25','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/26','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/28','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/29','JR西日本 5489サービス','16350');
INSERT INTO expenses VALUES('2019/07/29','JR東日本モバイルSuica','2000');
INSERT INTO expenses VALUES('2019/07/30','イトーヨーカドー 食品','2472');
INSERT INTO expenses VALUES('2019/07/01','DIGITALOCEAN.COM (DIGITALOCEAN.)','118');
INSERT INTO expenses VALUES('2019/07/11','CROP (0570001832 )','2142');
INSERT INTO expenses VALUES('2019/07/31','家賃','61000');
INSERT INTO expenses VALUES('2019/07/31','光熱費','10000');
決済内容        合計
----------  ----------
SUICA       29000
その他         65959
住宅費         71000
通信費         1414
食費          6359
-           173732
masashi@DESKTOP-N095TND:~/expenses$

OKですね!
集計結果まで出力してくれて楽ちんです。
7月は旅行行ったり夏季休暇用の切符買ったりして気持ち高くつきましたね。

途中の処理について

INSERT文がちゃんと変換されて出ているのでうまくいっていますが、
(出力するようにしておいてよかった)
一応ファイル確認します。

  • 元のファイル

SJISなのでWSL上では文字化けしています。

masashi@DESKTOP-N095TND:~/expenses$ head 201908.csv
؁@u@l,4980-0594-4494-3***,rlabb`qcNVbN
2019/06/30,C^[lbgCjVAeBu,1798,,,,
2019/06/30,t[Wp,498,,,,
2019/07/01,iFbnl@T[rXp,5431,,,,
2019/07/02,iq{oCr,2000,,,,
2019/07/02,Cg[[Jh[,1780,,,,
2019/07/04,iq{oCr,2000,,,,
2019/07/05,Cg[[Jh[@Hi,2308,,,,
2019/07/06,iq{oCr,2000,,,,
2019/07/07,iq{oCr,2000,,,,
masashi@DESKTOP-N095TND:~/expenses$

1行目が消えて日本語で確認できるようになっています。

masashi@DESKTOP-N095TND:~/expenses$ head 201908.csv
2019/06/30,ヤフージャパン,498,,,,
2019/07/01,J:COM サービス利用料,5431,,,,
2019/07/02,JR東日本モバイルSuica,2000,,,,
2019/07/02,イトーヨーカドー,1780,,,,
2019/07/04,JR東日本モバイルSuica,2000,,,,
2019/07/05,イトーヨーカドー 食品,2308,,,,
2019/07/06,JR東日本モバイルSuica,2000,,,,
2019/07/07,JR東日本モバイルSuica,2000,,,,
2019/07/09,JR東日本モバイルSuica,2000,,,,
2019/07/10,JR東日本モバイルSuica,21340,,,,

まとめ

ブログ上は1発でいったように書きましたが、
細かな処理をちょろちょろやっているので単体ごとの試験が大変でした。

8月分ファイルをダウンロードしたら、windows で落としたファイルがWSLで見えず、
しばらく頓挫していました。(前回は見えていたんですが、Win updateとか?)
結局WSL側のマウント領域からコピーして対応できました。

あと集計SQLファイルは先月分の支払いを集計するので
固定費の挿入月とCSVファイルの指定が混ざって混乱してしまい、
いろいろチューニングしました。
無理やり1ファイルでやろうとしたからなのですが、
おとなしくファイル分ければよかったですね。

また、出力はほとんどノーカットで書いたので、生活垂れ流しで恥ずかしいですw

あとは年末か年明けに今年の総決算をしたいですね。
(お給料少ないので質素に暮らしてるのがばれちゃう)

残りの作業としてはDBのバックアップをどうするか考えつつ
スクリプトリファクタリングをしていこうと思います。

今月から増税で定期代が変わったのでshaping.pyはいじらないといけませんねw

追記

ここまで書いてて気づきましたが、通信費の合計が安すぎておかしいですね。
原因を追究しますが、合計があっていればしばらくは良しとします…。

参考サイト

Windows Subsystem for Linux(WSL)でWindowsのファイルにアクセス - lisz-works

  • 本シリーズ

月次の支払い集計ツールを作る SQL準備編 - paloma blog

月次の支払い集計ツールを作る 2019前半を集計する編 - paloma blog