'This method makes use of the aggregation framework to produce
top values for a given field or pseudo-field. Pseudo-fields are:
- category / label / asnum / country / net[:mask]
- port
- port:open / :closed / :filtered / :<servicename>
- portlist:open / :closed / :filtered
- countports:open / :closed / :filtered
- service / service:<portnbr>
- product / product:<portnbr>
- cpe / cpe.<part> / cpe:<cpe_spec> / cpe.<part>:<cpe_spec>
- devicetype / devicetype:<portnbr>
- script:<scriptid> / script:<port>:<scriptid>
/ script:host:<scriptid>
- cert.* / smb.* / sshkey.*
- modbus.* / s7.* / enip.*
- mongo.dbs.*
- vulns.*
- screenwords
- file.* / file.*:scriptid
- hop'

def topvalues(self, field, flt=None, topnbr=10, sort=None, limit=None, skip=None, least=False, archive=False):

    if (flt is None):
        flt = NmapFilter()
    base = flt.query(select([Scan.id]).select_from(flt.select_from), archive=archive).cte('base')
    order = ('count' if least else desc('count'))
    outputproc = None
    if (field == 'port'):
        field = (Port, [Port.protocol, Port.port], (Port.state == 'open'))
    elif (field == 'ttl'):
        field = (Port, [Port.state_reason_ttl], (Port.state_reason_ttl != None))
    elif (field == 'ttlinit'):
        field = (Port, [func.least(255, func.power(2, func.ceil(func.log(2, Port.state_reason_ttl))))], (Port.state_reason_ttl != None))
        outputproc = int
    elif field.startswith('port:'):
        info = field[5:]
        field = (Port, [Port.protocol, Port.port], ((Port.state == info) if (info in set(['open', 'filtered', 'closed', 'open|filtered'])) else (Port.service_name == info)))
    elif field.startswith('countports:'):
        info = field[11:]
        return ({'count': result[0], '_id': result[1]} for result in self.db.execute(select([func.count().label('count'), column('cnt')]).select_from(select([func.count().label('cnt')]).select_from(Port).where(and_((Port.state == info), exists(select([1]).select_from(base).where((Port.scan == base.c.id))))).group_by(Port.scan).alias('cnt')).group_by('cnt').order_by(order).limit(topnbr)))
    elif field.startswith('portlist:'):
        info = field[9:]
        return ({'count': result[0], '_id': [(proto, int(port)) for (proto, port) in (elt.split(',') for elt in result[1][3:(-3)].split(')","('))]} for result in self.db.execute(select([func.count().label('count'), column('ports')]).select_from(select([func.array_agg(postgresql.aggregate_order_by(tuple_(Port.protocol, Port.port).label('a'), tuple_(Port.protocol, Port.port).label('a'))).label('ports')]).where(and_((Port.state == info), Port.scan.in_(base))).group_by(Port.scan).alias('ports')).group_by('ports').order_by(order).limit(topnbr)))
    elif (field == 'service'):
        field = (Port, [Port.service_name], (Port.state == 'open'))
    elif field.startswith('service:'):
        info = field[8:]
        if ('/' in info):
            info = info.split('/', 1)
            field = (Port, [Port.service_name], and_((Port.protocol == info[0]), (Port.port == int(info[1]))))
        else:
            field = (Port, [Port.service_name], (Port.port == int(info)))
    elif (field == 'product'):
        field = (Port, [Port.service_name, Port.service_product], (Port.state == 'open'))
    elif field.startswith('product:'):
        info = field[8:]
        if info.isdigit():
            info = int(info)
            flt = self.flt_and(flt, self.searchport(info))
            field = (Port, [Port.service_name, Port.service_product], and_((Port.state == 'open'), (Port.port == info)))
        elif (info.startswith('tcp/') or info.startswith('udp/')):
            info = (info[:3], int(info[4:]))
            flt = self.flt_and(flt, self.searchport(info[1], protocol=info[0]))
            field = (Port, [Port.service_name, Port.service_product], and_((Port.state == 'open'), (Port.port == info[1]), (Port.protocol == info[0])))
        else:
            flt = self.flt_and(flt, self.searchservice(info))
            field = (Port, [Port.service_name, Port.service_product], and_((Port.state == 'open'), (Port.service_name == info)))
    elif (field == 'devicetype'):
        field = (Port, [Port.service_devicetype], (Port.state == 'open'))
    elif field.startswith('devicetype:'):
        info = field[11:]
        if info.isdigit():
            info = int(info)
            flt = self.flt_and(flt, self.searchport(info))
            field = (Port, [Port.service_devicetype], and_((Port.state == 'open'), (Port.port == info)))
        elif (info.startswith('tcp/') or info.startswith('udp/')):
            info = (info[:3], int(info[4:]))
            flt = self.flt_and(flt, self.searchport(info[1], protocol=info[0]))
            field = (Port, [Port.service_devicetype], and_((Port.state == 'open'), (Port.port == info[1]), (Port.protocol == info[0])))
        else:
            flt = self.flt_and(flt, self.searchservice(info))
            field = (Port, [Port.service_devicetype], and_((Port.state == 'open'), (Port.service_name == info)))
    elif (field == 'version'):
        field = (Port, [Port.service_name, Port.service_product, Port.service_version], (Port.state == 'open'))
    elif field.startswith('version:'):
        info = field[8:]
        if info.isdigit():
            info = int(info)
            flt = self.flt_and(flt, self.searchport(info))
            field = (Port, [Port.service_name, Port.service_product, Port.service_version], and_((Port.state == 'open'), (Port.port == info)))
        elif (info.startswith('tcp/') or info.startswith('udp/')):
            info = (info[:3], int(info[4:]))
            flt = self.flt_and(flt, self.searchport(info[1], protocol=info[0]))
            field = (Port, [Port.service_name, Port.service_product, Port.service_version], and_((Port.state == 'open'), (Port.port == info[1]), (Port.protocol == info[0])))
        elif (':' in info):
            info = info.split(':', 1)
            flt = self.flt_and(flt, self.searchproduct(info[1], service=info[0]))
            field = (Port, [Port.service_name, Port.service_product, Port.service_version], and_((Port.state == 'open'), (Port.service_name == info[0]), (Port.service_product == info[1])))
        else:
            flt = self.flt_and(flt, self.searchservice(info))
            field = (Port, [Port.service_name, Port.service_product, Port.service_version], and_((Port.state == 'open'), (Port.service_name == info)))
    elif (field == 'asnum'):
        field = (Scan, [Scan.info['as_num']], None)
    elif (field == 'as'):
        field = (Scan, [Scan.info['as_num'], Scan.info['as_name']], None)
    elif (field == 'country'):
        field = (Scan, [Scan.info['country_code'], Scan.info['country_name']], None)
    elif (field == 'city'):
        field = (Scan, [Scan.info['country_code'], Scan.info['city']], None)
    elif ((field == 'net') or field.startswith('net:')):
        info = field[4:]
        info = (int(info) if info else 24)
        field = (Scan, [func.set_masklen(text('scan.addr::cidr'), info)], None)
    elif ((field == 'script') or field.startswith('script:')):
        info = field[7:]
        if info:
            field = (Script, [Script.output], (Script.name == info))
        else:
            field = (Script, [Script.name], None)
    elif (field in ['category', 'categories']):
        field = (Category, [Category.name], None)
    elif (field == 'source'):
        field = (Scan, [Scan.source], None)
    elif (field == 'domains'):
        field = (Hostname, [func.unnest(Hostname.domains)], None)
    elif field.startswith('domains:'):
        level = (int(field[8:]) - 1)
        base1 = select([func.unnest(Hostname.domains).label('domains')]).where(exists(select([1]).select_from(base).where((Hostname.scan == base.c.id)))).cte('base1')
        return ({'count': result[1], '_id': result[0]} for result in self.db.execute(select([base1.c.domains, func.count().label('count')]).where(base1.c.domains.op('~')(('^([^\\.]+\\.){%d}[^\\.]+$' % level))).group_by(base1.c.domains).order_by(order).limit(topnbr)))
    elif (field == 'hop'):
        field = (Hop, [Hop.ipaddr], None)
    elif (field.startswith('hop') and (field[3] in ':>')):
        ttl = int(field[4:])
        field = (Hop, [Hop.ipaddr], ((Hop.ttl > ttl) if (field[3] == '>') else (Hop.ttl == ttl)))
    elif ((field == 'file') or (field.startswith('file') and (field[4] in '.:'))):
        if field.startswith('file:'):
            scripts = field[5:]
            if ('.' in scripts):
                (scripts, field) = scripts.split('.', 1)
            else:
                field = 'filename'
            scripts = scripts.split(',')
            flt = ((Script.name == scripts[0]) if (len(scripts) == 1) else Script.name.in_(scripts))
        else:
            field = (field[5:] or 'filename')
            flt = True
        field = (Script, [func.jsonb_array_elements(func.jsonb_array_elements(Script.data['ls']['volumes']).op('->')('files')).op('->>')(field).label(field)], and_(flt, Script.data.op('@>')('{"ls":    {"volumes":    [{"files":    []}]}}')))
    elif field.startswith('modbus.'):
        subfield = field[7:]
        field = (Script, [Script.data['modbus-discover'][subfield]], and_((Script.name == 'modbus-discover'), Script.data['modbus-discover'].has_key(subfield)))
    else:
        raise NotImplementedError()
    s_from = {Script: join(Script, Port), Port: Port, Category: join(Association_Scan_Category, Category), Hostname: Hostname, Hop: join(Trace, Hop)}
    where_clause = {Script: (Port.scan == base.c.id), Port: (Port.scan == base.c.id), Category: (Association_Scan_Category.scan == base.c.id), Hostname: (Hostname.scan == base.c.id), Hop: (Trace.scan == base.c.id)}
    if (field[0] == Scan):
        req = flt.query(select(([func.count().label('count')] + field[1])).select_from(Scan).group_by(*field[1]), archive=archive)
    else:
        req = select(([func.count().label('count')] + field[1])).select_from(s_from[field[0]]).group_by(*field[1]).where(exists(select([1]).select_from(base).where(where_clause[field[0]])))
    if (field[2] is not None):
        req = req.where(field[2])
    if (outputproc is None):
        return ({'count': result[0], '_id': (result[1:] if (len(result) > 2) else result[1])} for result in self.db.execute(req.order_by(order).limit(topnbr)))
    else:
        return ({'count': result[0], '_id': outputproc((result[1:] if (len(result) > 2) else result[1]))} for result in self.db.execute(req.order_by(order).limit(topnbr)))
