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 = 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.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 = 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 = 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 = 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( objects.ClientLabel(name=label_name, owner=label_owner)) if c_full_info: yield mysql_utils.IntToClientID(prev_cid), c_full_info
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 _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) 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 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 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(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 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 = 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 ReadApprovalRequest(self, requestor_username, approval_id, cursor=None): """Reads an approval request object with a given id.""" query = ( "SELECT approval_request.approval_id, approval_request.timestamp, " "approval_request.approval_request, " "approval_grant.grantor_username, approval_grant.timestamp " "FROM approval_request " "LEFT JOIN approval_grant USING (username, approval_id) " "WHERE approval_request.approval_id=%s " "AND approval_request.username=%s") cursor.execute(query, [_ApprovalIDToInt(approval_id), 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( 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( objects.ApprovalGrant( grantor_username=grantor_username, timestamp=mysql_utils.MysqlToRDFDatetime(timestamp))) return approval_request
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)] = 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.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 ret
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 = 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 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