個人メモ(PythonでOSコマンドとExcelファイルをマージするタスクを自動実行）

開発環境の管理でExcelで何かと定義してあって、そのチェックはOSコマンドを発行してなにやらチェック作業をやることがままあります。
はい、OSコマンド結果をコピペで貼り付けてExcelの諸機能を使って。。。ですね。

それでもいいのですが
どうやっても「手作業」を介在するわけで、自動化の妨げになっていたりするわけです。

というわけでこんな感じでPythonでやってみます。

~~~
１．OSコマンドをPythonから発行、結果をDataFrameに獲得
２．ExcelファイルをPythonから読み込み
３．１と２の結果をマージしてなにやらチェック作業

そして
１〜３をJenkinsで自動実行
~~~

サンプルとしてこんなことをやってみたい
lastコマンド結果から、監視対象ユーザ（Excelで管理）のサーバログイン状況をチェックする。


1.OSコマンド結果をPythonから発行、結果をDataFrameに獲得

lastの結果をDataFrameに取り込みます。
read_csvがよく使われていますがログなどにはとても有効なのですが
OSコマンド結果は固定長で対応したほうがよさ気でした。
固定長列の中にいろいろと例外的にぶっこまれるので。。。
ログは固定長での対応は難しいですね。

使い分けると便利に扱えます。

~~~bash
# last -F -a -w -i | grep -v wtmp | grep -v ^$ | grep -v reboot 
pipimi   tty8         Mon Feb 12 11:11:23 2018 - Mon Feb 12 11:15:23 2018  (00:04)     0.0.0.0
popko    tty8         Mon Feb 12 11:10:59 2018 - Mon Feb 12 11:11:14 2018  (00:00)     0.0.0.0
akira    tty8         Mon Feb 12 11:02:58 2018 - Mon Feb 12 11:07:18 2018  (00:04)     0.0.0.0
satoshi  pts/19       Sun Feb 11 20:46:30 2018   gone - no logout                      0.0.0.0
satoshi  tty7         Sun Feb 11 20:46:26 2018   gone - no logout                      0.0.0.0
satoshi  pts/2        Sun Feb 11 20:46:04 2018   still logged in                       0.0.0.0
satoshi  pts/18       Sun Feb 11 11:05:10 2018   still logged in                       0.0.0.0
satoshi  tty7         Sun Feb 11 11:05:06 2018 - Sun Feb 11 20:46:21 2018  (09:41)     0.0.0.0
satoshi  pts/19       Thu Feb  8 21:42:24 2018 - down                     (2+13:21)    0.0.0.0
satoshi  tty7         Thu Feb  8 21:42:21 2018 - down                     (2+13:22)    0.0.0.0
root@ThinkPad:~# 
~~~


１週間サイクルで処理するならこんな感じで上書き運用ですかね。

~~~bash
# 毎日ログ採取(xx:xx時定期実行）
# last -F -a -w -i | grep -v wtmp | grep -v ^$ | grep -v reboot | grep "`export LANG=C; date +'%a %b %d'`" > "last_"`date +"%a"`".log"
pipimi   tty8         Mon Feb 12 11:11:23 2018 - Mon Feb 12 11:15:23 2018  (00:04)     0.0.0.0
popko    tty8         Mon Feb 12 11:10:59 2018 - Mon Feb 12 11:11:14 2018  (00:00)     0.0.0.0
akira    tty8         Mon Feb 12 11:02:58 2018 - Mon Feb 12 11:07:18 2018  (00:04)     0.0.0.0

# 週末処理（週１定期実行）
cat last_*.log | sort | uniq > last_all.result

~~~

Pythonコードはこんな感じです。
コマンド実行してもよし、ファイルに書きだしたログから処理もできます。


~~~python
# 制御が難しい状況なので
# コマンド結果を固定長として読み込むようにしてみる
#
#  コマンドを発行する場合と
#　コマンド結果を格納したファイルから読み込む場合を想定する。
# 
#  OSコマンド結果は固定長制約の中でさまざまな結果パターンを返してくる。
#　つまり区切り文字での制御は例外が多くて綺麗にヒットしないケースが発生する。
#　したがってread_csvではなくread_fwfを使って固定長処理を行う。
#　ただしコードは美しくない。
#
#  @see pandas.read_fwf
# 

import subprocess
import pandas as pd
import io
from datetime import datetime

def command_result(cmd, file, colnames, colspecs, header, index):

    if file == None:
        # コマンド実行結果を取得
        f = subprocess.Popen(cmd,
                             stdout=subprocess.PIPE,
                             shell=True,
                             universal_newlines=True).communicate()[0]
        data = io.StringIO(''.join(f))
        
    else:
        # ファイルからコマンド結果を取得
        data = file

    # 結果をDataFrameに格納（固定長区切り）
    df = pd.read_fwf(data,
                     header=header,
                     index=index,
                     names=colnames,
                     colspecs=colspecs)
    return df


def cmd_last():
    
    # コマンドパラメータ設定 ---------------------------------------------------------------
    
    # コマンドを発行する場合
    cmd = 'last -F -a -w -i | grep -v wtmp | grep -v ^$ | grep -v reboot ' ; CommandResultFile = None

    # 結果をファイルから取得する場合
    #CommandResultFile = '/home/satoshi/last_all.result'
   
    # --------------------------------------------------------------------------------------       
    
    # 結果取得パラメータ設定（コマンドによって異なる、個別カスタム）
    colnames = ['user','tty','start','end','logintime','ip']
    colspecs = [(0, 8), (9, 21), (22, 46), (49, 73), (74, 86), (87, 100)]
    header = None
    index  = None

    # 結果取得
    x = command_result(cmd,  CommandResultFile, colnames, colspecs, header, index)

    # 時間列parse
    x.loc[:,'DayOfWeek'] = x.loc[:,'start'].map(lambda x : x[0:3])
    x.loc[:,'start'] = pd.to_datetime(x.loc[:,'start'])
    
    return x

# 実行
y = cmd_last()
print(y.info())
y

~~~

実行結果はこんな感じになります

~~~python
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10 entries, 0 to 9
Data columns (total 7 columns):
user         10 non-null object
tty          10 non-null object
start        10 non-null datetime64[ns]
end          10 non-null object
logintime    6 non-null object
ip           10 non-null object
DayOfWeek    10 non-null object
dtypes: datetime64[ns](1), object(6)
memory usage: 640.0+ bytes
None
~~~

|  | user | tty | start | end | logintime | ip |
|--:|:--|:--|:--|:--|:--|:--|
| 0 | akira | tty8 | 2018-02-12 11:02:58 | Mon Feb 12 11:07:18 2018 | (00:04) | 0.0.0.0 |
| 1 | pipimi | tty8 | 2018-02-12 11:11:23 | Mon Feb 12 11:15:23 2018 | (00:04) | 0.0.0.0 |
| 2 | popko | tty8 | 2018-02-12 11:10:59 | Mon Feb 12 11:11:14 2018 | (00:00) | 0.0.0.0 |


で、Excel取り込みは read_excelを参考に
http://pandas-docs.github.io/pandas-docs-travis/io.html#excelfile-class

~~~python
# Excelファイル
ExcelFile = '/home/satoshi/login_ng.xlsx'
sheetname='Sheet1'

Excel_df = pd.read_excel(ExcelFile, sheetname=sheetname)
Excel_df.info()
Excel_df

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2 entries, 0 to 1
Data columns (total 2 columns):
user     2 non-null object
allow    2 non-null object
dtypes: object(2)
memory usage: 112.0+ bytes
user	allow
0	akira	NG
1	popko	OK

~~~

で、あとはDataFrameのmergeで一発チェック完了。

~~~python
z = pd.merge(y, Excel_df)
z[z['allow']=='NG']
~~~
|  | user | tty | start | end | logintime | ip | allow |
|--:|:--|:--|:--|:--|:--|:--|:--|
| 0 | akira | tty8 | 2018-02-12 11:02:58 | Mon Feb 12 11:07:18 2018 | (00:04) | 0.0.0.0 | NG |

そしてJenkinsで登録・自動実行で目的完了。


この作業、手作業でやってますが
早々に自動化せねばですね。