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 list(self, service_filter={}):
service_bundle = Bundle('service',
Service.id, Service.name, Service.description, Service.protocol,
Service.status, Service.ports, Service.version, Service.owned,
Service.interface_id,
func.count(distinct(Vulnerability.id)).label('vuln_count'), 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.count(distinct(Credential.id)).label("credentials_count"))
query = self._session.query(service_bundle).\
group_by(Service.id).\
outerjoin(EntityMetadata, EntityMetadata.id == Service.entity_metadata_id).\
outerjoin(Vulnerability, Service.id == Vulnerability.service_id).group_by(Service.id).\
outerjoin(Interface, Interface.id == Service.interface_id).\
outerjoin(Credential, (Credential.service_id == Service.id) and (Credential.host_id == None)).\
outerjoin(Host, Host.id == Interface.host_id)
query = apply_search_filter(query, self.COLUMNS_MAP, None,
service_filter, self.STRICT_FILTERING)
# 'LIKE' for search services started by hostId.%.%
if service_filter.get('hostIdCouchdb') is not None:
query = query.filter(
EntityMetadata.couchdb_id.like(
service_filter.get('hostIdCouchdb') + ".%.%"))
raw_services = query.all()
services = [self.__get_service_data(r.service) for r in raw_services]
result = {'services': services}
return result
def __query_database(self, search=None, cred_filter={}):
creds_bundle = Bundle('cred', Credential.username, Credential.password, Credential.name,
Credential.description, Credential.owned, 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)
query = self._session.query(creds_bundle)\
.outerjoin(EntityMetadata, EntityMetadata.id == Credential.entity_metadata_id)
# Apply filtering options to the query
query = apply_search_filter(query, self.COLUMNS_MAP, search,
cred_filter, self.STRICT_FILTERING)
# I apply a custom filter for search by hostId and serviceId.
# 'LIKE' for search by serviceId.%, that return only credentials started with serviceId.
if cred_filter.get('service_id') is not None:
query = query.filter(
EntityMetadata.couchdb_id.like(
cred_filter.get('service_id') + ".%"))
# 'LIKE' for search by hostId.%, with that LIKE we receive credentials of services also.
# I need another like for filter credentials of services (%.%.%)
if cred_filter.get('host_id') is not None:
query = query.filter(
EntityMetadata.couchdb_id.like(
cred_filter.get('host_id') + ".%")).filter(
not_(EntityMetadata.couchdb_id.like("%.%.%")))
results = query.all()
return results
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 stats_apps():
require_admin()
last_months = [datetime.date.today().replace(day=1)]
first_month = db.session.query(sql.func.min(StatsLookups.date)).scalar()
if first_month is not None:
first_month = first_month.replace(day=1)
while last_months[-1] > first_month:
month = last_months[-1] - datetime.timedelta(1)
last_months.append(month.replace(day=1))
month_str = request.args.get('month')
if month_str:
month = datetime.datetime.strptime(month_str, '%Y-%m').date()
else:
month = last_months[0]
counts = Bundle(
'counts',
sql.func.sum(StatsLookups.count_hits).label('hits'),
sql.func.sum(StatsLookups.count_nohits).label('misses'),
sql.func.sum(StatsLookups.count_nohits +
StatsLookups.count_hits).label('all'))
stats = db.session.query(Application, counts).join(
StatsLookups.application).filter(
StatsLookups.date >= sql.func.date_trunc('month', month),
StatsLookups.date < sql.func.date_trunc('month', month) +
sql.text("INTERVAL '1 month'")).group_by(Application.id).order_by(
counts.c.all.desc()).all()
return render_template('admin_stats_apps.html',
stats=stats,
last_months=last_months,
month=month)
def __query_database(self, search=None, note_filter={}):
note_bundle = Bundle('note', Note.id, Note.name, Note.text, Note.description, Note.owned, 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)
query = self._session.query(note_bundle)\
.outerjoin(EntityMetadata, EntityMetadata.id == Note.entity_metadata_id)
# Apply filtering options to the query
query = apply_search_filter(query, self.COLUMNS_MAP, search, note_filter, self.STRICT_FILTERING)
results = query.all()
return results
def __query_database(self, search=None, cred_filter={}):
creds_bundle = Bundle('cred', Credential.username, Credential.password, Credential.name,
Credential.description, Credential.owned, 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)
query = self._session.query(creds_bundle)\
.outerjoin(EntityMetadata, EntityMetadata.id == Credential.entity_metadata_id)
# Apply filtering options to the query
query = apply_search_filter(query, self.COLUMNS_MAP, search,
cred_filter, self.STRICT_FILTERING)
results = query.all()
return results
def list(self, interface_filter={}):
interface_bundle = Bundle('interface',
Interface.id, Interface.name, Interface.description, Interface.mac,
Interface.owned, Interface.hostnames, Interface.network_segment, Interface.ipv4_address,
Interface.ipv4_gateway, Interface.ipv4_dns, Interface.ipv4_mask, Interface.ipv6_address,
Interface.ipv6_gateway, Interface.ipv6_dns, Interface.ipv6_prefix, Interface.ports_filtered,
Interface.ports_opened, Interface.ports_closed, Interface.host_id, 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)
query = self._session.query(interface_bundle).\
outerjoin(EntityMetadata, EntityMetadata.id == Interface.entity_metadata_id)
query = apply_search_filter(query, self.COLUMNS_MAP, None, interface_filter, self.STRICT_FILTERING)
raw_interfaces = query.all()
interfaces = [self.__get_interface_data(r.interface) for r in raw_interfaces]
result = {'interfaces': interfaces}
return result
def __query_database(self, search=None, command_filter={}):
command_bundle = Bundle('command',
Command.itime,
Command.ip,
Command.hostname,
Command.command,
Command.user,
Command.workspace,
Command.duration,
Command.params,
EntityMetadata.couchdb_id)
query = self._session.query(command_bundle)\
.outerjoin(EntityMetadata, EntityMetadata.id == Command.entity_metadata_id)
# Apply filtering options to the query
query = apply_search_filter(query, self.COLUMNS_MAP, None, command_filter, self.STRICT_FILTERING)
results = query.all()
return results
def count(self, group_by=None, search=None, vuln_filter={}):
query = self._session.query(Vulnerability.vuln_type, func.count())\
.group_by(Vulnerability.vuln_type)
query = apply_search_filter(query, self.COLUMNS_MAP, search,
vuln_filter)
total_count = dict(query.all())
# Return total amount of services if no group-by field was provided
result_count = {
'total_count': sum(total_count.values()),
'web_vuln_count': total_count.get('VulnerabilityWeb', 0),
'vuln_count': total_count.get('Vulnerability', 0),
}
if group_by is None:
return result_count
# Otherwise return the amount of services grouped by the field specified
# Don't perform group-by counting on fields with less or more than 1 column mapped to it
if group_by not in VulnerabilityDAO.COLUMNS_MAP or\
len(VulnerabilityDAO.COLUMNS_MAP.get(group_by)) != 1:
return None
col = VulnerabilityDAO.COLUMNS_MAP.get(group_by)[0]
vuln_bundle = Bundle('vuln', Vulnerability.id, col)
query = self._session.query(vuln_bundle, func.count())\
.group_by(col)\
.outerjoin(EntityMetadata, EntityMetadata.id == Vulnerability.entity_metadata_id)
query = apply_search_filter(query, self.COLUMNS_MAP, search,
vuln_filter, self.STRICT_FILTERING)
result = query.all()
result_count['groups'] = [{
group_by: value[1],
'count': count
} for value, count in result]
return result_count
def list(self, service_filter={}):
service_bundle = Bundle('service',
Service.id, Service.name, Service.description, Service.protocol,
Service.status, Service.ports, Service.version, Service.owned,
Service.interface_id,
func.count(distinct(Vulnerability.id)).label('vuln_count'), 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)
query = self._session.query(service_bundle).\
group_by(Service.id).\
outerjoin(EntityMetadata, EntityMetadata.id == Service.entity_metadata_id).\
outerjoin(Vulnerability, Service.id == Vulnerability.service_id).group_by(Service.id).\
outerjoin(Interface, Interface.id == Service.interface_id).\
outerjoin(Host, Host.id == Interface.host_id)
query = apply_search_filter(query, self.COLUMNS_MAP, None,
service_filter, self.STRICT_FILTERING)
raw_services = query.all()
services = [self.__get_service_data(r.service) for r in raw_services]
result = {'services': services}
return result