def select(self, obj): range_obj = func.concat(TrueLocationReputation.low, '-', TrueLocationReputation.high).label('range') no_loc = func.log(TrueLocationReputation.no_location + 1).label('no_location') has_loc = func.log(TrueLocationReputation.has_location + 1).label('has_location') return obj.query(range_obj, no_loc, has_loc)
def hybridMag(cls): if index is not None: # It needs to be index + 1 because Postgresql arrays are 1-indexed. flux = getattr(cls, flux_parameter)[index + 1] else: flux = getattr(cls, flux_parameter) flux *= 1e-9 bb_band = bb[band] xx = flux / (2. * bb_band) asinh_mag = (-2.5 / func.log(10) * (func.log(xx + func.sqrt(func.pow(xx, 2) + 1)) + func.log(bb_band))) return cast(asinh_mag, Float)
def make_relative_metrics_sub(self, num_metric, den_metric, metric_name_lu=-1): session = self.get_session() qr = session.query( num_metric.c.account_id, func.DATE(num_metric.c.metric_time).label("metric_time"), literal(metric_name_lu).label("metric_name_id"), case([ (num_metric.c.num_value == None, 0), (den_metric.c.den_value == None, 0), (den_metric.c.den_value == 0, 0), (den_metric.c.den_value != 0, num_metric.c.num_value / den_metric.c.den_value) ], else_=0).label("metric_value"), case([ (num_metric.c.num_value == None, 0), (den_metric.c.den_value == None, 0), (den_metric.c.den_value == 0, 0), (num_metric.c.num_value / den_metric.c.den_value > 0, func.log(num_metric.c.num_value / den_metric.c.den_value)) ], else_=0).label("metric_value_log") ) \ .join(den_metric, and_( num_metric.c.account_id == den_metric.c.account_id, num_metric.c.metric_time == den_metric.c.metric_time ), isouter=True) \ .order_by(num_metric.c.account_id, num_metric.c.metric_time) return qr
def reddit_score(self): s = self.upvotes - self.downvotes order = func.log(10, func.greatest(func.abs(s), 1)) sign = func.sign(s) seconds = func.date_part('epoch', self.timestamp) - 1134028003 return func.round(func.cast(sign * order + seconds / 45000, Numeric), 7)
def get_time_priority_query(self): t0 = self.priority_conf['t0'] t1 = self.priority_conf['t1'] a = self.priority_threshold / (math.log10(t1) - math.log10(t0)) b = -a * math.log10(t0) time_expr = func.greatest(a * func.log( hours_since(func.max(Build.started)) + 0.00001) + b, -30) return self.db.query(Build.package_id.label('pkg_id'), time_expr.label('priority'))\ .group_by(Build.package_id)
def get_time_priority_query(self): t0 = self.priority_conf['t0'] t1 = self.priority_conf['t1'] a = self.priority_threshold / (math.log10(t1) - math.log10(t0)) b = -a * math.log10(t0) log_arg = func.greatest(0.000001, hours_since(func.max(Build.started))) time_expr = func.greatest(a * func.log(log_arg) + b, -30) return self.db.query(Build.package_id.label('pkg_id'), time_expr.label('priority'))\ .group_by(Build.package_id)
def get_time_priority_query(self): t0 = get_config('priorities.t0') t1 = get_config('priorities.t1') a = get_config('priorities.build_threshold') / (math.log10(t1) - math.log10(t0)) b = -a * math.log10(t0) log_arg = func.greatest(0.000001, hours_since(func.max(Build.started))) time_expr = func.greatest(a * func.log(log_arg) + b, -30) return self.db.query(Build.package_id.label('pkg_id'), time_expr.label('priority'))\ .group_by(Build.package_id)
def nsa_logmstar_el(cls): return func.log(cls.nsa_mstar_el)
def mass(cls): par = getattr(cls, parameter) return cast(case([(par > 0., func.log(par)), (par == 0., 0.)]), Float)
def topvalues(self, field, flt=None, topnbr=10, sort=None, limit=None, skip=None, least=False): """ 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.* - httphdr / httphdr.{name,value} / httphdr:<name> - modbus.* / s7.* / enip.* - mongo.dbs.* - vulns.* - screenwords - file.* / file.*:scriptid - hop """ if flt is None: flt = self.flt_empty base = flt.query( select([self.tables.scan.id ]).select_from(flt.select_from)).cte("base") order = "count" if least else desc("count") outputproc = None if field == "port": field = self._topstructure( self.tables.port, [self.tables.port.protocol, self.tables.port.port], self.tables.port.state == "open") elif field == "ttl": field = self._topstructure( self.tables.port, [self.tables.port.state_reason_ttl], self.tables.port.state_reason_ttl != None, # noqa: E711 (BinaryExpression) ) elif field == "ttlinit": field = self._topstructure( self.tables.port, [ func.least( 255, func.power( 2, func.ceil( func.log(2, self.tables.port.state_reason_ttl)))) ], self.tables.port.state_reason_ttl != None, # noqa: E711 (BinaryExpression) ) outputproc = int elif field.startswith('port:'): info = field[5:] field = self._topstructure( self.tables.port, [self.tables.port.protocol, self.tables.port.port], (self.tables.port.state == info) if info in ['open', 'filtered', 'closed', 'open|filtered'] else (self.tables.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(self.tables.port) .where(and_( self.tables.port.state == info, # self.tables.port.scan.in_(base), exists( select([1])\ .select_from(base)\ .where( self.tables.port.scan == base.c.id ) ), ))\ .group_by(self.tables.port.scan)\ .alias('cnt') ).group_by('cnt').order_by(order).limit(topnbr) ) ) elif field.startswith('portlist:'): ### Deux options pour filtrer: ### -1- self.tables.port.scan.in_(base), ### -2- exists(select([1])\ ### .select_from(base)\ ### .where( ### self.tables.port.scan == base.c.id ### )), ### ### D'après quelques tests, l'option -1- est plus beaucoup ### rapide quand (base) est pas ou peu sélectif, l'option ### -2- un peu plus rapide quand (base) est très sélectif ### ### TODO: vérifier si c'est pareil pour: ### - countports:open ### - tous les autres 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_(self.tables.port.protocol, self. tables.port.port).label('a'), tuple_( self.tables.port.protocol, self.tables. port.port).label('a'))).label('ports'), ]).where( and_( self.tables.port.state == info, self.tables.port.scan.in_( base), # exists(select([1])\ # .select_from(base)\ # .where( # self.tables.port.scan == base.c.id # )), )).group_by(self.tables.port.scan).alias('ports') ).group_by('ports').order_by(order).limit(topnbr))) elif field == "service": field = self._topstructure(self.tables.port, [self.tables.port.service_name], self.tables.port.state == "open") elif field.startswith("service:"): info = field[8:] if '/' in info: info = info.split('/', 1) field = self._topstructure( self.tables.port, [self.tables.port.service_name], and_(self.tables.port.protocol == info[0], self.tables.port.port == int(info[1])), ) else: field = self._topstructure(self.tables.port, [self.tables.port.service_name], self.tables.port.port == int(info)) elif field == "product": field = self._topstructure( self.tables.port, [ self.tables.port.service_name, self.tables.port.service_product ], self.tables.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 = self._topstructure( self.tables.port, [ self.tables.port.service_name, self.tables.port.service_product ], and_(self.tables.port.state == "open", self.tables.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 = self._topstructure( self.tables.port, [ self.tables.port.service_name, self.tables.port.service_product ], and_(self.tables.port.state == "open", self.tables.port.port == info[1], self.tables.port.protocol == info[0]), ) else: flt = self.flt_and(flt, self.searchservice(info)) field = self._topstructure( self.tables.port, [ self.tables.port.service_name, self.tables.port.service_product ], and_(self.tables.port.state == "open", self.tables.port.service_name == info), ) elif field == "devicetype": field = self._topstructure(self.tables.port, [self.tables.port.service_devicetype], self.tables.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 = self._topstructure( self.tables.port, [self.tables.port.service_devicetype], and_(self.tables.port.state == "open", self.tables.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 = self._topstructure( self.tables.port, [self.tables.port.service_devicetype], and_(self.tables.port.state == "open", self.tables.port.port == info[1], self.tables.port.protocol == info[0])) else: flt = self.flt_and(flt, self.searchservice(info)) field = self._topstructure( self.tables.port, [self.tables.port.service_devicetype], and_(self.tables.port.state == "open", self.tables.port.service_name == info)) elif field == "version": field = self._topstructure( self.tables.port, [ self.tables.port.service_name, self.tables.port.service_product, self.tables.port.service_version ], self.tables.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 = self._topstructure( self.tables.port, [ self.tables.port.service_name, self.tables.port.service_product, self.tables.port.service_version ], and_(self.tables.port.state == "open", self.tables.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 = self._topstructure( self.tables.port, [ self.tables.port.service_name, self.tables.port.service_product, self.tables.port.service_version ], and_(self.tables.port.state == "open", self.tables.port.port == info[1], self.tables.port.protocol == info[0]), ) elif ':' in info: info = info.split(':', 1) flt = self.flt_and( flt, self.searchproduct(info[1], service=info[0])) field = self._topstructure( self.tables.port, [ self.tables.port.service_name, self.tables.port.service_product, self.tables.port.service_version ], and_(self.tables.port.state == "open", self.tables.port.service_name == info[0], self.tables.port.service_product == info[1]), ) else: flt = self.flt_and(flt, self.searchservice(info)) field = self._topstructure( self.tables.port, [ self.tables.port.service_name, self.tables.port.service_product, self.tables.port.service_version ], and_(self.tables.port.state == "open", self.tables.port.service_name == info), ) elif field == "asnum": field = self._topstructure(self.tables.scan, [self.tables.scan.info["as_num"]]) elif field == "as": field = self._topstructure(self.tables.scan, [ self.tables.scan.info["as_num"], self.tables.scan.info["as_name"] ]) elif field == "country": field = self._topstructure(self.tables.scan, [ self.tables.scan.info["country_code"], self.tables.scan.info["country_name"] ]) elif field == "city": field = self._topstructure(self.tables.scan, [ self.tables.scan.info["country_code"], self.tables.scan.info["city"] ]) elif field == "net" or field.startswith("net:"): info = field[4:] info = int(info) if info else 24 field = self._topstructure( self.tables.scan, [func.set_masklen(text("scan.addr::cidr"), info)], ) elif field == "script" or field.startswith("script:"): info = field[7:] if info: field = self._topstructure(self.tables.script, [self.tables.script.output], self.tables.script.name == info) else: field = self._topstructure(self.tables.script, [self.tables.script.name]) elif field in ["category", "categories"]: field = self._topstructure(self.tables.category, [self.tables.category.name]) elif field.startswith('cert.'): subfield = field[5:] field = self._topstructure( self.tables.script, [self.tables.script.data['ssl-cert'][subfield]], and_(self.tables.script.name == 'ssl-cert', self.tables.script.data['ssl-cert'].has_key( subfield))) # noqa: W601 (BinaryExpression) elif field == "source": field = self._topstructure(self.tables.scan, [self.tables.scan.source]) elif field == "domains": field = self._topstructure( self.tables.hostname, [func.unnest(self.tables.hostname.domains)]) elif field.startswith("domains:"): level = int(field[8:]) - 1 base1 = (select([ func.unnest(self.tables.hostname.domains).label("domains") ]).where( exists( select([1]).select_from(base).where( self.tables.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 = self._topstructure(self.tables.hop, [self.tables.hop.ipaddr]) elif field.startswith('hop') and field[3] in ':>': ttl = int(field[4:]) field = self._topstructure( self.tables.hop, [self.tables.hop.ipaddr], (self.tables.hop.ttl > ttl) if field[3] == '>' else (self.tables.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 = (self.tables.script.name == scripts[0] if len(scripts) == 1 else self.tables.script.name.in_(scripts)) else: field = field[5:] or 'filename' flt = True field = self._topstructure( self.tables.script, [ func.jsonb_array_elements( func.jsonb_array_elements( self.tables.script.data['ls']['volumes']).op('->') ('files')).op('->>')(field).label(field) ], and_( flt, self.tables.script.data.op('@>')( '{"ls": {"volumes": [{"files": []}]}}'), ), ) elif field.startswith('modbus.'): subfield = field[7:] field = self._topstructure( self.tables.script, [self.tables.script.data['modbus-discover'][subfield]], and_( self.tables.script.name == 'modbus-discover', self.tables.script.data['modbus-discover'].has_key( subfield)), # noqa: W601 (BinaryExpression) ) elif field.startswith('s7.'): subfield = field[3:] field = self._topstructure( self.tables.script, [self.tables.script.data['s7-info'][subfield]], and_(self.tables.script.name == 's7-info', self.tables.script.data['s7-info'].has_key(subfield)), # noqa: W601 (BinaryExpression) ) elif field == 'httphdr': flt = self.flt_and(flt, self.searchscript(name="http-headers")) field = self._topstructure( self.tables.script, [ column("hdr").op('->>')('name').label("name"), column("hdr").op('->>')('value').label("value") ], self.tables.script.name == 'http-headers', [column("name"), column("value")], func.jsonb_array_elements( self.tables.script.data['http-headers']).alias('hdr'), ) elif field.startswith('httphdr.'): flt = self.flt_and(flt, self.searchscript(name="http-headers")) field = self._topstructure( self.tables.script, [column("hdr").op('->>')(field[8:]).label("topvalue")], self.tables.script.name == 'http-headers', [column("topvalue")], func.jsonb_array_elements( self.tables.script.data['http-headers']).alias('hdr'), ) elif field.startswith('httphdr:'): flt = self.flt_and(flt, self.searchhttphdr(name=field[8:].lower())) field = self._topstructure( self.tables.script, [column("hdr").op('->>')("value").label("value")], and_(self.tables.script.name == 'http-headers', column("hdr").op('->>')("name") == field[8:].lower()), [column("value")], func.jsonb_array_elements( self.tables.script.data['http-headers']).alias('hdr'), ) else: raise NotImplementedError() s_from = { self.tables.script: join(self.tables.script, self.tables.port), self.tables.port: self.tables.port, self.tables.category: join(self.tables.association_scan_category, self.tables.category), self.tables.hostname: self.tables.hostname, self.tables.hop: join(self.tables.trace, self.tables.hop), } where_clause = { self.tables.script: self.tables.port.scan == base.c.id, self.tables.port: self.tables.port.scan == base.c.id, self.tables.category: self.tables.association_scan_category.scan == base.c.id, self.tables.hostname: self.tables.hostname.scan == base.c.id, self.tables.hop: self.tables.trace.scan == base.c.id } if field.base == self.tables.scan: req = flt.query( select([func.count().label("count")] + field.fields).select_from( self.tables.scan).group_by(*field.fields)) else: req = (select([func.count().label("count")] + field.fields).select_from(s_from[field.base])) if field.extraselectfrom is not None: req = req.select_from(field.extraselectfrom) req = (req.group_by( *(field.fields if field.group_by is None else field.group_by )).where( exists( select([1]).select_from(base).where( where_clause[field.base])))) if field.where is not None: req = req.where(field.where) 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)))
def hot(cls): ts = cls.td - 1449619200 x = cls.upvotes - cls.downvotes o = func.log(func.IF(func.abs(x) > 1, func.abs(x), 1)) y = func.IF(x > 0, 1, func.IF(x < 0, -1, 0)) return o * y + ts * 1.0 / 300000