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())