-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathcheck_saphana_health.py
212 lines (190 loc) · 10.2 KB
/
check_saphana_health.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
# coding=utf-8
################################################
# Rodolphe ALT
# 0.1b
# goal : check SAP HANA database from Nagios
#################################################
# hostname = sap_hana_server
# sqlport = 30044
# username = TECH_MONI
# password = UltraComplexPassword2020!
# example : python check_saphana_health.py --hostname sap_hana_server --username TECH_MONI --password UltraComplexPassword2020! --sqlport 30044 --mode backup
# example : python check_saphana_health.py --hostname sap_hana_server --username TECH_MONI --password UltraComplexPassword2020! --sqlport 30044 --mode alert --timeout 600
# example : ./check_saphana_health.sh --hostname sap_hana_server --username TECH_MONI --password UltraComplexPassword2020! --sqlport 30044 --mode alert --timeout 600
#################################################
import sys
import pyhdb
import argparse
from prettytable import PrettyTable
def function_exit(status):
if status == "OK": sys.exit(0)
if status == "WARNING": sys.exit(1)
if status == "CRITICAL": sys.exit(2)
if status == "UNKNOWN": sys.exit(3)
def function_check_M_SYSTEM_OVERVIEW(section,name,type):
command_sql = "SELECT STATUS,VALUE FROM SYS.M_SYSTEM_OVERVIEW WHERE SECTION='" + section + "' and NAME='" + name + "'"
cursor.execute(command_sql)
resultat = cursor.fetchone()
resultat_0 = resultat[0]
resultat_1 = resultat[1]
print ("%s - SAP HANA %s : %s " % (resultat_0,type,resultat_1))
function_exit(resultat_0)
if __name__ == '__main__':
parser = argparse.ArgumentParser(description = 'check SAP HANA database \n backup : last backup')
requiredNamed = parser.add_argument_group('required named arguments')
requiredNamed.add_argument('--hostname', help = "SAP HANA hostname", required=True)
requiredNamed.add_argument('--username', help = "SAP HANA login", required=True)
requiredNamed.add_argument('--password', help = "SAP HANA password", required=True)
requiredNamed.add_argument('--sqlport', help = "SAP HANA SQL port", required=True)
requiredNamed.add_argument('--mode', help = "backup_data, backup_log , version, cpu, memory, mem_host, services, services_all, license_usage, db_data, db_log, db_trace, alert, sid", required=True)
requiredNamed.add_argument('--timeout', help = "increase the default (60s) timeout")
args = parser.parse_args(sys.argv[1:])
connection = pyhdb.connect(args.hostname, args.sqlport, args.username, args.password)
if args.timeout != None: connection.timeout = int(args.timeout)
cursor = connection.cursor()
if args.mode == "backup_data":
#-- last backups data since 3 days
command_sql = "SELECT count(*) FROM SYS.M_BACKUP_CATALOG where entry_type_name = 'complete data backup' and state_name = 'successful' and (sys_start_time between ADD_DAYS(current_timestamp, -3) and current_timestamp);"
cursor.execute(command_sql)
resultat = cursor.fetchone()
last_successful_backup = ''
last_successful_detail = ''
if resultat[0] > 0:
command_sql = "SELECT top 1 sys_start_time FROM SYS.M_BACKUP_CATALOG where entry_type_name = 'complete data backup' and state_name='successful'order by entry_id asc;"
cursor.execute(command_sql)
last_successful_backup = (cursor.fetchone())
resultat_status = 'OK'
last_successful_detail = 'last successful ' + str(last_successful_backup[0])
else:
resultat_status = 'CRITICAL'
last_successful_detail = 'No successful log backup since 3 days'
print ("%s - SAP HANA Data Backups: %s" % (resultat_status,last_successful_detail))
function_exit(resultat_status)
if args.mode == "backup_log":
#-- last backups log since 3 hours
command_sql = "SELECT count(*) FROM SYS.M_BACKUP_CATALOG where entry_type_name = 'log backup' and state_name = 'successful' and (sys_start_time between ADD_SECONDS(current_timestamp, -10800) and current_timestamp);"
cursor.execute(command_sql)
resultat = cursor.fetchone()
last_successful_backup = ''
last_successful_detail = ''
if resultat[0] > 0:
command_sql = "SELECT top 1 sys_start_time FROM SYS.M_BACKUP_CATALOG where entry_type_name = 'log backup' and state_name='successful' order by entry_id asc;"
cursor.execute(command_sql)
last_successful_backup = (cursor.fetchone())
resultat_status = 'OK'
last_successful_detail = 'last successful ' + str(last_successful_backup[0])
else:
command_sql="SELECT value FROM m_inifile_contents where key='log_mode';"
cursor.execute(command_sql)
log_mode = (cursor.fetchone())
if log_mode[0] == "overwrite":
resultat_status = 'WARNING'
last_successful_detail = 'LOG MODE Overwrite enabled'
else:
resultat_status = 'CRITICAL'
last_successful_detail = 'No successful log backup since 3 hours'
print ("%s - SAP HANA LOG Backups: %s" % (resultat_status,last_successful_detail))
function_exit(resultat_status)
if args.mode == "version":
# check SAP HANA version
command_sql = "SELECT VERSION FROM SYS.M_DATABASE"
cursor.execute(command_sql)
resultat = cursor.fetchone()
print ("OK - SAP HANA running version : %s |\n|" % resultat)
if args.mode == "memory":
# check SAP HANA memory
command_sql = "SELECT LPAD(TO_DECIMAL(ROUND(SUM(INSTANCE_TOTAL_MEMORY_USED_SIZE) OVER () / 1024 / 1024 / 1024), 10, 0), 9) as \"HANA instance memory (used)\", LPAD(TO_DECIMAL(ROUND(SUM(INSTANCE_TOTAL_MEMORY_ALLOCATED_SIZE) OVER () / 1024 / 1024 / 1024), 10, 0), 9) as \"HANA instance memory (allocated)\", LPAD(TO_DECIMAL(ROUND(SUM(ALLOCATION_LIMIT) OVER () / 1024 / 1024 / 1024), 10, 0), 9) as \"HANA instance memory (limit)\" FROM M_HOST_RESOURCE_UTILIZATION"
cursor.execute(command_sql)
resultat = cursor.fetchone()
resultat_0 = int(resultat[0])
resultat_1 = int(resultat[1])
resultat_2 = int(resultat[2])
resultat_1_80 = format(resultat_1 * 80 / 100)
resultat_1_90 = format(resultat_1 * 90 / 100)
resultat_percentage = "{0:.0%}".format(1. * resultat_0/resultat_1)
resultat_per_num = float(resultat_percentage[:-1])
if resultat_per_num <=80: resultat_status="OK"
elif resultat_per_num >= 90: resultat_status="CRITICAL"
elif resultat_per_num > 80 and resultat_per_num < 90: resultat_status="WARNING"
print ("%s - SAP HANA Used Memory (%s) : %s GB Used / %s GB Allocated / %s GB Limit | mem=%sMB;%s;%s;0;%s" % (resultat_status,resultat_percentage,resultat_0,resultat_1,resultat_2,resultat_0,resultat_1_80,resultat_1_90,resultat_1))
function_exit(resultat_status)
if args.mode == "services":
# check SAP HANA services
command_sql = "SELECT SERVICE_NAME,ACTIVE_STATUS FROM SYS.M_SERVICES where IS_DATABASE_LOCAL='TRUE'"
cursor.execute(command_sql)
resultat = cursor.fetchall()
# init variables
resultat_all = ''
resultat_status = ''
resultat_control = 0
for row in resultat:
# Details about each services
if row[0] == 'indexserver': resultat_all = resultat_all + row[0] + ':' + row[1] + ' [mandatory]\n'
else: resultat_all = resultat_all + row[0] + ':' + row[1] + '\n'
# Status
if row[1] == 'NO': resultat_control = resultat_control + 1
else: resultat_control = resultat_control + 0
if row[0] == 'indexserver' and row[1] == 'NO': resultat_control = resultat_control + 20
if resultat_control == 0: resultat_status = "OK"
elif resultat_control <= 1: resultat_status = "WARNING"
elif resultat_control <= 20: resultat_status = "CRITICAL"
print ("%s - SAP HANA Services. \n%s | 'services'=%s;1;20;0;100" % (resultat_status,resultat_all,resultat_control))
function_exit(resultat_status)
if args.mode == "license_usage":
# check SAP HANA license usage
command_sql = "SELECT PRODUCT_LIMIT,PRODUCT_USAGE FROM SYS.M_LICENSE"
cursor.execute(command_sql)
resultat = cursor.fetchone()
resultat_1 = int(resultat[0])
resultat_0 = int(resultat[1])
resultat_percentage = "{0:.0%}".format(1. * resultat_0/resultat_1)
resultat_per_num = float(resultat_percentage[:-1])
if resultat_per_num <=80: resultat_status="OK"
elif resultat_per_num >= 90: resultat_status="CRITICAL"
elif resultat_per_num > 80 and resultat_per_num < 90: resultat_status="WARNING"
print ("%s - SAP HANA license (%s) : %s GB Usage / %s GB Limit" % (resultat_status,resultat_percentage,resultat_0,resultat_1))
function_exit(resultat_status)
if args.mode == "db_data":
# check SAP HANA data files disk usage
function_check_M_SYSTEM_OVERVIEW('Disk','Data','Datafiles')
if args.mode == "db_log":
# check SAP HANA log files disk usage
function_check_M_SYSTEM_OVERVIEW('Disk','Log','Logfiles')
if args.mode == "db_trace":
# check SAP HANA trace files disk usage
function_check_M_SYSTEM_OVERVIEW('Disk','Trace','Tracefiles')
if args.mode == "cpu":
# check SAP HANA cpu
function_check_M_SYSTEM_OVERVIEW('CPU','CPU','CPU')
if args.mode == "mem_host":
# check SAP HANA memory
function_check_M_SYSTEM_OVERVIEW('Memory','Memory','Memory')
if args.mode == "services_all":
# check SAP HANA services
function_check_M_SYSTEM_OVERVIEW('Services','All Started','Services')
if args.mode == "alert":
# check SAP HANA cpu
command_sql = "SELECT STATUS,VALUE FROM SYS.M_SYSTEM_OVERVIEW WHERE SECTION='Statistics' and NAME='Alerts'"
cursor.execute(command_sql)
resultat = cursor.fetchone()
resultat_0 = resultat[0]
resultat_1 = resultat[1]
if resultat_0 != "OK":
command_sql = "SELECT ALERT_RATING,ALERT_NAME,ALERT_DETAILS FROM _SYS_STATISTICS.STATISTICS_CURRENT_ALERTS WHERE ALERT_RATING >1"
cursor.execute(command_sql)
resultat = cursor.fetchall()
resultat_all = ''
for row in resultat:
resultat_all = resultat_all + str(row[0]) + ':' + row[1] + '(' + row[2] + ')' + '\n'
print ("%s - SAP HANA Alerts : %s |\n%s" % (resultat_0,resultat_1,resultat_all))
else:
print ("%s - SAP HANA Alerts : %s " % (resultat_0,resultat_1))
function_exit(resultat_0)
if args.mode == "sid":
# check SAP HANA SID
command_sql = "select top 1 DATABASE_NAME from SYS.M_DATABASES where ACTIVE_STATUS='YES';"
cursor.execute(command_sql)
resultat = cursor.fetchone()
print ("OK - SAP HANA SID : %s |\n|" % resultat)
connection.commit()
connection.close()