前陣子在鑽研 log 分析,之前有設定一些有關 log parse , 不過剛好有實際用到有關報表的部份,後來就找到有關 pyecharts 的用法,那就直接用它來產生報表… 也透過一些程式轉成 jpeg 檔,透過 email 自動傳送出去給客戶。

記錄下:

程式執行順序

 

x-owlet-raw-7.sh → x-owlet-report.sh xx xx (取伺服器名稱) :  u4_append.py and u4_cal.py →  x-owlet-online-report.sh (name or group name) :owlet-online_report.py →  x-owlet-sendmail.sh :  x-owlet.sendmail.py

│   ├── u4_all.csv  (u4_sql.csv + u4_v3.csv + u4_v4.csv + u4_v5.csv + u4_v6.csv)

│   ├── u4_full.csv   (u4_sql.csv + u4_v3.csv + u4_v4.csv + u4_v5.csv + u4_v6.csv + u4_v7.csv)                     

│   ├── u4_sql.csv ( sqlite3 data)

│   ├── u4_v3.csv  (relay session data)

│   ├── u4_v4.csv  (traffic data)

│   ├── u4_v5.csv  (login data)

│   └── u4_v6.csv  (server response time)

x-owlet-raw-7.sh 下載 prtg raw data and sqlite3 raw data 並清除其餘,保留需要資料。

x-owlet-report.sh 先合併資料欄再計算欄位數值

u4_append.py 
u4_cal.py

x-owlet-online-report.sh 產生 html and jpeg

owlet-online_report.py

x-owlet-sendmail.sh 附加信件並計出去

 x-owlet.sendmail.py

shell scrpit tree;

/home/ubuntu/script/prtg$

olb-py
│   ├── 52_append.py
│   ├── 52_cal.py
│   ├── owlet.jpg
│   ├── u4_append.py
│   ├── u4_cal.py
│   ├── x-owlet-online-report.sh
│   ├── x-owlet-raw-7.sh
│   ├── x-owlet-report.sh
│   ├── x-owlet.sendmail.py
│   ├── x-owlet-sendmail.sh
│   └── z-report-jpg
│   └── owlet-online_report.py

report and raw data tree:

/TUTK/prtg$

── olb_raw
│   ├── 52_network_raw.csv
│   ├── 52_relay_raw.csv
│   ├── 52_udp_raw.csv
│   ├── u4_network_raw.csv
│   ├── u4_relay_raw.csv
│   └── u4_udp_raw.csv
├── olb_report-7
│   ├── 52_all.csv
│   ├── 52_full.csv
│   ├── 52_sql.csv
│   ├── 52_v3.csv
│   ├── 52_v4.csv
│   ├── 52_v5.csv
│   ├── 52_v6.csv
│   ├── u4_all.csv
│   ├── u4_full.csv
│   ├── u4_sql.csv
│   ├── u4_v3.csv
│   ├── u4_v4.csv
│   ├── u4_v5.csv
│   └── u4_v6.csv

參考連結:

https://codertw.com/%E7%A8%8B%E5%BC%8F%E8%AA%9E%E8%A8%80/371681/

https://ecomfe.github.io/echarts-doc/public/cn/option.html#xAxis.axisLabel.showMinLabel

https://echarts.baidu.com/echarts2/doc/option.html#line-mark2~xAxis-i(category).axisTick.show

time change

 

https://blog.csdn.net/nbxzkok/article/details/79055952

https://blog.csdn.net/redraiment/article/details/5114503

new

cat ~/script/prtg/jan_raw/00_relay_raw.csv | awk ‘NR%171>=2 {print substr($14,5,6)}’ | tr -s ‘\n’| sed -e ’25d’| wc -l

cat ~/script/prtg/jan_raw/00_network_raw.csv | awk -F ‘,’ ‘{print $9}’| tr -s ‘\n’| sed -e ‘1,1d’| sed ‘s/\”//g’ | wc -l

https://bbs.csdn.net/topics/390539418

 

去除空白 空格

https://blog.longwin.com.tw/2010/01/sed-remove-line-2010/

https://blog.51cto.com/10742668/1944831

https://blog.csdn.net/richerg85/article/details/17766823

https://blog.longwin.com.tw/2009/03/linux-use-sed-delete-row-2009/

 

 

email

https://stackoverflow.com/questions/882712/sending-html-email-using-python

附件 test.sendmail.py

https://blog.csdn.net/handsomekang/article/details/9811355

sqlite

https://stackoverflow.com/questions/2887878/importing-a-csv-file-into-a-sqlite3-database-table-using-python

判斷heads 是否存在

https://stackoverflow.com/questions/28325622/python-csv-writing-headers-only-once

 

 sqlite3 p2prate.sqlite “select * from p2prate”| grep u3| grep “2019-04-17 ” | grep ALL| awk -F “|” ‘{print $1″,”$2″,”$4″,”$5}’ > u3.csv

 

首 尾

https://stackoverflow.com/questions/20347766/pythonically-add-header-to-a-csv-file

#!/usr/bin/env python
import datetime
import csv
#import numpy as np
today_string = datetime.datetime.today().strftime('%y%m%d')
new_rows = []
with open('u3-5.csv''r') as csvfile:
#       next(csvfile, None)
        line = csvfile.readlines()[1:]
#       headerline = csvfile.next()
        for row in line:
                string = row.strip("\n").split(",")
                a = str(string[0])
                b = str(string[1])
                c = float(string[2])
                d = float(string[3])
                e = float(string[4])
                row1 = []
                row1 = (a,b,c,d,e,(round(e/(d/(c+d)),3)))
                new_rows.append(row1)
#               print new_rows
with open( 'file-' + today_string + '.csv''wb') as csvfile:
        csv.writer(csvfile).writerow(["Date""temperature 1""Temperature 2"])
        csv.writer(csvfile).writerows(new_rows)

 

https://stackoverflow.com/questions/14674275/skip-first-linefield-in-loop-using-csv-file

#!/usr/bin/env python

import datetime
import csv
#import numpy as np

today_string = datetime.datetime.today().strftime(‘%y%m%d’)

new_rows = []

with open(‘u3-5.csv’, ‘r’) as csvfile:
# next(csvfile, None)
line = csvfile.readlines()[1:]
for row in line:
string = row.strip(“\n”).split(“,”)
a = str(string[0])
b = str(string[1])
c = float(string[2])
d = float(string[3])
e = float(string[4])
row1 = []
row1 = (a,b,c,d,e,(round(e/(d/(c+d)),3)))
new_rows.append(row1)

# print new_rows

with open( ‘file-‘ + today_string + ‘.csv’, ‘w’) as csvfile:
csv.writer(csvfile).writerows(new_rows)

 

 

 

[email protected]iotclog:/TUTK/prtg$ cat test.csv | awk ‘NR%26>=2 {print substr($17,5,9)}’
528.5000″
547.9167″
568.1667″
611.3333″
590.5000″
549.8333″
587.9167″
562.5000″
546.9167″
595.1667″
523.4167″
545.0833″
431.1667″
403.1667″
282.7500″
191.9167″
173.5000″
151.7500″
147.5000″
169.7500″
222.1667″
352.2500″
420.3636″
452.4167″

 

#!/usr/bin/env python

import datetime
import csv

today_string = datetime.datetime.today().strftime(‘%y%m%d’)

new_rows = []

with open(‘u3-test.csv’, ‘r’) as csvfile:
for row in csv.reader(csvfile):
row = [float(input) for input in row]
# row.append[row]
new_rows.append(row)
with open( ‘u3.csv’, ‘a’) as csvfile:
csv.writer(csvfile).writerows(new_rows)

https://stackoverflow.com/questions/42740869/use-python-to-calculate-data-in-csv

 

 

 

 

 

tr

https://www.jianshu.com/p/82c7a18c1120

 

link:https://superuser.com/questions/1291124/sum-all-lines-of-a-csv-file

awk -F, ‘{sum=0; for (i=2; i<=NF; i++) {sum+= $i}  print $1/ ( $3/ sum) }’ d.csv 

 

 

  cat  1612.txt | grep “MSG_QUERY_DEVICE5” | grep “UDP” |grep “D008” | awk  ‘{ print substr($5,0,40),$1 }’

curl -o txt.csv -v -X GET “https://prtg-master.tutk.com/api/historicdata.csv?id=11503&avg=3600&sdate=2019-04-13-00-00-00&edate=2019-04-13-00-00-00&username=opapi&passhash=4025917408

 

 

ref:

https://kb.paessler.com/en/topic/343-how-can-i-export-raw-sensor-data-automatically-from-prtg

https://kb.paessler.com/en/topic/69586-http-api:-how-to-retrieve-prtg-chart-using-curl

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *