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