def _FlowObjectFromRow(self, row): """Generates a flow object from a database row.""" flow, fs, cci, pt, nr, pd, po, ps, ts, lut = row flow_obj = rdf_flow_objects.Flow.FromSerializedString(flow) if fs not in [None, rdf_flow_objects.Flow.FlowState.UNSET]: flow_obj.flow_state = fs if cci is not None: cc_cls = rdf_client.ClientCrash flow_obj.client_crash_info = cc_cls.FromSerializedString(cci) if pt is not None: pt_cls = rdf_flow_objects.PendingFlowTermination flow_obj.pending_termination = pt_cls.FromSerializedString(pt) if nr: flow_obj.next_request_to_process = nr if pd is not None: flow_obj.processing_deadline = mysql_utils.MysqlToRDFDatetime(pd) if po is not None: flow_obj.processing_on = po if ps is not None: flow_obj.processing_since = mysql_utils.MysqlToRDFDatetime(ps) flow_obj.timestamp = mysql_utils.MysqlToRDFDatetime(ts) flow_obj.last_update_time = mysql_utils.MysqlToRDFDatetime(lut) return flow_obj
def _ResponseToApprovalsWithGrants(response): """Converts a generator with approval rows into ApprovalRequest objects.""" prev_triplet = None cur_approval_request = None for (approval_id_int, approval_timestamp, approval_request_bytes, grantor_username, grant_timestamp) in response: cur_triplet = (approval_id_int, approval_timestamp, approval_request_bytes) if cur_triplet != prev_triplet: prev_triplet = cur_triplet if cur_approval_request: yield cur_approval_request cur_approval_request = mysql_utils.StringToRDFProto( rdf_objects.ApprovalRequest, approval_request_bytes) cur_approval_request.approval_id = _IntToApprovalID( approval_id_int) cur_approval_request.timestamp = mysql_utils.MysqlToRDFDatetime( approval_timestamp) if grantor_username and grant_timestamp: cur_approval_request.grants.append( rdf_objects.ApprovalGrant( grantor_username=grantor_username, timestamp=mysql_utils.MysqlToRDFDatetime(grant_timestamp))) if cur_approval_request: yield cur_approval_request
def ReadFlowRequestsReadyForProcessing(self, client_id, flow_id, next_needed_request, cursor=None): """Reads all requests for a flow that can be processed by the worker.""" query = ( "SELECT request, needs_processing, timestamp FROM flow_requests " "WHERE client_id=%s AND flow_id=%s") args = [ mysql_utils.ClientIDToInt(client_id), mysql_utils.FlowIDToInt(flow_id) ] cursor.execute(query, args) requests = {} for req, needs_processing, ts in cursor.fetchall(): if not needs_processing: continue request = rdf_flow_objects.FlowRequest.FromSerializedString(req) request.needs_processing = needs_processing request.timestamp = mysql_utils.MysqlToRDFDatetime(ts) requests[request.request_id] = request query = ( "SELECT response, status, iterator, timestamp FROM flow_responses " "WHERE client_id=%s AND flow_id=%s") cursor.execute(query, args) responses = {} for res, status, iterator, ts in cursor.fetchall(): if status: response = rdf_flow_objects.FlowStatus.FromSerializedString( status) elif iterator: response = rdf_flow_objects.FlowIterator.FromSerializedString( iterator) else: response = rdf_flow_objects.FlowResponse.FromSerializedString( res) response.timestamp = mysql_utils.MysqlToRDFDatetime(ts) responses.setdefault(response.request_id, []).append(response) res = {} while next_needed_request in requests: req = requests[next_needed_request] sorted_responses = sorted(responses.get(next_needed_request, []), key=lambda r: r.response_id) res[req.request_id] = (req, sorted_responses) next_needed_request += 1 return res
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 _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 MultiReadClientSnapshot(self, client_ids, cursor=None): """Reads the latest client snapshots for a list of clients.""" int_ids = [mysql_utils.ClientIDToInt(cid) for cid in client_ids] query = ( "SELECT h.client_id, h.client_snapshot, h.timestamp, s.startup_info " "FROM clients as c, client_snapshot_history as h, " "client_startup_history as s " "WHERE h.client_id = c.client_id " "AND s.client_id = c.client_id " "AND h.timestamp = c.last_client_timestamp " "AND s.timestamp = c.last_startup_timestamp " "AND c.client_id IN ({})").format(", ".join(["%s"] * len(client_ids))) ret = {cid: None for cid in client_ids} cursor.execute(query, int_ids) while True: row = cursor.fetchone() if not row: break cid, snapshot, timestamp, startup_info = row client_obj = mysql_utils.StringToRDFProto( rdf_objects.ClientSnapshot, snapshot) client_obj.startup_info = mysql_utils.StringToRDFProto( rdf_client.StartupInfo, startup_info) client_obj.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp) ret[mysql_utils.IntToClientID(cid)] = client_obj return ret
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 _CronjobFromRow(self, row): """Creates a cronjob object from a database result row.""" (job, create_time, disabled, last_run_status, last_run_time, current_run_id, state, leased_until, leased_by) = row job = rdf_cronjobs.CronJob.FromSerializedString(job) job.current_run_id = current_run_id job.disabled = disabled job.last_run_status = last_run_status job.last_run_time = mysql_utils.MysqlToRDFDatetime(last_run_time) if state: job.state = rdf_protodict.AttributedDict.FromSerializedString( state) job.create_time = mysql_utils.MysqlToRDFDatetime(create_time) job.leased_until = mysql_utils.MysqlToRDFDatetime(leased_until) job.leased_by = leased_by return job
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 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 ReadApprovalRequest(self, requestor_username, approval_id, cursor=None): """Reads an approval request object with a given id.""" query = (""" SELECT ar.approval_id, ar.timestamp, ar.approval_request, u.username, ag.timestamp FROM approval_request ar LEFT JOIN approval_grant ag USING (username_hash, approval_id) LEFT JOIN grr_users u ON u.username_hash = ag.grantor_username_hash WHERE ar.approval_id = %s AND ar.username_hash = %s """) cursor.execute(query, [ _ApprovalIDToInt(approval_id), mysql_utils.Hash(requestor_username) ]) res = cursor.fetchall() if not res: raise db.UnknownApprovalRequestError("Approval '%s' not found." % approval_id) approval_id_int, timestamp, approval_request_bytes, _, _ = res[0] approval_request = mysql_utils.StringToRDFProto( rdf_objects.ApprovalRequest, approval_request_bytes) approval_request.approval_id = _IntToApprovalID(approval_id_int) approval_request.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp) for _, _, _, grantor_username, timestamp in res: if not grantor_username: continue # Note: serialized approval_request objects are guaranteed to not # have any grants. approval_request.grants.append( rdf_objects.ApprovalGrant( grantor_username=grantor_username, timestamp=mysql_utils.MysqlToRDFDatetime(timestamp))) return approval_request
def ReadMessageHandlerRequests(self, cursor=None): """Reads all message handler requests from the database.""" query = ("SELECT timestamp, request, leased_until, leased_by " "FROM message_handler_requests " "ORDER BY timestamp DESC") cursor.execute(query) res = [] for timestamp, request, leased_until, leased_by in cursor.fetchall(): req = rdf_objects.MessageHandlerRequest.FromSerializedString(request) req.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp) req.leased_by = leased_by req.leased_until = mysql_utils.MysqlToRDFDatetime(leased_until) res.append(req) return res
def ReadAllFlowRequestsAndResponses(self, client_id, flow_id, cursor=None): """Reads all requests and responses for a given flow from the database.""" query = ( "SELECT request, needs_processing, responses_expected, timestamp " "FROM flow_requests WHERE client_id=%s AND flow_id=%s") args = [ mysql_utils.ClientIDToInt(client_id), mysql_utils.FlowIDToInt(flow_id) ] cursor.execute(query, args) requests = [] for req, needs_processing, resp_expected, ts in cursor.fetchall(): request = rdf_flow_objects.FlowRequest.FromSerializedString(req) request.needs_processing = needs_processing request.nr_responses_expected = resp_expected request.timestamp = mysql_utils.MysqlToRDFDatetime(ts) requests.append(request) query = ("SELECT response, status, iterator, timestamp " "FROM flow_responses WHERE client_id=%s AND flow_id=%s") cursor.execute(query, args) responses = {} for res, status, iterator, ts in cursor.fetchall(): if status: response = rdf_flow_objects.FlowStatus.FromSerializedString( status) elif iterator: response = rdf_flow_objects.FlowIterator.FromSerializedString( iterator) else: response = rdf_flow_objects.FlowResponse.FromSerializedString( res) response.timestamp = mysql_utils.MysqlToRDFDatetime(ts) responses.setdefault(response.request_id, {})[response.response_id] = response ret = [] for req in sorted(requests, key=lambda r: r.request_id): ret.append((req, responses.get(req.request_id, {}))) return ret
def ReadFlowProcessingRequests(self, cursor=None): """Reads all flow processing requests from the database.""" query = "SELECT request, timestamp FROM flow_processing_requests" cursor.execute(query) res = [] for serialized_request, ts in cursor.fetchall(): req = rdf_flows.FlowProcessingRequest.FromSerializedString( serialized_request) req.timestamp = mysql_utils.MysqlToRDFDatetime(ts) res.append(req) return res
def ReadClientCrashInfoHistory(self, client_id, cursor=None): """Reads the full crash history for a particular client.""" cursor.execute( "SELECT timestamp, crash_info FROM client_crash_history WHERE " "client_crash_history.client_id = %s " "ORDER BY timestamp DESC", [mysql_utils.ClientIDToInt(client_id)]) ret = [] for timestamp, crash_info in cursor.fetchall(): ci = rdf_client.ClientCrash.FromSerializedString(crash_info) ci.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp) ret.append(ci) return ret
def MultiReadClientMetadata(self, client_ids, cursor=None): """Reads ClientMetadata records for a list of clients.""" ids = [ mysql_utils.ClientIDToInt(client_id) for client_id in client_ids ] query = ( "SELECT client_id, fleetspeak_enabled, certificate, last_ping, " "last_clock, last_ip, last_foreman, first_seen, " "last_crash_timestamp, last_startup_timestamp FROM " "clients WHERE client_id IN ({})").format(", ".join(["%s"] * len(ids))) ret = {} cursor.execute(query, ids) while True: row = cursor.fetchone() if not row: break cid, fs, crt, ping, clk, ip, foreman, first, lct, lst = row ret[mysql_utils.IntToClientID(cid)] = rdf_objects.ClientMetadata( certificate=crt, fleetspeak_enabled=fs, first_seen=mysql_utils.MysqlToRDFDatetime(first), ping=mysql_utils.MysqlToRDFDatetime(ping), clock=mysql_utils.MysqlToRDFDatetime(clk), ip=mysql_utils.StringToRDFProto( rdf_client_network.NetworkAddress, ip), last_foreman_time=mysql_utils.MysqlToRDFDatetime(foreman), startup_info_timestamp=mysql_utils.MysqlToRDFDatetime(lst), last_crash_timestamp=mysql_utils.MysqlToRDFDatetime(lct)) return ret
def ReadClientCrashInfo(self, client_id, cursor=None): """Reads the latest client crash record for a single client.""" cursor.execute( "SELECT timestamp, crash_info FROM clients, client_crash_history WHERE " "clients.client_id = client_crash_history.client_id AND " "clients.last_crash_timestamp = client_crash_history.timestamp AND " "clients.client_id = %s", [mysql_utils.ClientIDToInt(client_id)]) row = cursor.fetchone() if not row: return None timestamp, crash_info = row res = rdf_client.ClientCrash.FromSerializedString(crash_info) res.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp) return res
def ReadClientStartupInfo(self, client_id, cursor=None): """Reads the latest client startup record for a single client.""" query = ( "SELECT startup_info, timestamp FROM clients, client_startup_history " "WHERE clients.last_startup_timestamp=client_startup_history.timestamp " "AND clients.client_id=client_startup_history.client_id " "AND clients.client_id=%s") cursor.execute(query, [mysql_utils.ClientIDToInt(client_id)]) row = cursor.fetchone() if row is None: return None startup_info, timestamp = row res = rdf_client.StartupInfo.FromSerializedString(startup_info) res.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp) return res
def ReadClientMessages(self, client_id, cursor=None): """Reads all client messages available for a given client_id.""" query = ("SELECT message, leased_until, leased_by FROM client_messages " "WHERE client_id = %s") cursor.execute(query, [mysql_utils.ClientIDToInt(client_id)]) ret = [] for msg, leased_until, leased_by in cursor.fetchall(): message = rdf_flows.GrrMessage.FromSerializedString(msg) if leased_until: message.leased_by = leased_by message.leased_until = mysql_utils.MysqlToRDFDatetime(leased_until) ret.append(message) return sorted(ret, key=lambda msg: msg.task_id)
def ReadSignedBinaryReferences(self, binary_id, cursor): """Reads blob references for the signed binary with the given id.""" cursor.execute( """ SELECT blob_references, timestamp FROM signed_binary_references WHERE binary_type = %s AND binary_path_hash = %s """, [ binary_id.binary_type.SerializeToDataStore(), mysql_utils.Hash(binary_id.path) ]) row = cursor.fetchone() if not row: raise db.UnknownSignedBinaryError(binary_id) raw_references, timestamp = row references = rdf_objects.BlobReferences.FromSerializedString( raw_references) return references, mysql_utils.MysqlToRDFDatetime(timestamp)
def ReadAllAuditEvents(self, cursor=None): """Reads all audit events stored in the database.""" cursor.execute(""" SELECT username, urn, client_id, timestamp, details FROM audit_event ORDER BY timestamp """) result = [] for username, urn, client_id, timestamp, details in cursor.fetchall(): event = rdf_events.AuditEvent.FromSerializedString(details) event.user = username if urn: event.urn = rdfvalue.RDFURN(urn) if client_id is not None: event.client = rdf_client.ClientURN( mysql_utils.IntToClientID(client_id)) event.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp) result.append(event) return result
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
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 _CronJobRunFromRow(self, row): serialized_run, timestamp = row res = rdf_cronjobs.CronJobRun.FromSerializedString(serialized_run) res.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp) return res
def _ResponseToClientsFullInfo(self, response): """Creates a ClientFullInfo object from a database response.""" c_full_info = None prev_cid = None for row in response: (cid, fs, crt, ping, clk, ip, foreman, first, last_client_ts, last_crash_ts, last_startup_ts, client_obj, client_startup_obj, last_startup_obj, label_owner, label_name) = row if cid != prev_cid: if c_full_info: yield mysql_utils.IntToClientID(prev_cid), c_full_info metadata = rdf_objects.ClientMetadata( certificate=crt, fleetspeak_enabled=fs, first_seen=mysql_utils.MysqlToRDFDatetime(first), ping=mysql_utils.MysqlToRDFDatetime(ping), clock=mysql_utils.MysqlToRDFDatetime(clk), ip=mysql_utils.StringToRDFProto( rdf_client_network.NetworkAddress, ip), last_foreman_time=mysql_utils.MysqlToRDFDatetime(foreman), startup_info_timestamp=mysql_utils.MysqlToRDFDatetime( last_startup_ts), last_crash_timestamp=mysql_utils.MysqlToRDFDatetime( last_crash_ts)) if client_obj is not None: l_snapshot = rdf_objects.ClientSnapshot.FromSerializedString( client_obj) l_snapshot.timestamp = mysql_utils.MysqlToRDFDatetime( last_client_ts) l_snapshot.startup_info = rdf_client.StartupInfo.FromSerializedString( client_startup_obj) l_snapshot.startup_info.timestamp = l_snapshot.timestamp else: l_snapshot = rdf_objects.ClientSnapshot( client_id=mysql_utils.IntToClientID(cid)) if last_startup_obj is not None: startup_info = rdf_client.StartupInfo.FromSerializedString( last_startup_obj) startup_info.timestamp = mysql_utils.MysqlToRDFDatetime( last_startup_ts) else: startup_info = None prev_cid = cid c_full_info = rdf_objects.ClientFullInfo( metadata=metadata, labels=[], last_snapshot=l_snapshot, last_startup_info=startup_info) if label_owner and label_name: c_full_info.labels.append( rdf_objects.ClientLabel(name=label_name, owner=label_owner)) if c_full_info: yield mysql_utils.IntToClientID(prev_cid), c_full_info
def _AuditEntryFromRow(details, timestamp): entry = rdf_objects.APIAuditEntry.FromSerializedString(details) entry.timestamp = mysql_utils.MysqlToRDFDatetime(timestamp) return entry