def get_ip_from_db(request):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
sql = "SELECT DISTINCT IP FROM Info_DatabaseInfo"
infos = connection.select_sql(sql)
result = json.dumps(infos)
return HttpResponse(result)
def get_ldap_id(action_ip, ldap_account):
sql = "SELECT [id] FROM [dbo].[Info_Users] WHERE [LdapAccount] = '%s'" % ldap_account
c = dbconnecter.Connection(action_ip)
result = c.select_sql(sql)
c.__del__()
if result.__len__() == 0:
return []
else:
return result[0]
def get_database_with_id(action_ip, db_id):
sql = "SELECT [IP],[DbName] FROM [dbo].[Info_DatabaseInfo] where id = '%s'" % db_id
conn = dbconnecter.Connection(action_ip)
result = conn.select_sql(sql)[0]
result = {
'IP': result[0],
'DbName': result[1]
}
return result
def get_databases_with_ip(request, ip):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
sql = "select dbname from Info_DatabaseInfo where ip = '%s'" % ip
infos = connection.select_sql(sql)
result = json.dumps(infos)
return HttpResponse(result)
def get_account_with_ip(request, ip):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
sql = "select account from Info_DbAccount where ip = '%s'" % ip
infos = connection.select_sql(sql)
result = json.dumps(infos)
return HttpResponse(result)
def get_account_with_id(action_ip, acount_id):
sql = "SELECT [IP],[Account],[Password] FROM [dbo].[Info_DbAccount] where id = '%s'" % acount_id
conn = dbconnecter.Connection(action_ip)
result = conn.select_sql(sql)[0]
result = {
'IP': result[0],
'AccountName': result[1],
'hashPassword': result[2]
}
return result
def get_log(request, t):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
if t == 'account':
sql = "select * from Info_ToolsLog where actiontype in (2,4)"
info = connection.select_sql(sql)
logs = []
for i in info:
log = {
'action_ip': i[1],
'type': i[2],
'account': i[3],
'action_time': str(i[5])[:-7]
}
logs.append(log)
elif t == 'databases':
sql = "select * from Info_ToolsLog where actiontype in (1,3)"
info = connection.select_sql(sql)
logs = []
for i in info:
log = {
'action_ip': i[1],
'type': i[2],
'database': i[4],
'action_time': str(i[5])[:-7]
}
logs.append(log)
else:
sql = "select * from Info_ToolsLog where actiontype in (0,10)"
info = connection.select_sql(sql)
logs = []
for i in info:
log = {
'action_ip': i[1],
'type': i[2],
'account': i[3],
'database': i[4],
'action_time': str(i[5])[:-7]
}
logs.append(log)
logs = json.dumps(logs)
return HttpResponse(logs)
def get_accounts(request):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
sql = "select ip,account,info from Info_DbAccount"
infos = connection.select_sql(sql)
accounts = []
for i in infos:
account_info = {
'ip': i[0],
'account': i[1],
'info': i[2]
}
accounts.append(account_info)
result = json.dumps(accounts)
return HttpResponse(result)
def get_databases(request):
action_ip = db_reister.get_action_ip(request)
connection = dbconnecter.Connection(action_ip)
sql = "select ip,dbname,info from Info_DatabaseInfo"
infos = connection.select_sql(sql)
databases = []
for i in infos:
database_info = {
'ip': i[0],
'dbname': i[1],
'info': i[2]
}
databases.append(database_info)
result = json.dumps(databases)
return HttpResponse(result)
def init_exec_plan(action_ip, ip, db_name, account, path, owner_id):
print u"初始化执行计划"
# 创建链接
conn = dbconnecter.Connection(action_ip)
# 获取账号id
account_id = get_account_id(action_ip, account, ip)
# 获取数据库id
db_id = get_db_id(action_ip, db_name, ip)
# 查询计划是否存在
sql = "SELECT [IsDone] FROM [dbo].[Exec_PlanInfo] WHERE dbid = '%s' AND path = '%s'" % (db_id, path)
result = conn.select_sql(sql)
# 不存在，则创建任务
if result.__len__() == 0:
sql = "INSERT INTO " \
"[dbo].[Exec_PlanInfo]([IP],[DbID],[AccountID],[Path],[StartTime],[EndTime],[IsDone],[UserID])" \
"VALUES ('%s','%d','%d','%s',getdate(),'',0,'%d')" % (ip, db_id, account_id, path, owner_id)
conn.insert_sql(sql)
else:
print u'已存在'
pass
def get_plan_info(request):
user_id = request.session['user_id']
action_ip = dbregister_views.get_action_ip(request)
conn = dbconnecter.Connection(action_ip)
sql = "SELECT top 25 [IP],[DbID],[AccountID],[StartTime],[EndTime],[IsDone],[ID] FROM [dbo].[Exec_PlanInfo] " \
"where [UserID] = %d order by IsDone ,StartTime desc,EndTime DESC" % user_id
result = list(conn.select_sql(sql))
column = ['IP', 'DbID', 'AccountID', 'StartTime', 'EndTime', 'IsDone', 'ID']
plan_info = []
for i in result:
i = list(i)
i[1] = get_database_with_id(action_ip, i[1])['DbName']
i[2] = get_account_with_id(action_ip, i[2])['AccountName']
i[3], i[4] = str(i[3])[:-7], str(i[4])[:-7]
if not i[5]:
i[5] = u"未完成"
else:
i[5] = u"执行成功"
plan_info.append(dict(zip(column, i)))
return HttpResponse(json.dumps(plan_info))
def register(request):
if request.method == 'POST':
action_ip = get_action_ip(request)
print action_ip
connection = dbconnecter.Connection(action_ip)
if request.POST.has_key('account'):
account = request.POST['account']
password = request.POST['password']
ip = request.POST['ip']
account_info = request.POST['accountInfo']
hash_password = dbconnecter.password_encoding(password)
if connection.account_validate(ip, account):
connection.create_user(ip, account, hash_password,
account_info)
return render(request, 'DbRegister/Base.html')
else:
return HttpResponse('账号已经存在')
else:
ip = request.POST['ip']
db_name = request.POST['dbName']
db_info = request.POST['dbinfo']
if connection.database_validate(ip, db_name):
connection.create_database(ip, db_name, db_info)
return HttpResponseRedirect('/welcome')
else:
return HttpResponse('数据库已经存在')
else:
return HttpResponseRedirect('/welcome')
def exec_exec_plan(action_ip, ip, db_name, account, path):
print u"开始执行.."
# 获取数据库id
db_id = get_db_id(action_ip, db_name, ip)
# 获取账号id
account_id = get_account_id(action_ip, account, ip)
account_info = get_account_with_id(action_ip, account_id)
# 通过账号ID获取解密密码
password = dbconnecter.password_decoding(account_info['hashPassword'])
# 从配置文件中获取PowerShell路径
sh_path = config.Configer().get_shell_path()
# 创建链接
conn = dbconnecter.Connection(action_ip)
# 查找执行计划
sql = "SELECT [IsDone] FROM [dbo].[Exec_PlanInfo] WHERE ip = '%s' and dbid = %d and path = '%s'" \
% (ip, db_id, path)
# 检查执行状态
result = conn.select_sql(sql)[0][0]
if not result:
# 调用PowerShell执行脚本
args = [r"powershell", sh_path, db_name, ip, path, account, password]
try:
p = subprocess.Popen(args, stdout=subprocess.PIPE)
dt = p.stdout.read()
print dt
pattern = r'.*error:.*'
if not re.match(pattern, dt):
# 修改执行状态
sql = "UPDATE [dbo].[Exec_PlanInfo] SET [IsDone] = 1 , [EndTime] = getdate() " \
"WHERE ip = '%s' and dbid = %d and path = '%s'" % (ip, db_id, path)
conn.insert_sql(sql)
except Exception, e:
print e
return u"执行失败"
def insert_login(action_ip, ldap_account):
sql = "INSERT INTO [dbo].[Info_Users]([LdapAccount],[LastLoginIp],[LastLoginTime])" \
"VALUES('%s','%s',GETDATE())" % (ldap_account, action_ip)
c = dbconnecter.Connection(action_ip)
c.insert_sql(sql)
c.__del__()
def update_login_time(action_ip, ldap_account):
sql = "update [dbo].[Info_Users] set [LastLoginIp] = '%s', [LastLoginTime] = getdate() where [LdapAccount] = '%s'" \
% (action_ip, ldap_account)
c = dbconnecter.Connection(action_ip)
c.insert_sql(sql)
c.__del__()
def get_account_id(action_ip, account, ip):
conn = dbconnecter.Connection(action_ip)
sql = "SELECT [ID] FROM [dbo].[Info_DbAccount] WHERE account = '%s' and IP = '%s' and isActive = 0" % (account, ip)
result = conn.select_sql(sql)
return result[0][0]
def get_db_id(action_ip, dbname, ip):
conn = dbconnecter.Connection(action_ip)
sql = "SELECT [ID] FROM [dbo].[Info_DatabaseInfo] WHERE Dbname = '%s' and IP = '%s' and isActive = 0" % (dbname, ip)
result = conn.select_sql(sql)
return result[0][0]