第通過(guò)Python實(shí)現(xiàn)對(duì)SQLServer數(shù)據(jù)文件大小的監(jiān)控告警功能#發(fā)送告警郵件

defsend_monitor_mail(mail_subject,mail_body,mail_receivers="testwukongbaigujing@"):

db_ps_file=os.path.join(sys.path[0],"qqmssqltest_db_server_conf.ini")

config=configparser.ConfigParser()

config.read(db_ps_file,encoding="utf-8")

mail_host=config.get('mail','host')

mail_port=config.get('mail','port')

#mail_user=config.get('mail','user')

#mail_pwd=config.get('mail','pwd')

sender=config.get('mail','sender')

#receivers=config.get('mail','receivers')

#發(fā)送HTML格式郵件

message=MIMEText(mail_body,'html','utf-8')

#message=MIMEText(mail_body,'plain','utf-8')

message['subject']=mail_subject

message['From']=sender

message['To']=mail_receivers

try:

smtpObj=smtplib.SMTP()

smtpObj.connect(mail_host,mail_port)#25為SMTP端口號(hào)

#SMTPAUTHextensionnotsupportedbyserver.

#/miguelgrinberg/microblog/issues/76

#smtpObj.ehlo()

#smtpObj.starttls()

#smtpObj.login(mail_user,mail_pwd)

smtpObj.sendmail(sender,mail_receivers,message.as_string())

smtpObj.quit()

print("郵件發(fā)送成功")

exceptExceptionase:

print(e)

#exceptsmtplib.SMTPException:

#print("Error:無(wú)法發(fā)送郵件")

3.主要功能代碼

收集到的DB數(shù)據(jù)文件的信息保存到表mssql_dblogsize中，其建表的腳本如下：

CREATETABLE[dbo].[mssql_dblogsize](

[id][int]IDENTITY(1,1)NOTNULL,

[createtime][datetime]NULL,

[vip][nvarchar](100)NULL,

[port][nvarchar](100)NULL,

[Environment][nvarchar](200)NULL,

[Dbname][varchar](200)NULL,

[Logical_Name][varchar](200)NULL,

[Physical_Name][varchar](1500)NULL,

[Size][bigint]NULL,

PRIMARYKEYCLUSTERED

[id]ASC

)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]

)ON[PRIMARY]

ALTERTABLE[dbo].[mssql_dblogsize]ADDDEFAULT(getdate())FOR[createtime]

GO

為了方便對(duì)表mssql_dblogsize的數(shù)據(jù)進(jìn)行管理和展示，在其基礎(chǔ)上抽象加工出了一個(gè)視圖v_mssql_dblogsize，注意Size大小的轉(zhuǎn)換（Size/128/1024asSizeGB）

創(chuàng)建視圖的腳本如下：

CREATEview[dbo].[v_mssql_dblogsize]

SELECT[id]

,[createtime]

,[vip]

,[port]

,[Environment]

,[Dbname]

,[Logical_Name]

,[Physical_Name]

,Size/128/1024asSizeGB

FROM[dbo].[mssql_dblogsize]

wheresize50*128*1024

andPhysical_Namelike'%ldf%'

GO

本測(cè)試實(shí)例使用的數(shù)