def _WriteFlowProcessingRequests(self, requests, cursor):
"""Returns a (query, args) tuple that inserts the given requests."""
timestamp = rdfvalue.RDFDatetime.Now()
timestamp_str = mysql_utils.RDFDatetimeToMysqlString(timestamp)
templates = []
args = []
for req in requests:
templates.append("(%s, %s, %s, %s, %s)")
req = req.Copy()
req.timestamp = timestamp
args.append(mysql_utils.ClientIDToInt(req.client_id))
args.append(mysql_utils.FlowIDToInt(req.flow_id))
args.append(timestamp_str)
args.append(req.SerializeToString())
if req.delivery_time:
args.append(
mysql_utils.RDFDatetimeToMysqlString(req.delivery_time))
else:
args.append(None)
query = (
"INSERT INTO flow_processing_requests "
"(client_id, flow_id, timestamp, request, delivery_time) VALUES ")
query += ", ".join(templates)
cursor.execute(query, args)
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 ReadAllFlowObjects(self,
client_id = None,
min_create_time = None,
max_create_time = None,
include_child_flows = True,
cursor = None
):
"""Returns all flow objects."""
conditions = []
args = []
if client_id is not None:
conditions.append("client_id = %s")
args.append(mysql_utils.ClientIDToInt(client_id))
if min_create_time is not None:
conditions.append("timestamp >= %s")
args.append(mysql_utils.RDFDatetimeToMysqlString(min_create_time))
if max_create_time is not None:
conditions.append("timestamp <= %s")
args.append(mysql_utils.RDFDatetimeToMysqlString(max_create_time))
if not include_child_flows:
conditions.append("parent_flow_id IS NULL")
query = "SELECT {} FROM flows".format(self.FLOW_DB_FIELDS)
if conditions:
query += " WHERE " + " AND ".join(conditions)
cursor.execute(query, args)
return [self._FlowObjectFromRow(row) for row in cursor.fetchall()]
def WriteFlowObject(self, flow_obj, cursor=None):
"""Writes a flow object to the database."""
query = ("INSERT INTO flows "
"(client_id, flow_id, long_flow_id, parent_flow_id, flow, "
"next_request_to_process, timestamp, last_update) VALUES "
"(%s, %s, %s, %s, %s, %s, %s, %s) "
"ON DUPLICATE KEY UPDATE "
"flow=VALUES(flow), "
"next_request_to_process=VALUES(next_request_to_process),"
"last_update=VALUES(last_update)")
if flow_obj.parent_flow_id:
pfi = mysql_utils.FlowIDToInt(flow_obj.parent_flow_id)
else:
pfi = None
timestamp_str = mysql_utils.RDFDatetimeToMysqlString(flow_obj.create_time)
now_str = mysql_utils.RDFDatetimeToMysqlString(rdfvalue.RDFDatetime.Now())
args = [
mysql_utils.ClientIDToInt(flow_obj.client_id),
mysql_utils.FlowIDToInt(flow_obj.flow_id), flow_obj.long_flow_id, pfi,
flow_obj.SerializeToString(), flow_obj.next_request_to_process,
timestamp_str, now_str
]
try:
cursor.execute(query, args)
except MySQLdb.IntegrityError as e:
raise db.UnknownClientError(flow_obj.client_id, cause=e)
def ReadAllClientGraphSeries(self,
client_label,
report_type,
time_range=None,
cursor=None):
"""Reads graph series for the given label and report-type from the DB."""
query = """
SELECT timestamp, graph_series
FROM client_report_graphs
WHERE client_label = %s AND report_type = %s
"""
args = [client_label, report_type.SerializeToDataStore()]
if time_range is not None:
query += " AND `timestamp` BETWEEN %s AND %s"
args += [
mysql_utils.RDFDatetimeToMysqlString(time_range.start),
mysql_utils.RDFDatetimeToMysqlString(time_range.end)
]
cursor.execute(query, args)
results = {}
for timestamp, raw_series in cursor.fetchall():
series = rdf_stats.ClientGraphSeries.FromSerializedString(
raw_series)
results[mysql_utils.MysqlToRDFDatetime(timestamp)] = series
return results
def _LeaseFlowProcessingReqests(self, cursor=None):
"""Leases a number of flow processing requests."""
now = rdfvalue.RDFDatetime.Now()
now_str = mysql_utils.RDFDatetimeToMysqlString(now)
expiry = now + rdfvalue.Duration("10m")
expiry_str = mysql_utils.RDFDatetimeToMysqlString(expiry)
query = ("UPDATE flow_processing_requests "
"SET leased_until=%s, leased_by=%s "
"WHERE (delivery_time IS NULL OR delivery_time <= %s) AND "
"(leased_until IS NULL OR leased_until < %s) "
"LIMIT %s")
id_str = utils.ProcessIdString()
args = (expiry_str, id_str, now_str, now_str, 50)
updated = cursor.execute(query, args)
if updated == 0:
return []
cursor.execute(
"SELECT timestamp, request FROM flow_processing_requests "
"WHERE leased_by=%s AND leased_until=%s LIMIT %s",
(id_str, expiry_str, updated))
res = []
for timestamp, request in cursor.fetchall():
req = rdf_flows.FlowProcessingRequest.FromSerializedString(request)
req.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp)
req.leased_until = expiry
req.leased_by = id_str
res.append(req)
return res
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, last_ping FROM clients "
query_values = []
where_filters = []
if min_last_ping is not None:
where_filters.append("last_ping >= %s ")
query_values.append(
mysql_utils.RDFDatetimeToMysqlString(min_last_ping))
if max_last_ping is not None:
where_filters.append("(last_ping IS NULL OR last_ping <= %s)")
query_values.append(
mysql_utils.RDFDatetimeToMysqlString(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 = mysql_utils.IntToClientID(int_client_id)
last_pings[client_id] = mysql_utils.MysqlToRDFDatetime(last_ping)
return last_pings
def CountAPIAuditEntriesByUserAndDay(self,
min_timestamp=None,
max_timestamp=None,
cursor=None):
"""Returns audit entry counts grouped by user and calendar day."""
query = """
SELECT username, CAST(timestamp AS DATE) AS day, COUNT(*)
FROM api_audit_entry
{WHERE_PLACEHOLDER}
GROUP BY username, day
"""
conditions = []
values = []
where = ""
if min_timestamp is not None:
conditions.append("timestamp >= %s")
values.append(mysql_utils.RDFDatetimeToMysqlString(min_timestamp))
if max_timestamp is not None:
conditions.append("timestamp <= %s")
values.append(mysql_utils.RDFDatetimeToMysqlString(max_timestamp))
if conditions:
where = "WHERE " + " AND ".join(conditions)
query = query.replace("{WHERE_PLACEHOLDER}", where)
cursor.execute(query, values)
return {(username, rdfvalue.RDFDatetime.FromDate(day)): count
for (username, day, count) in cursor.fetchall()}
def _LeaseMessageHandlerRequests(self, lease_time, limit, 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 = rdf_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 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 UpdateFlow(self,
client_id,
flow_id,
flow_obj=db.Database.unchanged,
flow_state=db.Database.unchanged,
client_crash_info=db.Database.unchanged,
pending_termination=db.Database.unchanged,
processing_on=db.Database.unchanged,
processing_since=db.Database.unchanged,
processing_deadline=db.Database.unchanged,
cursor=None):
"""Updates flow objects in the database."""
updates = []
args = []
if flow_obj != db.Database.unchanged:
updates.append("flow=%s")
args.append(flow_obj.SerializeToString())
updates.append("flow_state=%s")
args.append(int(flow_obj.flow_state))
if flow_state != db.Database.unchanged:
updates.append("flow_state=%s")
args.append(int(flow_state))
if client_crash_info != db.Database.unchanged:
updates.append("client_crash_info=%s")
args.append(client_crash_info.SerializeToString())
if pending_termination != db.Database.unchanged:
updates.append("pending_termination=%s")
args.append(pending_termination.SerializeToString())
if processing_on != db.Database.unchanged:
updates.append("processing_on=%s")
args.append(processing_on)
if processing_since != db.Database.unchanged:
updates.append("processing_since=%s")
args.append(mysql_utils.RDFDatetimeToMysqlString(processing_since))
if processing_deadline != db.Database.unchanged:
updates.append("processing_deadline=%s")
args.append(mysql_utils.RDFDatetimeToMysqlString(processing_deadline))
if not updates:
return
query = "UPDATE flows SET "
query += ", ".join(updates)
query += " WHERE client_id=%s AND flow_id=%s"
args.append(mysql_utils.ClientIDToInt(client_id))
args.append(mysql_utils.FlowIDToInt(flow_id))
updated = cursor.execute(query, args)
if updated == 0:
raise db.UnknownFlowError(client_id, flow_id)
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 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 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()
args = {
"username_hash":
mysql_utils.Hash(notification.username),
"timestamp":
mysql_utils.RDFDatetimeToMysqlString(notification.timestamp),
"notification_state":
int(notification.state),
"notification":
notification.SerializeToString(),
}
query = "INSERT INTO user_notification {columns} VALUES {values}".format(
columns=mysql_utils.Columns(args),
values=mysql_utils.NamedPlaceholders(args))
try:
cursor.execute(query, args)
except MySQLdb.IntegrityError:
raise db.UnknownGRRUserError(notification.username)
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())
client_ids = set()
value_templates = []
args = []
for m in messages:
cid = db_utils.ClientIdFromGrrMessage(m)
client_ids.add(cid)
client_id_int = mysql_utils.ClientIDToInt(cid)
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.AtLeastOneUnknownClientError(client_ids=client_ids, cause=e)
def WriteSignedBinaryReferences(self,
binary_id,
references,
cursor=None):
"""Writes blob references for a signed binary to the DB."""
args = {
"binary_type":
binary_id.binary_type.SerializeToDataStore(),
"binary_path":
binary_id.path,
"binary_path_hash":
mysql_utils.Hash(binary_id.path),
"timestamp":
mysql_utils.RDFDatetimeToMysqlString(rdfvalue.RDFDatetime.Now()),
"blob_references":
references.SerializeToString()
}
query = """
INSERT INTO signed_binary_references {cols}
VALUES {vals}
ON DUPLICATE KEY UPDATE
timestamp = VALUES(timestamp),
blob_references = VALUES(blob_references)
""".format(
cols=mysql_utils.Columns(args),
vals=mysql_utils.NamedPlaceholders(args))
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, leased_count=leased_count+1 "
"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, leased_count 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 = []
expired = []
for msg, leased_count in cursor.fetchall():
message = rdf_flows.GrrMessage.FromSerializedString(msg)
message.leased_by = proc_id_str
message.leased_until = expiry
message.ttl = db.Database.CLIENT_MESSAGES_TTL - leased_count
# > comparison since this check happens after the lease.
if leased_count > db.Database.CLIENT_MESSAGES_TTL:
expired.append((client_id, message.task_id))
else:
ret.append(message)
if expired:
self._DeleteClientMessages(expired, cursor=cursor)
return sorted(ret, key=lambda msg: msg.task_id)
def WriteFlowRequests(self, requests, cursor=None):
"""Writes a list of flow requests to the database."""
args = []
templates = []
flow_keys = []
needs_processing = {}
now_str = mysql_utils.RDFDatetimeToMysqlString(
rdfvalue.RDFDatetime.Now())
for r in requests:
if r.needs_processing:
needs_processing.setdefault((r.client_id, r.flow_id),
[]).append(r.request_id)
flow_keys.append((r.client_id, r.flow_id))
templates.append("(%s, %s, %s, %s, %s, %s)")
args.extend([
mysql_utils.ClientIDToInt(r.client_id),
mysql_utils.FlowIDToInt(r.flow_id), r.request_id,
r.needs_processing,
r.SerializeToString(), now_str
])
if needs_processing:
flow_processing_requests = []
nr_conditions = []
nr_args = []
for client_id, flow_id in needs_processing:
nr_conditions.append("(client_id=%s AND flow_id=%s)")
nr_args.append(mysql_utils.ClientIDToInt(client_id))
nr_args.append(mysql_utils.FlowIDToInt(flow_id))
nr_query = ("SELECT client_id, flow_id, next_request_to_process "
"FROM flows WHERE ")
nr_query += " OR ".join(nr_conditions)
cursor.execute(nr_query, nr_args)
db_result = cursor.fetchall()
for client_id_int, flow_id_int, next_request_to_process in db_result:
client_id = mysql_utils.IntToClientID(client_id_int)
flow_id = mysql_utils.IntToFlowID(flow_id_int)
if next_request_to_process in needs_processing[(client_id,
flow_id)]:
flow_processing_requests.append(
rdf_flows.FlowProcessingRequest(client_id=client_id,
flow_id=flow_id))
if flow_processing_requests:
self._WriteFlowProcessingRequests(flow_processing_requests,
cursor)
query = ("INSERT INTO flow_requests "
"(client_id, flow_id, request_id, needs_processing, request, "
"timestamp) VALUES ")
query += ", ".join(templates)
try:
cursor.execute(query, args)
except MySQLdb.IntegrityError as e:
raise db.AtLeastOneUnknownFlowError(flow_keys, cause=e)
def ReadAPIAuditEntries(self,
username=None,
router_method_names=None,
min_timestamp=None,
max_timestamp=None,
cursor=None):
"""Returns audit entries stored in the database."""
query = """SELECT details, timestamp
FROM api_audit_entry
WHERE_PLACEHOLDER
ORDER BY timestamp ASC
"""
conditions = []
values = []
where = ""
if username is not None:
conditions.append("username_hash = %s")
values.append(mysql_utils.Hash(username))
if router_method_names:
placeholders = ["%s"] * len(router_method_names)
placeholders = ", ".join(placeholders)
conditions.append("router_method_name IN (%s)" % placeholders)
values.extend(router_method_names)
if min_timestamp is not None:
conditions.append("timestamp >= %s")
values.append(mysql_utils.RDFDatetimeToMysqlString(min_timestamp))
if max_timestamp is not None:
conditions.append("timestamp <= %s")
values.append(mysql_utils.RDFDatetimeToMysqlString(max_timestamp))
if conditions:
where = "WHERE " + " AND ".join(conditions)
query = query.replace("WHERE_PLACEHOLDER", where)
cursor.execute(query, values)
return [
_AuditEntryFromRow(details, timestamp)
for details, timestamp in cursor.fetchall()
]
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 ReadFlowForProcessing(self,
client_id,
flow_id,
processing_time,
cursor=None):
"""Marks a flow as being processed on this worker and returns it."""
query = ("SELECT " + self.FLOW_DB_FIELDS +
"FROM flows WHERE client_id=%s AND flow_id=%s")
cursor.execute(query, [
mysql_utils.ClientIDToInt(client_id),
mysql_utils.FlowIDToInt(flow_id)
])
response = cursor.fetchall()
if not response:
raise db.UnknownFlowError(client_id, flow_id)
row, = response
rdf_flow = self._FlowObjectFromRow(row)
now = rdfvalue.RDFDatetime.Now()
if rdf_flow.processing_on and rdf_flow.processing_deadline > now:
raise ValueError(
"Flow %s on client %s is already being processed." %
(client_id, flow_id))
update_query = (
"UPDATE flows SET processing_on=%s, processing_since=%s, "
"processing_deadline=%s WHERE client_id=%s and flow_id=%s")
processing_deadline = now + processing_time
process_id_string = utils.ProcessIdString()
args = [
process_id_string,
mysql_utils.RDFDatetimeToMysqlString(now),
mysql_utils.RDFDatetimeToMysqlString(processing_deadline),
mysql_utils.ClientIDToInt(client_id),
mysql_utils.FlowIDToInt(flow_id)
]
cursor.execute(update_query, args)
# This needs to happen after we are sure that the write has succeeded.
rdf_flow.processing_on = process_id_string
rdf_flow.processing_since = now
rdf_flow.processing_deadline = processing_deadline
return rdf_flow
def ReadAllClientIDs(self, min_last_ping=None, cursor=None):
"""Reads client ids for all clients in the database."""
query = "SELECT client_id FROM clients "
query_values = []
if min_last_ping is not None:
query += "WHERE last_ping >= %s"
query_values.append(
mysql_utils.RDFDatetimeToMysqlString(min_last_ping))
cursor.execute(query, query_values)
return [mysql_utils.IntToClientID(res[0]) for res in cursor.fetchall()]
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 ReadAllFlowObjects(self, client_id, min_create_time=None, cursor=None):
"""Reads all flow objects from the database for a given client."""
query = "SELECT " + self.FLOW_DB_FIELDS + " FROM flows WHERE client_id=%s"
args = [mysql_utils.ClientIDToInt(client_id)]
if min_create_time is not None:
query += " AND timestamp >= %s"
args.append(mysql_utils.RDFDatetimeToMysqlString(min_create_time))
cursor.execute(query, args)
return [self._FlowObjectFromRow(row) for row in cursor.fetchall()]
def GrantApproval(self,
requestor_username,
approval_id,
grantor_username,
cursor=None):
"""Grants approval for a given request using given username."""
self._GrantApproval(
requestor_username, _ApprovalIDToInt(approval_id),
grantor_username,
mysql_utils.RDFDatetimeToMysqlString(rdfvalue.RDFDatetime.Now()),
cursor)
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 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()
now_str = mysql_utils.RDFDatetimeToMysqlString(
rdfvalue.RDFDatetime.Now())
grants = approval_request.grants
approval_request.grants = None
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,
"timestamp":
now_str,
"expiration_time":
mysql_utils.RDFDatetimeToMysqlString(
approval_request.expiration_time),
"approval_request":
approval_request.SerializeToString()
}
query = (
"INSERT INTO approval_request {columns} VALUES {values}".format(
columns=mysql_utils.Columns(args),
values=mysql_utils.NamedPlaceholders(args)))
cursor.execute(query, args)
for grant in grants:
self._GrantApproval(approval_request.requestor_username,
approval_id_int, grant.grantor_username,
now_str, cursor)
return _IntToApprovalID(approval_id_int)
def ReadClientStats(self,
client_id,
min_timestamp,
max_timestamp,
cursor=None):
"""Reads ClientStats for a given client and time range."""
cursor.execute(
"""
SELECT payload FROM client_stats
WHERE client_id = %s AND timestamp BETWEEN %s AND %s
ORDER BY timestamp ASC
""", [
mysql_utils.ClientIDToInt(client_id),
mysql_utils.RDFDatetimeToMysqlString(min_timestamp),
mysql_utils.RDFDatetimeToMysqlString(max_timestamp)
])
return [
rdf_client_stats.ClientStats.FromSerializedString(stats_bytes)
for stats_bytes, in cursor.fetchall()
]
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 = rdf_objects.ClientSnapshot.FromSerializedString(snapshot)
client.startup_info = rdf_client.StartupInfo.FromSerializedString(
startup_info)
client.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp)
ret.append(client)
return ret
