def __query_database(self, search=None, page=0, page_size=0, order_by=None, order_dir=None, host_filter={}):
host_bundle = Bundle('host', Host.id, Host.name, Host.os, Host.description, Host.owned,\
Host.default_gateway_ip, Host.default_gateway_mac, EntityMetadata.couchdb_id,\
EntityMetadata.revision, EntityMetadata.update_time, EntityMetadata.update_user,\
EntityMetadata.update_action, EntityMetadata.creator, EntityMetadata.create_time,\
EntityMetadata.update_controller_action, EntityMetadata.owner, EntityMetadata.command_id,\
func.group_concat(distinct(Interface.id)).label('interfaces'),\
func.count(distinct(Vulnerability.id)).label('vuln_count'),\
func.count(distinct(Service.id)).label('open_services_count'))
query = self._session.query(host_bundle)\
.outerjoin(EntityMetadata, EntityMetadata.id == Host.entity_metadata_id)\
.outerjoin(Interface, Host.id == Interface.host_id)\
.outerjoin(Vulnerability, Host.id == Vulnerability.host_id)\
.outerjoin(Service, (Host.id == Service.host_id) & (Service.status.in_(('open', 'running', 'opened'))))\
.group_by(Host.id)
# Apply pagination, sorting and filtering options to the query
query = sort_results(query, self.COLUMNS_MAP, order_by, order_dir, default=Host.id)
query = apply_search_filter(query, self.COLUMNS_MAP, search, host_filter, self.STRICT_FILTERING)
count = get_count(query, count_col=Host.id)
if page_size:
query = paginate(query, page, page_size)
results = query.all()
return results, count
def __query_database(self, search=None, page=0, page_size=0, order_by=None, order_dir=None, host_filter={}):
host_bundle = Bundle('host', Host.id, Host.name, Host.os, Host.description, Host.owned,\
Host.default_gateway_ip, Host.default_gateway_mac, EntityMetadata.couchdb_id,\
EntityMetadata.revision, EntityMetadata.update_time, EntityMetadata.update_user,\
EntityMetadata.update_action, EntityMetadata.creator, EntityMetadata.create_time,\
EntityMetadata.update_controller_action, EntityMetadata.owner, EntityMetadata.command_id,\
func.group_concat(distinct(Interface.id)).label('interfaces'),\
func.count(distinct(Vulnerability.id)).label('vuln_count'),\
func.count(distinct(Service.id)).label('open_services_count'))
query = self._session.query(host_bundle)\
.outerjoin(EntityMetadata, EntityMetadata.id == Host.entity_metadata_id)\
.outerjoin(Interface, Host.id == Interface.host_id)\
.outerjoin(Vulnerability, Host.id == Vulnerability.host_id)\
.outerjoin(Service, (Host.id == Service.host_id) & (Service.status.in_(('open', 'running', 'opened'))))\
.group_by(Host.id)
# Apply pagination, sorting and filtering options to the query
query = sort_results(query, self.COLUMNS_MAP, order_by, order_dir, default=Host.id)
query = apply_search_filter(query, self.COLUMNS_MAP, search, host_filter, self.STRICT_FILTERING)
count = get_count(query, count_col=Host.id)
if page_size:
query = paginate(query, page, page_size)
results = query.all()
return results, count
def __query_database(self,
search=None,
page=0,
page_size=0,
order_by=None,
order_dir=None,
vuln_filter={}):
# Instead of using SQLAlchemy ORM facilities to fetch rows, we bundle involved columns for
# organizational and MAINLY performance reasons. Doing it this way, we improve retrieving
# times from large workspaces almost 2x.
vuln_bundle = Bundle('vuln', Vulnerability.id.label('server_id'),Vulnerability.name.label('v_name'),\
Vulnerability.confirmed, Vulnerability.data,\
Vulnerability.description, Vulnerability.easeofresolution, Vulnerability.impact_accountability,\
Vulnerability.impact_availability, Vulnerability.impact_confidentiality, Vulnerability.impact_integrity,\
Vulnerability.refs, Vulnerability.resolution, Vulnerability.severity, Vulnerability.owned, Vulnerability.status,\
Vulnerability.website, Vulnerability.path, Vulnerability.request, Vulnerability.response,\
Vulnerability.method, Vulnerability.params, Vulnerability.pname, Vulnerability.query,\
EntityMetadata.couchdb_id, EntityMetadata.revision, EntityMetadata.create_time, EntityMetadata.creator,\
EntityMetadata.owner, EntityMetadata.update_action, EntityMetadata.update_controller_action,\
EntityMetadata.update_time, EntityMetadata.update_user, EntityMetadata.document_type, EntityMetadata.command_id, \
Vulnerability.attachments, Vulnerability.policyviolations)
service_bundle = Bundle('service', Service.name.label('s_name'),
Service.ports, Service.protocol, Service.id)
host_bundle = Bundle('host', Host.name)
# IMPORTANT: OUTER JOINS on those tables is IMPERATIVE. Changing them could result in loss of
# data. For example, on vulnerabilities not associated with any service and instead to its host
# directly.
query = self._session.query(vuln_bundle,
service_bundle,
host_bundle,
func.group_concat(Interface.hostnames))\
.group_by(Vulnerability.id)\
.outerjoin(EntityMetadata, EntityMetadata.id == Vulnerability.entity_metadata_id)\
.outerjoin(Service, Service.id == Vulnerability.service_id)\
.outerjoin(Host, Host.id == Vulnerability.host_id)\
.join(Interface, Interface.host_id == Host.id)
# Apply pagination, sorting and filtering options to the query
query = self.__specialized_sort(query, order_by, order_dir)
query = apply_search_filter(query, self.COLUMNS_MAP, search,
vuln_filter, self.STRICT_FILTERING)
count = get_count(query, count_col=Vulnerability.id)
if page_size:
query = paginate(query, page, page_size)
results = query.all()
return results, count
def __query_database(self, search=None, page=0, page_size=0, order_by=None, order_dir=None, vuln_filter={}):
# Instead of using SQLAlchemy ORM facilities to fetch rows, we bundle involved columns for
# organizational and MAINLY performance reasons. Doing it this way, we improve retrieving
# times from large workspaces almost 2x.
vuln_bundle = Bundle('vuln', Vulnerability.id.label('server_id'),Vulnerability.name.label('v_name'),\
Vulnerability.confirmed, Vulnerability.data,\
Vulnerability.description, Vulnerability.easeofresolution, Vulnerability.impact_accountability,\
Vulnerability.impact_availability, Vulnerability.impact_confidentiality, Vulnerability.impact_integrity,\
Vulnerability.refs, Vulnerability.resolution, Vulnerability.severity, Vulnerability.owned, Vulnerability.status,\
Vulnerability.website, Vulnerability.path, Vulnerability.request, Vulnerability.response,\
Vulnerability.method, Vulnerability.params, Vulnerability.pname, Vulnerability.query,\
EntityMetadata.couchdb_id, EntityMetadata.revision, EntityMetadata.create_time, EntityMetadata.creator,\
EntityMetadata.owner, EntityMetadata.update_action, EntityMetadata.update_controller_action,\
EntityMetadata.update_time, EntityMetadata.update_user, EntityMetadata.document_type, EntityMetadata.command_id, \
Vulnerability.attachments, Vulnerability.policyviolations)
service_bundle = Bundle('service', Service.name.label('s_name'), Service.ports, Service.protocol, Service.id)
host_bundle = Bundle('host', Host.name)
# IMPORTANT: OUTER JOINS on those tables is IMPERATIVE. Changing them could result in loss of
# data. For example, on vulnerabilities not associated with any service and instead to its host
# directly.
query = self._session.query(vuln_bundle,
service_bundle,
host_bundle,
func.group_concat(Interface.hostnames))\
.group_by(Vulnerability.id)\
.outerjoin(EntityMetadata, EntityMetadata.id == Vulnerability.entity_metadata_id)\
.outerjoin(Service, Service.id == Vulnerability.service_id)\
.outerjoin(Host, Host.id == Vulnerability.host_id)\
.join(Interface, Interface.host_id == Host.id)
# Apply pagination, sorting and filtering options to the query
query = self.__specialized_sort(query, order_by, order_dir)
query = apply_search_filter(query, self.COLUMNS_MAP, search, vuln_filter, self.STRICT_FILTERING)
count = get_count(query)
if page_size:
query = paginate(query, page, page_size)
results = query.all()
return results, count