def WriteClientSnapshot(self, snapshot, cursor=None):
"""Write new client snapshot."""
insert_history_query = (
"INSERT INTO client_snapshot_history(client_id, timestamp, "
"client_snapshot) VALUES (%s, FROM_UNIXTIME(%s), %s)")
insert_startup_query = (
"INSERT INTO client_startup_history(client_id, timestamp, "
"startup_info) VALUES(%s, FROM_UNIXTIME(%s), %s)")
now = rdfvalue.RDFDatetime.Now()
current_timestamp = mysql_utils.RDFDatetimeToTimestamp(now)
client_info = {
"last_snapshot_timestamp": current_timestamp,
"last_startup_timestamp": current_timestamp,
"last_version_string": snapshot.GetGRRVersionString(),
"last_platform": snapshot.knowledge_base.os,
"last_platform_release": snapshot.Uname(),
}
update_clauses = [
"last_snapshot_timestamp = FROM_UNIXTIME(%(last_snapshot_timestamp)s)",
"last_startup_timestamp = FROM_UNIXTIME(%(last_startup_timestamp)s)",
"last_version_string = %(last_version_string)s",
"last_platform = %(last_platform)s",
"last_platform_release = %(last_platform_release)s",
]
update_query = (
"UPDATE clients SET {} WHERE client_id = %(client_id)s".format(
", ".join(update_clauses)))
int_client_id = db_utils.ClientIDToInt(snapshot.client_id)
client_info["client_id"] = int_client_id
startup_info = snapshot.startup_info
snapshot.startup_info = None
try:
cursor.execute(insert_history_query,
(int_client_id, current_timestamp,
snapshot.SerializeToString()))
cursor.execute(insert_startup_query,
(int_client_id, current_timestamp,
startup_info.SerializeToString()))
cursor.execute(update_query, client_info)
except MySQLdb.IntegrityError as e:
raise db.UnknownClientError(snapshot.client_id, cause=e)
finally:
snapshot.startup_info = startup_info
def UpdateUserNotifications(self,
username,
timestamps,
state=None,
cursor=None):
"""Updates existing user notification objects."""
query = ("UPDATE user_notification "
"SET notification_state = %s "
"WHERE username_hash = %s"
"AND UNIX_TIMESTAMP(timestamp) IN {}").format(
mysql_utils.Placeholders(len(timestamps)))
args = [
int(state),
mysql_utils.Hash(username),
] + [mysql_utils.RDFDatetimeToTimestamp(t) for t in timestamps]
cursor.execute(query, args)
def WriteForemanRule(self, rule, cursor=None):
"""Writes a foreman rule to the database."""
query = (
"INSERT INTO foreman_rules "
" (hunt_id, expiration_time, rule) "
"VALUES (%(hunt_id)s, FROM_UNIXTIME(%(exp_time)s), %(rule_bytes)s) "
"ON DUPLICATE KEY UPDATE "
" expiration_time=FROM_UNIXTIME(%(exp_time)s), rule=%(rule_bytes)s")
cursor.execute(
query, {
"hunt_id":
rule.hunt_id,
"exp_time":
mysql_utils.RDFDatetimeToTimestamp(rule.expiration_time),
"rule_bytes":
rule.SerializeToBytes()
})
def MultiReadClientFullInfo(self,
client_ids,
min_last_ping=None,
cursor=None):
"""Reads full client information for a list of clients."""
if not client_ids:
return {}
query = (
"SELECT "
"c.client_id, c.fleetspeak_enabled, c.certificate, "
"UNIX_TIMESTAMP(c.last_ping), UNIX_TIMESTAMP(c.last_clock), "
"c.last_ip, UNIX_TIMESTAMP(c.last_foreman), "
"UNIX_TIMESTAMP(c.first_seen), "
"UNIX_TIMESTAMP(c.last_snapshot_timestamp), "
"UNIX_TIMESTAMP(c.last_crash_timestamp), "
"UNIX_TIMESTAMP(c.last_startup_timestamp), "
"h.client_snapshot, s.startup_info, s_last.startup_info, "
"l.owner_username, l.label "
"FROM clients as c "
"FORCE INDEX (PRIMARY) "
"LEFT JOIN client_snapshot_history as h FORCE INDEX (PRIMARY) ON ( "
"c.client_id = h.client_id AND "
"h.timestamp = c.last_snapshot_timestamp) "
"LEFT JOIN client_startup_history as s FORCE INDEX (PRIMARY) ON ( "
"c.client_id = s.client_id AND "
"s.timestamp = c.last_snapshot_timestamp) "
"LEFT JOIN client_startup_history as s_last FORCE INDEX (PRIMARY) ON ( "
"c.client_id = s_last.client_id "
"AND s_last.timestamp = c.last_startup_timestamp) "
"LEFT JOIN client_labels AS l FORCE INDEX (PRIMARY) "
"ON (c.client_id = l.client_id) ")
query += "WHERE c.client_id IN (%s) " % ", ".join(
["%s"] * len(client_ids))
values = [db_utils.ClientIDToInt(cid) for cid in client_ids]
if min_last_ping is not None:
query += "AND c.last_ping >= FROM_UNIXTIME(%s)"
values.append(mysql_utils.RDFDatetimeToTimestamp(min_last_ping))
cursor.execute(query, values)
return dict(self._ResponseToClientsFullInfo(cursor.fetchall()))
def WriteAPIAuditEntry(self, entry, cursor=None):
"""Writes an audit entry to the database."""
args = {
"username":
entry.username,
"router_method_name":
entry.router_method_name,
"details":
entry.SerializeToString(),
"timestamp":
mysql_utils.RDFDatetimeToTimestamp(rdfvalue.RDFDatetime.Now()),
}
query = """
INSERT INTO api_audit_entry (username, router_method_name, details,
timestamp)
VALUES (%(username)s, %(router_method_name)s, %(details)s,
FROM_UNIXTIME(%(timestamp)s))
"""
cursor.execute(query, args)
def WriteClientStats(self, client_id, stats, cursor=None):
"""Stores a ClientStats instance."""
try:
cursor.execute(
"""
INSERT INTO client_stats (client_id, payload, timestamp)
VALUES (%s, %s, FROM_UNIXTIME(%s))
ON DUPLICATE KEY UPDATE payload=VALUES(payload)
""", [
db_utils.ClientIDToInt(client_id),
stats.SerializeToBytes(),
mysql_utils.RDFDatetimeToTimestamp(
rdfvalue.RDFDatetime.Now())
])
except MySQLdb.IntegrityError as e:
if e.args[0] == mysql_error_constants.NO_REFERENCED_ROW_2:
raise db.UnknownClientError(client_id, cause=e)
else:
raise
def ListClientsForKeywords(self, keywords, start_time=None, cursor=None):
"""Lists the clients associated with keywords."""
keywords = set(keywords)
hash_to_kw = {mysql_utils.Hash(kw): kw for kw in keywords}
result = {kw: [] for kw in keywords}
query = """
SELECT keyword_hash, client_id
FROM client_keywords
FORCE INDEX (client_index_by_keyword_hash)
WHERE keyword_hash IN ({})
""".format(", ".join(["%s"] * len(result)))
args = list(iterkeys(hash_to_kw))
if start_time:
query += " AND timestamp >= FROM_UNIXTIME(%s)"
args.append(mysql_utils.RDFDatetimeToTimestamp(start_time))
cursor.execute(query, args)
for kw_hash, cid in cursor.fetchall():
result[hash_to_kw[kw_hash]].append(db_utils.IntToClientID(cid))
return result
def WriteCronJobRun(self, run_object, cursor=None):
"""Stores a cron job run object in the database."""
query = ("INSERT INTO cron_job_runs "
"(job_id, run_id, write_time, run) "
"VALUES (%s, %s, FROM_UNIXTIME(%s), %s) "
"ON DUPLICATE KEY UPDATE "
"run=VALUES(run), write_time=VALUES(write_time)")
write_time_str = mysql_utils.RDFDatetimeToTimestamp(
rdfvalue.RDFDatetime.Now())
try:
cursor.execute(query, [
run_object.cron_job_id,
db_utils.CronJobRunIDToInt(run_object.run_id),
write_time_str,
run_object.SerializeToBytes(),
])
except MySQLdb.IntegrityError as e:
raise db.UnknownCronJobError("CronJob with id %s not found." %
run_object.cron_job_id,
cause=e)
def WriteApprovalRequest(self, approval_request, cursor=None):
"""Writes an approval request object."""
# Copy the approval_request to ensure we don't modify the source object.
approval_request = approval_request.Copy()
# Generate random approval id.
approval_id_int = random.UInt64()
grants = approval_request.grants
approval_request.grants = None
expiry_time = approval_request.expiration_time
args = {
"username_hash":
mysql_utils.Hash(approval_request.requestor_username),
"approval_type": int(approval_request.approval_type),
"subject_id": approval_request.subject_id,
"approval_id": approval_id_int,
"expiration_time": mysql_utils.RDFDatetimeToTimestamp(expiry_time),
"approval_request": approval_request.SerializeToString()
}
query = """
INSERT INTO approval_request (username_hash, approval_type,
subject_id, approval_id, expiration_time,
approval_request)
VALUES (%(username_hash)s, %(approval_type)s,
%(subject_id)s, %(approval_id)s, FROM_UNIXTIME(%(expiration_time)s),
%(approval_request)s)
"""
cursor.execute(query, args)
for grant in grants:
self._GrantApproval(approval_request.requestor_username,
approval_id_int, grant.grantor_username,
cursor)
return _IntToApprovalID(approval_id_int)
def UpdateCronJob(self,
cronjob_id,
last_run_status=db.Database.unchanged,
last_run_time=db.Database.unchanged,
current_run_id=db.Database.unchanged,
state=db.Database.unchanged,
forced_run_requested=db.Database.unchanged,
cursor=None):
"""Updates run information for an existing cron job."""
updates = []
args = []
if last_run_status != db.Database.unchanged:
updates.append("last_run_status=%s")
args.append(int(last_run_status))
if last_run_time != db.Database.unchanged:
updates.append("last_run_time=FROM_UNIXTIME(%s)")
args.append(mysql_utils.RDFDatetimeToTimestamp(last_run_time))
if current_run_id != db.Database.unchanged:
updates.append("current_run_id=%s")
args.append(db_utils.CronJobRunIDToInt(current_run_id))
if state != db.Database.unchanged:
updates.append("state=%s")
args.append(state.SerializeToBytes())
if forced_run_requested != db.Database.unchanged:
updates.append("forced_run_requested=%s")
args.append(forced_run_requested)
if not updates:
return
query = "UPDATE cron_jobs SET "
query += ", ".join(updates)
query += " WHERE job_id=%s"
res = cursor.execute(query, args + [cronjob_id])
if res != 1:
raise db.UnknownCronJobError("CronJob with id %s not found." %
cronjob_id)
def WriteClientGraphSeries(
self,
graph_series: rdf_stats.ClientGraphSeries,
client_label: Text,
timestamp: rdfvalue.RDFDatetime,
cursor=None,
):
"""Writes the provided graphs to the DB with the given client label."""
args = {
"client_label": client_label,
"report_type": graph_series.report_type.SerializeToWireFormat(),
"timestamp": mysql_utils.RDFDatetimeToTimestamp(timestamp),
"graph_series": graph_series.SerializeToBytes(),
}
query = """
INSERT INTO client_report_graphs (client_label, report_type, timestamp,
graph_series)
VALUES (%(client_label)s, %(report_type)s, FROM_UNIXTIME(%(timestamp)s),
%(graph_series)s)
ON DUPLICATE KEY UPDATE graph_series = VALUES(graph_series)
"""
cursor.execute(query, args)
def ReadPathInfo(self,
client_id,
path_type,
components,
timestamp=None,
cursor=None):
"""Retrieves a path info record for a given path."""
if timestamp is None:
path_infos = self.ReadPathInfos(client_id, path_type, [components])
path_info = path_infos[components]
if path_info is None:
raise db.UnknownPathError(client_id=client_id,
path_type=path_type,
components=components)
return path_info
query = """
SELECT directory, UNIX_TIMESTAMP(p.timestamp),
stat_entry, UNIX_TIMESTAMP(last_stat_entry_timestamp),
hash_entry, UNIX_TIMESTAMP(last_hash_entry_timestamp)
FROM client_paths as p
LEFT JOIN (SELECT client_id, path_type, path_id, stat_entry
FROM client_path_stat_entries
WHERE client_id = %(client_id)s
AND path_type = %(path_type)s
AND path_id = %(path_id)s
AND UNIX_TIMESTAMP(timestamp) <= %(timestamp)s
ORDER BY timestamp DESC
LIMIT 1) AS s
ON p.client_id = s.client_id
AND p.path_type = s.path_type
AND p.path_id = s.path_id
LEFT JOIN (SELECT client_id, path_type, path_id, hash_entry
FROM client_path_hash_entries
WHERE client_id = %(client_id)s
AND path_type = %(path_type)s
AND path_id = %(path_id)s
AND UNIX_TIMESTAMP(timestamp) <= %(timestamp)s
ORDER BY timestamp DESC
LIMIT 1) AS h
ON p.client_id = h.client_id
AND p.path_type = h.path_type
AND p.path_id = h.path_id
WHERE p.client_id = %(client_id)s
AND p.path_type = %(path_type)s
AND p.path_id = %(path_id)s
"""
values = {
"client_id": db_utils.ClientIDToInt(client_id),
"path_type": int(path_type),
"path_id": rdf_objects.PathID.FromComponents(components).AsBytes(),
"timestamp": mysql_utils.RDFDatetimeToTimestamp(timestamp),
}
cursor.execute(query, values)
row = cursor.fetchone()
if row is None:
raise db.UnknownPathError(client_id=client_id,
path_type=path_type,
components=components)
# pyformat: disable
(directory, timestamp, stat_entry_bytes, last_stat_entry_timestamp,
hash_entry_bytes, last_hash_entry_timestamp) = row
# pyformat: enable
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
datetime = mysql_utils.TimestampToRDFDatetime
return rdf_objects.PathInfo(
path_type=path_type,
components=components,
timestamp=datetime(timestamp),
last_stat_entry_timestamp=datetime(last_stat_entry_timestamp),
last_hash_entry_timestamp=datetime(last_hash_entry_timestamp),
directory=directory,
stat_entry=stat_entry,
hash_entry=hash_entry)
def RemoveExpiredForemanRules(self, cursor=None):
now = rdfvalue.RDFDatetime.Now()
cursor.execute(
"DELETE FROM foreman_rules WHERE expiration_time < FROM_UNIXTIME(%s)",
[mysql_utils.RDFDatetimeToTimestamp(now)])
def DeleteOldCronJobRuns(self, cutoff_timestamp, cursor=None):
"""Deletes cron job runs that are older then the given timestamp."""
query = "DELETE FROM cron_job_runs WHERE write_time < FROM_UNIXTIME(%s)"
cursor.execute(query,
[mysql_utils.RDFDatetimeToTimestamp(cutoff_timestamp)])
def WritePathInfos(
self,
client_id: str,
path_infos: Sequence[rdf_objects.PathInfo],
cursor: Optional[MySQLdb.cursors.Cursor] = None,
) -> None:
"""Writes a collection of path_info records for a client."""
now = rdfvalue.RDFDatetime.Now()
int_client_id = db_utils.ClientIDToInt(client_id)
path_info_values = []
parent_path_info_values = []
stat_entry_keys = []
stat_entry_values = []
hash_entry_keys = []
hash_entry_values = []
for path_info in path_infos:
path = mysql_utils.ComponentsToPath(path_info.components)
key = (
int_client_id,
int(path_info.path_type),
path_info.GetPathID().AsBytes(),
)
path_info_values.append(key +
(mysql_utils.RDFDatetimeToTimestamp(now),
path, bool(path_info.directory),
len(path_info.components)))
if path_info.HasField("stat_entry"):
stat_entry_keys.extend(key)
stat_entry_values.append(
key + (mysql_utils.RDFDatetimeToTimestamp(now),
path_info.stat_entry.SerializeToBytes()))
if path_info.HasField("hash_entry"):
hash_entry_keys.extend(key)
hash_entry_values.append(
key + (mysql_utils.RDFDatetimeToTimestamp(now),
path_info.hash_entry.SerializeToBytes(),
path_info.hash_entry.sha256.AsBytes()))
# TODO(hanuszczak): Implement a trie in order to avoid inserting
# duplicated records.
for parent_path_info in path_info.GetAncestors():
path = mysql_utils.ComponentsToPath(
parent_path_info.components)
parent_path_info_values.append((
int_client_id,
int(parent_path_info.path_type),
parent_path_info.GetPathID().AsBytes(),
path,
len(parent_path_info.components),
))
if path_info_values:
query = """
INSERT INTO client_paths(client_id, path_type, path_id,
timestamp,
path, directory, depth)
VALUES (%s, %s, %s, FROM_UNIXTIME(%s), %s, %s, %s)
ON DUPLICATE KEY UPDATE
timestamp = VALUES(timestamp),
directory = directory OR VALUES(directory)
"""
try:
cursor.executemany(query, path_info_values)
except MySQLdb.IntegrityError as error:
raise db.UnknownClientError(client_id=client_id, cause=error)
if parent_path_info_values:
query = """
INSERT INTO client_paths(client_id, path_type, path_id, path,
directory, depth)
VALUES (%s, %s, %s, %s, TRUE, %s)
ON DUPLICATE KEY UPDATE
directory = TRUE,
timestamp = NOW(6)
"""
cursor.executemany(query, parent_path_info_values)
if stat_entry_values:
query = """
INSERT INTO client_path_stat_entries(client_id, path_type, path_id,
timestamp,
stat_entry)
VALUES (%s, %s, %s, FROM_UNIXTIME(%s), %s)
"""
cursor.executemany(query, stat_entry_values)
condition = "(client_id = %s AND path_type = %s AND path_id = %s)"
query = """
UPDATE client_paths
FORCE INDEX (PRIMARY)
SET last_stat_entry_timestamp = FROM_UNIXTIME(%s)
WHERE {}
""".format(" OR ".join([condition] * len(stat_entry_values)))
params = [mysql_utils.RDFDatetimeToTimestamp(now)
] + stat_entry_keys
cursor.execute(query, params)
if hash_entry_values:
query = """
INSERT INTO client_path_hash_entries(client_id, path_type, path_id,
timestamp,
hash_entry, sha256)
VALUES (%s, %s, %s, FROM_UNIXTIME(%s), %s, %s)
"""
cursor.executemany(query, hash_entry_values)
condition = "(client_id = %s AND path_type = %s AND path_id = %s)"
query = """
UPDATE client_paths
FORCE INDEX (PRIMARY)
SET last_hash_entry_timestamp = FROM_UNIXTIME(%s)
WHERE {}
""".format(" OR ".join([condition] * len(hash_entry_values)))
params = [mysql_utils.RDFDatetimeToTimestamp(now)
] + hash_entry_keys
cursor.execute(query, params)
def ListDescendentPathInfos(self,
client_id,
path_type,
components,
timestamp=None,
max_depth=None,
cursor=None):
"""Lists path info records that correspond to descendants of given path."""
path_infos = []
query = ""
path = mysql_utils.ComponentsToPath(components)
values = {
"client_id": db_utils.ClientIDToInt(client_id),
"path_type": int(path_type),
"path": db_utils.EscapeWildcards(path),
}
query += """
SELECT path, directory, UNIX_TIMESTAMP(p.timestamp),
stat_entry, UNIX_TIMESTAMP(last_stat_entry_timestamp),
hash_entry, UNIX_TIMESTAMP(last_hash_entry_timestamp)
FROM client_paths AS p
"""
if timestamp is None:
query += """
LEFT JOIN client_path_stat_entries AS s ON
(p.client_id = s.client_id AND
p.path_type = s.path_type AND
p.path_id = s.path_id AND
p.last_stat_entry_timestamp = s.timestamp)
LEFT JOIN client_path_hash_entries AS h ON
(p.client_id = h.client_id AND
p.path_type = h.path_type AND
p.path_id = h.path_id AND
p.last_hash_entry_timestamp = h.timestamp)
"""
only_explicit = False
else:
query += """
LEFT JOIN (SELECT sr.client_id, sr.path_type, sr.path_id, sr.stat_entry
FROM client_path_stat_entries AS sr
INNER JOIN (SELECT client_id, path_type, path_id,
MAX(timestamp) AS max_timestamp
FROM client_path_stat_entries
WHERE UNIX_TIMESTAMP(timestamp) <= %(timestamp)s
GROUP BY client_id, path_type, path_id) AS st
ON sr.client_id = st.client_id
AND sr.path_type = st.path_type
AND sr.path_id = st.path_id
AND sr.timestamp = st.max_timestamp) AS s
ON (p.client_id = s.client_id AND
p.path_type = s.path_type AND
p.path_id = s.path_id)
LEFT JOIN (SELECT hr.client_id, hr.path_type, hr.path_id, hr.hash_entry
FROM client_path_hash_entries AS hr
INNER JOIN (SELECT client_id, path_type, path_id,
MAX(timestamp) AS max_timestamp
FROM client_path_hash_entries
WHERE UNIX_TIMESTAMP(timestamp) <= %(timestamp)s
GROUP BY client_id, path_type, path_id) AS ht
ON hr.client_id = ht.client_id
AND hr.path_type = ht.path_type
AND hr.path_id = ht.path_id
AND hr.timestamp = ht.max_timestamp) AS h
ON (p.client_id = h.client_id AND
p.path_type = h.path_type AND
p.path_id = h.path_id)
"""
values["timestamp"] = mysql_utils.RDFDatetimeToTimestamp(timestamp)
only_explicit = True
query += """
WHERE p.client_id = %(client_id)s
AND p.path_type = %(path_type)s
AND path LIKE concat(%(path)s, '/%%')
"""
if max_depth is not None:
query += """
AND depth <= %(depth)s
"""
values["depth"] = len(components) + max_depth
cursor.execute(query, values)
for row in cursor.fetchall():
# pyformat: disable
(path, directory, timestamp, stat_entry_bytes,
last_stat_entry_timestamp, hash_entry_bytes,
last_hash_entry_timestamp) = row
# pyformat: enable
components = mysql_utils.PathToComponents(path)
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
datetime = mysql_utils.TimestampToRDFDatetime
path_info = rdf_objects.PathInfo(
path_type=path_type,
components=components,
timestamp=datetime(timestamp),
last_stat_entry_timestamp=datetime(last_stat_entry_timestamp),
last_hash_entry_timestamp=datetime(last_hash_entry_timestamp),
directory=directory,
stat_entry=stat_entry,
hash_entry=hash_entry)
path_infos.append(path_info)
path_infos.sort(key=lambda _: tuple(_.components))
# For specific timestamp, we return information only about explicit paths
# (paths that have associated stat or hash entry or have an ancestor that is
# explicit).
if not only_explicit:
return path_infos
explicit_path_infos = []
has_explicit_ancestor = set()
# This list is sorted according to the keys component, so by traversing it
# in the reverse order we make sure that we process deeper paths first.
for path_info in reversed(path_infos):
components = tuple(path_info.components)
if (path_info.HasField("stat_entry")
or path_info.HasField("hash_entry")
or components in has_explicit_ancestor):
explicit_path_infos.append(path_info)
has_explicit_ancestor.add(components[:-1])
# Since we collected explicit paths in reverse order, we need to reverse it
# again to conform to the interface.
return list(reversed(explicit_path_infos))
def ReadPathInfosHistories(self,
client_id,
path_type,
components_list,
cutoff=None,
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
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]
}
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_ids)s
{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_ids)s
{hash_entry_timestamp_condition}
""".format(stat_entry_timestamp_condition=stat_entry_timestamp_condition,
hash_entry_timestamp_condition=hash_entry_timestamp_condition)
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
def WriteClientSnapshotHistory(self, clients, cursor=None):
"""Writes the full history for a particular client."""
client_id = clients[0].client_id
latest_timestamp = max(client.timestamp for client in clients)
base_params = {
"client_id":
db_utils.ClientIDToInt(client_id),
"latest_timestamp":
mysql_utils.RDFDatetimeToTimestamp(latest_timestamp)
}
try:
for client in clients:
startup_info = client.startup_info
client.startup_info = None
params = base_params.copy()
params.update({
"timestamp":
mysql_utils.RDFDatetimeToTimestamp(client.timestamp),
"client_snapshot":
client.SerializeToBytes(),
"startup_info":
startup_info.SerializeToBytes(),
})
cursor.execute(
"""
INSERT INTO client_snapshot_history (client_id, timestamp,
client_snapshot)
VALUES (%(client_id)s, FROM_UNIXTIME(%(timestamp)s),
%(client_snapshot)s)
""", params)
cursor.execute(
"""
INSERT INTO client_startup_history (client_id, timestamp,
startup_info)
VALUES (%(client_id)s, FROM_UNIXTIME(%(timestamp)s),
%(startup_info)s)
""", params)
client.startup_info = startup_info
cursor.execute(
"""
UPDATE clients
SET last_snapshot_timestamp = FROM_UNIXTIME(%(latest_timestamp)s)
WHERE client_id = %(client_id)s
AND (last_snapshot_timestamp IS NULL OR
last_snapshot_timestamp < FROM_UNIXTIME(%(latest_timestamp)s))
""", base_params)
cursor.execute(
"""
UPDATE clients
SET last_startup_timestamp = FROM_UNIXTIME(%(latest_timestamp)s)
WHERE client_id = %(client_id)s
AND (last_startup_timestamp IS NULL OR
last_startup_timestamp < FROM_UNIXTIME(%(latest_timestamp)s))
""", base_params)
except MySQLdb.IntegrityError as error:
raise db.UnknownClientError(client_id, cause=error)
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 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 _MultiWritePathInfos(self, path_infos, cursor=None):
"""Writes a collection of path info records for specified clients."""
now = rdfvalue.RDFDatetime.Now()
path_info_values = []
parent_path_info_values = []
stat_entry_keys = []
stat_entry_values = []
hash_entry_keys = []
hash_entry_values = []
for client_id, client_path_infos in iteritems(path_infos):
for path_info in client_path_infos:
path = mysql_utils.ComponentsToPath(path_info.components)
key = (
db_utils.ClientIDToInt(client_id),
int(path_info.path_type),
path_info.GetPathID().AsBytes(),
)
path_info_values.append(key + (
mysql_utils.RDFDatetimeToTimestamp(now), path,
bool(path_info.directory), len(path_info.components)))
if path_info.HasField("stat_entry"):
stat_entry_keys.extend(key)
stat_entry_values.append(
key + (mysql_utils.RDFDatetimeToTimestamp(now),
path_info.stat_entry.SerializeToBytes()))
if path_info.HasField("hash_entry"):
hash_entry_keys.extend(key)
hash_entry_values.append(
key + (mysql_utils.RDFDatetimeToTimestamp(now),
path_info.hash_entry.SerializeToBytes(),
path_info.hash_entry.sha256.AsBytes()))
# TODO(hanuszczak): Implement a trie in order to avoid inserting
# duplicated records.
for parent_path_info in path_info.GetAncestors():
path = mysql_utils.ComponentsToPath(
parent_path_info.components)
parent_path_info_values.append((
db_utils.ClientIDToInt(client_id),
int(parent_path_info.path_type),
parent_path_info.GetPathID().AsBytes(),
path,
len(parent_path_info.components),
))
if path_info_values:
query = """
INSERT INTO client_paths(client_id, path_type, path_id,
timestamp,
path, directory, depth)
VALUES (%s, %s, %s, FROM_UNIXTIME(%s), %s, %s, %s)
ON DUPLICATE KEY UPDATE
timestamp = VALUES(timestamp),
directory = directory OR VALUES(directory)
"""
cursor.executemany(query, path_info_values)
if parent_path_info_values:
query = """
INSERT INTO client_paths(client_id, path_type, path_id, path,
directory, depth)
VALUES (%s, %s, %s, %s, TRUE, %s)
ON DUPLICATE KEY UPDATE
directory = TRUE,
timestamp = NOW(6)
"""
cursor.executemany(query, parent_path_info_values)
if stat_entry_values:
query = """
INSERT INTO client_path_stat_entries(client_id, path_type, path_id,
timestamp,
stat_entry)
VALUES (%s, %s, %s, FROM_UNIXTIME(%s), %s)
"""
cursor.executemany(query, stat_entry_values)
condition = "(client_id = %s AND path_type = %s AND path_id = %s)"
query = """
UPDATE client_paths
SET last_stat_entry_timestamp = FROM_UNIXTIME(%s)
WHERE {}
""".format(" OR ".join([condition] * len(stat_entry_values)))
params = [mysql_utils.RDFDatetimeToTimestamp(now)
] + stat_entry_keys
cursor.execute(query, params)
if hash_entry_values:
query = """
INSERT INTO client_path_hash_entries(client_id, path_type, path_id,
timestamp,
hash_entry, sha256)
VALUES (%s, %s, %s, FROM_UNIXTIME(%s), %s, %s)
"""
cursor.executemany(query, hash_entry_values)
condition = "(client_id = %s AND path_type = %s AND path_id = %s)"
query = """
UPDATE client_paths
SET last_hash_entry_timestamp = FROM_UNIXTIME(%s)
WHERE {}
""".format(" OR ".join([condition] * len(hash_entry_values)))
params = [mysql_utils.RDFDatetimeToTimestamp(now)
] + hash_entry_keys
cursor.execute(query, params)
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 _DeleteClientStats(self, limit, retention_time, cursor=None):
"""Deletes up to `limit` ClientStats older than `retention_time`."""
cursor.execute(
"DELETE FROM client_stats WHERE timestamp < FROM_UNIXTIME(%s) LIMIT %s",
[mysql_utils.RDFDatetimeToTimestamp(retention_time), limit])
return cursor.rowcount
def _CountClientStatisticByLabel(self, statistic, day_buckets, cursor):
"""Returns client-activity metrics for a given statistic.
Args:
statistic: The name of the statistic, which should also be a column in the
'clients' table.
day_buckets: A set of n-day-active buckets.
cursor: MySQL cursor for executing queries.
"""
day_buckets = sorted(day_buckets)
sum_clauses = []
ping_cast_clauses = []
timestamp_buckets = []
now = rdfvalue.RDFDatetime.Now()
for day_bucket in day_buckets:
column_name = "days_active_{}".format(day_bucket)
sum_clauses.append(
"CAST(SUM({0}) AS UNSIGNED) AS {0}".format(column_name))
ping_cast_clauses.append(
"CAST(c.last_ping > FROM_UNIXTIME(%s) AS UNSIGNED) AS {}".
format(column_name))
timestamp_bucket = now - rdfvalue.DurationSeconds.FromDays(
day_bucket)
timestamp_buckets.append(
mysql_utils.RDFDatetimeToTimestamp(timestamp_bucket))
# Count all clients with a label owned by 'GRR', aggregating by label.
query = """
SELECT j.{statistic}, j.label, {sum_clauses}
FROM (
SELECT c.{statistic} AS {statistic}, l.label AS label, {ping_cast_clauses}
FROM clients c
LEFT JOIN client_labels l USING(client_id)
WHERE c.last_ping IS NOT NULL AND l.owner_username = '******'
) AS j
GROUP BY j.{statistic}, j.label
""".format(statistic=statistic,
sum_clauses=", ".join(sum_clauses),
ping_cast_clauses=", ".join(ping_cast_clauses))
cursor.execute(query, timestamp_buckets)
fleet_stats_builder = fleet_utils.FleetStatsBuilder(day_buckets)
for response_row in cursor.fetchall():
statistic_value, client_label = response_row[:2]
for i, num_actives in enumerate(response_row[2:]):
if num_actives <= 0:
continue
fleet_stats_builder.IncrementLabel(client_label,
statistic_value,
day_buckets[i],
delta=num_actives)
# Get n-day-active totals for the statistic across all clients (including
# those that do not have a 'GRR' label).
query = """
SELECT j.{statistic}, {sum_clauses}
FROM (
SELECT c.{statistic} AS {statistic}, {ping_cast_clauses}
FROM clients c
WHERE c.last_ping IS NOT NULL
) AS j
GROUP BY j.{statistic}
""".format(statistic=statistic,
sum_clauses=", ".join(sum_clauses),
ping_cast_clauses=", ".join(ping_cast_clauses))
cursor.execute(query, timestamp_buckets)
for response_row in cursor.fetchall():
statistic_value = response_row[0]
for i, num_actives in enumerate(response_row[1:]):
if num_actives <= 0:
continue
fleet_stats_builder.IncrementTotal(statistic_value,
day_buckets[i],
delta=num_actives)
return fleet_stats_builder.Build()
def WriteClientMetadata(self,
client_id,
certificate=None,
fleetspeak_enabled=None,
first_seen=None,
last_ping=None,
last_clock=None,
last_ip=None,
last_foreman=None,
fleetspeak_validation_info=None,
cursor=None):
"""Write metadata about the client."""
placeholders = []
values = collections.OrderedDict()
placeholders.append("%(client_id)s")
values["client_id"] = db_utils.ClientIDToInt(client_id)
if certificate:
placeholders.append("%(certificate)s")
values["certificate"] = certificate.SerializeToBytes()
if fleetspeak_enabled is not None:
placeholders.append("%(fleetspeak_enabled)s")
values["fleetspeak_enabled"] = fleetspeak_enabled
if first_seen is not None:
placeholders.append("FROM_UNIXTIME(%(first_seen)s)")
values["first_seen"] = mysql_utils.RDFDatetimeToTimestamp(
first_seen)
if last_ping is not None:
placeholders.append("FROM_UNIXTIME(%(last_ping)s)")
values["last_ping"] = mysql_utils.RDFDatetimeToTimestamp(last_ping)
if last_clock:
placeholders.append("FROM_UNIXTIME(%(last_clock)s)")
values["last_clock"] = mysql_utils.RDFDatetimeToTimestamp(
last_clock)
if last_ip:
placeholders.append("%(last_ip)s")
values["last_ip"] = last_ip.SerializeToBytes()
if last_foreman:
placeholders.append("FROM_UNIXTIME(%(last_foreman)s)")
values["last_foreman"] = mysql_utils.RDFDatetimeToTimestamp(
last_foreman)
placeholders.append("%(last_fleetspeak_validation_info)s")
if fleetspeak_validation_info:
pb = rdf_client.FleetspeakValidationInfo.FromStringDict(
fleetspeak_validation_info)
values["last_fleetspeak_validation_info"] = pb.SerializeToBytes()
else:
# Write null for empty or non-existent validation info.
values["last_fleetspeak_validation_info"] = None
updates = []
for column in values.keys():
updates.append("{column} = VALUES({column})".format(column=column))
query = """
INSERT INTO clients ({columns})
VALUES ({placeholders})
ON DUPLICATE KEY UPDATE {updates}
""".format(columns=", ".join(values.keys()),
placeholders=", ".join(placeholders),
updates=", ".join(updates))
cursor.execute(query, values)
