def count_ip_total(subnet_ids=None):
"""
:param subnet_ids:
:return: counts
"""
params = []
result = {}
db = dbpools.get_pool(dbpools.NEUTRON_DB)
try:
sql = "select subnet_id, first_ip,last_ip from ipallocationpools where 1=1 "
if subnet_ids:
sql += " and subnet_id in %s "
params.append(tuple(subnet_ids))
cur = yield db.execute(sql, params)
ippool_all = cur.fetchall()
for item in ippool_all:
ip_start = int(netaddr.IPAddress(item["first_ip"]))
ip_end = int(netaddr.IPAddress(item["last_ip"]))
ip_total = ip_end - ip_start + 1
if item["subnet_id"] in result:
result[item["subnet_id"]] += ip_total
else:
result[item["subnet_id"]] = ip_total
except Exception, e:
raise e
def get_securitygrouprules_by_db(rule_id=None, rule_info=None, tenant_id=None):
"""get securitygroups from database"""
try:
db = dbpools.get_pool(dbpools.NEUTRON_DB)
sql = "select sgr.tenant_id, sgr.id, sgr.security_group_id, sgr.remote_group_id, " \
"sgr.direction, sgr.ethertype, sgr.protocol, sgr.port_range_min, " \
"sgr.port_range_max, sgr.remote_ip_prefix " \
"from securitygroups sg, securitygrouprules sgr " \
"where sgr.security_group_id = sg.id and sg.name = 'default'"
if tenant_id:
sql += " and sg.tenant_id='%s' " % tenant_id
if rule_id:
sql += " and sgr.id='%s' " % rule_id
if rule_info:
if rule_info.get("direction"):
sql += " and sgr.direction='%s' " % rule_info.get("direction")
if rule_info.get("cidr"):
sql += " and sgr.remote_ip_prefix='%s' " % rule_info.get(
"cidr")
if rule_info.get("cidr"):
sql += " and sgr.protocol='%s' " % rule_info.get("protocol")
if rule_info.get("to_port"):
sql += " and sgr.port_range_max='%s' " % rule_info.get(
"to_port")
if rule_info.get("from_port"):
sql += " and port_range_min='%s' " % rule_info.get("from_port")
if rule_info.get("from_port"):
sql += " and sgr.ethertype='%s' " % rule_info.get("ethertype")
cur = yield db.execute(sql)
all_data = cur.fetchall()
except Exception as e:
LOG.error("get securitygrouprules error %s" % e)
raise SecGroupRuleOperationFailed()
raise gen.Return(all_data)
def get_resource(woid):
"""
"""
db = dbpools.get_pool(dbpools.COMMON_DB)
sql = "select * from resource where wo_id = %s"
resources = []
cur = yield db.execute(sql, [woid])
if not cur:
raise WorkOrderIdNotFoundException
rs = cur.fetchall()
for r in rs:
res = dict()
res["name"] = r["name"]
res["displayname"] = r["displayname"]
res["status"] = r["status"]
res["id"] = r["id"]
res["type"] = r["type"]
res["uuid"] = r["uuid"]
res["remark"] = r["remark"]
pros = yield pro.get_pros(db, r["id"])
for p in pros:
if p['name'] in __VM_INT_PRO:
res[p['name']] = int(p['value'])
elif p['name'] in __VM_DICT_PRO:
res[p['name']] = json.loads(p['value'])
else:
res[p['name']] = p['value']
resources.append(res)
raise gen.Return(resources)
def query_subnet_tenant_all(subnet_id=None, tenant_id=None):
""" query ips of vlan
:param subnet_id: vlan id
:param tenant_id: tenant id
:return: vlan ips
"""
db = dbpools.get_pool(dbpools.LOCAL_DB)
try:
if tenant_id and subnet_id:
sql = "select * from vlan_subnet_tenant where subnet_id = '%s' and tenant_id = '%s'" % (
subnet_id, tenant_id)
elif not tenant_id and subnet_id:
sql = "select * from vlan_subnet_tenant where subnet_id = '%s'" % subnet_id
elif tenant_id and not subnet_id:
sql = "select * from vlan_subnet_tenant where tenant_id = '%s'" % tenant_id
else:
sql = "select * from vlan_subnet_tenant"
cur = yield db.execute(sql)
ips = cur.fetchall()
if not ips:
ips = []
except Exception as e:
LOG.error(e)
raise e
raise gen.Return(ips)
def get_subnet_db(network_id=None, subnet_ids=None, name=None, cidr=None):
"""
Get network info with sub_net info from neutron-network
:param subnet_ids:
:param network_id: network id list
:param name: network name
"""
try:
if subnet_ids and isinstance(subnet_ids, basestring):
subnet_ids = [subnet_ids]
params = []
db = dbpools.get_pool(dbpools.NEUTRON_DB)
sql = "select a.id, a.network_id, a.`name`, a.cidr, a.gateway_ip as gateway ,b.`name` as network_name from subnets as a left join networks as b on a.network_id = b.id where 1=1"
if subnet_ids:
sql += " and a.id in %s"
params.append(tuple(subnet_ids))
if cidr:
sql += " and a.cidr like %s "
cidr = cidr[:cidr.index("/")] + "%"
params.append(cidr)
if name:
sql += " and a.name = %s"
params.append(name)
if network_id:
sql += " and a.network_id = %s"
params.append(network_id)
cur = yield db.execute(sql, params)
networks = cur.fetchall()
except Exception, e:
LOG.error("Get network info with sub_net info error: %s" % e)
raise e
def count_vlan_ip_used(subnet_ids=None, dhcp=False):
""" Query used ips count of vlan
:param subnet_ids: subnet_ids
:return:
"""
params = []
result = {}
db = dbpools.get_pool(dbpools.NEUTRON_DB)
try:
sql = "select ipl.subnet_id, count(*) as total from ipallocations ipl " \
"left join ports p on p.id = ipl.port_id " \
"where 1=1 "
if subnet_ids:
sql += "and subnet_id in %s "
params.append(tuple(subnet_ids))
if dhcp:
sql += "and p.device_owner='network:dhcp' "
sql += "group by subnet_id "
cur = yield db.execute(sql, params)
ips = cur.fetchall()
for item in ips:
result[item["subnet_id"]] = item["total"]
except Exception, e:
LOG.error("count vlan ips used error: %s" % e)
raise e
def query_subnet_tenants(subnet_ids):
"""
Query tenants of subnet
:param subnet_ids: str or list
:return:
[{
"tenant_id":"xxx",
"subnet_id":"xxx"
},{
"tenant_id":"xxx",
"subnet_id":"xxx"
},...]
"""
subnets = []
if isinstance(subnet_ids, str):
subnets = [subnet_ids]
try:
db = dbpools.get_pool(dbpools.LOCAL_DB)
params = []
sql = "select tenant_id, subnet_id from vlan_subnet_tenant where 1=1"
if subnets:
sql += " and subnet_id in %s"
params.append(tuple(subnets))
cur = yield db.execute(sql, params)
tenants = cur.fetchall()
except Exception, e:
LOG.error("=====ERROR= query_subnet_tenants query error: %s" % e)
raise e
def __update_vm_pros(res, rs):
LOG.debug("__update_vm_pros")
db = dbpools.get_pool(dbpools.COMMON_DB)
try:
tx = yield db.begin()
if not str(res['host']):
yield pro.set_pro(tx, res['id'], 'host', str(rs['host']['name']))
if not str(res['sys_volume']):
yield pro.set_pro(tx, res['id'], 'sys_volume',
str(rs['metadata']['sys_volume']['type']))
network = list()
for n in res['network']:
nw = dict()
nw['vlan'] = {
"id": str(n['vlan']['id']),
"name": str(n['vlan']['name'])
}
if n["ip"]:
nw["ip"] = str(n["ip"])
else:
nw["ip"] = str(rs['network'][str(n['vlan']['name'])][0])
network.append(nw)
yield pro.set_pro(tx, res['id'], 'network', json.dumps(network))
yield tx.commit()
LOG.debug("__update_vm_pros: success")
except Exception, e:
LOG.debug("__update_vm_pros: failed")
yield tx.rollback()
raise e
def get_time(target, msg):
db = dbpools.get_pool(dbpools.LOCAL_DB)
cur = yield db.execute(
"select id, create_at, update_at from alarm where target = %s and message = %s",
(target, msg))
rs = cur.fetchone()
raise gen.Return(rs)
def servers_metadata(vm_ids):
"""
:param vm_ids: list
:return:
"""
if isinstance(vm_ids, basestring):
vm_ids = [vm_ids]
if not vm_ids:
raise gen.Return([])
params = []
try:
db = dbpools.get_pool(dbpools.NOVA_DB)
sql = "SELECT im.instance_uuid as server_id, im.key as meta_key, im.value as meta_value " \
"FROM instance_metadata im WHERE deleted=0 and im.instance_uuid "
if isinstance(vm_ids, basestring):
sql += " =%s "
params.append(vm_ids)
else:
sql += " in %s "
params.append(tuple(vm_ids))
cur = yield db.execute(sql, params)
metadatas = cur.fetchall()
except Exception, e:
LOG.error("instance metadata error: %s" % e)
raise e
def get_network_db(network_ids=None, network_name=None):
"""
Get networks info from neutron-network
:param network_ids: network id list
:param network_name: network name
:return:
[{
"network_id":"xxx",
"network_name":"xxx",
"vlan_id":"xxx",
},...]
"""
try:
if network_ids and isinstance(network_ids, basestring):
network_ids = [network_ids]
params = []
db = dbpools.get_pool(dbpools.NEUTRON_DB)
sql = "SELECT n.id as network_id, n.name as network_name, n.`status`, " \
"ml.segmentation_id as vlan_id,ml.network_type as vlan_type, ml.physical_network as physical_network " \
"FROM networks n left join ml2_network_segments ml on n.id = ml.network_id "
sql += " where 1=1 "
if network_ids:
sql += " and n.id in %s"
params.append(tuple(network_ids))
if network_name:
sql += " and n.name = %s"
params.append(network_name)
cur = yield db.execute(sql, params)
networks = cur.fetchall()
except Exception, e:
LOG.error("Get network info error: %s" % e)
raise e
def add_network_hosts(network_id, network_name, hosts):
""" add hosts to vlan
:param network_id：id of network
:param network_name: Network={"id":"uuid","name":"vlan140","hosts":[host1,host2]}
:param hosts: the hosts assign to network
"""
vlan_hosts = {
"vlan_id": network_id,
"vlan_name": network_name,
"hosts": hosts
}
try:
if vlan_hosts and isinstance(vlan_hosts, dict):
hosts = vlan_hosts["hosts"]
new_hosts = [(str(uuid4()), vlan_hosts["vlan_id"],
vlan_hosts["vlan_name"], host) for host in hosts
if host]
for new_host in new_hosts:
yield dbpools.execute_commit(
dbpools.get_pool(dbpools.LOCAL_DB),
sql=
"insert into vlan_hosts (id, vlan_id, vlan_name, host_id) VALUES (%s,%s,%s,%s)",
param=new_host)
except Exception, e:
LOG.error("insert vlan hosts error: %s" % e)
raise e
def delete_instance_info(vm_id):
try:
db = dbpools.get_pool(dbpools.NOVA_DB)
sql = "update instances set deleted=1, updated_at=utc_timestamp(), deleted_at=utc_timestamp(), vm_state='deleted' where uuid=%s"
yield dbpools.update(db, sql, [vm_id])
except Exception, e:
LOG.error("delete server error: %s" % e)
raise ServerOperationFailed()
def list_simple_network():
try:
db = dbpools.get_pool(dbpools.NEUTRON_DB)
sql = "select a.id , a.`name`, a.`status` ,b.physical_network ,b.segmentation_id as vlan_id from networks as a LEFT JOIN ml2_network_segments as b on a.id = b.network_id"
cur = yield db.execute(sql)
rst = cur.fetchall()
except Exception, e:
LOG.error("list simple network from db error: %s" % e)
raise e
def volume_type_list():
try:
db = dbpools.get_pool(dbpools.CINDER_DB)
sql = "SELECT vt.id as id, vt.name as name FROM volume_types vt WHERE vt.deleted=0"
cur = yield db.execute(sql)
volume_types = cur.fetchall()
except Exception, e:
LOG.error("Volume type list error: %s" % e)
raise e
def server_state_count():
try:
db = dbpools.get_pool(dbpools.NOVA_DB)
sql = "SELECT count(id) count, vm_state state " \
"FROM instances WHERE vm_state='active' or vm_state='stopped' group by vm_state"
cur = yield db.execute(sql)
rst = cur.fetchall()
except Exception, e:
LOG.error("Server state count error: %s" % e)
raise e
def get_count_alarm(target):
try:
db = dbpools.get_pool(dbpools.LOCAL_DB)
sql = "select count(*) as count from alarm where 1 = 1 "
if target:
sql += " and target='%s'" % target
cur = yield db.execute(sql)
vm_alarm_count = cur.fetchone()
except Exception, e:
LOG.error("get alarm failed: %s" % e)
def __activate_user(token_id, user_id):
db = dbpools.get_pool(dbpools.COMMON_DB)
cur = yield db.execute("select token from token where user_id = %s ", (user_id,))
old_token = cur.fetchone()
if old_token:
yield __delete_token(old_token['token'])
yield dbpools.execute_commit(
db,
"insert into token (token, user_id) values (%s, %s)",
(token_id, user_id)
)
def volume_real_used(tenant_id):
try:
db = dbpools.get_pool(dbpools.CINDER_DB)
sql = "SELECT COUNT(id) as count, SUM(size) as used_size " \
"FROM volumes " \
"WHERE display_description = 0 and project_id=%s and status != 'deleted'"
cur = yield db.execute(sql, [tenant_id])
volume_used = cur.fetchone()
except Exception, e:
LOG.error("volume real used error: %s" % e)
raise e
def update_tenant_hosts(tenant_id, tenant_hosts):
db = dbpools.get_pool(dbpools.LOCAL_DB)
tenant_hosts = json.dumps(tenant_hosts)
try:
sql = "UPDATE tenant_hosts SET hosts=%s " \
"WHERE tenant_id=%s"
yield db.execute(sql, (tenant_hosts, tenant_id))
except Exception, e:
LOG.error("update the tenant_hosts error :%s" % e)
raise e
def summary():
"""首页显示故障 告警 注意"""
rs = dict()
level = ['notice', 'warning', 'fatal']
db = dbpools.get_pool(dbpools.LOCAL_DB)
for l in level:
sql = "select count(*) from alarm where level = %s"
cur = yield db.execute(sql, (l, ))
trs = cur.fetchone()
rs[l] = trs['count(*)']
raise gen.Return(rs)
def query_instance_nova(device_ids):
try:
subnet_vm = []
if device_ids:
db = dbpools.get_pool(dbpools.NOVA_DB)
sql = "SELECT uuid,display_name as vm " \
"from instances WHERE uuid in %s "
cur = yield db.execute(sql, (device_ids,))
subnet_vm = cur.fetchall()
except Exception, e:
LOG.error("Query used vm of subnet from nava error: %s" % e)
raise e
def __update_vm_id(res_id, vm_id):
LOG.debug("__update_vm_id: begin")
db = dbpools.get_pool(dbpools.COMMON_DB)
try:
tx = yield db.begin()
yield pro.set_pro(tx, res_id, 'vm_id', vm_id)
yield tx.commit()
LOG.debug("__update_vm_id: success")
except Exception, e:
LOG.debug("__update_vm_id: failed")
yield tx.rollback()
raise e
def get_tenant_hosts(tenant_id):
db = dbpools.get_pool(dbpools.LOCAL_DB)
try:
cur = yield db.execute(
"select hosts from tenant_hosts where tenant_id=%s", [tenant_id])
hosts = cur.fetchone()
hosts = hosts and hosts["hosts"] and json.loads(hosts["hosts"])
if not hosts:
hosts = []
except Exception, e:
LOG.error("query hosts of tenant error: %s" % e)
raise e
def is_active(token_id, user_id):
db = dbpools.get_pool(dbpools.COMMON_DB)
try:
cur = yield db.execute(
"select token from token where user_id = %s and token = %s ", (
user_id,
token_id,
))
old_token = cur.fetchone()
if not old_token:
raise DialogueTimeOut
except Exception:
raise DialogueTimeOut
def get_drive_image_id():
"""
get drive image id
:return: image_id
"""
try:
db = dbpools.get_pool(dbpools.GLANCE_DB)
sql = "SELECT image_id FROM image_properties WHERE NAME = 'ecloud_image_type' AND VALUE = '2'"
cur = yield db.execute(sql)
result = cur.fetchall()
except Exception, e:
LOG.error("Get drive image id error: %s" % e)
raise e
def volume_state_count():
"""
:return:
"""
try:
db = dbpools.get_pool(dbpools.CINDER_DB)
sql = "SELECT count(*) count, sum(size) size, display_description as v_type FROM volumes " \
"WHERE deleted=0 and display_description in (0, 2) GROUP BY display_description"
cur = yield db.execute(sql)
metadatas = cur.fetchall()
except Exception, e:
LOG.error("Volume state count error: %s" % e)
raise e
def update(id_, utime):
LOG.debug("alarmAPI.update")
db = dbpools.get_pool(dbpools.LOCAL_DB)
sql = "update alarm set times = times + 1, update_at = %s where id = %s"
try:
tx = yield db.begin()
yield tx.execute(sql, (utime, id_))
yield tx.commit()
LOG.debug("alarmAPI.update success")
except Exception, e:
yield tx.rollback()
LOG.error("update alarm failed: %s" % e)
raise e
def get_security_groups_from_db(tenant_id=None):
try:
db = dbpools.get_pool(dbpools.NEUTRON_DB)
sql = "select tenant_id, id, name ,description from securitygroups where 1=1"
if tenant_id:
sql += " and tenant_id='%s' " % tenant_id
from_db = yield db.execute(sql)
all_data = from_db.fetchall()
except Exception as e:
LOG.error("get get_security_groups_from_db error %s" % e)
raise SecGroupRuleOperationFailed()
raise gen.Return(all_data)
def insert(target, typ, level, msg, create_time):
LOG.debug("alarmAPI.insert")
db = dbpools.get_pool(dbpools.LOCAL_DB)
sql = "insert into alarm (target, type, times, message, level, create_at) values (%s, %s, 1, %s, %s, %s)"
vals = (target, typ, msg, level, create_time)
tx = yield db.begin()
try:
yield tx.execute(sql, vals)
yield tx.commit()
LOG.debug("alarmAPI.insert: success")
except Exception, e:
yield tx.rollback()
LOG.error("insert alarm failed: %s" % e)
raise e
