def get_package_data(pkgname):
entries = (db.session.query(
Package, CVEGroup, CVE,
Advisory).filter(Package.name == pkgname).outerjoin(
CVEGroupPackage, CVEGroupPackage.pkgname ==
Package.name).outerjoin(CVEGroup, CVEGroupPackage.group).outerjoin(
CVEGroupEntry,
CVEGroup.issues).outerjoin(CVE, CVEGroupEntry.cve).outerjoin(
Advisory,
and_(
Advisory.group_package_id == CVEGroupPackage.id,
Advisory.publication == Publication.published))).all()
# fallback for dropped packages
if not entries:
entries = (db.session.query(
CVEGroupPackage, CVEGroup, CVE,
Advisory).filter(CVEGroupPackage.pkgname == pkgname).join(
CVEGroup, CVEGroupPackage.group).join(
CVEGroupEntry,
CVEGroup.issues).join(CVE, CVEGroupEntry.cve).outerjoin(
Advisory,
and_(Advisory.group_package_id == CVEGroupPackage.id,
Advisory.publication == Publication.published))
).all()
if not entries:
return None
groups = set()
issues = set()
advisories = set()
versions = set()
for package, group, cve, advisory in entries:
if isinstance(package, Package):
versions.add(package)
if group:
groups.add(group)
if cve:
issues.add((cve, group))
if advisory:
advisories.add(advisory)
issues = [{'issue': e[0], 'group': e[1]} for e in issues]
issues = sorted(issues, key=lambda item: item['issue'], reverse=True)
issues = sorted(issues, key=lambda item: item['group'].status)
groups = sorted(groups, key=lambda item: item.id, reverse=True)
groups = sorted(groups, key=lambda item: item.status)
advisories = sorted(advisories, key=lambda item: item.id, reverse=True)
versions = filter_duplicate_packages(sort_packages(list(versions)), True)
package = versions[0] if versions else None
return {
'package': package,
'pkgname': pkgname,
'versions': versions,
'groups': groups,
'issues': issues,
'advisories': advisories
}
def get_group_data(avg):
avg_id = int(avg.replace('AVG-', ''))
entries = (db.session.query(
CVEGroup, CVE, CVEGroupPackage,
Advisory, Package).filter(CVEGroup.id == avg_id).join(
CVEGroupEntry, CVEGroup.issues).join(CVE, CVEGroupEntry.cve).join(
CVEGroupPackage, CVEGroup.packages).outerjoin(
Package,
Package.name == CVEGroupPackage.pkgname).outerjoin(
Advisory,
Advisory.group_package_id == CVEGroupPackage.id)
).all()
if not entries:
return None
group = None
issues = set()
packages = set()
advisories = set()
issue_types = set()
versions = set()
for group_entry, cve, pkg, advisory, package in entries:
group = group_entry
issues.add(cve)
issue_types.add(cve.issue_type)
packages.add(pkg)
if package:
versions.add(package)
if advisory:
advisories.add(advisory)
advisories = sorted(advisories, key=lambda item: item.id, reverse=True)
issue_types = list(issue_types)
issues = sorted(issues, key=lambda item: item, reverse=True)
packages = sorted(packages, key=lambda item: item.pkgname)
versions = filter_duplicate_packages(sort_packages(list(versions)), True)
advisories_pending = group.status == Status.fixed and group.advisory_qualified and len(
advisories) <= 0
return {
'group': group,
'packages': packages,
'versions': versions,
'issues': issues,
'issue_types': issue_types,
'advisories': advisories,
'advisories_pending': advisories_pending
}
def get_todo_data():
incomplete_advisories = (db.session.query(
Advisory, CVEGroupPackage,
CVEGroup).join(CVEGroupPackage, Advisory.group_package).join(
CVEGroup, CVEGroupPackage.group).filter(
and_(
Advisory.publication == Publication.published,
or_(Advisory.content == '',
Advisory.content.is_(None), Advisory.reference == '',
Advisory.reference.is_(None)))).group_by(
CVEGroupPackage.id).order_by(
Advisory.created.desc())).all()
scheduled_advisories = (db.session.query(
Advisory, CVEGroupPackage,
CVEGroup).join(CVEGroupPackage, Advisory.group_package).join(
CVEGroup, CVEGroupPackage.group).filter(
Advisory.publication == Publication.scheduled).group_by(
CVEGroupPackage.id).order_by(
Advisory.created.desc())).all()
unhandled_advisories = (db.session.query(CVEGroup, Package).join(
CVEGroupPackage, CVEGroup.packages).join(
Package, Package.name == CVEGroupPackage.pkgname).outerjoin(
Advisory).filter(CVEGroup.advisory_qualified).filter(
CVEGroup.status == Status.fixed).group_by(
CVEGroup.id).group_by(CVEGroupPackage.id).having(
func.count(Advisory.id) == 0).order_by(
CVEGroup.id)).all()
unhandled_advisories_data = defaultdict(list)
for group, package in unhandled_advisories:
unhandled_advisories_data[group].append(package)
unhandled_advisories = [
(group, packages)
for group, packages in unhandled_advisories_data.items()
]
unhandled_advisories = sorted(unhandled_advisories,
key=lambda item: item[0].id)
unhandled_advisories = sorted(unhandled_advisories,
key=lambda item: item[0].severity)
unknown_issues = (db.session.query(CVE).filter(
or_(CVE.remote == Remote.unknown, CVE.severity == Severity.unknown,
CVE.description.is_(None), CVE.description == '',
CVE.issue_type.is_(None),
CVE.issue_type == 'unknown')).order_by(CVE.id.desc())).all()
unknown_groups = CVEGroup.query.filter(
CVEGroup.status == Status.unknown).all()
unknown_groups = (db.session.query(CVEGroup, Package).join(
CVEGroupPackage, CVEGroup.packages).join(
Package, Package.name == CVEGroupPackage.pkgname).filter(
CVEGroup.status == Status.unknown).group_by(
CVEGroupPackage.id).order_by(
CVEGroup.created.desc())).all()
unknown_groups_data = defaultdict(list)
for group, package in unknown_groups:
unknown_groups_data[group].append(package)
unknown_groups = []
for group, packages in unknown_groups_data.items():
unknown_groups.append((group, packages))
unknown_groups = sorted(unknown_groups, key=lambda item: item[0].id)
vulnerable_groups = (db.session.query(CVEGroup, Package).join(
CVEGroupPackage, CVEGroup.packages).join(
Package, Package.name == CVEGroupPackage.pkgname).filter(
CVEGroup.status == Status.vulnerable).filter(
or_(CVEGroup.fixed.is_(None),
CVEGroup.fixed == '')).group_by(CVEGroup.id).group_by(
Package.name, Package.version).order_by(
CVEGroup.created.desc())).all()
vulnerable_group_data = defaultdict(list)
for group, package in vulnerable_groups:
vulnerable_group_data[group].append(package)
bumped_groups = []
for group, packages in vulnerable_group_data.items():
packages = sorted(packages,
key=cmp_to_key(vercmp, attrgetter('version')),
reverse=True)
if 0 == vercmp(group.affected, packages[0].version):
continue
versions = filter_duplicate_packages(packages, filter_arch=True)
pkgnames = set([pkg.name for pkg in packages])
bumped_groups.append((group, pkgnames, versions))
bumped_groups = sorted(bumped_groups,
key=lambda item: item[0].id,
reverse=True)
bumped_groups = sorted(bumped_groups, key=lambda item: item[0].severity)
orphan_issues = (db.session.query(CVE).outerjoin(CVEGroupEntry).group_by(
CVE.id).having(func.count(CVEGroupEntry.id) == 0).order_by(
CVE.id)).all()
return {
'scheduled_advisories': scheduled_advisories,
'incomplete_advisories': incomplete_advisories,
'unhandled_advisories': unhandled_advisories,
'unknown_issues': unknown_issues,
'unknown_groups': unknown_groups,
'bumped_groups': bumped_groups,
'orphan_issues': orphan_issues
}
def todo():
incomplete_advisories = (db.session.query(Advisory, CVEGroupPackage, CVEGroup)
.join(CVEGroupPackage).join(CVEGroup)
.filter(and_(
Advisory.publication == Publication.published,
or_(Advisory.content == '', Advisory.content.is_(None),
Advisory.reference == '', Advisory.reference.is_(None))))
.group_by(CVEGroupPackage.id)
.order_by(Advisory.created.desc())).all()
scheduled_advisories = (db.session.query(Advisory, CVEGroupPackage, CVEGroup)
.join(CVEGroupPackage).join(CVEGroup)
.filter(Advisory.publication == Publication.scheduled)
.group_by(CVEGroupPackage.id)
.order_by(Advisory.created.desc())).all()
unhandled_advisories = (db.session.query(CVEGroup, func.group_concat(CVEGroupPackage.pkgname, ' '))
.join(CVEGroupPackage)
.outerjoin(Advisory)
.filter(CVEGroup.advisory_qualified)
.filter(CVEGroup.status == Status.fixed)
.group_by(CVEGroup.id)
.having(func.count(Advisory.id) == 0)
.order_by(CVEGroup.id)).all()
for index, item in enumerate(unhandled_advisories):
unhandled_advisories[index] = (item[0], item[1].split(' '))
unhandled_advisories = sorted(unhandled_advisories, key=lambda item: item[0].id)
unhandled_advisories = sorted(unhandled_advisories, key=lambda item: item[0].severity)
unknown_issues = (db.session.query(CVE)
.filter(or_(CVE.remote == Remote.unknown,
CVE.severity == Severity.unknown,
CVE.description.is_(None),
CVE.description == '',
CVE.issue_type.is_(None),
CVE.issue_type == 'unknown'))
.order_by(CVE.id.desc())).all()
unknown_groups = CVEGroup.query.filter(CVEGroup.status == Status.unknown).all()
unknown_groups = (db.session.query(CVEGroup, Package)
.join(CVEGroupPackage).join(Package, Package.name == CVEGroupPackage.pkgname)
.filter(CVEGroup.status == Status.unknown)
.group_by(CVEGroupPackage.id)
.order_by(CVEGroup.created.desc())).all()
unknown_groups_data = defaultdict(list)
for group, package in unknown_groups:
unknown_groups_data[group].append(package)
unknown_groups = []
for group, packages in unknown_groups_data.items():
unknown_groups.append((group, packages))
unknown_groups = sorted(unknown_groups, key=lambda item: item[0].id)
vulnerable_groups = (db.session.query(CVEGroup, Package)
.join(CVEGroupPackage).join(Package, Package.name == CVEGroupPackage.pkgname)
.filter(CVEGroup.status == Status.vulnerable)
.filter(or_(CVEGroup.fixed is None, CVEGroup.fixed == ''))
.group_by(CVEGroup.id).group_by(Package.name, Package.version)
.order_by(CVEGroup.created.desc())).all()
vulnerable_group_data = defaultdict(list)
for group, package in vulnerable_groups:
vulnerable_group_data[group].append(package)
bumped_groups = []
for group, packages in vulnerable_group_data.items():
packages = sorted(packages, key=cmp_to_key(vercmp, attrgetter('version')), reverse=True)
if 0 == vercmp(group.affected, packages[0].version):
continue
versions = filter_duplicate_packages(packages, filter_arch=True)
pkgnames = set([pkg.name for pkg in packages])
bumped_groups.append((group, pkgnames, versions))
bumped_groups = sorted(bumped_groups, key=lambda item: item[0].id, reverse=True)
bumped_groups = sorted(bumped_groups, key=lambda item: item[0].severity)
orphan_issues = (db.session.query(CVE)
.outerjoin(CVEGroupEntry)
.group_by(CVE.id)
.having(func.count(CVEGroupEntry.id) == 0)
.order_by(CVE.id)).all()
entries = {
'scheduled_advisories': scheduled_advisories,
'incomplete_advisories': incomplete_advisories,
'unhandled_advisories': unhandled_advisories,
'unknown_issues': unknown_issues,
'unknown_groups': unknown_groups,
'bumped_groups': bumped_groups,
'orphan_issues': orphan_issues
}
return render_template('todo.html',
title='Todo Lists',
entries=entries,
smiley=smileys_happy[randint(0, len(smileys_happy) - 1)],
can_handle_advisory=user_can_handle_advisory(),
can_edit_group=user_can_edit_group(),
can_edit_issue=user_can_edit_issue())
