?
本篇博客總結一下Python采集SQL Server數據庫服務器的磁盤使用信息，其實這里也是根據需求不斷推進演化的一個歷程，我們監控服務器的磁盤走了大概這樣一個歷程：
?
?
1：使用SQL Server作業監控磁盤空間
?
??? 很久之前寫過一篇博客 “MS SQL 監控磁盤空間告警 ” ，后面對這個腳本進行過多次完善和優化，做成了一個模板。在每臺SQL Server服務器上都部署了，確實也很實用。告警也很給力，但是缺點也非常明顯。
?
優點：1： 自己動手DIY，在沒有部署運維工具的前提下，確實能提前預警，拋開不足來說，告警還是非常給力的。
?
缺點: 1： 每臺服務器都需要部署，升級也很是麻煩。
??? ?
????? 2： 數據分散，在模式上有致命的先天不足。監控工具一般為星型結構，采集集中數據。
?
??????? 每臺服務器都需要部署，如果有修改，每臺服務器都需要發布更新，維護管理不方便。采集的一些數據分散，每臺SQL Server數據庫都需要保存一點數據。
?
????? 3： 通用性差，只能監控SQL Server服務器，Linux服務器，我們用的是crontab跑shell+perl腳本來監控磁盤空間并告警。
?
?
2： Zabbix監控磁盤告警
?
?
后面部署了Zabbix監控工具，Zabbix監控工具功能強大，不僅僅提供了磁盤空間告警功能，還提供了監控磁盤I/O等功能。更重要的是通用性很強大：只要是服務器就能監控，而方法1僅僅能監控SQL Server數據庫服務器。
?
優點： 1： Zabbix監控工具功能強大
??? 2： Zabbix監控工具通用性強
?
?
缺點： 1：需要分析磁盤空間的歷史數據比較麻煩，二次開發也比較麻煩（個人對zabbix了解不深入，可能對于高手而言，也是非常容易簡單的事情，僅僅是個人的一點體會感受）。例如我要獲取某個服務器的歷史數據，對磁盤的增長情況做分析。需要關聯好多表，非常麻煩。簡單研究后就直接放棄了。
?
自從Zabbix監控工具上線后，方法1就顯得可有可無。基本上處于被替換的尷尬境地。
?
?
?
3：使用Python腳本采集
?
這里存粹是為了擴展我自己的工具MyDB的功能，順手寫點Python腳本練手，而且目前而言，只能采集SQL Server服務器的磁盤空間使用情況。功能和通用性不能和Zabbix監控工具比。功能有很多局限性和不足之處，通用性也很差，但是也有一些不錯的優點。
?
優點： ? 1：不需要部署客戶端，也不需要每臺服務器去部署（與方法1對比而言）。
??? ? 2：批量采集，集中保存采集數據。方便統一告警，數據分析。
3：簡潔與簡單，靈活性高：目前就2個表，一個表 [dbo] . [SERVER_DISK_INFO] 保存最近一次采集的磁盤空間使用情況數據，另外一個表 [dbo] . [SERVER_DISK_INFO_HIS] 保存歷史數據，可以做一些擴容分析等。
??????? ? 例如，磁盤空間告警了，系統管理員會咨詢我，如果進行擴容，需要多大的空間，保證半年內，不會再次出現告警，那么就可以一個腳本計算一下（平均每個月增長值* 月數）
?
缺點： ? 1：通用性差，目前只能采集SQL Server數據庫服務器的磁盤信息。后續再考慮擴展性。實在沒有精力一步到位，慢慢完善擴充。
2：功能單一，不像Zabbix，還可以監控磁盤I/O,這個Python腳本就只能采集磁盤空間使用率，并不能擴展其功能，有一定局限性。
?
?
?
腳本 get_win_disk_info.py 如下所示：
# -*- coding: utf-8 -*-
''
'
-------------------------------------------------------------------------------------------
--? Script Name???????????? :?? get_win_disk_info.py
--? Script Auotor?????????? :?? 瀟湘隱者
--? Script Description????? :?? 采集SQL Server數據庫的磁盤使用數據，方便統一分析和告警處理！
-------------------------------------------------------------------------------------------
'
''
import pymssql
import logging
import os.path
import base64
from cryptography.fernet import Fernet
?
?
# 第一步，創建一個logger
logger = logging.getLogger()
logger.setLevel(logging.DEBUG)?
# Log等級開關
# 第二步，創建一個handler，用于寫入日志文件
#log_path = os.path.dirname(os.getcwd()) + '/logs/'
log_path =
'/home/konglb/logs/'
log_name = log_path +
'get_win_disk_info.log'
logfile = log_name
file_handler = logging.FileHandler(logfile, mode=
'a+'
)
file_handler.setLevel(logging.ERROR)?
# 輸出到file的log等級的開關
# 第三步，定義handler的輸出格式
formatter = logging.Formatter(
"%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s"
)
file_handler.setFormatter(formatter)
# 第四步，將handler添加到logger里面
logger.addHandler(file_handler)
# 如果需要同時需要在終端上輸出，定義一個streamHandler
print_handler = logging.StreamHandler()?
# 往屏幕上輸出
print_handler.setFormatter(formatter)?
# 設置屏幕上顯示的格式
logger.addHandler(print_handler)
?
?
key=bytes(os.environ.get(
'key'
),encoding=
"utf8"
)
?
cipher_suite = Fernet(key)
with open(
'/home/konglb/python/conf/ms_db_conf.bin'
,
'rb'
) as file_object:
???
for
line
in
file_object:
??????? encryptedpwd = line
decrypt_pwd = (cipher_suite.decrypt(encryptedpwd))
password_decrypted = bytes(decrypt_pwd).decode(
"utf-8"
)
#convert to string
env_db_user=os.environ.get(
'db_user'
)
db_user=base64.b64decode(bytes(env_db_user, encoding=
"utf8"
))
?
?
try:
??? dest_db_conn = pymssql.connect(host=os.environ.get(
'db_host'
),
????????????????????????????? user=bytes.decode(db_user),
????????????????????????????? password=password_decrypted,
????????????????????????????? database=
'DATABASE_REPOSITORY'
,
????????????????????????????? charset=
"utf8"
);key=bytes(os.environ.get(
'key'
),encoding=
"utf8"
)
?
?
?
???
# cursor = dest_db_conn.cursor();
???
# as_dict(bool) ：如果設置為True，則后面的查詢結果返回的是字典，關鍵字為查詢結果的列名；否則(默認)返回的為list。
???
# 可以通過在創建游標時指定as_dict參數來使游標返回字典變量,字典中的鍵為數據表的列名
??? cursor = dest_db_conn.cursor(as_dict=True)
???
#DELETE FROM [dbo].[DB_JOB_RUN_ERROR]
???
#? WHERE? RUN_DATE_TIME >= CAST(CONVERT(VARCHAR(10),GETDATE(),120) AS DATETIME);
??? sql_text =
""
"INSERT INTO dbo.SERVER_DISK_INFO_HIS
????????????????????????? ( COLLECT_DATE ,
??????????????????????????? FACTORY_CD ,
??????????????????????????? SERVER_NAME ,?????????????
??????????????????????????? DISK_NAME ,
??????????????????????????? TOTAL_SPACE ,
??????????????????????????? USED_SPACE ,
??????????????????????????? FREE_SPACE ,
??????????????????????????? FREE_PERCENT
????????????????????????? )
????????????????? SELECT? COLLECT_DATE ,
????????????????????????? FACTORY_CD ,
????????????????????????? SERVER_NAME ,
????????????????????????? DISK_NAME ,
????????????????????????? TOTAL_SPACE ,
????????????????????????? USED_SPACE ,
????????????????????????? FREE_SPACE ,
????????????????????????? FREE_PERCENT
????????????????? FROM??? [dbo].[SERVER_DISK_INFO];
?????????????????
????????????????? TRUNCATE TABLE? [dbo].[SERVER_DISK_INFO];
??????????????? "
""
??? cursor.execute(sql_text);
??? dest_db_conn.commit()
?
??? sql_text =
""
"
????????????? SELECT? SERVER_CD ,
????????????????????? SERVER_IP ,
????????????????????? USER_NAME ,
????????????????????? dbo.DecryptByPassPhrasePwd(PASSWORD) AS PASSWORD ,
????????????????????? SERVER_NAME,
????????????????????? DB_VERSION ,
????????????????????? INSTANCE_NAME
????????????? FROM?? dbo.DB_SERVER_CONFIG
????????????? WHERE? DATABASE_TYPE = 'SQL SERVER'
????????????????????? AND COLLECT_DATA = 1;
??????????? "
""
?
??? cursor.execute(sql_text);
??? rows = cursor.fetchall();
?
???
for
row
in
rows:
?
??????? try:
??????????? src_db_conn = pymssql.connect(host=row[
'SERVER_IP'
],
????????????????????????????????????????? user=row[
'USER_NAME'
],
????????????????????????????????????????? password=row[
'PASSWORD'
],
????????????????????????????????????????? database=
'master'
,
????????????????????????????????????????? charset=
"utf8"
,
????????????????????????????????????????? autocommit=True);
?
??????????? sub_cursor = src_db_conn.cursor(as_dict=True)
?
???????????
if
row[
'DB_VERSION'
] <= 2000:
??????????????? logger.info(row[
'SERVER_NAME'
] +
' not gather'
)
???????????????
continue
???????????
else
:
???????????????
#logger.info(row['DB_VERSION'])
??????????????? sql_db_patch=
"SELECT SERVERPROPERTY('productlevel') AS? PRODUCT_LEVEL"
??????????????? sub_cursor.execute(sql_db_patch)
??????????????? db_patch = sub_cursor.fetchone()
???????????????
#必須轉換，否則返回的為bytes，不是str
??????????????? patch_info=? str(db_patch[
'PRODUCT_LEVEL'
], encoding =
"utf-8"
)
?
?
?
?
?????????????
???????????????
if
((row[
'DB_VERSION'
]== 2005) or (row[
'DB_VERSION'
] ==2008? and patch_info ==
'RTM'
)):
???????????????????
#logger.info(row['SERVER_NAME'] + ' patch is ' + patch_info)
???????????????????
#continue
?
?
??????????????????? sql_job_info=
""
"
??????????????????????????????????? DECLARE @Result??????????? INT;
??????????????????????????????????? DECLARE @objectInfo??????? INT;
??????????????????????????????????? DECLARE @DriveInfo??????? CHAR(1);
??????????????????????????????????? DECLARE @TotalSize??????? VARCHAR(20);
??????????????????????????????????? DECLARE @OutDrive??????? INT;
??????????????????????????????????? DECLARE @UnitGB??????????? FLOAT;
??????????????????????????????????? DECLARE @CurrentDate??? DATETIME;
??????????????????????????????????? DECLARE @ConfValue??????? INT;
??????????????????????????????????? SET @UnitGB = 1073741824.0;
???????????????????????????????????
???????????????????????????????????
??????????????????????????????????? --創建臨時表保存服務器磁盤容量信息
??????????????????????????????????? CREATE TABLE #DiskCapacity
??????????????????????????????????? (
??????????????????????????????????????? COLLECT_DATE??? DATETIME??? ,
??????????????????????????????????????? FACTORY_CD????? NVARCHAR(24),
??????????????????????????????????????? SERVER_NAME??????? NVARCHAR(64),
??????????????????????????????????????? DISK_NAME??????? NVARCHAR(2) ,
??????????????????????????????????????? TOTAL_SPACE??????? FLOAT,
??????????????????????????????????????? USED_SPACE??????? FLOAT,
??????????????????????????????????????? FREE_SPACE??????? FLOAT,
??????????????????????????????????????? FREE_PERCENT??? FLOAT???
??????????????????????????????????? );
???????????????????????????????????
??????????????????????????????????? INSERT #DiskCapacity
??????????????????????????????????????????? (DISK_NAME,FREE_SPACE )
??????????????????????????????????? EXEC master.dbo.xp_fixeddrives;
????????????????????????????????????
??????????????????????????????????? EXEC sp_configure 'show advanced options', 1
??????????????????????????????????? RECONFIGURE WITH OVERRIDE;
???????????????????????????????????
??????????????????????????????????? SELECT @ConfValue = value FROM sys.sysconfigures WHERE comment LIKE '%Ole Automation Procedures%'
??????????????????????????????????? IF @ConfValue = 0
??????????????????????????????????? BEGIN
??????????????????????????????????????? EXEC sp_configure 'Ole Automation Procedures', 1;
??????????????????????????????????????? RECONFIGURE WITH OVERRIDE;
??????????????????????????????????? END
???????????????????????????????????
???????????????????????????????????
??????????????????????????????????? EXEC @Result = master.sys.sp_OACreate 'Scripting.FileSystemObject',@objectInfo OUT;
???????????????????????????????????
??????????????????????????????????? DECLARE CR_DiskInfo CURSOR LOCAL FAST_FORWARD
??????????????????????????????????? FOR SELECT? DISK_NAME FROM #DiskCapacity
??????????????????????????????????? ORDER by DISK_NAME
???????????????????????????????????
??????????????????????????????????? OPEN CR_DiskInfo;
???????????????????????????????????
???????????????????????????????????
??????????????????????????????????? SET @CurrentDate = GETDATE();
??????????????????????????????????? FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo
???????????????????????????????????
??????????????????????????????????? WHILE @@FETCH_STATUS=0
??????????????????????????????????? BEGIN
???????????????????????????????????
??????????????????????????????????????? EXEC @Result = sp_OAMethod @objectInfo,'GetDrive', @OutDrive OUT, @DriveInfo
???????????????????????????????????
???????????????????????????????????
??????????????????????????????????????? EXEC @Result = sp_OAGetProperty @OutDrive,'TotalSize', @TotalSize OUT
???????????????????????????????????
???????????????????????????????????
??????????????????????????????????????? UPDATE #DiskCapacity
??????????????????????????????????????? SET TOTAL_SPACE=ROUND(@TotalSize/@UnitGB,2), COLLECT_DATE=@CurrentDate,
??????????????????????????????????????????? FACTORY_CD=%s, SERVER_NAME=@@SERVERNAME,
??????????????????????????????????????????? --USED_SPACE=(@TotalSize-FREE_SPACE)/@UnitGB,
??????????????????????????????????????????? --FREE_PERCENT=FREE_SPACE/@TotalSize*100,
??????????????????????????????????????????? FREE_SPACE=ROUND(FREE_SPACE/1024,2)
??????????????????????????????????????? WHERE DISK_NAME =@DriveInfo
???????????????????????????????????
??????????????????????????????????????? UPDATE #DiskCapacity
??????????????????????????????????????? SET USED_SPACE=(TOTAL_SPACE-FREE_SPACE),
??????????????????????????????????????????? FREE_PERCENT=ROUND(FREE_SPACE/TOTAL_SPACE*100,2)
??????????????????????????????????????? WHERE DISK_NAME =@DriveInfo
???????????????????????????????????
??????????????????????????????????????? FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo
???????????????????????????????????
??????????????????????????????????? END
???????????????????????????????????
??????????????????????????????????? CLOSE CR_DiskInfo
??????????????????????????????????? DEALLOCATE CR_DiskInfo;
???????????????????????????????????
??????????????????????????????????? EXEC @Result=sp_OADestroy @objectInfo
???????????????????????????????????
??????????????????????????????????? EXEC sp_configure 'show advanced options', 1
??????????????????????????????????? RECONFIGURE WITH OVERRIDE;
???????????????????????????????????
??????????????????????????????????? IF @ConfValue = 0
??????????????????????????????????? BEGIN
??????????????????????????????????????? EXEC sp_configure 'Ole Automation Procedures', 0;
??????????????????????????????????????? RECONFIGURE WITH OVERRIDE;
??????????????????????????????????? END
???????????????????????????????????
???????????????????????????????????
??????????????????????????????????? EXEC sp_configure 'show advanced options', 0
??????????????????????????????????? RECONFIGURE WITH OVERRIDE;
??????????????????????????????????? SELECT * FROM #DiskCapacity?????????????????????????????
???????????????????????????????? "
""
??????????????????? sub_cursor.execute(sql_job_info, row[
'SERVER_CD'
]);
??????????????????? job_rows = sub_cursor.fetchall();
??????????????????? src_db_conn.close()
??????????????????? error_job_info = []
???????????????????
for
sub_row
in
job_rows:
??????????????????????? data = (
??????????????????????? sub_row[
'COLLECT_DATE'
], sub_row[
'FACTORY_CD'
], sub_row[
'SERVER_NAME'
], sub_row[
'DISK_NAME'
],
??????????????????????? sub_row[
'TOTAL_SPACE'
], sub_row[
'USED_SPACE'
], sub_row[
'FREE_SPACE'
], sub_row[
'FREE_PERCENT'
])
??????????????????????? error_job_info.append(data)
?
?
???????????????????
''
'
??????????????????? logger.info('2008 2005
')
??????????????????? logger.info(row['SERVER_NAME
'])
??????????????????? sub_cursor.callproc('[msdb].[dbo].[sp_get_diskinfo]
')
??????????????????? result_rows =sub_cursor.fetchall()
??????????????????? src_db_conn.close()
??????????????????? error_job_info = []
??????????????????? for sub_row in result_rows:
??????????????????????? data = (
??????????????????????? sub_row['COLLECT_DATE
'], sub_row['
FACTORY_CD
'], sub_row['
SERVER_NAME
'], sub_row['
DISK_NAME
'],
??????????????????????? sub_row['TOTAL_SPACE
'], sub_row['
USED_SPACE
'], sub_row['
FREE_SPACE
'], sub_row['
FREE_PERCENT
'])
??????????????????????? error_job_info.append(data)
??????????????????? '
''
???????????????
else
:
?
?
??????????????????? sql_job_info =
""
"WITH Server_Disk AS
????????????????????????????????????? (
????????????????????????????????????????????? SELECT DISTINCT
????????????????????????????????????????????????? REPLACE(vs.volume_mount_point, ':\\' , '') AS DISK_NAME,
????????????????????????????????????????????????? CAST(VS.total_bytes/1024.0/1024/1024 AS NUMERIC(18,2) ) AS [TOTAL_SPACE],
????????????????????????????????????????????????? CAST(VS.available_bytes/1024.0/1024/1024? AS NUMERIC(18,2)) AS [FREE_SPACE]
????????????????????????????????????????????? FROM? sys.master_files AS f
????????????????????????????????????????????? CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
????????????????????????????????????? )
????????????????????????????????????? SELECT? GETDATE()???????? AS COLLECT_DATE,
??????????????????????????????????????????? %s???????????????????? AS FACTORY_CD? ,
????????????????????????????????????????????? @@SERVERNAME??????? AS SERVER_NAME ,
????????????????????????????????????????????? D.DISK_NAME??????????? AS DISK_NAME,
????????????????????????????????????????????? D.[TOTAL_SPACE]??? AS TOTAL_SPACE,
????????????????????????????????????????????? D.[TOTAL_SPACE] - D.[FREE_SPACE]?
????????????????????????????????????????????????????????????????? AS USED_SPACE,
????????????????????????????????????????????? D.[FREE_SPACE]??? AS FREE_SPACE,
????????????????????????????????????????????? CAST(D.[FREE_SPACE] * 100 / D.[TOTAL_SPACE] AS NUMERIC(18, 2)) AS FREE_PERCENT
????????????????????????????????????? FROM??? Server_Disk AS D
????????????????????????????????????? ORDER BY D.DISK_NAME;
?????????????????????????????????? "
""
?
??????????????????? sub_cursor.execute(sql_job_info, row[
'SERVER_CD'
]);
??????????????????? job_rows = sub_cursor.fetchall();
??????????????????? src_db_conn.close()
??????????????????? error_job_info = []
???????????????????
for
sub_row
in
job_rows:
??????????????????????? data = (sub_row[
'COLLECT_DATE'
], sub_row[
'FACTORY_CD'
], sub_row[
'SERVER_NAME'
], sub_row[
'DISK_NAME'
],
??????????????????????????????? sub_row[
'TOTAL_SPACE'
], sub_row[
'USED_SPACE'
], sub_row[
'FREE_SPACE'
], sub_row[
'FREE_PERCENT'
])
??????????????????????? error_job_info.append(data)
?
??????????????? save_job_info =
""
"???????????????????????????????????
???????????????????????????????? INSERT? INTO dbo.SERVER_DISK_INFO
???????????????????????????????????????????? (?? COLLECT_DATE
?????????????????????????????????????????????? , FACTORY_CD
?????????????????????????????????????????????? , SERVER_NAME
?????????????????????????????????????????????? , DISK_NAME
?????????????????????????????????????????????? , TOTAL_SPACE
?????????????????????????????????????????????? , USED_SPACE
?????????????????????????????????????????????? , FREE_SPACE
?????????????????????????????????????????????? , FREE_PERCENT
???????????????????????????????????????????? )
???????????????????????????????? VALUES(%s,%s,%s,%s,%d,%d,%d,%d)"
""
??????????????? cursor.executemany(save_job_info, error_job_info);
??????????????? dest_db_conn.commit()
??????????????? logger.info(row[
'SERVER_NAME'
] +
' gather successful'
)
?
?
??????? except? pymssql.InterfaceError as fe:
??????????? logger.error(fe.message)
??????? except? pymssql.DatabaseError as e:
??????????? dest_db_conn.rollback();
??????????? logger.error(row[
'SERVER_IP'
] +
' 采集出錯，請檢查處理異常'
)
??????????? logger.error(e)
??????? finally:
??????????? src_db_conn.close()
except pymssql.InterfaceError as fe:
??? logger.error(fe.message)
except? pymssql.DatabaseError as e:
??? dest_db_conn.rollback();
?
??? logger.error(row[
'SERVER_IP'
] +
' 采集出錯，請檢查處理異常'
)
??? logger.error(e)
finally:
??? dest_db_conn.close()
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
