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 __specialized_sort(self, query, order_by, order_dir):
""" Before using sort_results(), handle special ordering cases
for some fields """
if order_by == 'severity':
# For severity only, we choose a risk-based ordering
# instead of a lexicographycally one
column_map = {
'severity': [
case(
{
'unclassified': 0,
'info': 1,
'low': 2,
'med': 3,
'high': 4,
'critical': 5
},
value=Vulnerability.severity)
]
}
else:
column_map = self.COLUMNS_MAP
return sort_results(query,
column_map,
order_by,
order_dir,
default=Vulnerability.id)
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 __specialized_sort(self, query, order_by, order_dir):
""" Before using sort_results(), handle special ordering cases
for some fields """
if order_by == "severity":
# For severity only, we choose a risk-based ordering
# instead of a lexicographycally one
column_map = {
"severity": [
case(
{"unclassified": 0, "info": 1, "low": 2, "med": 3, "high": 4, "critical": 5},
value=Vulnerability.severity,
)
]
}
else:
column_map = self.COLUMNS_MAP
return sort_results(query, column_map, order_by, order_dir, default=Vulnerability.id)
def __specialized_sort(self, query, order_by, order_dir):
""" Before using sort_results(), handle special ordering cases
for some fields """
if order_by == 'severity':
# For severity only, we choose a risk-based ordering
# instead of a lexicographycally one
column_map = {
'severity': [case(
{ 'unclassified': 0,
'info': 1,
'low': 2,
'med': 3,
'high': 4,
'critical': 5 },
value=Vulnerability.severity
)]
}
else:
column_map = self.COLUMNS_MAP
return sort_results(query, column_map, order_by, order_dir, default=Vulnerability.id)