def jsonlink(self):
from src_script.MySQL import Database
db = Database()
select_catid = """SELECT * FROM splunk
LEFT JOIN tts ON (splunk.cat_id = tts.cat_id)
LEFT join online_active ON (splunk.cat_id = online_active.catid)
WHERE host LIKE '10.5.0%' GROUP BY src_interface, host, hostname, path
ORDER BY ticketNo desc
"""
lst_catid = db.query(select_catid)
json_data = []
''' ticketNo, cat_id, src_interface, host, port_status, path, flap, problem_status, affected_item'''
for r in lst_catid:
if r['problem_status'] is None:
r['problem_status'] = ''
if r['affected_item'] is None:
r['affected_item'] = ''
tmp = """[ "{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}", "{9}", "{10}", "{11}" ] """.format(
r['ticketNo'], r['cat_id'], r['src_interface'], r['host'],
r['port_status'], r['path'], r['flap'], r['problem_status'],
r['affected_item'], r['device_time'], r['hostname'],
r['timestamp'])
json_data.append(json.loads(tmp))
print json.dumps(json_data)
def link(self):
from src_script.MySQL import Database
db = Database()
select_catid = """SELECT * FROM splunk
LEFT JOIN (SELECT * FROM tts ORDER BY ticketNo DESC) AS tts ON (splunk.cat_id = tts.cat_id)
LEFT join online_active ON (splunk.cat_id = online_active.catid)
WHERE host LIKE '10.5.0%'
GROUP BY src_interface, host, hostname, path
"""
lst_catid = db.query(select_catid)
for l in lst_catid:
print '<tr>'
if l['incident_id'] is not None:
print ' <td class="col-lg-1"><a href="activity.py?TicketNo={0}">{0}</a></td>'.format(
l['ticketNo'])
else:
print ' <td class="col-lg-1"></td>'
if len(l['cat_id']) < 12:
print ' <td class="col-lg-1">{0}</td>'.format(
l['cat_id'])
else:
print ' <td class="col-lg-1"></td>'
print ' <td class="col-lg-1">{0}</td>'.format(
l['src_interface'])
print ' <td class="col-lg-1 text-center"><div data-toggle="tooltip" data-placement="bottom" title="{1}">{0}</div></td>'.format(
l['host'], l['hostname'])
# print '<td class="col-lg-1">{0}</td>'.format(l['hostname'])
# print '<td class="col-lg-1">{0}</td>'.format(l['device_time'])
if (l['port_status'] == 'down' or l['port_status'] == 'Down'):
print ' <td class="col-lg-1 port_status_down text-center"><div data-toggle="tooltip" data-placement="bottom" title="{1}">{0}</div></td>'.format(
l['port_status'], l['device_time'])
else:
print ' <td class="col-lg-1 port_status_up text-center"><div data-toggle="tooltip" data-placement="bottom" title="{1}">{0}</div></td>'.format(
l['port_status'], l['device_time'])
print ' <td class="col-lg-1">{0}</td>'.format(l['path'])
print ' <td class="col-lg-1">{0}</td>'.format(l['flap'])
if l['problem_status'] is not None:
print ' <td class="col-lg-1">{0}</td>'.format(
l['problem_status'])
else:
print ' <td class="col-lg-1"></td>'
print ' <td class="col-lg-1">{0}</td>'.format(
l['affected_item'])
if l['port_status'] == 'down' or l['port_status'] == 'Down' and (
l['problem_status'] == 'Closed'
or l['problem_status'] is None):
if l['timestamp'] is not None:
t = datetime.datetime.strptime(str(l['timestamp']),
'%Y-%m-%d %H:%M:%S')
timediff = abs((time.mktime(t.timetuple()) + 3600) -
time.mktime(
datetime.datetime.now(tz=pytz.timezone(
'Asia/Bangkok')).timetuple()))
if timediff < 120:
status = 'disabled'
else:
status = ''
else:
status = ''
print ' <td class="col-lg-1"><a href="openticket.py?cat_id={0}"><input type="button" class="btn btn-default" value="open ticket" {1}></a></td>'.format(
l['cat_id'], status)
else:
print ' <td class="col-lg-1"></td>'
print '</tr>'
def open_ticket():
import datetime
import pytz
import cgi
from src_script.MySQL import Database
form = cgi.FieldStorage()
db = Database()
query_detail = """SELECT * FROM tts WHERE cat_id = '{0}'""".format(form["cat_id"].value)
query_splunk = """SELECT * FROM splunk WHERE cat_id = '{0}'""".format(form["cat_id"].value)
lst_detail = db.query(query_detail)
lst_splunk = db.query(query_splunk)
lst_splunk=lst_splunk[0]
if len(lst_detail) > 0:
lst_detail = lst_detail[0]
else:
lst_detail = collections.OrderedDict()
lst_detail['ticketNo'] = None
lst_detail['incident_id'] = None
lst_detail['affected_item'] = None
lst_detail['cat_id'] = None
lst_detail['status'] = None
lst_detail['problem_status'] = None
lst_detail['downtime_start'] = None
lst_detail['downtime_time'] = None
lst_detail['owner_group'] = None
lst_detail['repairteam'] = None
lst_detail['oss_source'] = None
lst_detail['oss_destination'] = None
lst_detail['address'] = None
lst_detail['title'] = None
lst_detail['description'] = None
lst_detail['activity'] = None
lst_detail['bandwidth'] = None
print '<input type=hidden id=CatID name=CatID value="{}">'.format(form["cat_id"].value)
print '<div class="box">'
print '<div class="box-form">'
print '<div class="box-header">'
print '<h2> open ticket </h2>'
print '</div>'
print '<div class="box-body">'
# action="/cgi-enabled/checkvalue.py" method="post" name="ticket_form"
print '<form id="form_openticket">'
print '<div class="column col-lg-5">'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right" for="status"> Status </label>'
print '<div class="col-sm-9">'
print '<input type="text" readonly class="form-control" id="status" name="status" value="open">'
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right" for="catid"> CAT ID <font color="red">*</font></label>'
print '<div class="col-sm-9">'
print '<input type="text" readonly class="form-control" id="catid" name="catid" value="{0}">'.format(form.getvalue('cat_id'))
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right" for="catid"> Hostname </label>'
print '<div class="col-sm-9">'
print '<input type="text" readonly class="form-control" id="hostname" name="hostname" value="{0}">'.format(
lst_splunk['hostname'])
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right" for="catid"> IP</label>'
print '<div class="col-sm-9">'
print '<input type="text" readonly class="form-control" id="ip" name="ip" value="{0}">'.format(
lst_splunk['host'])
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right" for="catid"> Source Interface </label>'
print '<div class="col-sm-9">'
print '<input type="text" readonly class="form-control" id="interface" name="interface" value="{0}">'.format(
lst_splunk['src_interface'])
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right"> Name <font color="red">*</font></label>'
print '<div class="col-sm-9">'
print '<input type="text" class="form-control" id="source" name="source" value="{0}" readonly>'.format(
lst_detail['oss_source'])
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right"> Destination</label>'
print '<div class="col-sm-9">'
print '<input type="text" class="form-control" id="destination" name="destination" value="{0}" readonly>'.format(
lst_detail['oss_destination'])
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right"> Address </label>'
print '<div class="col-sm-9">'
print '<textarea type="text" class="form-control" id="address" name="address" rows="5" readonly>{0}</textarea>'.format(
lst_detail['address'])
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right"> Bandwidth <font color="red">*</font> </label>'
print '<div class="col-sm-9">'
print '<input type="text" class="form-control" id="bandwidth" name="bandwidth" value="{0}" readonly>'.format(
lst_detail['bandwidth'])
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right"> Fault Time <font color="red">*</font></label>'
print '<div class="col-sm-9">'
print '<input type="text" class="form-control" id="faulttime" name="faulttime" value="{0}" readonly>'.format(
datetime.datetime.now(tz=pytz.timezone('Asia/Bangkok')).strftime('%d/%m/%Y %H:%M:%S'))
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-3 text-right"> Owner Group <font color="red">*</font></label>'
print '<div class="col-sm-9">'
print '<input type="text" class="form-control" id="owner.group" name="owner.group" value="{0}" readonly>'.format(
lst_detail['owner_group'])
print '</div>'
print '</div>'
print '</div>'
print '<div class="column col-lg-7">'
print '<div class="form-group row">'
print '<label class="control-label col-sm-2 text-right"> Title <font color="red">*</font></label>'
print '<div class="col-sm-10">'
print """
<select class="form-control" name="title">
<option value="Down">Down</option>
<option value="Up-Down/Bouncing/Flapping/TimeOut/Drop/Loss">Up-Down/Bouncing/Flapping/TimeOut/Drop/Loss</option>
<option value="Error">Error</option>
<option value="ช้า/Delay/Low Speed/ใช้งานได้ไม่เต็ม Bandwidth">ช้า/Delay/Low Speed/ใช้งานได้ไม่เต็ม Bandwidth</option>
<option value="ลูกค้าขอเช็ค Alarm<">ลูกค้าขอเช็ค Alarm</option>
<option value="ลูกค้าขอ Monitor">ลูกค้าขอ Monitor</option>
<option value="ลูกค้าขอ Test">ลูกค้าขอ Test</option>
<option value="SmartSignOn ใช้งานไม่ได้">SmartSignOn ใช้งานไม่ได้</option>
<option value="C internet Login ไม่ได้">C internet Login ไม่ได้</option>
<option value="C internet เปิดเว็บไม่ได้">C internet เปิดเว็บไม่ได้</option>'
<option value="C internet รับ-ส่ง Email Outlook ไม่ได้">C internet รับ-ส่ง Email Outlook ไม่ได้</option>
<option value="C internet ค่า Ping Time สูง">C internet ค่า Ping Time สูง</option>
<option value="Config อุปกรณ์ไม่ได้">Config อุปกรณ์ไม่ได้</option>
<option value="ทำ VPN ไม่ได้">ทำ VPN ไม่ได้</option>
<option value="ทำ Load balance ไม่ได้">ทำ Load balance ไม่ได้</option>
<option value="ภัยพิบัติ">ภัยพิบัติ</option>
<option value="Phishing">Phishing</option>
<option value="Attack">Attack</option>
<option value="IP Phishing">IP Phishing</option>
<option value="IP Attack">IP Attack</option>
<option value="IP Hacking">IP Hacking</option>
<option value="IP RBL">IP RBL</option>
<option value="High Latency">High Latency</option>
<option value="สาเหตุอื่นๆ">สาเหตุอื่นๆ</option>
<option value="Down 3G">Down 3G</option>
<option value="Down 3G/SA1">Down 3G/SA1</option>
<option value="Down 3G/SA2">Down 3G/SA2</option>
<option value="Down 3G/SA3">Down 3G/SA3</option>
<option value="Down 3G/SA4">Down 3G/SA4</option>
<option value="Down 3G/NSA1">Down 3G/NSA1</option>
<option value="Down 3G/NSA2">Down 3G/NSA2</option>
<option value="Down 3G/NSA3">Down 3G/NSA3</option>
<option value="Down 3G/NSA4">Down 3G/NSA4</option>
<option value="Help check: Link status (Up/Down)">Help check: Link status (Up/Down)</option>
<option value="Help check: Power of equipment status (On/Off)">Help check: Power of equipment status (On/Off)</option>
<option value="Help check: Power system down">Help check: Power system down</option>
</select>
"""
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-2 text-right"> Description <font color="red">*</font></label>'
print '<div class="col-sm-10">'
print """<textarea type="text" class="form-control" id="description" name="description" rows="10">พบ Link_Down
ข้อมูลต้นทาง
Nodename :{0}
IP :{1}
Interface :{2}
Location :
TX power :
RX power:
Log :
ข้อมูลปลายทาง
Nodename :
IP :
Interface :
Location :
TX power :
RX power:
Log :</textarea>""".format(lst_splunk['hostname'], lst_splunk['host'], lst_splunk['src_interface'])
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '<label class="control-label col-sm-2 text-right"> comment </label>'
print '<div class="col-sm-10">'
print '<textarea type="text" class="form-control" id="comment" name="comment" rows="5"></textarea>'
print '</div>'
print '</div>'
print '<div class="form-group row">'
print '''
<div class="pull-right">
<div class="col-lg-6">
<a class="btn btn-default pull-right" href="index.py">Cancel</a>
</div>
<div class="col-lg-6">
<button class="btn btn-default pull-right" type="submit">Submit</button>
</div>
</div>'''
print '</div>'
print '</div>'
print '</div>'
print '</form>'
print '</div>'
print '</div>'
print '</div>'
def index():
import cgi
import json
import random
from src_script.MySQL import Database
form = cgi.FieldStorage()
db = Database()
select_catid = """SELECT * FROM tts WHERE ticketNo = '{0}' LIMIT 1""".format(
form["TicketNo"].value)
lst_catid = db.query(select_catid)
if 'TicketNo' not in form or len(lst_catid) == 0:
t.redirect()
else:
lst_catid = lst_catid[0]
activity = json.loads(lst_catid['activity'])
print '<div class="box">'
print '<div class="box-body container">'
print '<div class="col-lg-6">'
print """
<div class="row form-group">
<div class="col-lg-3">Ticket No:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['ticketNo'])
print """
<div class="row form-group">
<div class="col-lg-3">Incident id:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['incident_id'])
print """
<div class="row form-group">
<div class="col-lg-3">Cat id:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['cat_id'])
print """
<div class="row form-group">
<div class="col-lg-3">Affected item:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['affected_item'])
print """
<div class="row form-group">
<div class="col-lg-3">Status:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['status'])
print """
<div class="row form-group">
<div class="col-lg-3">Problem status:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['problem_status'])
print """
<div class="row form-group">
<div class="col-lg-3">Address:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['address'])
print '</div>'
print '<div class="col-lg-6">'
print """
<div class="row form-group">
<div class="col-lg-3">Downtime start:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['downtime_start'])
print """
<div class="row form-group">
<div class="col-lg-3">Downtime:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['downtime_time'])
print """
<div class="row form-group">
<div class="col-lg-3">Owner group:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['owner_group'])
print """
<div class="row form-group">
<div class="col-lg-3">Repairteam:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['repairteam'])
print """
<div class="row form-group">
<div class="col-lg-3">Source:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['oss_source'])
print """
<div class="row form-group">
<div class="col-lg-3">Destination:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['oss_destination'])
print """
<div class="row form-group">
<div class="col-lg-3">Bandwidth:</div>
<div class="col-lg-9">{0}</div>
</div>
""".format(lst_catid['bandwidth'])
print '</div>'
# INFORMATION LINK
print '''
<div class="col-lg-12">
<div class="row form-group">
<div class="col-lg-2" >Title:</div>
<div class="col-lg-10"><input type="text" class="form-control" value="{}" readonly></div>
</div>
<div class="row form-group">
<div class="col-lg-2" >Description:</div>
<div class="col-lg-10"><textarea class="form-control" rows="20" readonly >{}</textarea></div>
</div>
</div>
'''.format(lst_catid['title'], lst_catid['description'])
# TABLE ACTIVITY
print '<div class="col-lg-12">'
print """
<div class="row form-group">
<div class="col-lg-2"> Activities:</div>
<div class="col-lg-10 table-responsive">
<table class="table table-bordered table-striped">
<thead>
<tr class="Bg-head-table">
<th class="col-lg-1 text-center"> Number </th>
<th class="col-lg-2 text-center"> Datetime </th>
<th class="col-lg-2 text-center"> Operator </th>
<th class="col-lg-2 text-center"> Division </th>
<th class="col-lg-3 text-center"> Description </th>
<th class="col-lg-1 text-center"> Type </th>
</tr>
</thead>
<tbody>
"""
for i in activity:
print '<tr>'
print '<td class="text-center" >{0}</td>'.format(i['number'])
print '<td class="text-center" >{0}</td>'.format(i['datestamp'])
print "<td>{0}</td>".format(i['operator'].encode('utf-8'))
print "<td>{0}</td>".format(i['division'].encode('utf-8'))
print "<td>{0}</td>".format(i['description'].encode('utf-8'))
print "<td>{0}</td>".format(i['type'])
print "</tr>"
print """
</tbody>
</table>
</div>
</div>
"""
print "</div>"
###############################
print "</div>"
print "</div>"
# <!-- SlimScroll -->
print '<script src="/cgi-enabled/AdminLTE-2.4.2/bower_components/jquery-slimscroll/jquery.slimscroll.min.js"></script>'
print "</body>"
print "</html>"
if __name__ == "__main__":
print_header()
from src_script.MySQL import Database
db = Database(host='localhost',
username='******',
password='',
db='alarm_ticket')
select_catid = """SELECT * FROM splunk LEFT JOIN tts ON (splunk.cat_id LIKE 'tts.cat_id%')"""
lst_catid = db.query(select_catid)
print '<pre>'
print lst_catid[0]
print '</pre>'
print '<nav class="navbar-default">'
print '<div class="container-fluid">'
print '<div class="navbar-header">'
print '<a class="navbar-brand" href="/cgi-enabled/index.py">Alarm Ticket</a>'
print "</div>"
print '<ul class="nav navbar-nav">'
# print '<li class="active"><a href="/home">Home</a></li>'
# print '<li><a href="/splunk">Splunk</a></li>'
print "</ul>"
print "</div>"
print "</nav>"
def index():
from src_script.MySQL import Database
db = Database(host='localhost',
username='******',
password='',
db='alarm_ticket')
select_catid = """SELECT * FROM splunk LEFT JOIN tts ON (splunk.cat_id LIKE tts.cat_id)"""
lst_catid = db.query(select_catid)
print '<nav class="navbar-default">'
print '<div class="container-fluid">'
print '<div class="navbar-header">'
print '<a class="navbar-brand" href="/cgi-enabled/index.py">Alarm Ticket</a>'
print "</div>"
print '<ul class="nav navbar-nav">'
# print '<li class="active"><a href="/home">Home</a></li>'
# print '<li><a href="/splunk">Splunk</a></li>'
print "</ul>"
print "</div>"
print "</nav>"
print '<div class="box">'
print '<div class="box-body ">'
print '<div class="table-responsive">'
print '<table class="table table-bordered table-striped" id="alarmtickets">'
print "<thead>"
print '<tr class="Bg-head-table">'
print '<th class="col-lg-1">Ticket No.</th>'
print '<th class="col-lg-1">Cat ID</th>'
print '<th class="col-lg-1">Source Interface</th>'
print '<th class="col-lg-1">Host</th>'
print '<th class="col-lg-1">Hostname</th>'
print '<th class="col-lg-1">device_time</th>'
print '<th class="col-lg-1 text-center">Port Status</th>'
print '<th class="col-lg-1">Path</th>'
print '<th class="col-lg-1">flap</th>'
print '<th class="col-lg-1">Ticket Status</th>'
print '<th class="col-lg-1">Affected Service</th>'
print '<th class="col-lg-1"></th>'
print "</tr>"
print "</thead>"
print "<tbody>"
for l in lst_catid:
print '<tr class="Bg-head-table">'
if l['incident_id'] is not None:
print '<td class="col-lg-1"><a href="/TTS/{0}">{0}</a></td>'.format(
l['ticketNo'])
else:
print '<td class="col-lg-1"></td>'
if len(l['cat_id']) < 12:
print '<td class="col-lg-1">{0}</td>'.format(l['cat_id'])
else:
print '<td class="col-lg-1"></td>'
print '<td class="col-lg-1">{0}</td>'.format(l['src_interface'])
print '<td class="col-lg-1">{0}</td>'.format(l['host'])
print '<td class="col-lg-1">{0}</td>'.format(l['hostname'])
print '<td class="col-lg-1">{0}</td>'.format(l['device_time'])
if l['port_status'] == 'Down':
css_portstatus = 'danger'
else:
css_portstatus = 'success'
print '<td class="col-lg-1 text-center {1}">{0}</td>'.format(
l['port_status'], css_portstatus)
print '<td class="col-lg-1">{0}</td>'.format(
l['path'].split('_[')[1].split(']')[0])
print '<td class="col-lg-1">{0}</td>'.format(l['flap'])
if l['status'] is not None:
print '<td class="col-lg-1">{0}</td>'.format(l['problem_status'])
else:
print '<td class="col-lg-1"></td>'
print '<td class="col-lg-1">{0}</td>'.format(l['affected_item'])
print '<td class="col-lg-1"></td>'
print "</tr>"
print "</tbody>"
print "</table>"
print "</div>"
print "</div>"
print "</div>"