将日志数据自动写入excel的脚本

最近用sysbench给服务器性能做测评，要将不同测试参数下产生的日志的数据提取出来，写到excel里面，用excel生成趋势图。
如果日志比较大，还手工一项项填，那必定是天长地久海枯石烂，不累死也烦死。
幸好有脚本啊！

涉及的知识有sed,awk,bash,python基本语法,python读写excel的模块，都是比较初步的知识

首先，看看我们的数据源--日志
$ ls -1
IO_mode[rndrd]_threads[16]_filesize[10G]_file-block-size[16384]_{20131213030130}.log
IO_mode[rndrd]_threads[16]_filesize[10G]_file-block-size[4096]_{20131213024440}.log
IO_mode[rndrd]_threads[16]_filesize[10G]_file-block-size[8192]_{20131213025302}.log
IO_mode[rndrd]_threads[2]_filesize[10G]_file-block-size[16384]_{20131213014835}.log
IO_mode[rndrd]_threads[2]_filesize[10G]_file-block-size[4096]_{20131213013312}.log
IO_mode[rndrd]_threads[2]_filesize[10G]_file-block-size[8192]_{20131213014052}.log
IO_mode[rndrd]_threads[32]_filesize[10G]_file-block-size[16384]_{20131213032750}.log
IO_mode[rndrd]_threads[32]_filesize[10G]_file-block-size[4096]_{20131213031004}.log
IO_mode[rndrd]_threads[32]_filesize[10G]_file-block-size[8192]_{20131213031848}.log
IO_mode[rndrd]_threads[4]_filesize[10G]_file-block-size[16384]_{20131213021210}.log
IO_mode[rndrd]_threads[4]_filesize[10G]_file-block-size[4096]_{20131213015623}.log
IO_mode[rndrd]_threads[4]_filesize[10G]_file-block-size[8192]_{20131213020414}.log
IO_mode[rndrd]_threads[8]_filesize[10G]_file-block-size[16384]_{20131213023624}.log
IO_mode[rndrd]_threads[8]_filesize[10G]_file-block-size[4096]_{20131213022010}.log
IO_mode[rndrd]_threads[8]_filesize[10G]_file-block-size[8192]_{20131213022815}.log
……

然后看看一个日志里的内容：
$cat   IO_mode\[rndrd\]_threads\[16\]_filesize\[10G\]_file-block-size\[16384\]_\{20131213030130\}.log
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Random number generator seed is 0 and will be ignored

Extra file open flags: 0
128 files, 80Mb each
10Gb total file size
Block size 16Kb
Number of IO requests: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!

Operations performed:  10000 reads, 0 writes, 0 Other = 10000 Total
Read 156.25Mb  Written 0b  Total transferred 156.25Mb  (514.97Mb/sec)
32958.13 Requests/sec executed

General statistics:
    total time:                          0.3034s
    total number of events:              10000
    total time taken by event execution: 4.1829s
    response time:
         min:                                  0.00ms
         avg:                                  0.42ms
         max:                                208.05ms
         approx.  95 percentile:               0.79ms

Threads fairness:
    events (avg/stddev):           625.0000/303.67
    execution time (avg/stddev):   0.2614/0.03

以上标记为蓝色的数据是要提取出来的，填到excel的

先来看看目标长什么样：没错，我们的目标是生成这样的excel：


分2步走：
第一步，是把日志里的数据提取出来,方便写入excel。
写数据提取脚本：
vim grab.sh

#!/bin/bash

 IOlogpath=/tpm/sysbench/log

IOlogfile=/tpm/sysbench/log/IO_mode*

function IO()
{
    for f in `ls $IOlogfile`; do
        input=`echo $f|awk -F "[][]" '{print $1,$2,$3,$4,$5,$6,$7,$8}'` # something like: "IO_mode seqwr _threads 32 _filesize 10G _file-block-size 16384"
        total_time=`sed -n 's/total time:.*\([0-9]\.[0-9]*\).*/\1/p' $f`
        response_time=`sed -n 's/approx..*\([0-9]\.[0-9]*\).*/\1/p' $f`
        tps=`awk -F '[()]' '/Mb\/sec/{print $2}' $f |awk -F'M' '{print $1}'` #transfer data per second
        Reqps=`awk '/Requests\/sec/{print $1}' $f`  #Requests per second executed
        echo $input\ total_time $total_time\ response_time $response_time\ tps $tps\ Reqps $Reqps
    done
}

IO |sort -k2,2 -k4,4n -k8,8n > $IOlogpath/IO.rec

执行grab.sh生成IO.rec文件

看看IO.rec里面是什么：
IO_mode rndrd _threads 2 _filesize 10G _file-block-size 4096 total_time 0.5035 response_time 0.19 tps 77.581 Reqps 19860.69
IO_mode rndrd _threads 2 _filesize 10G _file-block-size 8192 total_time 0.5295 response_time 0.20 tps 147.53 Reqps 18884.25
IO_mode rndrd _threads 2 _filesize 10G _file-block-size 16384 total_time 0.8527 response_time 0.35 tps 183.24 Reqps 11727.44
IO_mode rndrd _threads 4 _filesize 10G _file-block-size 4096 total_time 0.2972 response_time 0.22 tps 131.44 Reqps 33648.44
IO_mode rndrd _threads 4 _filesize 10G _file-block-size 8192 total_time 0.3419 response_time 0.26 tps 228.52 Reqps 29250.71
IO_mode rndrd _threads 4 _filesize 10G _file-block-size 16384 total_time 0.3699 response_time 0.29 tps 422.38 Reqps 27032.54
IO_mode rndrd _threads 8 _filesize 10G _file-block-size 4096 total_time 0.2671 response_time 0.33 tps 146.24 Reqps 37437.47
IO_mode rndrd _threads 8 _filesize 10G _file-block-size 8192 total_time 0.2807 response_time 0.49 tps 278.28 Reqps 35619.99
IO_mode rndrd _threads 8 _filesize 10G _file-block-size 16384 total_time 0.2825 response_time 0.50 tps 553.14 Reqps 35400.78
IO_mode rndrd _threads 16 _filesize 10G _file-block-size 4096 total_time 0.2295 response_time 0.75 tps 170.21 Reqps 43574.90
IO_mode rndrd _threads 16 _filesize 10G _file-block-size 8192 total_time 0.2657 response_time 0.87 tps 294.02 Reqps 37634.59
IO_mode rndrd _threads 16 _filesize 10G _file-block-size 16384 total_time 0.3034 response_time 0.79 tps 514.97 Reqps 32958.13
IO_mode rndrd _threads 32 _filesize 10G _file-block-size 4096 total_time 0.2095 response_time 1.44 tps 186.49 Reqps 47741.06
IO_mode rndrd _threads 32 _filesize 10G _file-block-size 8192 total_time 0.3147 response_time 1.94 tps 248.28 Reqps 31779.53
IO_mode rndrd _threads 32 _filesize 10G _file-block-size 16384 total_time 0.2250 response_time 1.82 tps 694.33 Reqps 44437.09
…………
…………

接下来进行第二步：
把IO.rec里面的数据写进excel
读和写excel分别是用xlrd、xlwt这两个模块
先安装模块
pip install xlwt

然后，写导入脚本：
vim txt2excel.py

# -* - coding: UTF-8 -* -
from tempfile import TemporaryFile
from xlwt import Workbook

book = Workbook()

def wexcel(sheetname,file):
    sheet = book.add_sheet(sheetname)
    i=0
    for line in open(file):
        line=line.strip('\n') # 去掉换行符"\n"如果不去掉换行符，导入到excel中会使最后一列带换行符，造成的格式问题会导致最后一列不显示
        data=line.split(' ')
        row=sheet.row(i)
        i+=1
        j=0
        for col in range(1,len(data)): # 轮询列，写当前行
            row.write(j,str(data[col]))
            j+=1

def main():
    wexcel('IO',"/tpm/sysbench/log/IO.rec")
    book.save('simple.xls')
    book.save(TemporaryFile())

if __name__=="__main__":
    main()

执行 txt2excel.py脚本，生成simple.xls  excel文件，大功告成！