def tag_asset(tag_id, asset_id, asset_type, organization_id, commit=True):
"""
Tag the asset with the given asset_type ("device" or "gateway") and asset_id
(device_id or gateway_id) with the tag with tag_id and organization_id.
"""
if not is_from_organization(tag_id, organization_id):
raise Error.Forbidden("Trying to use a tag from other organization.")
if is_tagged(tag_id, asset_id, asset_type): return
if asset_type == "device":
if not DeviceRepository.is_from_organization(asset_id,
organization_id):
raise Error.Forbidden(
"Trying to tag a device from other organization")
asset_tag = DeviceToTag(tag_id=tag_id, device_id=asset_id)
db.session.add(asset_tag)
elif asset_type == "gateway":
if not GatewayRepository.is_from_organization(asset_id,
organization_id):
raise Error.Forbidden(
"Trying to tag a gateway from other organization")
asset_tag = GatewayToTag(tag_id=tag_id, gateway_id=asset_id)
db.session.add(asset_tag)
else:
raise Error.BadRequest(f"Invalid asset_type: {asset_type}")
if commit: db.session.commit()
def untag_asset(tag_id, asset_id, asset_type, organization_id, commit=True):
"""
Remove the tag with the tag_id and organization_id from the asset with the
given asset_type ("device" or "gateway") and asset_id (device_id or
gateway_id).
"""
if not is_from_organization(tag_id, organization_id):
raise Error.Forbidden(
"Trying to delete a tag from other organization.")
if asset_type == "device":
if not DeviceRepository.is_from_organization(asset_id,
organization_id):
raise Error.Forbidden(
"Trying to untag a device from other organization")
asset_tag = db.session.query(DeviceToTag).\
filter(DeviceToTag.tag_id==tag_id, DeviceToTag.device_id==asset_id).first()
if asset_tag: db.session.delete(asset_tag)
elif asset_type == "gateway":
if not GatewayRepository.is_from_organization(asset_id,
organization_id):
raise Error.Forbidden(
"Trying to untag a gateway from other organization")
asset_tag = db.session.query(GatewayToTag).\
filter(GatewayToTag.tag_id==tag_id, GatewayToTag.gateway_id==asset_id).first()
if asset_tag: db.session.delete(asset_tag)
else:
raise Error.BadRequest(f"Invalid asset_type: {asset_type}")
if commit: db.session.commit()
def validate_keys(app_keys):
"""
Helper function to validate that every key in a
list of keys is a hex string of length 32
"""
hex_digits = set(list(string.hexdigits))
for key in app_keys:
if len(key) != 32:
raise Error.BadRequest(
f"Every key must have 32 characters, but received one with length {len(key)}"
)
for char in key:
if char not in hex_digits:
raise Error.BadRequest(
f"Every key must contain only hex digits, but one had a \"{char}\""
)
def delete(self):
user = User.find_by_username(get_jwt_identity())
if not user or is_system(user.id):
raise Error.Forbidden("User not allowed")
organization_id = user.organization_id
body = json.loads(request.data)
parsed_result = AppKeysSchema().load(body).data
app_keys = parsed_result.get('keys')
if app_keys is None:
raise Error.BadRequest(
f"AppKeysAPI POST request body must contain a non-empty list of keys with at most {MAX_PER_ORGANIZATION} keys"
)
app_keys = [key.upper() for key in app_keys]
total = len(app_keys)
app_keys = list(set(app_keys))
not_duplicated = len(app_keys)
validate_keys(app_keys)
deleted = AppKeysRepository.delete(keys_list=app_keys,
organization_id=organization_id)
return {
"message":
f"{deleted} app keys deleted, {total-not_duplicated} were duplicated and {not_duplicated-deleted} were not present in user's organization"
}, 200
def find_all_with(gateway_id=None, device_id=None):
if (not gateway_id and not device_id):
raise Error.BadRequest("Either device or gateway id is required")
query = db.session.query(GatewayToDevice)
if gateway_id:
query = query.filter(GatewayToDevice.gateway_id == gateway_id)
if device_id:
query = query.filter(GatewayToDevice.device_id == device_id)
return query.all()
def is_tagged(tag_id, asset_id, asset_type):
if asset_type == "device":
return db.session.query(DeviceToTag.query.\
filter(DeviceToTag.tag_id == tag_id).\
filter(DeviceToTag.device_id == asset_id).exists()).scalar()
elif asset_type == "gateway":
return db.session.query(GatewayToTag.query.\
filter(GatewayToTag.tag_id == tag_id).\
filter(GatewayToTag.gateway_id == asset_id).exists()).scalar()
else:
raise Error.BadRequest(f"Invalid asset_type: {asset_type}")
def query_for_count(dev_query, gtw_query, asset_type):
"""
Helper function to execute the queries for
count methods, filtering by asset type
"""
if asset_type is None:
result = dev_query.all() + gtw_query.all()
elif asset_type == "device":
result = dev_query.all()
elif asset_type == "gateway":
result = gtw_query.all()
elif asset_type == "none":
result = []
else:
raise Error.BadRequest("Invalid asset type parameter")
return result
def list_asset_tags(asset_id, asset_type, organization_id):
if asset_type == "device":
if not DeviceRepository.is_from_organization(asset_id,
organization_id):
raise Error.Forbidden(
"Trying to list tags from a device from other organization")
return db.session.query(Tag).\
join(DeviceToTag).\
filter(DeviceToTag.device_id == asset_id).all()
elif asset_type == "gateway":
if not GatewayRepository.is_from_organization(asset_id,
organization_id):
raise Error.Forbidden(
"Trying to list tags from a gateway from other organization")
return db.session.query(Tag).\
join(GatewayToTag).\
filter(GatewayToTag.gateway_id == asset_id).all()
else:
raise Error.BadRequest(f"Invalid asset_type: {asset_type}")
def get_with(asset_id, asset_type, organization_id=None):
""" Gets an asset from database
Request parameters:
- asset_id: database id of the asset
- asset_type: type of the requested asset, can be "device" or "gateway".
- organization_id (optional): when given, asserts that received organization
matchs the asset's organization
Returns:
- Model object of requested asset
"""
if asset_type == "device":
asset = Device.query.get(asset_id)
if not asset:
raise Error.NotFound(
f"Asset with id {asset_id} and type {asset_type} not found")
device_session = db.session.query(DeviceSession).\
filter(DeviceSession.device_id==asset_id).\
order_by(DeviceSession.last_activity.desc()).\
first()
asset.dev_addr = device_session.dev_addr if device_session else None
asset.hex_id = asset.dev_eui
elif asset_type == "gateway":
asset = db.session.query(Gateway).\
filter(Gateway.id == asset_id).\
first()
if not asset:
raise Error.NotFound(
f"Asset with id {asset_id} and type {asset_type} not found")
asset.hex_id = asset.gw_hex_id
else:
raise Error.BadRequest(
f"Invalid asset_type: {asset_type}. Valid values are \'device\' or \'gateway\'"
)
if organization_id and asset.organization_id != organization_id:
raise Error.Forbidden(
"User's organization's different from asset organization")
asset.type = asset_type
return asset
def get(self, asset_type, asset_id):
organization_id = get_jwt_claims().get("organization_id")
since = request.args.get('created_at[gte]')
until = request.args.get('created_at[lte]')
alert_types = request.args.getlist('type[]')
risks = request.args.getlist('risk[]')
order_by = request.args.getlist('order_by[]')
page = request.args.get('page', default=1, type=int)
size = request.args.get('size', default=20, type=int)
if since:
try:
since = dp.parse(since)
except Exception:
raise Error.BadRequest('no valid created_at[gte] value')
if until:
try:
until = dp.parse(until)
except Exception:
raise Error.BadRequest('no valid created_at[lte] value')
if since and until and since > until:
raise Error.BadRequest('since value must be before to until value')
if not order_by or len(order_by) < 2 or order_by[1] not in ('ASC',
'DESC'):
order_by = None
if page:
try:
page = int(page)
except Exception:
return Error.BadRequest('no valid page value')
if size:
try:
size = int(size)
except Exception:
return Error.BadRequest('no valid size value')
asset = AssetRepository.get_with(asset_id, asset_type, organization_id)
if asset_type == 'device': # for a device, return all the issues that this device has created
results = Quarantine.find(
organization_id=organization_id,
since=since,
until=until,
alert_types=[
AlertType.find_one(alert_type_code).id
for alert_type_code in alert_types
],
devices=[asset.id],
asset_type=asset_type,
risks=risks,
data_collectors=None,
order_by=order_by,
page=page,
size=size)
else: # for a gateway, return all the issues that this gateway has created
results = Quarantine.find(
organization_id=organization_id,
since=since,
until=until,
alert_types=[
AlertType.find_one(alert_type_code).id
for alert_type_code in alert_types
],
devices=None,
gateway_id=asset.id,
asset_type=asset_type,
risks=risks,
data_collectors=None,
order_by=order_by,
page=page,
size=size)
issues = [{
'id':
issue.id,
'organization_id':
issue.organization_id,
'since':
issue.since.strftime(DATE_FORMAT) if issue.since else None,
'alert': {
'id':
issue.alert.id,
'type':
issue.alert.alert_type.to_json(),
'created_at':
issue.alert.created_at.strftime(DATE_FORMAT)
if issue.alert.created_at else None,
'device_id':
issue.alert.device_id,
'data_collector_id':
issue.alert.data_collector_id,
'device_session_id':
issue.alert.device_session_id,
'gateway_id':
issue.alert.gateway_id,
'device_auth_id':
issue.alert.device_auth_id,
'parameters':
json.loads(issue.alert.parameters if issue.alert.
parameters is not None else '{}'),
'resolved_at':
None if issue.alert.resolved_at is None else
issue.alert.resolved_at.strftime(DATE_FORMAT),
'resolved_by_id':
issue.alert.resolved_by_id,
'resolution_comment':
issue.alert.resolution_comment
},
'parameters':
json.loads(
issue.parameters if issue.parameters is not None else '{}'),
'last_checked':
issue.last_checked.strftime(DATE_FORMAT)
if issue.last_checked else None,
'resolved_at':
issue.resolved_at.strftime(DATE_FORMAT)
if issue.resolved_at else None,
'resolved_by_id':
issue.resolved_by_id,
'resolution_comment':
issue.resolution_comment,
'resolution_reason_id':
issue.resolution_reason_id
} for issue in results.items]
response = {
'issues': issues,
'total_pages': results.pages,
'total_items': results.total
}
return response, 200
def get(self, asset_type, asset_id):
organization_id = get_jwt_claims().get("organization_id")
since = request.args.get('created_at[gte]')
until = request.args.get('created_at[lte]')
types = request.args.getlist('type[]')
resolved = request.args.get('resolved')
risks = request.args.getlist('risk[]')
order_by = request.args.getlist('order_by[]')
page = request.args.get('page', default=1, type=int)
size = request.args.get('size', default=20, type=int)
if since:
try:
since = dp.parse(since)
except Exception:
raise Error.BadRequest('no valid created_at[gte] value')
if until:
try:
until = dp.parse(until)
except Exception:
raise Error.BadRequest('no valid created_at[lte] value')
if since and until and since > until:
raise Error.BadRequest('since value must be before to until value')
if not order_by or len(order_by) < 2 or order_by[1] not in ('ASC',
'DESC'):
order_by = None
if page:
try:
page = int(page)
except Exception:
raise Error.BadRequest('no valid page value')
if size:
try:
size = int(size)
except Exception:
raise Error.BadRequest('no valid size value')
if resolved:
resolved = resolved == 'true'
asset = AssetRepository.get_with(asset_id, asset_type, organization_id)
if asset_type == 'device':
results = Alert.find_with(device_id=asset.id,
organization_id=organization_id,
since=since,
until=until,
types=types,
resolved=resolved,
risks=risks,
order_by=order_by,
page=page,
size=size,
asset_type=asset_type)
else:
results = Alert.find_with(gateway_id=asset.id,
organization_id=organization_id,
since=since,
until=until,
types=types,
resolved=resolved,
risks=risks,
order_by=order_by,
page=page,
size=size,
asset_type=asset_type)
alerts = [{
'id':
alert.id,
'type':
alert.alert_type.to_json(),
'created_at':
alert.created_at.strftime(DATE_FORMAT)
if alert.created_at else None,
'device_id':
alert.device_id,
'data_collector_id':
alert.data_collector_id,
'data_collector_name':
alert.data_collector.name,
'device_session_id':
alert.device_session_id,
'gateway_id':
alert.gateway_id,
'device_auth_id':
alert.device_auth_id,
'parameters':
json.loads(
alert.parameters if alert.parameters is not None else '{}'),
'resolved_at':
None if alert.resolved_at is None else
alert.resolved_at.strftime(DATE_FORMAT),
'resolved_by_id':
alert.resolved_by_id,
'resolution_comment':
alert.resolution_comment,
'asset_importance':
alert.get_asset_importance()
} for alert in results.items]
response = {
'alerts': alerts,
'total_pages': results.pages,
'total_items': results.total
}
return response, 200
def add_filters(dev_query,
gtw_query,
asset_type=None,
asset_status=None,
data_collector_ids=None,
gateway_ids=None,
device_ids=None,
min_signal_strength=None,
max_signal_strength=None,
min_packet_loss=None,
max_packet_loss=None):
"""
Helper function to add the filters to dev_query and gtw_query.
Returns the tuple (dev_query, gtw_query) with the corresponding filters added.
"""
if asset_status == 'connected':
dev_query = dev_query.filter(Device.connected)
gtw_query = gtw_query.filter(Gateway.connected)
elif asset_status == 'disconnected':
dev_query = dev_query.filter(not_(Device.connected))
gtw_query = gtw_query.filter(not_(Gateway.connected))
elif asset_status is not None:
raise Error.BadRequest("Invalid asset status parameter")
if data_collector_ids:
dev_query = dev_query.filter(
Device.data_collector_id.in_(data_collector_ids))
gtw_query = gtw_query.filter(
Gateway.data_collector_id.in_(data_collector_ids))
if gateway_ids:
wanted_devs = db.session.\
query(distinct(GatewayToDevice.device_id).label('device_id')).\
filter(GatewayToDevice.gateway_id.in_(gateway_ids)).\
subquery()
dev_query = dev_query.join(wanted_devs,
Device.id == wanted_devs.c.device_id)
gtw_query = gtw_query.filter(Gateway.id.in_(gateway_ids))
if device_ids:
wanted_gtws = db.session.\
query(distinct(GatewayToDevice.gateway_id).label('gateway_id')).\
filter(GatewayToDevice.device_id.in_(device_ids)).\
subquery()
dev_query = dev_query.filter(Device.id.in_(device_ids))
gtw_query = gtw_query.join(wanted_gtws,
Gateway.id == wanted_gtws.c.gateway_id)
if min_signal_strength is not None:
dev_query = dev_query.filter(
and_(Device.max_rssi != null(),
Device.max_rssi >= min_signal_strength))
if max_signal_strength is not None:
dev_query = dev_query.filter(
and_(Device.max_rssi != null(),
Device.max_rssi < max_signal_strength))
packets_up = build_count_subquery(CounterType.PACKETS_UP)
packets_down = build_count_subquery(CounterType.PACKETS_DOWN)
packets_lost = build_count_subquery(CounterType.PACKETS_LOST)
if min_packet_loss is not None or max_packet_loss is not None:
dev_query = dev_query.\
join(packets_up, Device.id == packets_up.c.device_id).\
join(packets_down, Device.id == packets_down.c.device_id).\
join(packets_lost, Device.id == packets_lost.c.device_id)
if min_packet_loss is not None:
dev_query = dev_query.filter(
and_(
packets_up.c.count + packets_down.c.count +
packets_lost.c.count > 0, 100 * packets_lost.c.count /
(packets_up.c.count + packets_down.c.count +
packets_lost.c.count) >= min_packet_loss))
if max_packet_loss is not None:
dev_query = dev_query.filter(
and_(
packets_up.c.count + packets_down.c.count +
packets_lost.c.count > 0, 100 * packets_lost.c.count /
(packets_up.c.count + packets_down.c.count +
packets_lost.c.count) < max_packet_loss))
return (dev_query, gtw_query)
def get_with(asset_id, asset_type, organization_id=None):
""" Gets an asset from database
Request parameters:
- asset_id: database id of the asset
- asset_type: type of the requested asset, can be "device" or "gateway".
- organization_id (optional): when given, asserts that received organization
matchs the asset's organization
Returns:
- Model object of requested asset
"""
if asset_type == "device":
asset_query = db.session.query(
Device.id,
Device.organization_id,
Device.dev_eui.label('hex_id'),
expression.literal_column('\'Device\'').label('type'),
Device.name,
Device.app_name,
DataCollector.name.label('data_collector'),
PolicyItem.parameters.label('policy_parameters'),
Device.connected.label('connected'),
Device.last_activity,
Device.activity_freq,
Device.activity_freq_variance,
Device.npackets_up,
Device.npackets_down,
Device.npackets_lost.label('packet_loss'),
Device.max_rssi,
Device.max_lsnr,
Device.ngateways_connected_to,
Device.payload_size,
Device.last_packets_list
).filter(Device.id == asset_id).\
join(DataCollector, Device.data_collector_id == DataCollector.id).\
join(Policy, Policy.id == DataCollector.policy_id).\
join(PolicyItem, and_(Policy.id == PolicyItem.policy_id, PolicyItem.alert_type_code == 'LAF-401')).\
join(RowProcessed, RowProcessed.analyzer == 'packet_analyzer').\
join(Packet, Packet.id == RowProcessed.last_row).\
join(DeviceCounters, and_(
DeviceCounters.device_id == Device.id,
DeviceCounters.counter_type.in_(dev_wanted_counters),
DeviceCounters.last_update + func.make_interval(0,0,0,1) > Packet.date
), isouter=True).\
group_by(Device.id, DataCollector.name, PolicyItem.parameters)
asset_query = add_counters_columns(asset_query, dev_wanted_counters,
DeviceCounters)
asset = asset_query.first()
elif asset_type == "gateway":
asset_query = db.session.query(
Gateway.id,
Gateway.organization_id,
Gateway.gw_hex_id.label('hex_id'),
expression.literal_column('\'Gateway\'').label('type'),
Gateway.name,
expression.null().label('app_name'),
DataCollector.name.label('data_collector'),
expression.null().label('policy_parameters'),
Gateway.connected.label('connected'),
Gateway.last_activity,
Gateway.activity_freq,
cast(expression.null(), Float).label('activity_freq_variance'),
Gateway.npackets_up,
Gateway.npackets_down,
cast(expression.null(), Float).label('packet_loss'),
cast(expression.null(), Float).label('max_rssi'),
cast(expression.null(), Float).label('max_lsnr'),
cast(expression.null(), Float).label('ngateways_connected_to'),
cast(expression.null(), Float).label('payload_size'),
expression.null().label('last_packets_list')
).filter(Gateway.id == asset_id).\
join(DataCollector, Gateway.data_collector_id == DataCollector.id).\
join(RowProcessed, RowProcessed.analyzer == 'packet_analyzer').\
join(Packet, Packet.id == RowProcessed.last_row).\
join(GatewayCounters, and_(
GatewayCounters.gateway_id == Gateway.id,
GatewayCounters.counter_type.in_(gtw_wanted_counters),
GatewayCounters.last_update + func.make_interval(0,0,0,1) > Packet.date
), isouter=True).\
group_by(Gateway.id, DataCollector.name)
asset_query = add_counters_columns(asset_query, gtw_wanted_counters,
GatewayCounters)
asset = asset_query.first()
else:
raise Error.BadRequest(
f"Invalid asset_type: {asset_type}. Valid values are \'device\' or \'gateway\'"
)
if not asset:
raise Error.NotFound(
f"Asset with id {asset_id} and type {asset_type} not found")
if organization_id and asset.organization_id != organization_id:
raise Error.Forbidden(
"User's organization's different from asset organization")
return asset
def count_per_importance(organization_id,
vendors=None,
gateway_ids=None,
data_collector_ids=None,
tag_ids=None,
asset_type=None,
importances=None):
""" Count the number of assets per importance.
Parameters:
- organization_id: which organization.
- vendors[]: for filtering, lists only assets that have ANY one of these vendors.
- gateway_ids[]: for filtering, list only the assets connected to ANY one of these gateways.
- data_collector_ids[]: for filtering, list only the assest related to ANY of these data collectors.
- tag_ids[]: for filtering, list only the assest that have ALL these tags.
- asset_type: for filtering, list only this type of asset ("device" or "gateway").
- importances: for filtering, list only the assets that have ANY of these importances
Returns:
- A list of dicts, where each dict has three fields: id, name, count.
"""
# Build two queries, one for devices and one for gateways
dev_query = db.session.query(Device.importance, func.count(distinct(Device.id))).\
join(GatewayToDevice).\
group_by(Device.importance).\
filter(Device.organization_id==organization_id).\
filter(Device.pending_first_connection==False)
gtw_query = db.session.query(Gateway.importance, func.count(distinct(Gateway.id))).\
group_by(Gateway.importance).\
filter(Gateway.organization_id==organization_id)
# If filter arguments were given, add the respective where clauses to the queries
if None in vendors:
dev_query = dev_query.filter(
or_(Device.vendor.in_(vendors), Device.vendor.is_(None)))
gtw_query = gtw_query.filter(
or_(Gateway.vendor.in_(vendors), Gateway.vendor.is_(None)))
elif vendors:
dev_query = dev_query.filter(Device.vendor.in_(vendors))
gtw_query = gtw_query.filter(Gateway.vendor.in_(vendors))
if gateway_ids:
dev_query = dev_query.filter(
GatewayToDevice.gateway_id.in_(gateway_ids))
gtw_query = gtw_query.filter(Gateway.id.in_(gateway_ids))
if data_collector_ids:
dev_query = dev_query.filter(
Device.data_collector_id.in_(data_collector_ids))
gtw_query = gtw_query.filter(
Gateway.data_collector_id.in_(data_collector_ids))
if tag_ids:
dev_query = dev_query.filter(
Device.id.in_(DeviceRepository.query_ids_with(tag_ids=tag_ids)))
gtw_query = gtw_query.filter(
Gateway.id.in_(GatewayRepository.query_ids_with(tag_ids=tag_ids)))
if importances:
dev_query = dev_query.filter(Device.importance.in_(importances))
gtw_query = gtw_query.filter(Gateway.importance.in_(importances))
# Execute the queries, filtering by asset type
if asset_type is None:
all_counts = dev_query.all() + gtw_query.all()
elif asset_type == "device":
all_counts = dev_query.all()
elif asset_type == "gateway":
all_counts = gtw_query.all()
else:
raise Error.BadRequest(
f"Invalid asset_type: {asset_type}. Valid values are \'device\' or \'gateway\'"
)
counts = defaultdict(lambda: {'name': None, 'count': 0})
for entry in all_counts:
counts[entry[0].value]['name'] = entry[0].value
counts[entry[0].value]['count'] += entry[1]
return [{
'id': k,
'name': v['name'],
'count': v['count']
} for k, v in counts.items()]
def count_per_datacollector(organization_id,
vendors=None,
gateway_ids=None,
data_collector_ids=None,
tag_ids=None,
asset_type=None,
importances=None):
""" Count the number of assets per data collector.
Parameters:
- organization_id: which organization.
- vendors[]: for filtering, lists only assets that have ANY one of these vendors.
- gateway_ids[]: for filtering, list only the assets connected to ANY one of these gateways.
- data_collector_ids[]: for filtering, list only the assest related to ANY of these data collectors.
- tag_ids[]: for filtering, list only the assest that have ALL these tags.
- asset_type: for filtering, list only this type of asset ("device" or "gateway").
- importances: for filtering, list only the assets that have ANY of these importances
Returns:
- List of dicts, where each dict has the data_collector id and name and the count
of assets.
"""
# Base queries, one for devices and one for gateways
dev_query = db.session.query(DataCollector.id, DataCollector.name, func.count(distinct(Device.id))).\
select_from(Device).\
join(DataCollector).\
join(GatewayToDevice).\
group_by(DataCollector.id, DataCollector.name).\
filter(DataCollector.organization_id == organization_id).\
filter(Device.pending_first_connection==False)
gtw_query = db.session.query(DataCollector.id, DataCollector.name, func.count(distinct(Gateway.id))).\
select_from(Gateway).\
join(DataCollector).\
group_by(DataCollector.id, DataCollector.name).\
filter(DataCollector.organization_id==organization_id)
# If filtering parameters are given, add the respective where clauses to the queries
if None in vendors:
dev_query = dev_query.filter(
or_(Device.vendor.in_(vendors), Device.vendor.is_(None)))
gtw_query = gtw_query.filter(
or_(Gateway.vendor.in_(vendors), Gateway.vendor.is_(None)))
elif vendors:
dev_query = dev_query.filter(Device.vendor.in_(vendors))
gtw_query = gtw_query.filter(Gateway.vendor.in_(vendors))
if gateway_ids:
dev_query = dev_query.filter(
GatewayToDevice.gateway_id.in_(gateway_ids))
gtw_query = gtw_query.filter(Gateway.id.in_(gateway_ids))
if data_collector_ids:
dev_query = dev_query.filter(DataCollector.id.in_(data_collector_ids))
gtw_query = gtw_query.filter(
Gateway.data_collector_id.in_(data_collector_ids))
if tag_ids:
dev_query = dev_query.filter(
Device.id.in_(DeviceRepository.query_ids_with(tag_ids=tag_ids)))
gtw_query = gtw_query.filter(
Gateway.id.in_(GatewayRepository.query_ids_with(tag_ids=tag_ids)))
if importances:
dev_query = dev_query.filter(Device.importance.in_(importances))
gtw_query = gtw_query.filter(Gateway.importance.in_(importances))
# Execute the queries, filtering by asset type
if asset_type is None:
all_counts = dev_query.all() + gtw_query.all()
elif asset_type == "device":
all_counts = dev_query.all()
elif asset_type == "gateway":
all_counts = gtw_query.all()
else:
raise Error.BadRequest("Invalid device type parameter")
# Join the results of the queries
counts = defaultdict(lambda: {'name': None, 'count': 0})
for e in all_counts:
counts[e[0]]['name'] = e[1]
counts[e[0]]['count'] += e[2]
return [{
'id': k,
'name': v['name'],
'count': v['count']
} for k, v in counts.items()]
def list_all(organization_id,
page=None,
size=None,
vendors=None,
gateway_ids=None,
data_collector_ids=None,
tag_ids=None,
asset_type=None,
importances=None):
""" List assets of an organization.
Parameters:
- organization_id: which organization.
- page: for pagination.
- size: for pagination.
- vendors[]: for filtering, lists only assets that have ANY one of these vendors.
- gateway_ids[]: for filtering, list only the assets connected to ANY one of these gateways.
- data_collector_ids[]: for filtering, list only the assest related to ANY of these data collectors.
- tag_ids[]: for filtering, list only the assest that have ALL these tags.
- asset_type: for filtering, list only this type of asset ("device" or "gateway").
- importances: for filtering, list only the assets that have ANY of these importances
Returns:
- A dict with the list of assets.
"""
# Build two queries, one for devices and one for gateways
dev_query = db.session.query(
distinct(Device.id).label('id'),
Device.dev_eui.label('hex_id'),
expression.literal_column('\'Device\'').label('type'),
Device.join_eui.label('join_eui'),
Device.name,
cast(expression.null(), Float).label('location_latitude'),
cast(expression.null(), Float).label('location_longitude'),
Device.app_name,
DataCollector.name.label('data_collector'),
Device.vendor,
Device.importance,
Device.connected,
Device.first_activity,
Device.last_activity
).select_from(Device).\
join(DataCollector).\
join(GatewayToDevice).\
filter(Device.organization_id==organization_id).\
filter(Device.pending_first_connection==False)
gtw_query = db.session.query(
distinct(Gateway.id).label('id'),
Gateway.gw_hex_id.label('hex_id'),
expression.literal_column('\'Gateway\'').label('type'),
expression.null().label('join_eui'),
Gateway.name,
Gateway.location_latitude,
Gateway.location_longitude,
expression.null().label('app_name'),
DataCollector.name.label('data_collector'),
Gateway.vendor,
Gateway.importance,
Gateway.connected,
Gateway.first_activity,
Gateway.last_activity
).select_from(Gateway).\
join(DataCollector).\
filter(Gateway.organization_id == organization_id)
# If filter parameters were given, add the respective where clauses to the queries
if None in vendors:
dev_query = dev_query.filter(
or_(Device.vendor.in_(vendors), Device.vendor.is_(None)))
gtw_query = gtw_query.filter(
or_(Gateway.vendor.in_(vendors), Gateway.vendor.is_(None)))
elif vendors:
dev_query = dev_query.filter(Device.vendor.in_(vendors))
gtw_query = gtw_query.filter(Gateway.vendor.in_(vendors))
if gateway_ids:
dev_query = dev_query.filter(
GatewayToDevice.gateway_id.in_(gateway_ids))
gtw_query = gtw_query.filter(Gateway.id.in_(gateway_ids))
if data_collector_ids:
dev_query = dev_query.filter(
Device.data_collector_id.in_(data_collector_ids))
gtw_query = gtw_query.filter(
Gateway.data_collector_id.in_(data_collector_ids))
if tag_ids:
dev_query = dev_query.filter(
Device.id.in_(DeviceRepository.query_ids_with(tag_ids=tag_ids)))
gtw_query = gtw_query.filter(
Gateway.id.in_(GatewayRepository.query_ids_with(tag_ids=tag_ids)))
if importances:
dev_query = dev_query.filter(Device.importance.in_(importances))
gtw_query = gtw_query.filter(Gateway.importance.in_(importances))
# Filter by device type if the parameter was given, else, make a union with queries.
if asset_type is None:
asset_query = dev_query.union(gtw_query)
elif asset_type == "device":
asset_query = dev_query
elif asset_type == "gateway":
asset_query = gtw_query
else:
raise Error.BadRequest("Invalid device type parameter")
asset_query = asset_query.order_by(text('type desc, id'))
if page and size:
return asset_query.paginate(page=page, per_page=size, error_out=False)
else:
return asset_query.all()
def list_all(organization_id,
page=None,
size=None,
asset_type=None,
asset_status=None,
data_collector_ids=None,
gateway_ids=None,
device_ids=None,
min_signal_strength=None,
max_signal_strength=None,
min_packet_loss=None,
max_packet_loss=None):
""" List assets of an organization and their resource usage information.
Parameters:
- asset_type: for filtering, count only this type of asset ("device" or "gateway").
- asset_status: for filtering, count only assets with this status ("connected" or "disconnected").
- data_collector_ids[]: for filtering, count only the assets belongs to specific data collectors.
- gateway_ids[]: for filtering, count only the assets connected to ANY one of these gateways.
- device_ids[]: for filtering, list only the assets related to ANY of these devices
- min_signal_strength: for filtering, count only the assets with signal strength not lower than this value (dBm)
- max_signal_strength: for filtering, count only the assets with signal strength not higher than this value (dBm)
- min_packet_loss: for filtering, count only the assets with packet loss not lower than this value (percentage)
- max_packet_loss: for filtering, count only the assets with packet loss not higher than this value (percentage)
Returns:
- Dict with the list of assets.
"""
last_dev_addrs = db.session.\
query(DeviceSession.device_id, func.max(DeviceSession.last_activity).label('last_activity')).\
group_by(DeviceSession.device_id).\
subquery()
# Build two queries, one for devices and one for gateways
dev_query = db.session.query(
Device.id.label('id'),
Device.dev_eui.label('hex_id'),
DeviceSession.dev_addr.label('dev_addr'),
expression.literal_column('\'Device\'').label('type'),
Device.name,
Device.app_name,
DataCollector.name.label('data_collector'),
PolicyItem.parameters.label('policy_parameters'),
Device.connected.label('connected'),
Device.last_activity,
Device.activity_freq,
Device.activity_freq_variance,
Device.npackets_up,
Device.npackets_down,
Device.npackets_lost.label('packet_loss'),
Device.max_rssi,
Device.max_lsnr,
Device.payload_size,
Device.ngateways_connected_to
).select_from(Device).\
filter(Device.organization_id==organization_id).\
filter(Device.pending_first_connection==False).\
join(DataCollector, Device.data_collector_id == DataCollector.id).\
join(Policy, Policy.id == DataCollector.policy_id).\
join(PolicyItem, and_(Policy.id == PolicyItem.policy_id, PolicyItem.alert_type_code == 'LAF-401')).\
join(last_dev_addrs, Device.id == last_dev_addrs.c.device_id).\
join(DeviceSession, and_(DeviceSession.device_id == Device.id, DeviceSession.last_activity == last_dev_addrs.c.last_activity)).\
join(RowProcessed, RowProcessed.analyzer == 'packet_analyzer').\
join(Packet, Packet.id == RowProcessed.last_row).\
join(DeviceCounters, and_(
DeviceCounters.device_id == Device.id,
DeviceCounters.counter_type.in_(dev_wanted_counters),
DeviceCounters.last_update + func.make_interval(0,0,0,1) > Packet.date
), isouter=True).\
group_by(Device.id, DeviceSession.dev_addr, DataCollector.name, PolicyItem.parameters)
gtw_query = db.session.query(
distinct(Gateway.id).label('id'),
Gateway.gw_hex_id.label('hex_id'),
expression.null().label('dev_addr'),
expression.literal_column('\'Gateway\'').label('type'),
Gateway.name,
expression.null().label('app_name'),
DataCollector.name.label('data_collector'),
expression.null().label('policy_parameters'),
Gateway.connected.label('connected'),
Gateway.last_activity,
Gateway.activity_freq,
cast(expression.null(), Float).label('activity_freq_variance'),
Gateway.npackets_up,
Gateway.npackets_down,
cast(expression.null(), Float).label('packet_loss'),
cast(expression.null(), Float).label('max_rssi'),
cast(expression.null(), Float).label('max_lsnr'),
cast(expression.null(), Float).label('payload_size'),
cast(expression.null(), Float).label('ngateways_connected_to')
).select_from(Gateway).\
filter(Gateway.organization_id == organization_id).\
join(DataCollector, Gateway.data_collector_id == DataCollector.id).\
join(RowProcessed, RowProcessed.analyzer == 'packet_analyzer').\
join(Packet, Packet.id == RowProcessed.last_row).\
join(GatewayCounters, and_(
GatewayCounters.gateway_id == Gateway.id,
GatewayCounters.counter_type.in_(gtw_wanted_counters),
GatewayCounters.last_update + func.make_interval(0,0,0,1) > Packet.date
), isouter=True).\
group_by(Gateway.id, DataCollector.name)
# Add a column for every counter type to each query, using the wanted_counters lists
dev_query = add_counters_columns(dev_query, dev_wanted_counters,
DeviceCounters)
gtw_query = add_counters_columns(gtw_query, gtw_wanted_counters,
GatewayCounters)
queries = add_filters(dev_query=dev_query,
gtw_query=gtw_query,
asset_type=asset_type,
asset_status=asset_status,
data_collector_ids=data_collector_ids,
gateway_ids=gateway_ids,
device_ids=device_ids,
min_signal_strength=min_signal_strength,
max_signal_strength=max_signal_strength,
min_packet_loss=min_packet_loss,
max_packet_loss=max_packet_loss)
dev_query = queries[0]
gtw_query = queries[1]
# Filter by device type if the parameter was given, else, make a union with queries.
if asset_type is None:
asset_query = dev_query.union(gtw_query)
elif asset_type == "device":
asset_query = dev_query
elif asset_type == "gateway":
asset_query = gtw_query
else:
raise Error.BadRequest("Invalid asset type parameter")
asset_query = asset_query.order_by(text('type desc, connected desc, id'))
if page and size:
return asset_query.paginate(page=page, per_page=size, error_out=False)
else:
return asset_query.all()
def search(organization_id,
page=None,
size=None,
page_ids=None,
size_ids=None,
search_param="",
asset_type=None,
asset_status=None,
data_collector_ids=None,
gateway_ids=None,
tag_ids=None,
importances=None):
""" Get assets and devices filtered by string search param that found in multiple fields of the asset.
Also you can pass other params for filtering - keep this params for future filters features.
Args:
organization_id (integer): Filter by assets belongs to this organization id
page (integer, optional): [description]. Defaults to 1.
size (integer, optional): [description]. Defaults to 3.
page_ids (integer, optional): [description]. Defaults to 1.
size_ids (integer, optional): [description]. Defaults to 20.
search_param (string, optional): A string used for find coincidence in vendor, hexid, datasource join eui and app name. Defaults to "".
asset_type (string, optional): Filter assets for only show devices or gateways. Defaults to None.
asset_status (string, optional): Filter assets by 'connected' or 'disconnected'. Defaults to None.
data_collector_ids (integer, optional): Filter assets belongs to data collector ids. Defaults to None.
gateway_ids (integer, optional): Filter devices connected to gateways ids or filter by specific gateway ids . Defaults to None.
tag_ids (integer, optional): Filter assets that has this tags ids. Defaults to None.
importances (string, optional): A string for filter by importance. Defaults to None.
Returns:
[dict]: return a dict with keys:
"devices": a list of devices that coincide with the search criteria,
"device_ids": a list of ids of devices that coincide with the search critera,
"gateways": a list of gateways that coincide with the search criteria,
"device_ids": a list of ids of gateways that coincide with the search critera,
}
"""
# Build two queries, one for devices and one for gateways
dev_query = db.session.query(
distinct(Device.id).label('id'),
Device.dev_eui.label('hex_id'),
expression.literal_column('\'Device\'').label('type'),
Device.join_eui.label('join_eui'),
Device.name,
cast(expression.null(), Float).label('location_latitude'),
cast(expression.null(), Float).label('location_longitude'),
Device.app_name,
DataCollector.name.label('data_collector'),
Device.vendor,
#Device.importance,
Device.connected,
#Device.first_activity,
#Device.last_activity
).select_from(Device).\
join(DataCollector).\
join(GatewayToDevice).\
filter(Device.organization_id==organization_id).\
filter(Device.pending_first_connection==False)
dev_query_ids = db.session.query(distinct(Device.id).label('id')).select_from(Device).\
join(DataCollector).\
join(GatewayToDevice).\
filter(Device.organization_id==organization_id).\
filter(Device.pending_first_connection==False)
gtw_query = db.session.query(
distinct(Gateway.id).label('id'),
Gateway.gw_hex_id.label('hex_id'),
expression.literal_column('\'Gateway\'').label('type'),
expression.null().label('join_eui'),
Gateway.name,
Gateway.location_latitude,
Gateway.location_longitude,
expression.null().label('app_name'),
DataCollector.name.label('data_collector'),
Gateway.vendor,
#Gateway.importance,
Gateway.connected,
#Gateway.first_activity,
#Gateway.last_activity
).select_from(Gateway).\
join(DataCollector).\
filter(Gateway.organization_id == organization_id)
if gateway_ids:
dev_query = dev_query.filter(
GatewayToDevice.gateway_id.in_(gateway_ids))
gtw_query = gtw_query.filter(Gateway.id.in_(gateway_ids))
if data_collector_ids:
dev_query = dev_query.filter(
Device.data_collector_id.in_(data_collector_ids))
gtw_query = gtw_query.filter(
Gateway.data_collector_id.in_(data_collector_ids))
if tag_ids:
dev_query = dev_query.filter(
Device.id.in_(DeviceRepository.query_ids_with(tag_ids=tag_ids)))
gtw_query = gtw_query.filter(
Gateway.id.in_(GatewayRepository.query_ids_with(tag_ids=tag_ids)))
#if importances:
# dev_query = dev_query.filter(Device.importance.in_(importances))
# gtw_query = gtw_query.filter(Gateway.importance.in_(importances))
# Filter by device type if the parameter was given, else, make a union with queries.
# if asset_type is None:
# asset_query = dev_query.union(gtw_query)
# elif asset_type == "device":
# asset_query = dev_query
# elif asset_type == "gateway":
# asset_query = gtw_query
# else:
# raise Error.BadRequest("Invalid device type parameter")
if asset_status == 'connected':
dev_query = dev_query.filter(Device.connected)
gtw_query = gtw_query.filter(Gateway.connected)
elif asset_status == 'disconnected':
dev_query = dev_query.filter(not_(Device.connected))
gtw_query = gtw_query.filter(not_(Gateway.connected))
elif asset_status is not None:
raise Error.BadRequest("Invalid asset status parameter")
#asset_query = asset_query.order_by(text('type desc, id'))
gtw_query_ids = db.session.query(distinct(Gateway.id).label('id')).select_from(Gateway).\
join(DataCollector).\
filter(Gateway.organization_id == organization_id)
# If filter parameters were given, add the respective where clauses to the queries
if search_param:
search_param_device_condition = or_(\
Device.vendor.ilike(f'%{search_param}%'),\
Device.dev_eui.ilike(f'%{search_param}%'),\
Device.name.ilike(f'%{search_param}%'),\
Device.join_eui.ilike(f'%{search_param}%'),\
Device.app_name.ilike(f'%{search_param}%'),\
DataCollector.name.ilike(f'%{search_param}%')
)
dev_query = dev_query.filter(search_param_device_condition)
dev_query_ids = dev_query_ids.filter(search_param_device_condition)
search_param_gateway_condition = or_(
Gateway.vendor.ilike(f'%{search_param}%'),\
Gateway.gw_hex_id.ilike(f'%{search_param}%'),\
Gateway.name.ilike(f'%{search_param}%'),\
DataCollector.name.ilike(f'%{search_param}%')
)
gtw_query = gtw_query.filter(search_param_gateway_condition)
gtw_query_ids = gtw_query_ids.filter(search_param_gateway_condition)
if page and size:
return {
"devices":
dev_query.paginate(page=page, per_page=size, error_out=False),
"device_ids":
dev_query_ids.paginate(page=page_ids,
per_page=size_ids,
error_out=False),
"gateways":
gtw_query.paginate(page=page, per_page=size, error_out=False),
"gateway_ids":
gtw_query_ids.paginate(page=page_ids,
per_page=size_ids,
error_out=False),
}
else:
return {
"devices": [],
"device_ids": [],
"gateways": [],
"gateways_ids": []
}