def home(request):
hosts_sql = """
SELECT COUNT( * ) AS quantity
FROM nagios_hosts a, nagios_hoststatus b
WHERE a.host_object_id = b.host_object_id
AND a.instance_id = b.instance_id
AND b.current_state !=0
"""
hosts_cursor = raw_sql(hosts_sql)
hosts_rows = hosts_cursor.fetchall()
problem_hosts = int(hosts_rows[0][0])
services_sql = """
SELECT COUNT( * ) AS quantity
FROM nagios_services a, nagios_servicestatus b
WHERE a.service_object_id = b.service_object_id
AND a.instance_id = b.instance_id
AND b.current_state !=0
"""
services_cursor = raw_sql(services_sql)
services_rows = services_cursor.fetchall()
problem_services = int(services_rows[0][0])
notifications = Notification.objects.filter(has_processed=None)
unhandled_notifications = len(notifications)
jobs = Notification_Result.objects.filter(to_user=request.session.get('username',None),op_processed_result=None)
unhandled_jobs = len(jobs)
return render('users/home.html',{'problem_hosts':problem_hosts,
'problem_services':problem_services,
'unhandled_notifications':unhandled_notifications,
'unhandled_jobs':unhandled_jobs
},request)
def get_hosts_list():
sql = "select display_name from nagios_hosts"
cursor = raw_sql(sql)
rows = cursor.fetchall()
ret_list = list()
for row in rows:
ret_list.append(row[0])
return ret_list
def get_hosts_state():
sql = """
SELECT a.host_object_id, b.current_state
FROM nagios_services a, nagios_servicestatus b
WHERE a.service_object_id = b.service_object_id
AND a.instance_id = b.instance_id
ORDER BY a.host_object_id, a.display_name ASC
"""
cursor = raw_sql(sql)
rows = cursor.fetchall()
return rows
def get_host_list():
"""
get host list from nagios database by raw sql.
"""
sql = """
SELECT host_object_id, display_name
FROM nagios_hosts;
"""
arr = list()
cursor = raw_sql(sql)
rows = cursor.fetchall()
for row in rows:
arr.append(row)
return arr
def get_user_list():
"""
get user list from nagios database by raw sql.
"""
sql = """
SELECT username
FROM users_account
"""
arr = list()
cursor = raw_sql(sql)
rows = cursor.fetchall()
for row in rows:
arr.append(row[0])
return arr
def get_host_service_list():
"""
get service list from nagios database by raw sql.
"""
arr = list()
hosts_list = get_host_list()
for k,v in hosts_list:
temp = dict()
temp[v] = list()
sql = """
SELECT display_name
FROM nagios_services
WHERE host_object_id=%s
""" % k
cursor = raw_sql(sql)
rows = cursor.fetchall()
for row in rows:
temp[v].append(row[0])
arr.append(temp)
return arr
def host_list(request):
host_type = request.REQUEST.get('host_type',None)
if not host_type:
sql = """
SELECT
a.host_object_id,a.display_name,a.address,
b.current_state,(select now()) as status_update_time,b.last_state_change,b.output
FROM nagios_hosts a, nagios_hoststatus b
WHERE a.host_object_id = b.host_object_id AND a.instance_id = b.instance_id
ORDER by a.display_name ASC
"""
elif host_type == "unhandled":
sql = """
SELECT
a.host_object_id,a.display_name,a.address,
b.current_state,(select now()) as status_update_time,b.last_state_change,b.output
FROM nagios_hosts a, nagios_hoststatus b
WHERE a.host_object_id = b.host_object_id AND a.instance_id = b.instance_id
AND b.current_state !=0
ORDER by a.display_name ASC
"""
cursor = raw_sql(sql)
rows = cursor.fetchall()
host_list = list()
for line in rows:
temp = dict()
temp['host_object_id'] = line[0]
temp['display_name'] = line[1]
temp['address'] = line[2]
temp['current_state'] = line[3]
duration = line[4] - line[5]
hms = time.gmtime(duration.seconds)
temp['state_duration'] = str(duration.days) + ' d ' + \
str(hms.tm_hour) + ' h ' + str(hms.tm_min) + ' m ' + str(hms.tm_sec) + ' s'
temp['output'] = line[6]
host_list.append(temp)
return render('host_list/main.html',{'host_list':host_list,'services_list':get_hosts_state()},request)
def service_list(request):
host = request.REQUEST.get('host',None)
service_type = request.REQUEST.get('service_type',None)
if host:
sql = """
SELECT host_object_id
FROM nagios_hosts
WHERE display_name = '%s'
""" % host
cursor = raw_sql(sql)
rows = cursor.fetchall()
try:
host_object_id = rows[0][0]
except:
return render('service_list/main.html',{'host_not_exist':True},request)
sql = """
SELECT
a.host_object_id, a.service_object_id, a.display_name,
(select now()) as status_update_time,b.last_state_change,b.current_state,b.output
FROM nagios_services a, nagios_servicestatus b
WHERE a.service_object_id = b.service_object_id
AND a.instance_id = b.instance_id
AND a.host_object_id = %d
ORDER BY a.host_object_id,a.display_name ASC
""" % host_object_id
cursor = raw_sql(sql)
rows = cursor.fetchall()
else:
if not service_type:
sql = """
SELECT
a.host_object_id, a.service_object_id, a.display_name,
(select now()) as status_update_time,b.last_state_change,b.current_state,b.output
FROM nagios_services a, nagios_servicestatus b
WHERE a.service_object_id = b.service_object_id
AND a.instance_id = b.instance_id
ORDER BY a.host_object_id,a.display_name ASC
"""
cursor = raw_sql(sql)
rows = cursor.fetchall()
elif service_type == "unhandled":
sql = """
SELECT
a.host_object_id, a.service_object_id, a.display_name,
(select now()) as status_update_time,b.last_state_change,b.current_state,b.output
FROM nagios_services a, nagios_servicestatus b
WHERE a.service_object_id = b.service_object_id
AND a.instance_id = b.instance_id
AND b.current_state !=0
ORDER BY a.host_object_id,a.display_name ASC
"""
cursor = raw_sql(sql)
rows = cursor.fetchall()
############### get unique host list ################
sql = """
SELECT host_object_id,display_name
FROM nagios_hosts
"""
cursor = raw_sql(sql)
rows_temp = cursor.fetchall()
unique_hostname_list = list()
for line in rows_temp:
unique_hostname_list.append({line[0]:line[1]})
def get_hostname_from_id(host_id):
for item in unique_hostname_list:
for k,v in item.items():
if host_id ==k:
return v
################## end get unique host list############
service_list = list()
for line in rows:
temp = dict()
temp['hostname'] = get_hostname_from_id(line[0])
temp['host_object_id'] = line[0]
temp['service_object_id'] = line[1]
temp['display_name'] = line[2]
duration = line[3] - line[4]
hms = time.gmtime(duration.seconds)
temp['state_duration'] = str(duration.days) + ' d ' + str(hms.tm_hour) + ' h ' + str(hms.tm_min) + ' m ' + str(hms.tm_sec) + ' s'
temp['current_state'] = line[5]
temp['output'] = line[6]
service_list.append(temp)
return render('service_list/main.html',{'hostname_list':unique_hostname_list,'service_list':service_list},request)
