def _ResponseToApprovalsWithGrants(response):
"""Converts a generator with approval rows into ApprovalRequest objects."""
prev_triplet = None
cur_approval_request = None
for (approval_id_int, approval_timestamp, approval_request_bytes,
grantor_username, grant_timestamp) in response:
cur_triplet = (approval_id_int, approval_timestamp,
approval_request_bytes)
if cur_triplet != prev_triplet:
prev_triplet = cur_triplet
if cur_approval_request:
yield cur_approval_request
cur_approval_request = mysql_utils.StringToRDFProto(
rdf_objects.ApprovalRequest, approval_request_bytes)
cur_approval_request.approval_id = _IntToApprovalID(
approval_id_int)
cur_approval_request.timestamp = mysql_utils.TimestampToRDFDatetime(
approval_timestamp)
if grantor_username and grant_timestamp:
cur_approval_request.grants.append(
rdf_objects.ApprovalGrant(
grantor_username=grantor_username,
timestamp=mysql_utils.TimestampToRDFDatetime(
grant_timestamp)))
if cur_approval_request:
yield cur_approval_request
def _HuntObjectFromRow(self, row):
"""Generates a flow object from a database row."""
(
create_time,
last_update_time,
creator,
duration_micros,
client_rate,
client_limit,
hunt_state,
hunt_state_comment,
init_start_time,
last_start_time,
num_clients_at_start_time,
description,
body,
) = row
hunt_obj = rdf_hunt_objects.Hunt.FromSerializedBytes(body)
hunt_obj.duration = rdfvalue.Duration.From(duration_micros,
rdfvalue.MICROSECONDS)
hunt_obj.create_time = mysql_utils.TimestampToRDFDatetime(create_time)
hunt_obj.last_update_time = mysql_utils.TimestampToRDFDatetime(
last_update_time)
# Checks below are needed for hunts that were written to the database before
# respective fields became part of F1 schema.
if creator is not None:
hunt_obj.creator = creator
if client_rate is not None:
hunt_obj.client_rate = client_rate
if client_limit is not None:
hunt_obj.client_limit = client_limit
if hunt_state is not None:
hunt_obj.hunt_state = hunt_state
if hunt_state_comment is not None:
hunt_obj.hunt_state_comment = hunt_state_comment
if init_start_time is not None:
hunt_obj.init_start_time = mysql_utils.TimestampToRDFDatetime(
init_start_time)
if last_start_time is not None:
hunt_obj.last_start_time = mysql_utils.TimestampToRDFDatetime(
last_start_time)
if num_clients_at_start_time is not None:
hunt_obj.num_clients_at_start_time = num_clients_at_start_time
if description is not None:
hunt_obj.description = description
return hunt_obj
def _ResponseToClientsFullInfo(self, response):
"""Creates a ClientFullInfo object from a database response."""
c_full_info = None
prev_cid = None
for row in response:
(cid, fs, crt, ping, clk, ip, foreman, first, last_client_ts,
last_crash_ts, last_startup_ts, client_obj, client_startup_obj,
last_startup_obj, label_owner, label_name) = row
if cid != prev_cid:
if c_full_info:
yield db_utils.IntToClientID(prev_cid), c_full_info
metadata = rdf_objects.ClientMetadata(
certificate=crt,
fleetspeak_enabled=fs,
first_seen=mysql_utils.TimestampToRDFDatetime(first),
ping=mysql_utils.TimestampToRDFDatetime(ping),
clock=mysql_utils.TimestampToRDFDatetime(clk),
ip=mysql_utils.StringToRDFProto(rdf_client_network.NetworkAddress,
ip),
last_foreman_time=mysql_utils.TimestampToRDFDatetime(foreman),
startup_info_timestamp=mysql_utils.TimestampToRDFDatetime(
last_startup_ts),
last_crash_timestamp=mysql_utils.TimestampToRDFDatetime(
last_crash_ts))
if client_obj is not None:
l_snapshot = rdf_objects.ClientSnapshot.FromSerializedString(
client_obj)
l_snapshot.timestamp = mysql_utils.TimestampToRDFDatetime(
last_client_ts)
l_snapshot.startup_info = rdf_client.StartupInfo.FromSerializedString(
client_startup_obj)
l_snapshot.startup_info.timestamp = l_snapshot.timestamp
else:
l_snapshot = rdf_objects.ClientSnapshot(
client_id=db_utils.IntToClientID(cid))
if last_startup_obj is not None:
startup_info = rdf_client.StartupInfo.FromSerializedString(
last_startup_obj)
startup_info.timestamp = mysql_utils.TimestampToRDFDatetime(
last_startup_ts)
else:
startup_info = None
prev_cid = cid
c_full_info = rdf_objects.ClientFullInfo(
metadata=metadata,
labels=[],
last_snapshot=l_snapshot,
last_startup_info=startup_info)
if label_owner and label_name:
c_full_info.labels.append(
rdf_objects.ClientLabel(name=label_name, owner=label_owner))
if c_full_info:
yield db_utils.IntToClientID(prev_cid), c_full_info
def ReadApprovalRequest(self,
requestor_username,
approval_id,
cursor=None):
"""Reads an approval request object with a given id."""
query = ("""
SELECT
ar.approval_id,
UNIX_TIMESTAMP(ar.timestamp),
ar.approval_request,
u.username,
UNIX_TIMESTAMP(ag.timestamp)
FROM approval_request ar
LEFT JOIN approval_grant ag USING (username_hash, approval_id)
LEFT JOIN grr_users u ON u.username_hash = ag.grantor_username_hash
WHERE ar.approval_id = %s AND ar.username_hash = %s
""")
cursor.execute(query, [
_ApprovalIDToInt(approval_id),
mysql_utils.Hash(requestor_username)
])
res = cursor.fetchall()
if not res:
raise db.UnknownApprovalRequestError("Approval '%s' not found." %
approval_id)
approval_id_int, timestamp, approval_request_bytes, _, _ = res[0]
approval_request = mysql_utils.StringToRDFProto(
rdf_objects.ApprovalRequest, approval_request_bytes)
approval_request.approval_id = _IntToApprovalID(approval_id_int)
approval_request.timestamp = mysql_utils.TimestampToRDFDatetime(
timestamp)
for _, _, _, grantor_username, timestamp in res:
if not grantor_username:
continue
# Note: serialized approval_request objects are guaranteed to not
# have any grants.
approval_request.grants.append(
rdf_objects.ApprovalGrant(
grantor_username=grantor_username,
timestamp=mysql_utils.TimestampToRDFDatetime(timestamp)))
return approval_request
def ReadClientStartupInfoHistory(self,
client_id,
timerange=None,
cursor=None):
"""Reads the full startup history for a particular client."""
client_id_int = db_utils.ClientIDToInt(client_id)
query = ("SELECT startup_info, UNIX_TIMESTAMP(timestamp) "
"FROM client_startup_history "
"WHERE client_id=%s ")
args = [client_id_int]
if timerange:
time_from, time_to = timerange # pylint: disable=unpacking-non-sequence
if time_from is not None:
query += "AND timestamp >= FROM_UNIXTIME(%s) "
args.append(mysql_utils.RDFDatetimeToTimestamp(time_from))
if time_to is not None:
query += "AND timestamp <= FROM_UNIXTIME(%s) "
args.append(mysql_utils.RDFDatetimeToTimestamp(time_to))
query += "ORDER BY timestamp DESC "
ret = []
cursor.execute(query, args)
for startup_info, timestamp in cursor.fetchall():
si = rdf_client.StartupInfo.FromSerializedBytes(startup_info)
si.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp)
ret.append(si)
return ret
def MultiReadClientSnapshot(self, client_ids, cursor=None):
"""Reads the latest client snapshots for a list of clients."""
int_ids = [db_utils.ClientIDToInt(cid) for cid in client_ids]
query = (
"SELECT h.client_id, h.client_snapshot, UNIX_TIMESTAMP(h.timestamp),"
" s.startup_info "
"FROM clients as c FORCE INDEX (PRIMARY), "
"client_snapshot_history as h FORCE INDEX (PRIMARY), "
"client_startup_history as s FORCE INDEX (PRIMARY) "
"WHERE h.client_id = c.client_id "
"AND s.client_id = c.client_id "
"AND h.timestamp = c.last_snapshot_timestamp "
"AND s.timestamp = c.last_startup_timestamp "
"AND c.client_id IN ({})").format(", ".join(["%s"] *
len(client_ids)))
ret = {cid: None for cid in client_ids}
cursor.execute(query, int_ids)
while True:
row = cursor.fetchone()
if not row:
break
cid, snapshot, timestamp, startup_info = row
client_obj = mysql_utils.StringToRDFProto(
rdf_objects.ClientSnapshot, snapshot)
client_obj.startup_info = mysql_utils.StringToRDFProto(
rdf_client.StartupInfo, startup_info)
client_obj.timestamp = mysql_utils.TimestampToRDFDatetime(
timestamp)
ret[db_utils.IntToClientID(cid)] = client_obj
return ret
def ReadSignedBinaryReferences(self, binary_id, cursor=None):
"""Reads blob references for the signed binary with the given id."""
cursor.execute(
"""
SELECT blob_references, UNIX_TIMESTAMP(timestamp)
FROM signed_binary_references
WHERE binary_type = %s AND binary_path_hash = %s
""", [
binary_id.binary_type.SerializeToDataStore(),
mysql_utils.Hash(binary_id.path)
])
row = cursor.fetchone()
if not row:
raise db.UnknownSignedBinaryError(binary_id)
raw_references, timestamp = row
# TODO(hanuszczak): pytype does not understand overloads, so we have to cast
# to a non-optional object.
datetime = cast(rdfvalue.RDFDatetime,
mysql_utils.TimestampToRDFDatetime(timestamp))
references = rdf_objects.BlobReferences.FromSerializedBytes(
raw_references)
return references, datetime
def ReadAllClientGraphSeries(
self,
client_label: Text,
report_type: rdf_structs.EnumNamedValue,
time_range: Optional[time_utils.TimeRange] = None,
cursor=None
) -> Dict[rdfvalue.RDFDatetime, rdf_stats.ClientGraphSeries]:
"""Reads graph series for the given label and report-type from the DB."""
query = """
SELECT UNIX_TIMESTAMP(timestamp), graph_series
FROM client_report_graphs
WHERE client_label = %s AND report_type = %s
"""
args = [client_label, report_type.SerializeToWireFormat()]
if time_range is not None:
query += "AND `timestamp` BETWEEN FROM_UNIXTIME(%s) AND FROM_UNIXTIME(%s)"
args += [
mysql_utils.RDFDatetimeToTimestamp(time_range.start),
mysql_utils.RDFDatetimeToTimestamp(time_range.end)
]
cursor.execute(query, args)
results = {}
for timestamp, raw_series in cursor.fetchall():
# TODO(hanuszczak): pytype does not seem to understand overloads, so it is
# not possible to correctly annotate `TimestampToRDFDatetime`.
timestamp = cast(rdfvalue.RDFDatetime,
mysql_utils.TimestampToRDFDatetime(timestamp))
series = rdf_stats.ClientGraphSeries.FromSerializedBytes(
raw_series)
results[timestamp] = series
return results
def ReadClientLastPings(self,
min_last_ping=None,
max_last_ping=None,
fleetspeak_enabled=None,
cursor=None):
"""Reads client ids for all clients in the database."""
query = "SELECT client_id, UNIX_TIMESTAMP(last_ping) FROM clients "
query_values = []
where_filters = []
if min_last_ping is not None:
where_filters.append("last_ping >= FROM_UNIXTIME(%s) ")
query_values.append(
mysql_utils.RDFDatetimeToTimestamp(min_last_ping))
if max_last_ping is not None:
where_filters.append(
"(last_ping IS NULL OR last_ping <= FROM_UNIXTIME(%s))")
query_values.append(
mysql_utils.RDFDatetimeToTimestamp(max_last_ping))
if fleetspeak_enabled is not None:
if fleetspeak_enabled:
where_filters.append("fleetspeak_enabled IS TRUE")
else:
where_filters.append(
"(fleetspeak_enabled IS NULL OR fleetspeak_enabled IS FALSE)"
)
if where_filters:
query += "WHERE " + "AND ".join(where_filters)
cursor.execute(query, query_values)
last_pings = {}
for int_client_id, last_ping in cursor.fetchall():
client_id = db_utils.IntToClientID(int_client_id)
last_pings[client_id] = mysql_utils.TimestampToRDFDatetime(
last_ping)
return last_pings
def _CronJobFromRow(self, row):
"""Creates a cronjob object from a database result row."""
(job, create_time, enabled, forced_run_requested, last_run_status,
last_run_time, current_run_id, state, leased_until, leased_by) = row
job = rdf_cronjobs.CronJob.FromSerializedBytes(job)
job.current_run_id = db_utils.IntToCronJobRunID(current_run_id)
job.enabled = enabled
job.forced_run_requested = forced_run_requested
job.last_run_status = last_run_status
job.last_run_time = mysql_utils.TimestampToRDFDatetime(last_run_time)
if state:
job.state = rdf_protodict.AttributedDict.FromSerializedBytes(state)
job.created_at = mysql_utils.TimestampToRDFDatetime(create_time)
job.leased_until = mysql_utils.TimestampToRDFDatetime(leased_until)
job.leased_by = leased_by
return job
def MultiReadClientMetadata(self, client_ids, cursor=None):
"""Reads ClientMetadata records for a list of clients."""
ids = [db_utils.ClientIDToInt(client_id) for client_id in client_ids]
query = ("SELECT client_id, fleetspeak_enabled, certificate, "
"UNIX_TIMESTAMP(last_ping), "
"UNIX_TIMESTAMP(last_clock), last_ip, "
"UNIX_TIMESTAMP(last_foreman), UNIX_TIMESTAMP(first_seen), "
"UNIX_TIMESTAMP(last_crash_timestamp), "
"UNIX_TIMESTAMP(last_startup_timestamp) FROM "
"clients WHERE client_id IN ({})").format(", ".join(["%s"] *
len(ids)))
ret = {}
cursor.execute(query, ids)
while True:
row = cursor.fetchone()
if not row:
break
cid, fs, crt, ping, clk, ip, foreman, first, lct, lst = row
ret[db_utils.IntToClientID(cid)] = rdf_objects.ClientMetadata(
certificate=crt,
fleetspeak_enabled=fs,
first_seen=mysql_utils.TimestampToRDFDatetime(first),
ping=mysql_utils.TimestampToRDFDatetime(ping),
clock=mysql_utils.TimestampToRDFDatetime(clk),
ip=mysql_utils.StringToRDFProto(
rdf_client_network.NetworkAddress, ip),
last_foreman_time=mysql_utils.TimestampToRDFDatetime(foreman),
startup_info_timestamp=mysql_utils.TimestampToRDFDatetime(lst),
last_crash_timestamp=mysql_utils.TimestampToRDFDatetime(lct))
return ret
def ReadHuntFlowsStatesAndTimestamps(self, hunt_id, cursor=None):
"""Reads hunt flows states and timestamps."""
query = """
SELECT
flow_state, UNIX_TIMESTAMP(timestamp), UNIX_TIMESTAMP(last_update)
FROM flows
FORCE INDEX(flows_by_hunt)
WHERE parent_hunt_id = %s AND parent_flow_id IS NULL
"""
cursor.execute(query, [db_utils.HuntIDToInt(hunt_id)])
result = []
for fs, ct, lup in cursor.fetchall():
result.append(
db.FlowStateAndTimestamps(
flow_state=rdf_flow_objects.Flow.FlowState.FromInt(fs),
create_time=mysql_utils.TimestampToRDFDatetime(ct),
last_update_time=mysql_utils.TimestampToRDFDatetime(lup)))
return result
def ReadClientCrashInfoHistory(self, client_id, cursor=None):
"""Reads the full crash history for a particular client."""
cursor.execute(
"SELECT UNIX_TIMESTAMP(timestamp), crash_info "
"FROM client_crash_history WHERE "
"client_crash_history.client_id = %s "
"ORDER BY timestamp DESC", [db_utils.ClientIDToInt(client_id)])
ret = []
for timestamp, crash_info in cursor.fetchall():
ci = rdf_client.ClientCrash.FromSerializedBytes(crash_info)
ci.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp)
ret.append(ci)
return ret
def ReadClientCrashInfo(self, client_id, cursor=None):
"""Reads the latest client crash record for a single client."""
cursor.execute(
"SELECT UNIX_TIMESTAMP(timestamp), crash_info "
"FROM clients, client_crash_history WHERE "
"clients.client_id = client_crash_history.client_id AND "
"clients.last_crash_timestamp = client_crash_history.timestamp AND "
"clients.client_id = %s", [db_utils.ClientIDToInt(client_id)])
row = cursor.fetchone()
if not row:
return None
timestamp, crash_info = row
res = rdf_client.ClientCrash.FromSerializedBytes(crash_info)
res.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp)
return res
def ReadClientStartupInfo(self, client_id, cursor=None):
"""Reads the latest client startup record for a single client."""
query = (
"SELECT startup_info, UNIX_TIMESTAMP(timestamp) "
"FROM clients, client_startup_history "
"WHERE clients.last_startup_timestamp=client_startup_history.timestamp "
"AND clients.client_id=client_startup_history.client_id "
"AND clients.client_id=%s")
cursor.execute(query, [db_utils.ClientIDToInt(client_id)])
row = cursor.fetchone()
if row is None:
return None
startup_info, timestamp = row
res = rdf_client.StartupInfo.FromSerializedBytes(startup_info)
res.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp)
return res
def ReadHuntOutputPluginLogEntries(self,
hunt_id,
output_plugin_id,
offset,
count,
with_type=None,
cursor=None):
"""Reads hunt output plugin log entries."""
query = ("SELECT client_id, flow_id, log_entry_type, message, "
"UNIX_TIMESTAMP(timestamp) "
"FROM flow_output_plugin_log_entries "
"FORCE INDEX (flow_output_plugin_log_entries_by_hunt) "
"WHERE hunt_id = %s AND output_plugin_id = %s ")
args = [
db_utils.HuntIDToInt(hunt_id),
db_utils.OutputPluginIDToInt(output_plugin_id)
]
if with_type is not None:
query += "AND log_entry_type = %s "
args.append(int(with_type))
query += "ORDER BY log_id ASC LIMIT %s OFFSET %s"
args.append(count)
args.append(offset)
cursor.execute(query, args)
ret = []
for (client_id_int, flow_id_int, log_entry_type, message,
timestamp) in cursor.fetchall():
ret.append(
rdf_flow_objects.FlowOutputPluginLogEntry(
hunt_id=hunt_id,
client_id=db_utils.IntToClientID(client_id_int),
flow_id=db_utils.IntToFlowID(flow_id_int),
output_plugin_id=output_plugin_id,
log_entry_type=log_entry_type,
message=message,
timestamp=mysql_utils.TimestampToRDFDatetime(timestamp)))
return ret
def _ReadClientLastPings(self,
last_client_id,
count,
min_last_ping=None,
max_last_ping=None,
fleetspeak_enabled=None,
cursor=None):
"""Yields dicts of last-ping timestamps for clients in the DB."""
where_filters = ["client_id > %s"]
query_values = [db_utils.ClientIDToInt(last_client_id)]
if min_last_ping is not None:
where_filters.append("last_ping >= FROM_UNIXTIME(%s) ")
query_values.append(
mysql_utils.RDFDatetimeToTimestamp(min_last_ping))
if max_last_ping is not None:
where_filters.append(
"(last_ping IS NULL OR last_ping <= FROM_UNIXTIME(%s))")
query_values.append(
mysql_utils.RDFDatetimeToTimestamp(max_last_ping))
if fleetspeak_enabled is not None:
if fleetspeak_enabled:
where_filters.append("fleetspeak_enabled IS TRUE")
else:
where_filters.append(
"(fleetspeak_enabled IS NULL OR fleetspeak_enabled IS FALSE)"
)
query = """
SELECT client_id, UNIX_TIMESTAMP(last_ping)
FROM clients
WHERE {}
ORDER BY client_id
LIMIT %s""".format(" AND ".join(where_filters))
cursor.execute(query, query_values + [count])
last_pings = {}
last_client_id = None
for int_client_id, last_ping in cursor.fetchall():
last_client_id = db_utils.IntToClientID(int_client_id)
last_pings[last_client_id] = mysql_utils.TimestampToRDFDatetime(
last_ping)
return last_client_id, last_pings
def ReadUserNotifications(self,
username,
state=None,
timerange=None,
cursor=None):
"""Reads notifications scheduled for a user within a given timerange."""
query = ("SELECT UNIX_TIMESTAMP(timestamp), "
" notification_state, notification "
"FROM user_notification "
"WHERE username_hash = %s ")
args = [mysql_utils.Hash(username)]
if state is not None:
query += "AND notification_state = %s "
args.append(int(state))
if timerange is not None:
time_from, time_to = timerange # pylint: disable=unpacking-non-sequence
if time_from is not None:
query += "AND timestamp >= FROM_UNIXTIME(%s) "
args.append(mysql_utils.RDFDatetimeToTimestamp(time_from))
if time_to is not None:
query += "AND timestamp <= FROM_UNIXTIME(%s) "
args.append(mysql_utils.RDFDatetimeToTimestamp(time_to))
query += "ORDER BY timestamp DESC "
ret = []
cursor.execute(query, args)
for timestamp, state, notification_ser in cursor.fetchall():
n = rdf_objects.UserNotification.FromSerializedString(
notification_ser)
n.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp)
n.state = state
ret.append(n)
return ret
def ReadHuntLogEntries(self,
hunt_id,
offset,
count,
with_substring=None,
cursor=None):
"""Reads hunt log entries of a given hunt using given query options."""
hunt_id_int = db_utils.HuntIDToInt(hunt_id)
query = (
"SELECT client_id, flow_id, message, UNIX_TIMESTAMP(timestamp) "
"FROM flow_log_entries "
"FORCE INDEX(flow_log_entries_by_hunt) "
"WHERE hunt_id = %s AND flow_id = hunt_id ")
args = [hunt_id_int]
if with_substring is not None:
query += "AND message LIKE %s "
args.append("%" + db_utils.EscapeWildcards(with_substring) + "%")
query += "ORDER BY timestamp ASC LIMIT %s OFFSET %s"
args.append(count)
args.append(offset)
cursor.execute(query, args)
flow_log_entries = []
for client_id_int, flow_id_int, message, timestamp in cursor.fetchall(
):
flow_log_entries.append(
rdf_flow_objects.FlowLogEntry(
client_id=db_utils.IntToClientID(client_id_int),
flow_id=db_utils.IntToFlowID(flow_id_int),
hunt_id=hunt_id,
message=message,
timestamp=mysql_utils.TimestampToRDFDatetime(timestamp)))
return flow_log_entries
def ReadClientSnapshotHistory(self,
client_id,
timerange=None,
cursor=None):
"""Reads the full history for a particular client."""
client_id_int = db_utils.ClientIDToInt(client_id)
query = ("SELECT sn.client_snapshot, st.startup_info, "
" UNIX_TIMESTAMP(sn.timestamp) FROM "
"client_snapshot_history AS sn, "
"client_startup_history AS st WHERE "
"sn.client_id = st.client_id AND "
"sn.timestamp = st.timestamp AND "
"sn.client_id=%s ")
args = [client_id_int]
if timerange:
time_from, time_to = timerange # pylint: disable=unpacking-non-sequence
if time_from is not None:
query += "AND sn.timestamp >= FROM_UNIXTIME(%s) "
args.append(mysql_utils.RDFDatetimeToTimestamp(time_from))
if time_to is not None:
query += "AND sn.timestamp <= FROM_UNIXTIME(%s) "
args.append(mysql_utils.RDFDatetimeToTimestamp(time_to))
query += "ORDER BY sn.timestamp DESC"
ret = []
cursor.execute(query, args)
for snapshot, startup_info, timestamp in cursor.fetchall():
client = rdf_objects.ClientSnapshot.FromSerializedBytes(snapshot)
client.startup_info = rdf_client.StartupInfo.FromSerializedBytes(
startup_info)
client.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp)
ret.append(client)
return ret
def ListHuntObjects(self,
offset,
count,
with_creator=None,
created_after=None,
with_description_match=None,
cursor=None):
"""Reads metadata for hunt objects from the database."""
query = """
SELECT
hunt_id,
UNIX_TIMESTAMP(create_timestamp),
UNIX_TIMESTAMP(last_update_timestamp),
creator,
duration_micros,
client_rate,
client_limit,
hunt_state,
hunt_state_comment,
UNIX_TIMESTAMP(init_start_time),
UNIX_TIMESTAMP(last_start_time),
description
FROM hunts """
args = []
components = []
if with_creator is not None:
components.append("creator = %s ")
args.append(with_creator)
if created_after is not None:
components.append("create_timestamp > FROM_UNIXTIME(%s) ")
args.append(mysql_utils.RDFDatetimeToTimestamp(created_after))
if with_description_match is not None:
components.append("description LIKE %s")
args.append("%" + with_description_match + "%")
if components:
query += "WHERE " + " AND ".join(components)
query += " ORDER BY create_timestamp DESC LIMIT %s OFFSET %s"
args.append(count)
args.append(offset)
cursor.execute(query, args)
result = []
for row in cursor.fetchall():
(hunt_id, create_timestamp, last_update_timestamp, creator,
duration_micros, client_rate, client_limit, hunt_state,
hunt_state_comment, init_start_time, last_start_time,
description) = row
result.append(
rdf_hunt_objects.HuntMetadata(
hunt_id=db_utils.IntToHuntID(hunt_id),
description=description or None,
create_time=mysql_utils.TimestampToRDFDatetime(
create_timestamp),
creator=creator,
duration=rdfvalue.Duration.From(duration_micros,
rdfvalue.MICROSECONDS),
client_rate=client_rate,
client_limit=client_limit,
hunt_state=hunt_state,
hunt_state_comment=hunt_state_comment or None,
last_update_time=mysql_utils.TimestampToRDFDatetime(
last_update_timestamp),
init_start_time=mysql_utils.TimestampToRDFDatetime(
init_start_time),
last_start_time=mysql_utils.TimestampToRDFDatetime(
last_start_time)))
return result
def ReadLatestPathInfosWithHashBlobReferences(self,
client_paths,
max_timestamp=None,
cursor=None):
"""Returns PathInfos that have corresponding HashBlobReferences."""
path_infos = {client_path: None for client_path in client_paths}
path_id_components = {}
for client_path in client_paths:
path_id_components[client_path.path_id] = client_path.components
params = []
query = """
SELECT t.client_id, t.path_type, t.path_id, UNIX_TIMESTAMP(t.timestamp),
s.stat_entry, h.hash_entry
FROM (SELECT h.client_id, h.path_type, h.path_id,
MAX(h.timestamp) AS timestamp
FROM client_path_hash_entries AS h
INNER JOIN hash_blob_references AS b
ON b.hash_id = h.sha256
WHERE {conditions}
GROUP BY client_id, path_type, path_id) AS t
LEFT JOIN client_path_stat_entries AS s
ON s.client_id = t.client_id
AND s.path_type = t.path_type
AND s.path_id = t.path_id
AND s.timestamp = t.timestamp
LEFT JOIN client_path_hash_entries AS h
ON h.client_id = t.client_id
AND h.path_type = t.path_type
AND h.path_id = t.path_id
AND h.timestamp = t.timestamp
"""
path_conditions = []
for client_path in client_paths:
path_conditions.append("""
(client_id = %s AND path_type = %s AND path_id = %s)
""")
params.append(db_utils.ClientIDToInt(client_path.client_id))
params.append(int(client_path.path_type))
params.append(client_path.path_id.AsBytes())
conditions = " OR ".join(path_conditions)
if max_timestamp is not None:
conditions = "({}) AND UNIX_TIMESTAMP(timestamp) <= %s".format(
conditions)
params.append(mysql_utils.RDFDatetimeToTimestamp(max_timestamp))
cursor.execute(query.format(conditions=conditions), params)
for row in cursor.fetchall():
# pyformat: disable
(client_id, path_type, path_id_bytes, timestamp, stat_entry_bytes,
hash_entry_bytes) = row
# pyformat: enable
path_id = rdf_objects.PathID.FromBytes(path_id_bytes)
components = path_id_components[path_id]
if stat_entry_bytes is not None:
stat_entry = rdf_client_fs.StatEntry.FromSerializedString(
stat_entry_bytes)
else:
stat_entry = None
hash_entry = rdf_crypto.Hash.FromSerializedString(hash_entry_bytes)
client_path = db.ClientPath(
client_id=db_utils.IntToClientID(client_id),
path_type=path_type,
components=path_id_components[path_id])
path_info = rdf_objects.PathInfo(
path_type=path_type,
components=components,
stat_entry=stat_entry,
hash_entry=hash_entry,
timestamp=mysql_utils.TimestampToRDFDatetime(timestamp))
path_infos[client_path] = path_info
return path_infos
def ReadPathInfosHistories(self,
client_id,
path_type,
components_list,
cursor=None):
"""Reads a collection of hash and stat entries for given paths."""
# MySQL does not handle well empty `IN` clauses so we guard against that.
if not components_list:
return {}
path_infos = {components: [] for components in components_list}
path_id_components = {}
for components in components_list:
path_id = rdf_objects.PathID.FromComponents(components)
path_id_components[path_id] = components
# MySQL does not support full outer joins, so we emulate them with a union.
query = """
SELECT s.path_id, s.stat_entry, UNIX_TIMESTAMP(s.timestamp),
h.path_id, h.hash_entry, UNIX_TIMESTAMP(h.timestamp)
FROM client_path_stat_entries AS s
LEFT JOIN client_path_hash_entries AS h
ON s.client_id = h.client_id
AND s.path_type = h.path_type
AND s.path_id = h.path_id
AND s.timestamp = h.timestamp
WHERE s.client_id = %(client_id)s
AND s.path_type = %(path_type)s
AND s.path_id IN %(path_ids)s
UNION
SELECT s.path_id, s.stat_entry, UNIX_TIMESTAMP(s.timestamp),
h.path_id, h.hash_entry, UNIX_TIMESTAMP(h.timestamp)
FROM client_path_hash_entries AS h
LEFT JOIN client_path_stat_entries AS s
ON h.client_id = s.client_id
AND h.path_type = s.path_type
AND h.path_id = s.path_id
AND h.timestamp = s.timestamp
WHERE h.client_id = %(client_id)s
AND h.path_type = %(path_type)s
AND h.path_id IN %(path_ids)s
"""
params = {
"client_id": db_utils.ClientIDToInt(client_id),
"path_type": int(path_type),
"path_ids": [path_id.AsBytes() for path_id in path_id_components]
}
cursor.execute(query, params)
for row in cursor.fetchall():
# pyformat: disable
(stat_entry_path_id_bytes, stat_entry_bytes, stat_entry_timestamp,
hash_entry_path_id_bytes, hash_entry_bytes,
hash_entry_timestamp) = row
# pyformat: enable
path_id_bytes = stat_entry_path_id_bytes or hash_entry_path_id_bytes
path_id = rdf_objects.PathID.FromBytes(path_id_bytes)
components = path_id_components[path_id]
timestamp = stat_entry_timestamp or hash_entry_timestamp
if stat_entry_bytes is not None:
stat_entry = rdf_client_fs.StatEntry.FromSerializedString(
stat_entry_bytes)
else:
stat_entry = None
if hash_entry_bytes is not None:
hash_entry = rdf_crypto.Hash.FromSerializedString(
hash_entry_bytes)
else:
hash_entry = None
path_info = rdf_objects.PathInfo(
path_type=path_type,
components=components,
stat_entry=stat_entry,
hash_entry=hash_entry,
timestamp=mysql_utils.TimestampToRDFDatetime(timestamp))
path_infos[components].append(path_info)
for components in components_list:
path_infos[components].sort(
key=lambda path_info: path_info.timestamp)
return path_infos
def ReadHuntResults(self,
hunt_id,
offset,
count,
with_tag=None,
with_type=None,
with_substring=None,
with_timestamp=None,
cursor=None):
"""Reads hunt results of a given hunt using given query options."""
hunt_id_int = db_utils.HuntIDToInt(hunt_id)
query = ("SELECT client_id, flow_id, hunt_id, payload, type, "
"UNIX_TIMESTAMP(timestamp), tag "
"FROM flow_results "
"FORCE INDEX(flow_results_hunt_id_flow_id_timestamp) "
"WHERE hunt_id = %s ")
args = [hunt_id_int]
if with_tag:
query += "AND tag = %s "
args.append(with_tag)
if with_type:
query += "AND type = %s "
args.append(with_type)
if with_substring:
query += "AND payload LIKE %s "
args.append("%" + db_utils.EscapeWildcards(with_substring) + "%")
if with_timestamp:
query += "AND timestamp = FROM_UNIXTIME(%s) "
args.append(mysql_utils.RDFDatetimeToTimestamp(with_timestamp))
query += "ORDER BY timestamp ASC LIMIT %s OFFSET %s"
args.append(count)
args.append(offset)
cursor.execute(query, args)
ret = []
for (
client_id_int,
flow_id_int,
hunt_id_int,
serialized_payload,
payload_type,
timestamp,
tag,
) in cursor.fetchall():
if payload_type in rdfvalue.RDFValue.classes:
payload = rdfvalue.RDFValue.classes[
payload_type].FromSerializedBytes(serialized_payload)
else:
payload = rdf_objects.SerializedValueOfUnrecognizedType(
type_name=payload_type, value=serialized_payload)
result = rdf_flow_objects.FlowResult(
client_id=db_utils.IntToClientID(client_id_int),
flow_id=db_utils.IntToFlowID(flow_id_int),
hunt_id=hunt_id,
payload=payload,
timestamp=mysql_utils.TimestampToRDFDatetime(timestamp))
if tag is not None:
result.tag = tag
ret.append(result)
return ret
def Now(self, cursor: MySQLdb.cursors.Cursor) -> rdfvalue.RDFDatetime:
cursor.execute("SELECT UNIX_TIMESTAMP(NOW(6))")
[(timestamp,)] = cursor.fetchall()
return mysql_utils.TimestampToRDFDatetime(timestamp)
def _CronJobRunFromRow(self, row):
serialized_run, timestamp = row
res = rdf_cronjobs.CronJobRun.FromSerializedBytes(serialized_run)
res.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp)
return res
def _AuditEntryFromRow(details, timestamp): entry = rdf_objects.APIAuditEntry.FromSerializedBytes(details) entry.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp) return entry
def ReadPathInfosHistories(
self,
client_id: Text,
path_type: rdf_objects.PathInfo.PathType,
components_list: Iterable[Sequence[Text]],
cutoff: Optional[rdfvalue.RDFDatetime] = None,
cursor: Optional[MySQLdb.cursors.Cursor] = None
) -> Dict[Sequence[Text], Sequence[rdf_objects.PathInfo]]:
"""Reads a collection of hash and stat entries for given paths."""
# MySQL does not handle well empty `IN` clauses so we guard against that.
if not components_list:
return {}
path_infos = {components: [] for components in components_list}
path_id_components = {}
for components in components_list:
path_id = rdf_objects.PathID.FromComponents(components)
path_id_components[path_id] = components
params = {
"client_id": db_utils.ClientIDToInt(client_id),
"path_type": int(path_type),
}
for path_id in path_id_components:
params["path_id_%s" % path_id.AsHexString()] = path_id.AsBytes()
path_id_placeholders = ", ".join([
"%(path_id_{})s".format(path_id.AsHexString())
for path_id in path_id_components
])
if cutoff is not None:
stat_entry_timestamp_condition = """
AND s.timestamp <= FROM_UNIXTIME(%(cutoff)s)
"""
hash_entry_timestamp_condition = """
AND h.timestamp <= FROM_UNIXTIME(%(cutoff)s)
"""
params["cutoff"] = mysql_utils.RDFDatetimeToTimestamp(cutoff)
else:
stat_entry_timestamp_condition = ""
hash_entry_timestamp_condition = ""
# MySQL does not support full outer joins, so we emulate them with a union.
query = """
SELECT s.path_id, s.stat_entry, UNIX_TIMESTAMP(s.timestamp),
h.path_id, h.hash_entry, UNIX_TIMESTAMP(h.timestamp)
FROM client_path_stat_entries AS s
LEFT JOIN client_path_hash_entries AS h
ON s.client_id = h.client_id
AND s.path_type = h.path_type
AND s.path_id = h.path_id
AND s.timestamp = h.timestamp
WHERE s.client_id = %(client_id)s
AND s.path_type = %(path_type)s
AND s.path_id IN ({path_id_placeholders})
{stat_entry_timestamp_condition}
UNION
SELECT s.path_id, s.stat_entry, UNIX_TIMESTAMP(s.timestamp),
h.path_id, h.hash_entry, UNIX_TIMESTAMP(h.timestamp)
FROM client_path_hash_entries AS h
LEFT JOIN client_path_stat_entries AS s
ON h.client_id = s.client_id
AND h.path_type = s.path_type
AND h.path_id = s.path_id
AND h.timestamp = s.timestamp
WHERE h.client_id = %(client_id)s
AND h.path_type = %(path_type)s
AND h.path_id IN ({path_id_placeholders})
{hash_entry_timestamp_condition}
""".format(
stat_entry_timestamp_condition=stat_entry_timestamp_condition,
hash_entry_timestamp_condition=hash_entry_timestamp_condition,
path_id_placeholders=path_id_placeholders)
cursor.execute(query, params)
for row in cursor.fetchall():
# pyformat: disable
(stat_entry_path_id_bytes, stat_entry_bytes, stat_entry_timestamp,
hash_entry_path_id_bytes, hash_entry_bytes, hash_entry_timestamp) = row
# pyformat: enable
path_id_bytes = stat_entry_path_id_bytes or hash_entry_path_id_bytes
path_id = rdf_objects.PathID.FromSerializedBytes(path_id_bytes)
components = path_id_components[path_id]
timestamp = stat_entry_timestamp or hash_entry_timestamp
if stat_entry_bytes is not None:
stat_entry = rdf_client_fs.StatEntry.FromSerializedBytes(
stat_entry_bytes)
else:
stat_entry = None
if hash_entry_bytes is not None:
hash_entry = rdf_crypto.Hash.FromSerializedBytes(hash_entry_bytes)
else:
hash_entry = None
path_info = rdf_objects.PathInfo(
path_type=path_type,
components=components,
stat_entry=stat_entry,
hash_entry=hash_entry,
timestamp=mysql_utils.TimestampToRDFDatetime(timestamp))
path_infos[components].append(path_info)
for components in components_list:
path_infos[components].sort(key=lambda path_info: path_info.timestamp)
return path_infos