def ensure_all_pm_scopes():
from noc.core.clickhouse.connect import connection
if not config.clickhouse.cluster or config.clickhouse.cluster_topology == "1":
# Standalone configuration
ensure_pm_scopes()
return
# Replicated configuration
ch = connection(read_only=False)
for host, port in ch.execute("SELECT host_address, port FROM system.clusters WHERE cluster = %s",
args=[config.clickhouse.cluster]):
c = connection(host=host, port=port, read_only=False)
ensure_pm_scopes(c)
def get_object_metrics(start, stop):
"""
:param start:
:type stop: datetime.datetime
:param stop:
:type stop: datetime.datetime
:return:
"""
r = {}
bi_map = {
str(bi_id): mo_id for mo_id, bi_id in ManagedObject.objects.values_list("id", "bi_id")
}
ch = connection()
for row in ch.execute(
"SELECT managed_object, sum(stp_topology_changes_delta) "
"FROM routing WHERE ts > '%s' and ts < '%s' GROUP BY managed_object"
% (
start.replace(microsecond=0).isoformat(sep=" "),
stop.replace(microsecond=0).isoformat(sep=" "),
)
): # delta
r[bi_map[row[0]]] = {"n_stp_topo_changes": row[1]}
del bi_map
return r
def connect(self):
"""
Connect to database
:return:
"""
self.connect = connection(host=self.host,
port=self.port,
read_only=False)
def ensure_table(self, connect=None):
"""
Ensure table is exists
:return: True, if table has been changed
"""
from noc.core.clickhouse.connect import connection
def ensure_columns(table_name):
c = False
# Alter when necessary
existing = {}
for name, type in ch.execute(
"""
SELECT name, type
FROM system.columns
WHERE
database=%s
AND table=%s
""",
[config.clickhouse.db, table_name],
):
existing[name] = type
after = None
for f, t in self.iter_fields():
if f not in existing:
ch.execute(
post="ALTER TABLE %s ADD COLUMN %s %s AFTER %s" %
(table_name, f, t, after))
c = True
after = f
if f in existing and existing[f] != t:
print("Warning! Type mismatch for column %s: %s <> %s" %
(f, existing[f], t))
print(
"Set command manually: ALTER TABLE %s MODIFY COLUMN %s %s"
% (table_name, f, t))
return c
changed = False
ch = connect or connection(read_only=False)
if not ch.has_table(self._get_raw_db_table()):
# Create new table
ch.execute(post=self.get_create_sql())
changed = True
else:
changed |= ensure_columns(self._get_raw_db_table())
# Check for distributed table
if config.clickhouse.cluster:
if not ch.has_table(self.table_name):
ch.execute(post=self.get_create_distributed_sql())
changed = True
else:
changed |= ensure_columns(self.table_name)
return changed
def do_query_ch(self, moss, query_map, f_date, to_date):
n = 0
client = connection()
mos_name = sorted(moss)
query = self.get_query_ch(query_map, f_date, to_date)
self.CHUNK_SIZE = 4000
while mos_name:
mos_name, m_r = mos_name[self.CHUNK_SIZE:], mos_name[:self.
CHUNK_SIZE]
for row in client.execute(query % ", ".join(m_r)):
yield row[0:2], row[2:]
n += 1
def __init__(self, mos_ids, f_date, to_date, columns=None):
super(ReportMetrics, self).__init__(mos_ids)
self.from_date = f_date
self.to_date = to_date
self.ch_client = connection()
if not (self.TABLE_NAME and self.SELECT_QUERY_MAP):
raise NotImplementedError
if columns and isinstance(columns, list):
for c in set(self.ATTRS) - set(columns):
self.ATTRS.pop(c)
elif columns and isinstance(columns, OrderedDict):
self.ATTRS = columns
self.unknown_value = ([
[""] * (len(self.SELECT_QUERY_MAP) + len(self.KEY_FIELDS))
], )
def handle_apply(self,
host=None,
port=None,
dry_run=True,
*args,
**options):
connect()
read_only = dry_run
ch = connection(host, port, read_only=read_only)
today = datetime.date.today()
# Get partitions
parts = self.get_parts(ch)
#
partition_claimed = []
claimed_bytes = 0
for p in CHPolicy.objects.filter(is_active=True).order_by("table"):
table_claimed = 0
if not p.ttl:
continue # Disabled
deadline = today - datetime.timedelta(days=p.ttl)
is_dry = dry_run or p.dry_run
self.print("# Table %s deadline %s%s" %
(p.table, deadline.isoformat(),
" (Dry Run)" if is_dry else ""))
for pi in parts[p.table]:
if pi.max_date >= deadline:
continue
self.print(
" Removing partition %s (%s -- %s, %d rows, %d bytes)" %
(pi.partition, pi.min_date, pi.max_date, pi.rows,
pi.bytes))
table_claimed += pi.bytes
if not is_dry:
partition_claimed += [(p.table, pi.partition)]
self.print(" Total %d bytes to be reclaimed" % table_claimed)
claimed_bytes += table_claimed
if partition_claimed:
self.print("Claimed data will be Loss..\n")
for i in reversed(range(1, 10)):
self.print("%d\n" % i)
time.sleep(1)
for c in partition_claimed:
table, part = c[0], c[1]
if table.startswith(".inner"):
table = table[7:]
ch.execute("ALTER TABLE %s.%s DROP PARTITION '%s'" %
(config.clickhouse.db, table, part))
self.print("# Done. %d bytes to be reclaimed" % claimed_bytes)
def get_data(self):
# Get span data
ch = connection()
data = [
Span(*r) for r in ch.execute(
"""
SELECT
ts, id, parent, server, service, client,
duration, sample, error_code,
error_text, in_label, out_label
FROM span
WHERE ctx = %s""",
[int(self.id)],
)
]
# Build hierarchy
smap = {s.id: s for s in data}
root = None
for s in data:
if s.parent:
smap[s.parent].children += [s]
else:
root = s
# Set width
for s in data:
if s.parent:
d = s.ts - root.ts
dt = d.seconds * 1000000 + d.microseconds
s.left = self.GRAPH_WIDTH * dt // root.duration
s.width = int(
float(self.GRAPH_WIDTH) /
(float(root.duration) / float(s.duration)))
else:
s.left = 0
s.width = self.GRAPH_WIDTH
# Flatten
spans = self.flatten_spans(root)
#
return {"context": int(self.id), "root": root, "spans": spans}
def get_data(self,
request,
interval=1,
repo_format=0,
from_date=None,
to_date=None,
**kwargs):
# Date Time Block
if from_date:
from_date = datetime.datetime.strptime(from_date, "%d.%m.%Y")
elif interval:
from_date = datetime.datetime.now() - datetime.timedelta(
days=int(interval))
else:
from_date = datetime.datetime.now() - datetime.timedelta(days=1)
if to_date:
to_date = datetime.datetime.strptime(to_date, "%d.%m.%Y")
if from_date == to_date:
to_date = from_date + datetime.timedelta(days=1)
elif interval:
to_date = from_date + datetime.timedelta(days=int(interval))
else:
to_date = from_date + datetime.timedelta(days=1)
columns = [
_("Server"),
_("Service"),
_("Request count"),
_("Success request count"),
_("Failed request count"),
_("Success request (%)"),
_("Q1 (ms)"),
_("Q2 (ms)"),
_("Q3 (ms)"),
_("p95 (ms)"),
_("max (ms)")
]
if repo_format == "1":
columns = [
_("Timestamp"),
_("Server"),
_("Service"),
_("Managed object"),
_("TT ID"),
_("Error code"),
_("Error text")
]
ts_from_date = time.mktime(from_date.timetuple())
ts_to_date = time.mktime(to_date.timetuple())
tt_systems = TTSystem.objects.filter().scalar("name")
# Manged Object block
q1 = """select server, service, count(), round(quantile(0.25)(duration), 0)/1000 as q1,
round(quantile(0.5)(duration), 0)/1000 as q2,
round(quantile(0.75)(duration), 0)/1000 as q3,
round(quantile(0.95)(duration),0)/1000 as p95,
round(max(duration),0)/1000 as max from span where %s
group by server, service"""
q2 = """select server, service, error_code, count(), avg(duration)
from span where %s group by server, service, error_code"""
q3 = """select ts, server, service, in_label, in_label, error_code, error_text from span
where service IN ('create_massive_damage_outer', 'change_massive_damage_outer_close') and
error_code <> 0 and %s"""
q_where = ["server IN ('%s')" % "', '".join(tt_systems)]
# q_where = ["managed_object IN (%s)" % ", ".join(mo_bi_dict.keys())]
q_where += [
"(date >= toDate(%d)) AND (ts >= toDateTime(%d) AND ts <= toDateTime(%d))"
% (ts_from_date, ts_from_date, ts_to_date)
]
r = []
ch = connection()
if repo_format == "1":
aa = {
aa.escalation_tt.split(":")[-1]: aa
for aa in ArchivedAlarm.objects.filter(
clear_timestamp__gte=from_date,
clear_timestamp__lte=to_date,
escalation_tt__exists=True)
}
query = q3 % " and ".join(q_where)
for row in ch.execute(query):
if row[2] in ["create_massive_damage_outer"]:
row[2] = u"Создание ТТ"
try:
row[3] = ManagedObject.objects.get(
tt_system_id=int(row[3]))
row[4] = ""
except ManagedObject.DoesNotExist:
pass
except ManagedObject.MultipleObjectsReturned:
row[3] = ManagedObject.objects.get(tt_system_id=int(
row[3]),
is_managed=True)
row[4] = ""
elif row[2] in ["change_massive_damage_outer_close"]:
row[2] = u"Закрытие ТТ"
row[4] = row[3]
row[3] = aa[
row[3]].managed_object if row[3] in aa else row[3]
else:
continue
r += [row]
else:
query = q1 % " and ".join(q_where)
# (server, service)
tt_s = {}
for row in ch.execute(query):
tt_s[(row[0], row[1])] = [row[2]] + [0, 0, 0] + row[3:]
query = q2 % " and ".join(q_where)
for row in ch.execute(query):
if row[2] == "0":
tt_s[(row[0], row[1])][1] = row[3]
else:
tt_s[(row[0], row[1])][2] += int(row[3])
r += [
SectionRow(name="Report from %s to %s" %
(from_date.strftime("%d.%m.%Y %H:%M"),
to_date.strftime("%d.%m.%Y %H:%M")))
]
for line in sorted(tt_s, key=lambda x: x[0]):
data = list(line)
data += tt_s[line]
data[5] = round((float(data[3]) / float(data[2])) * 100.0, 2)
r += [data]
return self.from_dataset(title=self.title,
columns=columns,
data=r,
enumerate=True)
def handler(self):
self.logger.info("Checking %s topology", self.name)
# Get segment hierarchy
segments = set(self.object.get_nested_ids())
# Get managed objects and id <-> bi_id mappings
bi_map = {} # bi_id -> mo
for mo in ManagedObject.objects.filter(
segment__in=[str(x) for x in segments]
):
bi_map[str(mo.bi_id)] = mo
if not bi_map:
self.logger.info("Empty segment tree. Skipping")
return
# Fetch latest MAC tables snapshots from ClickHouse
# @todo: Apply vlan restrictions
t0 = datetime.datetime.now() - datetime.timedelta(seconds=self.MAC_WINDOW)
t0 = t0.replace(microsecond=0)
SQL = """SELECT managed_object, mac, argMax(ts, ts), argMax(interface, ts)
FROM mac
WHERE
date >= toDate('%s')
AND ts >= toDateTime('%s')
AND managed_object IN (%s)
GROUP BY ts, managed_object, mac
""" % (t0.date().isoformat(), t0.isoformat(sep=" "),
", ".join(bi_map))
ch = connection()
# Fill FIB
mtable = [] # mo_id, mac, iface, ts
last_ts = {} # mo -> ts
for mo_bi_id, mac, ts, iface in ch.execute(post=SQL):
mo = bi_map.get(mo_bi_id)
if mo:
mtable += [[mo, MAC(int(mac)), iface, ts]]
last_ts[mo] = max(ts, last_ts.get(mo, ts))
# Filter out aged MACs
mtable = [m for m in mtable if m[3] == last_ts[m[0]]]
# Resolve objects
macs = set(x[1] for x in mtable)
if not macs:
self.logger.info("No MAC addresses collected. Stopping")
return
object_macs = DiscoveryID.find_objects(macs)
if not object_macs:
self.logger.info("Cannot resolve any MAC addresses. Stopping")
return
# Build FIB
fib = {} # object -> interface -> {seen objects}
for mo, mac, iface, ts in mtable:
ro = object_macs.get(mac)
if not ro:
continue
if mo not in fib:
fib[mo] = {}
if iface in fib[mo]:
fib[mo][iface].add(ro)
else:
fib[mo][iface] = {ro}
# Find uplinks and coverage
coverage = {} # mo -> covered objects
uplinks = {} # mo -> uplink interface
up_fib = {} # mo -> {seen via uplinks}
for mo in fib:
coverage[mo] = {mo}
for iface in fib[mo]:
if self.is_uplink(mo, fib[mo][iface], segments):
uplinks[mo] = iface
up_fib[mo] = fib[mo][iface]
else:
coverage[mo] |= fib[mo][iface]
if mo not in uplinks:
self.logger.info(
"[%s] Cannot detect uplinks. Topology may be imprecise",
mo.name
)
# Dump FIB
if self.logger.isEnabledFor(logging.DEBUG):
for mo in fib:
self.logger.debug("%s:", mo.name)
if mo in uplinks:
self.logger.debug(" * %s: %s", uplinks[mo], ", ".join(x.name for x in up_fib[mo]))
else:
self.logger.debug(" Warning: No uplinks. Topology may be imprecise")
for iface in fib[mo]:
self.logger.debug(" %s: %s", iface, ", ".join(x.name for x in fib[mo][iface]))
# Build topology
for mo in fib:
for iface in fib[mo]:
if iface == uplinks.get(mo):
continue
for ro in fib[mo][iface]:
cvr = coverage.get(ro)
if not cvr:
cvr = {ro}
coverage[ro] = cvr
if not fib[mo][iface] - cvr:
# All objects from mo:iface are seen via ro
uplink = uplinks.get(ro)
if uplink:
self.confirm_link(mo, iface, ro, uplink)
break
else:
self.logger.info(
"[%s] No uplinks. Cannot link to %s:%s. Topology may be imprecise",
ro.name, mo.name, iface
)
def get_metrics(mos):
from_date = datetime.datetime.now() - datetime.timedelta(days=1)
from_date = from_date.replace(microsecond=0)
# mo = self.object
bi_map = {str(mo.bi_id): mo for mo in mos}
SQL = """SELECT managed_object, arrayStringConcat(path) as iface, argMax(ts, ts), argMax(load_in, ts), argMax(load_out, ts), argMax(errors_in, ts), argMax(errors_out, ts)
FROM interface
WHERE
date >= toDate('%s')
AND ts >= toDateTime('%s')
AND managed_object IN (%s)
GROUP BY managed_object, iface
""" % (from_date.date().isoformat(),
from_date.isoformat(sep=" "), ", ".join(bi_map))
ch = connection()
mtable = [] # mo_id, mac, iface, ts
last_ts = {} # mo -> ts
metric_map = {
mo: {
"interface": defaultdict(dict),
"object": defaultdict(dict)
}
for mo in mos
}
msd = {ms.id: ms.table_name for ms in MetricScope.objects.filter()}
mts = {
str(mt.id): (msd[mt.scope.id], mt.field_name, mt.name)
for mt in MetricType.objects.all()
}
# Interface Metrics
for mo_bi_id, iface, ts, load_in, load_out, errors_in, errors_out in ch.execute(
post=SQL):
mo = bi_map.get(mo_bi_id)
if mo:
mtable += [[mo, iface, ts, load_in, load_out]]
metric_map[mo]["interface"][iface] = {
"load_in": int(load_in),
"load_out": int(load_out),
"errors_in": int(errors_in),
"errors_out": int(errors_out)
}
last_ts[mo] = max(ts, last_ts.get(mo, ts))
# Object Metrics
# object_profiles = set(mos.values_list("object_profile", flat=True))
object_profiles = set(mo.object_profile.id for mo in mos)
mmm = set()
op_fields_map = defaultdict(list)
for op in ManagedObjectProfile.objects.filter(id__in=object_profiles):
for mt in op.metrics:
mmm.add(mts[mt["metric_type"]])
op_fields_map[op.id] += [mts[mt["metric_type"]][1]]
for table, fields in itertools.groupby(sorted(mmm, key=lambda x: x[0]),
key=lambda x: x[0]):
# tb_fields = [f[1] for f in fields]
# mt_name = [f[2] for f in fields]
fields = list(fields)
SQL = """SELECT managed_object, argMax(ts, ts), %s
FROM %s
WHERE
date >= toDate('%s')
AND ts >= toDateTime('%s')
AND managed_object IN (%s)
GROUP BY managed_object
""" % (", ".join(
["argMax(%s, ts) as %s" % (f[1], f[1])
for f in fields]), table, from_date.date().isoformat(),
from_date.isoformat(sep=" "), ", ".join(bi_map))
# print SQL
for result in ch.execute(post=SQL):
mo_bi_id, ts = result[:2]
mo = bi_map.get(mo_bi_id)
i = 0
for r in result[2:]:
f_name = fields[i][2]
mtable += [[mo, ts, r]]
metric_map[mo]["object"][f_name] = r
last_ts[mo] = max(ts, last_ts.get(mo, ts))
i += 1
return metric_map
CH_USER = config.clickhouse.rw_user
# CH_USER = "******"
CH_PASSWORD = config.clickhouse.rw_password
# END data for old data
# END_DATE = datetime.datetime(2021, 4, 4)
END_DATE = None
# For speedup if used cluster replica data will be query between replica
# Example: [("10.10.10.1", "10.10.10.2")] - (replica1, replica2)
CH_REPLICAS = []
#
# Split query by month
# If needed one query - MIGRATE_CHUNK great than MIGRATE_DEPTH
MIGRATE_DEPTH = 120
MIGRATE_CHUNK = 30
client = connection()
def fix():
if CH_REPLICAS:
# For replicated schema
for rep1, rep2 in CH_REPLICAS:
rep1_migrate = []
rep2_migrate = []
for ms in MetricScope.objects.filter():
for start, stop in iter_time_interval():
query = get_insert_query(ms, start, stop, remote=rep2)
if not query:
continue
# print(f'clickhouse-client -h {rep1} --query="{query}"')
rep1_migrate += [f'clickhouse-client -h {rep1} --query="{query}"']
def ensure_table(self, connect=None):
"""
Ensure table is exists
:return: True, if table has been changed
"""
from noc.core.clickhouse.connect import connection
def ensure_column(table_name, column):
"""
If path not exists on column - new schema
:param table_name:
:return:
"""
return bool(
ch.execute(
"""
SELECT 1
FROM system.columns
WHERE
database=%s
AND table=%s
AND name=%s
""",
[config.clickhouse.db, table_name, column],
))
def ensure_columns(table_name):
c = False
# Alter when necessary
existing = {}
for name, type in ch.execute(
"""
SELECT name, type
FROM system.columns
WHERE
database=%s
AND table=%s
""",
[config.clickhouse.db, table_name],
):
existing[name] = type
after = None
for f, t, me, de in self.iter_fields():
if f not in existing:
ch.execute(
post=
f"ALTER TABLE {table_name} ADD COLUMN {f} {t} {me} {de} AFTER {after}"
)
c = True
after = f
if f in existing and existing[f] != t:
print(
f"Warning! Type mismatch for column {f}: {existing[f]} <> {t}"
)
print(
f"Set command manually: ALTER TABLE {table_name} MODIFY COLUMN {f} {t}"
)
return c
changed = False
ch = connect or connection(read_only=False)
is_cluster = bool(config.clickhouse.cluster)
table = self._get_db_table()
raw_table = self._get_raw_db_table()
dist_table = self._get_distributed_db_table()
# Legacy migration
if ch.has_table(table) and not ch.has_table(raw_table):
# Legacy scheme, data for non-clustered installations has been written
# to table itself. Move to raw_*
ch.rename_table(table, raw_table)
changed = True
# Old schema
if ensure_column(raw_table, "path"):
# Old schema, data table will be move to old_noc db for save data.
ch.ensure_db(OLD_PM_SCHEMA_TABLE)
ch.rename_table(raw_table, f"{OLD_PM_SCHEMA_TABLE}.{raw_table}")
# Ensure raw_* table
if ch.has_table(raw_table):
# raw_* table exists, check columns
changed |= ensure_columns(raw_table)
else:
# Create new table
ch.execute(post=self.get_create_sql())
changed = True
# For cluster mode check d_* distributed table
if is_cluster:
if ch.has_table(dist_table):
changed |= ensure_columns(dist_table)
else:
ch.execute(post=self.get_create_distributed_sql())
changed = True
# Synchronize view
ch.execute(post=self.get_create_view_sql())
return changed
def handle_vacuum_bulling(self, ids, *args, **kwargs):
connect()
for mo_id in ids:
mo = ManagedObject.get_by_id(mo_id)
if not mo:
self.print("@@@ %s is not found, skipping", mo_id)
continue
self.print("@@@ %s (%s, %s)", mo.name, mo.address, mo.id)
# Get interfaces suitable for bulling
bulling_ifaces: Set[Interface] = {
iface
for iface in Interface.objects.filter(managed_object=mo.id)
if not iface.profile.allow_vacuum_bulling
}
if not bulling_ifaces:
self.print("No interfaces suitable for vacuum bulling")
continue
# Get MAC addresses for bulling
t0 = datetime.datetime.now() - datetime.timedelta(
seconds=self.MAC_WINDOW)
t0 = t0.replace(microsecond=0)
sql = self.GET_MACS_SQL % (
mo.bi_id,
", ".join("'%s'" % iface.name.replace("'", "''")
for iface in bulling_ifaces),
t0.date().isoformat(),
t0.isoformat(sep=" "),
)
ch = connection()
last_ts: Optional[str] = None
all_macs: List[str] = []
mac_iface: Dict[str, str] = {}
for ts, iface, mac in ch.execute(post=sql):
if last_ts is None:
last_ts = ts
elif last_ts > ts:
continue
m = str(MAC(int(mac)))
all_macs += [m]
mac_iface[m] = iface
# Resolve MACs to known chassis-id
mac_map = DiscoveryID.find_objects(all_macs)
# Filter suitable rivals
seg_ifaces: DefaultDict[NetworkSegment,
Set[str]] = defaultdict(set)
iface_segs: DefaultDict[str,
Set[NetworkSegment]] = defaultdict(set)
for mac, r_mo in mac_map.items():
iface = mac_iface.get(mac)
if not iface:
continue
seg_ifaces[r_mo.segment].add(iface)
iface_segs[iface].add(r_mo.segment)
rej_ifaces: Set[str] = set()
for seg in seg_ifaces:
if len(seg_ifaces[seg]
) > 1 or seg.profile.is_persistent or seg == mo.segment:
# Seen on multiple interfaces or persistent segment or same segment
rej_ifaces |= set(seg_ifaces[seg])
continue
for iface in sorted(iface_segs, key=alnum_key):
if iface in rej_ifaces:
continue
for seg in iface_segs[iface]:
self.print(" '%s' challenging '%s' on %s" %
(mo.segment.name, seg.name, iface))
BioSegTrial.schedule_trial(seg, mo.segment)
def api_report(
self,
request,
reporttype=None,
from_date=None,
to_date=None,
object_profile=None,
filter_default=None,
exclude_zero=None,
interface_profile=None,
selector=None,
administrative_domain=None,
columns=None,
o_format=None,
enable_autowidth=False,
exclude_serial_change=False,
**kwargs,
):
def translate_row(row, cmap):
return [row[i] for i in cmap]
cols = [
"object_name",
"object_address",
"object_adm_domain",
"event_type",
"sn_changed",
"vendor_mac",
"mac",
"migrate_ts",
"from_iface_name",
"from_iface_down",
"to_iface_name",
"to_iface_down",
]
header_row = [
"OBJECT_NAME",
"OBJECT_ADDRESS",
"OBJECT_ADM_DOMAIN",
"EVENT_TYPE",
"SN_CHANGED",
"VENDOR_MAC",
"MAC",
"MIGRATE_TS",
"FROM_IFACE_NAME",
"FROM_IFACE_DOWN",
"TO_IFACE_NAME",
"TO_IFACE_DOWN",
]
if columns:
cmap = []
for c in columns.split(","):
try:
cmap += [cols.index(c)]
except ValueError:
continue
else:
cmap = list(range(len(cols)))
r = [translate_row(header_row, cmap)]
# Date Time Block
if not from_date:
from_date = datetime.datetime.now() - datetime.timedelta(days=1)
else:
from_date = datetime.datetime.strptime(from_date, "%d.%m.%Y")
if not to_date or from_date == to_date:
to_date = from_date + datetime.timedelta(days=1)
else:
to_date = datetime.datetime.strptime(to_date, "%d.%m.%Y") + datetime.timedelta(days=1)
# interval = (to_date - from_date).days
# ts_from_date = time.mktime(from_date.timetuple())
# ts_to_date = time.mktime(to_date.timetuple())
mos = self.get_report_object(
user=request.user, adm=administrative_domain, selector=selector
)
mos_id = set(mos.order_by("bi_id").values_list("bi_id", flat=True))
if interface_profile:
interface_profile = InterfaceProfile.objects.get(id=interface_profile)
iface_filter = (
"dictGetString('interfaceattributes', 'profile', (managed_object, interface)) == '%s'"
% interface_profile.name
)
else:
iface_filter = "is_uni = 1"
serials_changed = {}
ch = connection()
for row in ch.execute(
DEVICE_MOVED_QUERY
% (
from_date.date().isoformat(),
(to_date.date() - datetime.timedelta(days=1)).isoformat(),
)
):
serials_changed[int(row[0])] = row[1]
for (
mo,
mac,
mo_name,
mo_address,
mo_adm_domain,
ifaces,
migrate_ifaces,
migrate_count,
) in ch.execute(
MAC_MOVED_QUERY
% (iface_filter, from_date.date().isoformat(), to_date.date().isoformat())
):
if int(mo) not in mos_id:
continue
if exclude_serial_change and int(mo) in serials_changed:
continue
iface_from, iface_to, migrate = get_interface(ifaces)
event_type = _("Migrate")
if (
rx_port_num.search(iface_from).group() == rx_port_num.search(iface_to).group()
and iface_from != iface_to
):
event_type = _("Migrate (Device Changed)")
r += [
translate_row(
[
mo_name,
mo_address,
mo_adm_domain,
event_type,
_("Yes") if int(mo) in serials_changed else _("No"),
MACVendor.get_vendor(mac),
mac,
datetime.datetime.fromtimestamp(migrate[1]).isoformat(sep=" "), # TS
iface_from,
"--",
iface_to,
"--",
],
cmap,
)
]
filename = "macs_move_report_%s" % datetime.datetime.now().strftime("%Y%m%d")
if o_format == "csv":
response = HttpResponse(content_type="text/csv")
response["Content-Disposition"] = 'attachment; filename="%s.csv"' % filename
writer = csv.writer(response, dialect="excel", delimiter=",", quoting=csv.QUOTE_MINIMAL)
writer.writerows(r)
return response
elif o_format == "csv_zip":
response = BytesIO()
f = TextIOWrapper(TemporaryFile(mode="w+b"), encoding="utf-8")
writer = csv.writer(f, dialect="excel", delimiter=";", quotechar='"')
writer.writerows(r)
f.seek(0)
with ZipFile(response, "w", compression=ZIP_DEFLATED) as zf:
zf.writestr("%s.csv" % filename, f.read())
zf.filename = "%s.csv.zip" % filename
# response = HttpResponse(content_type="text/csv")
response.seek(0)
response = HttpResponse(response.getvalue(), content_type="application/zip")
response["Content-Disposition"] = 'attachment; filename="%s.csv.zip"' % filename
return response
elif o_format == "xlsx":
response = BytesIO()
wb = xlsxwriter.Workbook(response)
cf1 = wb.add_format({"bottom": 1, "left": 1, "right": 1, "top": 1})
ws = wb.add_worksheet("Alarms")
max_column_data_length = {}
for rn, x in enumerate(r):
for cn, c in enumerate(x):
if rn and (
r[0][cn] not in max_column_data_length
or len(str(c)) > max_column_data_length[r[0][cn]]
):
max_column_data_length[r[0][cn]] = len(str(c))
ws.write(rn, cn, c, cf1)
ws.autofilter(0, 0, rn, cn)
ws.freeze_panes(1, 0)
for cn, c in enumerate(r[0]):
# Set column width
width = get_column_width(c)
if enable_autowidth and width < max_column_data_length[c]:
width = max_column_data_length[c]
ws.set_column(cn, cn, width=width)
wb.close()
response.seek(0)
response = HttpResponse(response.getvalue(), content_type="application/vnd.ms-excel")
response["Content-Disposition"] = 'attachment; filename="%s.xlsx"' % filename
response.close()
return response
