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