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
