def ReadClientStartupInfoHistory(self,
client_id,
timerange=None,
cursor=None):
"""Reads the full startup history for a particular client."""
client_id_int = mysql_utils.ClientIDToInt(client_id)
query = ("SELECT startup_info, 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 >= %s "
args.append(mysql_utils.RDFDatetimeToMysqlString(time_from))
if time_to is not None:
query += "AND timestamp <= %s "
args.append(mysql_utils.RDFDatetimeToMysqlString(time_to))
query += "ORDER BY timestamp DESC "
ret = []
cursor.execute(query, args)
for startup_info, timestamp in cursor.fetchall():
si = rdf_client.StartupInfo.FromSerializedString(startup_info)
si.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp)
ret.append(si)
return ret
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 = utils.PRNG.GetUInt64()
now_str = mysql_utils.RDFDatetimeToMysqlString(rdfvalue.RDFDatetime.Now())
grants = approval_request.grants
approval_request.grants = None
query = ("INSERT INTO approval_request (username, approval_type, "
"subject_id, approval_id, timestamp, expiration_time, "
"approval_request) VALUES (%s, %s, %s, %s, %s, %s, %s)")
args = [
approval_request.requestor_username,
int(approval_request.approval_type), approval_request.subject_id,
approval_id_int, now_str,
mysql_utils.RDFDatetimeToMysqlString(approval_request.expiration_time),
approval_request.SerializeToString()
]
cursor.execute(query, args)
for grant in grants:
grant_query = ("INSERT INTO approval_grant (username, approval_id, "
"grantor_username, timestamp) VALUES (%s, %s, %s, %s)")
grant_args = [
approval_request.requestor_username, approval_id_int,
grant.grantor_username, now_str
]
cursor.execute(grant_query, grant_args)
return _IntToApprovalID(approval_id_int)
def LeaseCronJobs(self, cronjob_ids=None, lease_time=None, cursor=None):
"""Leases all available cron jobs."""
now = rdfvalue.RDFDatetime.Now()
now_str = mysql_utils.RDFDatetimeToMysqlString(now)
expiry_str = mysql_utils.RDFDatetimeToMysqlString(now + lease_time)
id_str = utils.ProcessIdString()
query = ("UPDATE cron_jobs "
"SET leased_until=%s, leased_by=%s "
"WHERE (leased_until IS NULL OR leased_until < %s)")
args = [expiry_str, id_str, now_str]
if cronjob_ids:
query += " AND job_id in (%s)" % ", ".join(
["%s"] * len(cronjob_ids))
args += cronjob_ids
updated = cursor.execute(query, args)
if updated == 0:
return []
cursor.execute(
"SELECT job, create_time, disabled, "
"last_run_status, last_run_time, current_run_id, state, "
"leased_until, leased_by "
"FROM cron_jobs WHERE leased_until=%s AND leased_by=%s",
[expiry_str, id_str])
return [self._CronjobFromRow(row) for row in cursor.fetchall()]
def ReturnLeasedCronJobs(self, jobs, cursor=None):
"""Makes leased cron jobs available for leasing again."""
if not jobs:
return
unleased_jobs = []
conditions = []
args = []
for job in jobs:
if not job.leased_by or not job.leased_until:
unleased_jobs.append(job)
continue
conditions.append(
"(job_id=%s AND leased_until=%s AND leased_by=%s)")
args += [
job.job_id,
mysql_utils.RDFDatetimeToMysqlString(job.leased_until),
job.leased_by
]
if conditions:
query = ("UPDATE cron_jobs "
"SET leased_until=NULL, leased_by=NULL "
"WHERE ") + " OR ".join(conditions)
returned = cursor.execute(query, args)
if unleased_jobs:
raise ValueError("Cronjobs to return are not leased: %s" %
unleased_jobs)
if returned != len(jobs):
raise ValueError("%d cronjobs in %s could not be returned." %
((len(jobs) - returned), jobs))
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,
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=%s")
args.append(mysql_utils.RDFDatetimeToMysqlString(last_run_time))
if current_run_id != db.Database.unchanged:
updates.append("current_run_id=%s")
args.append(current_run_id or 0)
if state != db.Database.unchanged:
updates.append("state=%s")
args.append(state.SerializeToString())
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 WriteClientMetadata(self,
client_id,
certificate=None,
fleetspeak_enabled=None,
first_seen=None,
last_ping=None,
last_clock=None,
last_ip=None,
last_foreman=None,
cursor=None):
"""Write metadata about the client."""
columns = ["client_id"]
values = [mysql_utils.ClientIDToInt(client_id)]
if certificate:
columns.append("certificate")
values.append(certificate.SerializeToString())
if fleetspeak_enabled is not None:
columns.append("fleetspeak_enabled")
values.append(int(fleetspeak_enabled))
if first_seen:
columns.append("first_seen")
values.append(mysql_utils.RDFDatetimeToMysqlString(first_seen))
if last_ping:
columns.append("last_ping")
values.append(mysql_utils.RDFDatetimeToMysqlString(last_ping))
if last_clock:
columns.append("last_clock")
values.append(mysql_utils.RDFDatetimeToMysqlString(last_clock))
if last_ip:
columns.append("last_ip")
values.append(last_ip.SerializeToString())
if last_foreman:
columns.append("last_foreman")
values.append(mysql_utils.RDFDatetimeToMysqlString(last_foreman))
query = ("INSERT INTO clients ({cols}) VALUES ({vals}) "
"ON DUPLICATE KEY UPDATE {updates}").format(
cols=", ".join(columns),
vals=", ".join(["%s"] * len(columns)),
updates=", ".join([
"{c} = VALUES ({c})".format(c=col)
for col in columns[1:]
]))
cursor.execute(query, values)
def WriteForemanRule(self, rule, cursor=None):
query = ("INSERT INTO foreman_rules "
"(hunt_id, expiration_time, rule) VALUES (%s, %s, %s) "
"ON DUPLICATE KEY UPDATE expiration_time=%s, rule=%s")
exp_str = mysql_utils.RDFDatetimeToMysqlString(rule.expiration_time),
rule_str = rule.SerializeToString()
cursor.execute(query,
[rule.hunt_id, exp_str, rule_str, exp_str, rule_str])
def WriteClientSnapshotHistory(self, clients, cursor=None):
"""Writes the full history for a particular client."""
cid = mysql_utils.ClientIDToInt(clients[0].client_id)
latest_timestamp = None
for client in clients:
startup_info = client.startup_info
client.startup_info = None
timestamp = mysql_utils.RDFDatetimeToMysqlString(client.timestamp)
latest_timestamp = max(latest_timestamp, client.timestamp)
try:
cursor.execute(
"INSERT INTO client_snapshot_history "
"(client_id, timestamp, client_snapshot) "
"VALUES (%s, %s, %s)",
[cid, timestamp,
client.SerializeToString()])
cursor.execute(
"INSERT INTO client_startup_history "
"(client_id, timestamp, startup_info) "
"VALUES (%s, %s, %s)",
[cid, timestamp,
startup_info.SerializeToString()])
except MySQLdb.IntegrityError as e:
raise db.UnknownClientError(clients[0].client_id, cause=e)
finally:
client.startup_info = startup_info
latest_timestamp_str = mysql_utils.RDFDatetimeToMysqlString(
latest_timestamp)
cursor.execute(
"UPDATE clients SET last_client_timestamp=%s "
"WHERE client_id = %s AND "
"(last_client_timestamp IS NULL OR last_client_timestamp < %s)",
[latest_timestamp_str, cid, latest_timestamp_str])
cursor.execute(
"UPDATE clients SET last_startup_timestamp=%s "
"WHERE client_id = %s AND "
"(last_startup_timestamp IS NULL OR last_startup_timestamp < %s)",
[latest_timestamp_str, cid, latest_timestamp_str])
def WriteCronJob(self, cronjob, cursor=None):
query = ("INSERT IGNORE INTO cron_jobs "
"(job_id, job, create_time, disabled) "
"VALUES (%s, %s, %s, %s)")
create_time_str = mysql_utils.RDFDatetimeToMysqlString(
cronjob.create_time or rdfvalue.RDFDatetime.Now())
cursor.execute(query, [
cronjob.job_id,
cronjob.SerializeToString(), create_time_str, cronjob.disabled
])
def ReadClientSnapshotHistory(self,
client_id,
timerange=None,
cursor=None):
"""Reads the full history for a particular client."""
client_id_int = mysql_utils.ClientIDToInt(client_id)
query = (
"SELECT sn.client_snapshot, st.startup_info, 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 >= %s "
args.append(mysql_utils.RDFDatetimeToMysqlString(time_from))
if time_to is not None:
query += "AND sn.timestamp <= %s "
args.append(mysql_utils.RDFDatetimeToMysqlString(time_to))
query += "ORDER BY sn.timestamp DESC"
ret = []
cursor.execute(query, args)
for snapshot, startup_info, timestamp in cursor.fetchall():
client = objects.ClientSnapshot.FromSerializedString(snapshot)
client.startup_info = rdf_client.StartupInfo.FromSerializedString(
startup_info)
client.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp)
ret.append(client)
return ret
def ReadUserNotifications(self,
username,
state=None,
timerange=None,
cursor=None):
"""Reads notifications scheduled for a user within a given timerange."""
query = ("SELECT timestamp, notification_state, notification "
"FROM user_notification "
"WHERE username=%s ")
args = [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 >= %s "
args.append(mysql_utils.RDFDatetimeToMysqlString(time_from))
if time_to is not None:
query += "AND timestamp <= %s "
args.append(mysql_utils.RDFDatetimeToMysqlString(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.MysqlToRDFDatetime(timestamp)
n.state = state
ret.append(n)
return ret
def WriteAuditEvent(self, event, cursor=None):
"""Writes an audit event to the database."""
event = event.Copy()
if event.HasField("user"):
username = event.user
event.user = None
else:
username = None
if event.HasField("urn"):
urn = str(event.urn)
event.urn = None
else:
urn = None
if event.HasField("client"):
client_id = mysql_utils.ClientIDToInt(event.client.Basename())
event.client = None
else:
client_id = None
if event.HasField("timestamp"):
timestamp = mysql_utils.RDFDatetimeToMysqlString(event.timestamp)
event.timestamp = None
else:
timestamp = mysql_utils.RDFDatetimeToMysqlString(
rdfvalue.RDFDatetime.Now())
details = event.SerializeToString()
query = """
INSERT INTO audit_event (username, urn, client_id, timestamp, details)
VALUES (%s, %s, %s, %s, %s)
"""
values = (username, urn, client_id, timestamp, details)
cursor.execute(query, values)
def GrantApproval(self,
requestor_username,
approval_id,
grantor_username,
cursor=None):
"""Grants approval for a given request using given username."""
now_str = mysql_utils.RDFDatetimeToMysqlString(rdfvalue.RDFDatetime.Now())
grant_query = ("INSERT INTO approval_grant (username, approval_id, "
"grantor_username, timestamp) VALUES (%s, %s, %s, %s)")
grant_args = [
requestor_username,
_ApprovalIDToInt(approval_id), grantor_username, now_str
]
cursor.execute(grant_query, grant_args)
def WriteMessageHandlerRequests(self, requests, cursor=None):
"""Writes a list of message handler requests to the database."""
query = ("INSERT IGNORE INTO message_handler_requests "
"(handlername, timestamp, request_id, request) VALUES ")
now = mysql_utils.RDFDatetimeToMysqlString(rdfvalue.RDFDatetime.Now())
value_templates = []
args = []
for r in requests:
args.extend([r.handler_name, now, r.request_id, r.SerializeToString()])
value_templates.append("(%s, %s, %s, %s)")
query += ",".join(value_templates)
cursor.execute(query, args)
def LeaseClientMessages(self,
client_id,
lease_time=None,
limit=None,
cursor=None):
"""Leases available client messages for the client with the given id."""
now = rdfvalue.RDFDatetime.Now()
now_str = mysql_utils.RDFDatetimeToMysqlString(now)
expiry = now + lease_time
expiry_str = mysql_utils.RDFDatetimeToMysqlString(expiry)
proc_id_str = utils.ProcessIdString()
client_id_int = mysql_utils.ClientIDToInt(client_id)
query = ("UPDATE client_messages "
"SET leased_until=%s, leased_by=%s "
"WHERE client_id=%s AND "
"(leased_until IS NULL OR leased_until < %s) "
"LIMIT %s")
args = [expiry_str, proc_id_str, client_id_int, now_str, limit]
num_leased = cursor.execute(query, args)
if num_leased == 0:
return []
query = ("SELECT message FROM client_messages "
"WHERE client_id=%s AND leased_until=%s AND leased_by=%s")
cursor.execute(query, [client_id_int, expiry_str, proc_id_str])
ret = []
for msg, in cursor.fetchall():
message = rdf_flows.GrrMessage.FromSerializedString(msg)
message.leased_by = proc_id_str
message.leased_until = expiry
ret.append(message)
return ret
def LeaseMessageHandlerRequests(self,
lease_time=None,
limit=1000,
cursor=None):
"""Leases a number of message handler requests up to the indicated limit."""
now = rdfvalue.RDFDatetime.Now()
now_str = mysql_utils.RDFDatetimeToMysqlString(now)
expiry = now + lease_time
expiry_str = mysql_utils.RDFDatetimeToMysqlString(expiry)
query = ("UPDATE message_handler_requests "
"SET leased_until=%s, leased_by=%s "
"WHERE leased_until IS NULL OR leased_until < %s "
"LIMIT %s")
id_str = utils.ProcessIdString()
args = (expiry_str, id_str, now_str, limit)
updated = cursor.execute(query, args)
if updated == 0:
return []
cursor.execute(
"SELECT timestamp, request FROM message_handler_requests "
"WHERE leased_by=%s AND leased_until=%s LIMIT %s",
(id_str, expiry_str, updated))
res = []
for timestamp, request in cursor.fetchall():
req = objects.MessageHandlerRequest.FromSerializedString(request)
req.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp)
req.leased_until = expiry
req.leased_by = id_str
res.append(req)
return res
def WriteClientCrashInfo(self, client_id, crash_info, cursor=None):
"""Writes a new client crash record."""
cid = mysql_utils.ClientIDToInt(client_id)
now = mysql_utils.RDFDatetimeToMysqlString(rdfvalue.RDFDatetime.Now())
try:
cursor.execute(
"INSERT INTO client_crash_history (client_id, timestamp, crash_info) "
"VALUES (%s, %s, %s)",
[cid, now, crash_info.SerializeToString()])
cursor.execute(
"UPDATE clients SET last_crash_timestamp = %s WHERE client_id=%s",
[now, cid])
except MySQLdb.IntegrityError as e:
raise db.UnknownClientError(client_id, cause=e)
def UpdateUserNotifications(self,
username,
timestamps,
state=None,
cursor=None):
"""Updates existing user notification objects."""
query = ("UPDATE user_notification n "
"SET n.notification_state = %s "
"WHERE n.username = %s AND n.timestamp IN ({})").format(", ".join(
["%s"] * len(timestamps)))
args = [
int(state),
username,
] + [mysql_utils.RDFDatetimeToMysqlString(t) for t in timestamps]
cursor.execute(query, args)
def WriteUserNotification(self, notification, cursor=None):
"""Writes a notification for a given user."""
# Copy the notification to ensure we don't modify the source object.
notification = notification.Copy()
if not notification.timestamp:
notification.timestamp = rdfvalue.RDFDatetime.Now()
query = ("INSERT INTO user_notification (username, timestamp, "
"notification_state, notification) "
"VALUES (%s, %s, %s, %s)")
args = [
notification.username,
mysql_utils.RDFDatetimeToMysqlString(notification.timestamp),
int(notification.state),
notification.SerializeToString()
]
cursor.execute(query, args)
def WriteClientMessages(self, messages, cursor=None):
"""Writes messages that should go to the client to the db."""
query = ("INSERT IGNORE INTO client_messages "
"(client_id, message_id, timestamp, message) "
"VALUES %s ON DUPLICATE KEY UPDATE "
"timestamp=VALUES(timestamp), message=VALUES(message)")
now = mysql_utils.RDFDatetimeToMysqlString(rdfvalue.RDFDatetime.Now())
value_templates = []
args = []
for m in messages:
client_id_int = mysql_utils.ClientIDToInt(m.queue.Split()[0])
args.extend([client_id_int, m.task_id, now, m.SerializeToString()])
value_templates.append("(%s, %s, %s, %s)")
query %= ",".join(value_templates)
try:
cursor.execute(query, args)
except MySQLdb.IntegrityError as e:
raise db.UnknownClientError(cause=e)
def ListClientsForKeywords(self, keywords, start_time=None, cursor=None):
"""Lists the clients associated with keywords."""
keywords = set(keywords)
keyword_mapping = {utils.SmartUnicode(kw): kw for kw in keywords}
result = {}
for kw in keyword_mapping.values():
result[kw] = []
query = (
"SELECT DISTINCT keyword, client_id FROM client_keywords WHERE "
"keyword IN ({})".format(",".join(["%s"] * len(keyword_mapping))))
args = keyword_mapping.keys()
if start_time:
query += " AND timestamp >= %s"
args.append(mysql_utils.RDFDatetimeToMysqlString(start_time))
cursor.execute(query, args)
for kw, cid in cursor.fetchall():
result[keyword_mapping[kw]].append(mysql_utils.IntToClientID(cid))
return result
def MultiReadClientFullInfo(self,
client_ids,
min_last_ping=None,
cursor=None):
"""Reads full client information for a list of clients."""
query = (
"SELECT "
"c.client_id, c.fleetspeak_enabled, c.certificate, c.last_ping, "
"c.last_clock, c.last_ip, c.last_foreman, c.first_seen, "
"c.last_client_timestamp, c.last_crash_timestamp, "
"c.last_startup_timestamp, h.client_snapshot, s.startup_info, "
"s_last.startup_info, l.owner, l.label "
"FROM clients as c "
"LEFT JOIN client_snapshot_history as h ON ( "
"c.client_id = h.client_id AND h.timestamp = c.last_client_timestamp) "
"LEFT JOIN client_startup_history as s ON ( "
"c.client_id = s.client_id AND s.timestamp = c.last_client_timestamp) "
"LEFT JOIN client_startup_history as s_last ON ( "
"c.client_id = s_last.client_id "
"AND s_last.timestamp = c.last_startup_timestamp) "
"LEFT JOIN client_labels AS l ON (c.client_id = l.client_id) ")
query += "WHERE c.client_id IN (%s) " % ", ".join(
["%s"] * len(client_ids))
values = [mysql_utils.ClientIDToInt(cid) for cid in client_ids]
if min_last_ping is not None:
query += "AND c.last_ping >= %s"
values.append(mysql_utils.RDFDatetimeToMysqlString(min_last_ping))
cursor.execute(query, values)
ret = {}
for c_id, c_info in self._ResponseToClientsFullInfo(cursor.fetchall()):
ret[c_id] = c_info
return ret
def RemoveExpiredForemanRules(self, cursor=None):
now = rdfvalue.RDFDatetime.Now()
cursor.execute("DELETE FROM foreman_rules WHERE expiration_time < %s",
[mysql_utils.RDFDatetimeToMysqlString(now)])
