#!/usr/bin/python #-*- coding: UTF-8 -*- #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++# #Name : collMonitorDataToDB.py # #Created : 2017/07/06 # #Author : @ruiy # #Version : 2.0 # #Copyright : 2016 ~ 2017 ahwater.net Corporation.` # #Description : collection monitor indicator data to DB. # #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++# import pyodbc import sys import os import commands import datetime import paramiko import re #import psutil #监控表字段主要: # id,timestramp,location,ip,hostname,port,port_est_counts,cpu_use_ratio # mem_total,mem_free,mem_use_ratio # disk_drive_c_total,disk_drive_c_free,disk_drive_c_use_ratio # send_flow,recv_flow,send_packets,recv_packets # #内存 #总内存容量(单位-字节bytes): wmic memorychip get capacity #剩余内存容量(单位-kbytes): wmic OS get FreePhysicalMemory #磁盘 #查看物理磁盘: wmic DISKDRIVE get deviceid,Caption,size,InterfaceType #查看逻辑分区: wmic LOGICALDISK get name,Description,filesystem,size,freespace #获取指定分区信息: fsutil volume diskfree c: #获取磁盘分区total or fre 或用 wmic LOGICALDISK get name,Description,filesystem,size,freespace #cpu #查看cpu核数: wmic cpu get name,addresswidth,processorid #获取cpu实时使用率: wmic cpu get LoadPercentage #process #process list: wmic process get Caption,KernelModeTime,UserModeTime #mystring.strip().replace(' ', '').replace('\n', '').replace('\t', '').replace('\r', '').strip() #网络流量信息 #python conn sql server2008R2 conn = pyodbc.connect( driver='{sql server native client 10.0}', server='10.34.1.30', database='LogFeedback', uid='sa', pwd='ahswyc' ) cursor = conn.cursor() #参考调试,入库测试语句 #cursor.execute("insert into iisEstablishConnCounts(timestramp,iisHostIp,connCounts) values('2017/07/06:22:10','10.34.1.23',90)") #sql入库字段基于变量 #80端口连接数统计 #netstat -na -p tcp| findstr 80 | find /C "ESTABLISH" #cmd="netstat -na -p tcp| findstr 80 | find /C \"ESTABLISH\"" #(status,output) = commands.getstatusoutput("%s" % cmd) #python执行调用系统命令并将结果保存到变量 #注意在linux下用python执行系统命令并将结果保存到变量与windows不同如下语句 #cmd="netstat -na -p tcp| findstr 80 | find /C \"ESTABLISH\"" #(status,output) = commands.getstatusoutput("%s" % cmd) #获取监控时间戳 dt= datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') #print(dt) dateTime =dt #print("debug1: ",dateTime) print("current dataTime: ",dateTime) #获取本地的弃用,程序主要用于获取远程主机的监控数据 #调试暂时打开 #portCounts=os.popen("netstat -na -p tcp| findstr 80 | find /C \"ESTABLISH\"").read() #print("debug2: ",portCounts) #人工配置数据 localT=ur'安徽省水文局' #localT="anhui shuiwen ju" print("monitr location: ",localT) mip="10.34.1.23" print("monitor ip:",mip) mport=80 print("port: ",mport) #远程监控数据获取 #paramiko ssh跨机建立 #transport = paramiko.Transport(('192.168.11.181',22)) transport = paramiko.Transport(('10.34.1.23', 22)) transport.connect(username='ahwater', password='Aa7788..') ssh = paramiko.SSHClient() ssh._transport = transport #主机名 cmd01='hostname' stdin, stdout, stderr = ssh.exec_command(cmd01) #print(stdout.read()) data01=stdout.read().strip().replace(' ', '').replace('\t', '').replace('\r', '').strip() print("monitor hostname: ",data01) #端口连接数统计 #在人工配置数据处填写的端口的端口establish port counts cmd02='netstat -na' stdin, stdout, stderr = ssh.exec_command(cmd02) data02=stdout.read().count('80') #data02=stdout.read().count(mport) print("port est counts: ",data02) #cpu使用率 cmd03='wmic cpu get LoadPercentage' stdin, stdout, stderr = ssh.exec_command(cmd03) #da03=stdout.read().strip('LoadPercentage').replace('\n', '').replace('\t', '').replace('\r', '').replace(' ','').strip() #da03=stdout.read().strip('LoadPercentage').replace(' ','').replace('\n','').replace('\t', '').replace('\r', '') da03=stdout.read().strip('LoadPercentage').replace('\n', '').replace('\t', '').replace('\r', '').replace(' ','').replace('\n','').strip() #print(da03) statis=0 counts=0 for i in da03: #cpu物理核心统计 counts = counts + 1 statis = statis + int(i) try: #print(counts) data03=float(statis)/counts/100 #data003="'" +data03 +"'" print("cpu use ratio: ",data03) except: pass #内存总量/Gb cmd04='wmic memorychip get capacity' stdin,stdout,stderr = ssh.exec_command(cmd04) da04=stdout.read().strip('Capacity').replace('\n','').replace('\t','').replace('\r','').replace(' ','').strip() data04=float(da04)/1024/1024/1024 print("mem total Gb: ",data04) #内存剩余量/Gb cmd05='wmic OS get FreePhysicalMemory' stdin,stdout,stderr = ssh.exec_command(cmd05) da05=stdout.read().strip('FreePhysicalMemory').replace('\n','').replace('\t','').replace('\r','').replace(' ','').strip() data05=round(float(da05)/1024/1024,4) print("mem free Gb: ",data05) #内存使用率 data06=round(float((data04 - data05)) / data04,4) print("mem use ratio: ",data06) #磁盘信息,根系统盘C: #cmd07='fsutil volume diskfree c:' cmd07='wmic LOGICALDISK get FreeSpace,Size' #C盘总量 stdin,stdout,stderr = ssh.exec_command(cmd07) #删除FreeSpace,Size字符 d7_1=stdout.read().strip().replace('FreeSpace','').replace('Size','') #删除r-n d7_2=d7_1.strip().replace('\r','').replace('\n','') #替换多个' '为单个 d7_3=(' '.join(filter(lambda x: x, d7_2.split(' ')))) #转换str->list disk_data=d7_3.split(' ') #获取C分区盘总量Gb,获取的数据默认单位是bytes data07=round(float(disk_data[1])/1024/1024/1024,4) print("C disk total Gb:",data07) #获取C分区盘剩余量Gb data08=round(float(disk_data[0])/1024/1024/1024,4) print("C disk free Gb:",data08) #C分区盘使用率 data09=round((data07 - data08) / data07,4) print("C disk space use ratio: ",data09) #获取网络流量信息 cmd08='netstat -e' stdin, stdout, stderr = ssh.exec_command(cmd08) d8_1=stdout.read().strip().rstrip().lstrip().replace('\r','').replace('\n','') d8_2=(' '.join(filter(lambda x: x, d8_1.split(' ')))) d8_3=d8_2.split(' ') #数据流默认bytes,把汉字字符剔除 net_data=re.sub('[^\u4e00-\u9fa5]','',d8_3[4]) #发送的流量累加总计Gb data10=round(float(net_data)/1024/1024/1024,4) print("send trafic flow Gb: ",data10) #接收的流量累加总计 #net_data2=re.sub('[^\u4e00-\u9fa5]','',d8_3[3]) net_data2=d8_3[3] data11=round(float(net_data2)/1024/1024/1024,4) print("recv trafic flow Gb: ",data11) #发送的数据包累加总数Tcp/ip层 #发送的数据包累加总数 data12=int(re.sub('[^\u4e00-\u9fa5]','',d8_3[6])) #data12=round(float(net_data3)/1024/1024/1024,4) print("send packets: ",data12) #接收的数据包累计 data13=int(d8_3[5]) print("recv packets: ",data13) #数据入库 #字段值基于变量的sql语句模型 sql_debug = """insert into iisEstablishConnCounts(timestramp,iisHostIp,connCounts) values( %(timestramp)s, '10.34.1.23', %(connCounts)s ) """ # id,timestramp,location,ip,hostname,port,port_est_counts,cpu_use_ratio # mem_total,mem_free,mem_use_ratio # disk_drive_c_total,disk_drive_c_free,disk_drive_c_use_ratio # send_flow,recv_flow,send_packets,recv_packets sql = """insert into ahwater_perf_monitor(timestramp,location,ip,hostname,port,port_est_counts,cpu_use_ratio, mem_total,mem_free,mem_use_ratio, disk_drive_c_total,disk_drive_c_free,disk_drive_c_use_ratio, send_flow,recv_flow,send_packets,recv_packets) values( %(timestramp)s, %(location)s, %(ip)s, %(hostname)s, %(port)s, %(port_est_counts)s, %(cpu_use_ratio)s, %(mem_total)s, %(mem_free)s, %(mem_use_ratio)s, %(disk_drive_c_total)s, %(disk_drive_c_free)s, %(disk_drive_c_use_ratio)s, %(send_flow)s, %(recv_flow)s, %(send_packets)s, %(recv_packets)s ) """ #print(sql) #cursor.execute(sql_debug % dict(timestramp = dateTime,connCounts = portCounts)) #print("\n") """ print dateTime print localT print mip print data01 print mport print data02 print data03 print data04 print data05 print data06 print data07 print data08 print data09 print data10 print data11 print data12 print data13 """ cursor.execute(sql % dict( timestramp="'" + dateTime + "'", location="'" + localT + "'", ip="'" + mip + "'", hostname="'" + data01 + "'", port=mport, port_est_counts=data02, cpu_use_ratio=data03, mem_total=data04, mem_free=data05, mem_use_ratio=data06, disk_drive_c_total=data07, disk_drive_c_free=data08, disk_drive_c_use_ratio=data09, send_flow=data10, recv_flow=data11, send_packets=data12, recv_packets=data13 )) """ cursor.execute(sql % dict( timestramp=dateTime, location=localT, ip=mip, hostname=data01, port=mport, port_est_counts="'" +str(data02) + "'", cpu_use_ratio="," +str(data03) + "'", mem_total="'"+str(data04)+"'", mem_free="'"+str(data05)+"'", mem_use_ratio="'"+str(data06)+"'", disk_drive_c_total="'"+str(data07)+"'", disk_drive_c_free="'" +str(data08)+"'", disk_drive_c_use_ratio="'"+str(data09)+"'", send_flow="'"+str(data10)+"'", recv_flow="'"+str(data11)+"'", send_packets=data12, recv_packets=data13 )) """ #关闭pyodbc conn连接 conn.commit() conn.close() #关闭paramiko ssh回话 transport.close()
