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.TimestampToRDFDatetime( approval_timestamp) if grantor_username and grant_timestamp: cur_approval_request.grants.append( rdf_objects.ApprovalGrant( grantor_username=grantor_username, timestamp=mysql_utils.TimestampToRDFDatetime( grant_timestamp))) if cur_approval_request: yield cur_approval_request
def _HuntObjectFromRow(self, row): """Generates a flow object from a database row.""" ( create_time, last_update_time, creator, duration_micros, client_rate, client_limit, hunt_state, hunt_state_comment, init_start_time, last_start_time, num_clients_at_start_time, description, body, ) = row hunt_obj = rdf_hunt_objects.Hunt.FromSerializedBytes(body) hunt_obj.duration = rdfvalue.Duration.From(duration_micros, rdfvalue.MICROSECONDS) hunt_obj.create_time = mysql_utils.TimestampToRDFDatetime(create_time) hunt_obj.last_update_time = mysql_utils.TimestampToRDFDatetime( last_update_time) # Checks below are needed for hunts that were written to the database before # respective fields became part of F1 schema. if creator is not None: hunt_obj.creator = creator if client_rate is not None: hunt_obj.client_rate = client_rate if client_limit is not None: hunt_obj.client_limit = client_limit if hunt_state is not None: hunt_obj.hunt_state = hunt_state if hunt_state_comment is not None: hunt_obj.hunt_state_comment = hunt_state_comment if init_start_time is not None: hunt_obj.init_start_time = mysql_utils.TimestampToRDFDatetime( init_start_time) if last_start_time is not None: hunt_obj.last_start_time = mysql_utils.TimestampToRDFDatetime( last_start_time) if num_clients_at_start_time is not None: hunt_obj.num_clients_at_start_time = num_clients_at_start_time if description is not None: hunt_obj.description = description return hunt_obj
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 db_utils.IntToClientID(prev_cid), c_full_info metadata = rdf_objects.ClientMetadata( certificate=crt, fleetspeak_enabled=fs, first_seen=mysql_utils.TimestampToRDFDatetime(first), ping=mysql_utils.TimestampToRDFDatetime(ping), clock=mysql_utils.TimestampToRDFDatetime(clk), ip=mysql_utils.StringToRDFProto(rdf_client_network.NetworkAddress, ip), last_foreman_time=mysql_utils.TimestampToRDFDatetime(foreman), startup_info_timestamp=mysql_utils.TimestampToRDFDatetime( last_startup_ts), last_crash_timestamp=mysql_utils.TimestampToRDFDatetime( last_crash_ts)) if client_obj is not None: l_snapshot = rdf_objects.ClientSnapshot.FromSerializedString( client_obj) l_snapshot.timestamp = mysql_utils.TimestampToRDFDatetime( 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=db_utils.IntToClientID(cid)) if last_startup_obj is not None: startup_info = rdf_client.StartupInfo.FromSerializedString( last_startup_obj) startup_info.timestamp = mysql_utils.TimestampToRDFDatetime( 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 db_utils.IntToClientID(prev_cid), c_full_info
def ReadApprovalRequest(self, requestor_username, approval_id, cursor=None): """Reads an approval request object with a given id.""" query = (""" SELECT ar.approval_id, UNIX_TIMESTAMP(ar.timestamp), ar.approval_request, u.username, UNIX_TIMESTAMP(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.TimestampToRDFDatetime( 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.TimestampToRDFDatetime(timestamp))) return approval_request
def ReadClientStartupInfoHistory(self, client_id, timerange=None, cursor=None): """Reads the full startup history for a particular client.""" client_id_int = db_utils.ClientIDToInt(client_id) query = ("SELECT startup_info, UNIX_TIMESTAMP(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 >= FROM_UNIXTIME(%s) " args.append(mysql_utils.RDFDatetimeToTimestamp(time_from)) if time_to is not None: query += "AND timestamp <= FROM_UNIXTIME(%s) " args.append(mysql_utils.RDFDatetimeToTimestamp(time_to)) query += "ORDER BY timestamp DESC " ret = [] cursor.execute(query, args) for startup_info, timestamp in cursor.fetchall(): si = rdf_client.StartupInfo.FromSerializedBytes(startup_info) si.timestamp = mysql_utils.TimestampToRDFDatetime(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 = [db_utils.ClientIDToInt(cid) for cid in client_ids] query = ( "SELECT h.client_id, h.client_snapshot, UNIX_TIMESTAMP(h.timestamp)," " s.startup_info " "FROM clients as c FORCE INDEX (PRIMARY), " "client_snapshot_history as h FORCE INDEX (PRIMARY), " "client_startup_history as s FORCE INDEX (PRIMARY) " "WHERE h.client_id = c.client_id " "AND s.client_id = c.client_id " "AND h.timestamp = c.last_snapshot_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.TimestampToRDFDatetime( timestamp) ret[db_utils.IntToClientID(cid)] = client_obj return ret
def ReadSignedBinaryReferences(self, binary_id, cursor=None): """Reads blob references for the signed binary with the given id.""" cursor.execute( """ SELECT blob_references, UNIX_TIMESTAMP(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 # TODO(hanuszczak): pytype does not understand overloads, so we have to cast # to a non-optional object. datetime = cast(rdfvalue.RDFDatetime, mysql_utils.TimestampToRDFDatetime(timestamp)) references = rdf_objects.BlobReferences.FromSerializedBytes( raw_references) return references, datetime
def ReadAllClientGraphSeries( self, client_label: Text, report_type: rdf_structs.EnumNamedValue, time_range: Optional[time_utils.TimeRange] = None, cursor=None ) -> Dict[rdfvalue.RDFDatetime, rdf_stats.ClientGraphSeries]: """Reads graph series for the given label and report-type from the DB.""" query = """ SELECT UNIX_TIMESTAMP(timestamp), graph_series FROM client_report_graphs WHERE client_label = %s AND report_type = %s """ args = [client_label, report_type.SerializeToWireFormat()] if time_range is not None: query += "AND `timestamp` BETWEEN FROM_UNIXTIME(%s) AND FROM_UNIXTIME(%s)" args += [ mysql_utils.RDFDatetimeToTimestamp(time_range.start), mysql_utils.RDFDatetimeToTimestamp(time_range.end) ] cursor.execute(query, args) results = {} for timestamp, raw_series in cursor.fetchall(): # TODO(hanuszczak): pytype does not seem to understand overloads, so it is # not possible to correctly annotate `TimestampToRDFDatetime`. timestamp = cast(rdfvalue.RDFDatetime, mysql_utils.TimestampToRDFDatetime(timestamp)) series = rdf_stats.ClientGraphSeries.FromSerializedBytes( raw_series) results[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, UNIX_TIMESTAMP(last_ping) FROM clients " query_values = [] where_filters = [] if min_last_ping is not None: where_filters.append("last_ping >= FROM_UNIXTIME(%s) ") query_values.append( mysql_utils.RDFDatetimeToTimestamp(min_last_ping)) if max_last_ping is not None: where_filters.append( "(last_ping IS NULL OR last_ping <= FROM_UNIXTIME(%s))") query_values.append( mysql_utils.RDFDatetimeToTimestamp(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 = db_utils.IntToClientID(int_client_id) last_pings[client_id] = mysql_utils.TimestampToRDFDatetime( last_ping) return last_pings
def _CronJobFromRow(self, row): """Creates a cronjob object from a database result row.""" (job, create_time, enabled, forced_run_requested, last_run_status, last_run_time, current_run_id, state, leased_until, leased_by) = row job = rdf_cronjobs.CronJob.FromSerializedBytes(job) job.current_run_id = db_utils.IntToCronJobRunID(current_run_id) job.enabled = enabled job.forced_run_requested = forced_run_requested job.last_run_status = last_run_status job.last_run_time = mysql_utils.TimestampToRDFDatetime(last_run_time) if state: job.state = rdf_protodict.AttributedDict.FromSerializedBytes(state) job.created_at = mysql_utils.TimestampToRDFDatetime(create_time) job.leased_until = mysql_utils.TimestampToRDFDatetime(leased_until) job.leased_by = leased_by return job
def MultiReadClientMetadata(self, client_ids, cursor=None): """Reads ClientMetadata records for a list of clients.""" ids = [db_utils.ClientIDToInt(client_id) for client_id in client_ids] query = ("SELECT client_id, fleetspeak_enabled, certificate, " "UNIX_TIMESTAMP(last_ping), " "UNIX_TIMESTAMP(last_clock), last_ip, " "UNIX_TIMESTAMP(last_foreman), UNIX_TIMESTAMP(first_seen), " "UNIX_TIMESTAMP(last_crash_timestamp), " "UNIX_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[db_utils.IntToClientID(cid)] = rdf_objects.ClientMetadata( certificate=crt, fleetspeak_enabled=fs, first_seen=mysql_utils.TimestampToRDFDatetime(first), ping=mysql_utils.TimestampToRDFDatetime(ping), clock=mysql_utils.TimestampToRDFDatetime(clk), ip=mysql_utils.StringToRDFProto( rdf_client_network.NetworkAddress, ip), last_foreman_time=mysql_utils.TimestampToRDFDatetime(foreman), startup_info_timestamp=mysql_utils.TimestampToRDFDatetime(lst), last_crash_timestamp=mysql_utils.TimestampToRDFDatetime(lct)) return ret
def ReadHuntFlowsStatesAndTimestamps(self, hunt_id, cursor=None): """Reads hunt flows states and timestamps.""" query = """ SELECT flow_state, UNIX_TIMESTAMP(timestamp), UNIX_TIMESTAMP(last_update) FROM flows FORCE INDEX(flows_by_hunt) WHERE parent_hunt_id = %s AND parent_flow_id IS NULL """ cursor.execute(query, [db_utils.HuntIDToInt(hunt_id)]) result = [] for fs, ct, lup in cursor.fetchall(): result.append( db.FlowStateAndTimestamps( flow_state=rdf_flow_objects.Flow.FlowState.FromInt(fs), create_time=mysql_utils.TimestampToRDFDatetime(ct), last_update_time=mysql_utils.TimestampToRDFDatetime(lup))) return result
def ReadClientCrashInfoHistory(self, client_id, cursor=None): """Reads the full crash history for a particular client.""" cursor.execute( "SELECT UNIX_TIMESTAMP(timestamp), crash_info " "FROM client_crash_history WHERE " "client_crash_history.client_id = %s " "ORDER BY timestamp DESC", [db_utils.ClientIDToInt(client_id)]) ret = [] for timestamp, crash_info in cursor.fetchall(): ci = rdf_client.ClientCrash.FromSerializedBytes(crash_info) ci.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp) ret.append(ci) return ret
def ReadClientCrashInfo(self, client_id, cursor=None): """Reads the latest client crash record for a single client.""" cursor.execute( "SELECT UNIX_TIMESTAMP(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", [db_utils.ClientIDToInt(client_id)]) row = cursor.fetchone() if not row: return None timestamp, crash_info = row res = rdf_client.ClientCrash.FromSerializedBytes(crash_info) res.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp) return res
def ReadClientStartupInfo(self, client_id, cursor=None): """Reads the latest client startup record for a single client.""" query = ( "SELECT startup_info, UNIX_TIMESTAMP(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, [db_utils.ClientIDToInt(client_id)]) row = cursor.fetchone() if row is None: return None startup_info, timestamp = row res = rdf_client.StartupInfo.FromSerializedBytes(startup_info) res.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp) return res
def ReadHuntOutputPluginLogEntries(self, hunt_id, output_plugin_id, offset, count, with_type=None, cursor=None): """Reads hunt output plugin log entries.""" query = ("SELECT client_id, flow_id, log_entry_type, message, " "UNIX_TIMESTAMP(timestamp) " "FROM flow_output_plugin_log_entries " "FORCE INDEX (flow_output_plugin_log_entries_by_hunt) " "WHERE hunt_id = %s AND output_plugin_id = %s ") args = [ db_utils.HuntIDToInt(hunt_id), db_utils.OutputPluginIDToInt(output_plugin_id) ] if with_type is not None: query += "AND log_entry_type = %s " args.append(int(with_type)) query += "ORDER BY log_id ASC LIMIT %s OFFSET %s" args.append(count) args.append(offset) cursor.execute(query, args) ret = [] for (client_id_int, flow_id_int, log_entry_type, message, timestamp) in cursor.fetchall(): ret.append( rdf_flow_objects.FlowOutputPluginLogEntry( hunt_id=hunt_id, client_id=db_utils.IntToClientID(client_id_int), flow_id=db_utils.IntToFlowID(flow_id_int), output_plugin_id=output_plugin_id, log_entry_type=log_entry_type, message=message, timestamp=mysql_utils.TimestampToRDFDatetime(timestamp))) return ret
def _ReadClientLastPings(self, last_client_id, count, min_last_ping=None, max_last_ping=None, fleetspeak_enabled=None, cursor=None): """Yields dicts of last-ping timestamps for clients in the DB.""" where_filters = ["client_id > %s"] query_values = [db_utils.ClientIDToInt(last_client_id)] if min_last_ping is not None: where_filters.append("last_ping >= FROM_UNIXTIME(%s) ") query_values.append( mysql_utils.RDFDatetimeToTimestamp(min_last_ping)) if max_last_ping is not None: where_filters.append( "(last_ping IS NULL OR last_ping <= FROM_UNIXTIME(%s))") query_values.append( mysql_utils.RDFDatetimeToTimestamp(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)" ) query = """ SELECT client_id, UNIX_TIMESTAMP(last_ping) FROM clients WHERE {} ORDER BY client_id LIMIT %s""".format(" AND ".join(where_filters)) cursor.execute(query, query_values + [count]) last_pings = {} last_client_id = None for int_client_id, last_ping in cursor.fetchall(): last_client_id = db_utils.IntToClientID(int_client_id) last_pings[last_client_id] = mysql_utils.TimestampToRDFDatetime( last_ping) return last_client_id, last_pings
def ReadUserNotifications(self, username, state=None, timerange=None, cursor=None): """Reads notifications scheduled for a user within a given timerange.""" query = ("SELECT UNIX_TIMESTAMP(timestamp), " " notification_state, notification " "FROM user_notification " "WHERE username_hash = %s ") args = [mysql_utils.Hash(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 >= FROM_UNIXTIME(%s) " args.append(mysql_utils.RDFDatetimeToTimestamp(time_from)) if time_to is not None: query += "AND timestamp <= FROM_UNIXTIME(%s) " args.append(mysql_utils.RDFDatetimeToTimestamp(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.TimestampToRDFDatetime(timestamp) n.state = state ret.append(n) return ret
def ReadHuntLogEntries(self, hunt_id, offset, count, with_substring=None, cursor=None): """Reads hunt log entries of a given hunt using given query options.""" hunt_id_int = db_utils.HuntIDToInt(hunt_id) query = ( "SELECT client_id, flow_id, message, UNIX_TIMESTAMP(timestamp) " "FROM flow_log_entries " "FORCE INDEX(flow_log_entries_by_hunt) " "WHERE hunt_id = %s AND flow_id = hunt_id ") args = [hunt_id_int] if with_substring is not None: query += "AND message LIKE %s " args.append("%" + db_utils.EscapeWildcards(with_substring) + "%") query += "ORDER BY timestamp ASC LIMIT %s OFFSET %s" args.append(count) args.append(offset) cursor.execute(query, args) flow_log_entries = [] for client_id_int, flow_id_int, message, timestamp in cursor.fetchall( ): flow_log_entries.append( rdf_flow_objects.FlowLogEntry( client_id=db_utils.IntToClientID(client_id_int), flow_id=db_utils.IntToFlowID(flow_id_int), hunt_id=hunt_id, message=message, timestamp=mysql_utils.TimestampToRDFDatetime(timestamp))) return flow_log_entries
def ReadClientSnapshotHistory(self, client_id, timerange=None, cursor=None): """Reads the full history for a particular client.""" client_id_int = db_utils.ClientIDToInt(client_id) query = ("SELECT sn.client_snapshot, st.startup_info, " " UNIX_TIMESTAMP(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 >= FROM_UNIXTIME(%s) " args.append(mysql_utils.RDFDatetimeToTimestamp(time_from)) if time_to is not None: query += "AND sn.timestamp <= FROM_UNIXTIME(%s) " args.append(mysql_utils.RDFDatetimeToTimestamp(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.FromSerializedBytes(snapshot) client.startup_info = rdf_client.StartupInfo.FromSerializedBytes( startup_info) client.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp) ret.append(client) return ret
def ListHuntObjects(self, offset, count, with_creator=None, created_after=None, with_description_match=None, cursor=None): """Reads metadata for hunt objects from the database.""" query = """ SELECT hunt_id, UNIX_TIMESTAMP(create_timestamp), UNIX_TIMESTAMP(last_update_timestamp), creator, duration_micros, client_rate, client_limit, hunt_state, hunt_state_comment, UNIX_TIMESTAMP(init_start_time), UNIX_TIMESTAMP(last_start_time), description FROM hunts """ args = [] components = [] if with_creator is not None: components.append("creator = %s ") args.append(with_creator) if created_after is not None: components.append("create_timestamp > FROM_UNIXTIME(%s) ") args.append(mysql_utils.RDFDatetimeToTimestamp(created_after)) if with_description_match is not None: components.append("description LIKE %s") args.append("%" + with_description_match + "%") if components: query += "WHERE " + " AND ".join(components) query += " ORDER BY create_timestamp DESC LIMIT %s OFFSET %s" args.append(count) args.append(offset) cursor.execute(query, args) result = [] for row in cursor.fetchall(): (hunt_id, create_timestamp, last_update_timestamp, creator, duration_micros, client_rate, client_limit, hunt_state, hunt_state_comment, init_start_time, last_start_time, description) = row result.append( rdf_hunt_objects.HuntMetadata( hunt_id=db_utils.IntToHuntID(hunt_id), description=description or None, create_time=mysql_utils.TimestampToRDFDatetime( create_timestamp), creator=creator, duration=rdfvalue.Duration.From(duration_micros, rdfvalue.MICROSECONDS), client_rate=client_rate, client_limit=client_limit, hunt_state=hunt_state, hunt_state_comment=hunt_state_comment or None, last_update_time=mysql_utils.TimestampToRDFDatetime( last_update_timestamp), init_start_time=mysql_utils.TimestampToRDFDatetime( init_start_time), last_start_time=mysql_utils.TimestampToRDFDatetime( last_start_time))) return result
def ReadLatestPathInfosWithHashBlobReferences(self, client_paths, max_timestamp=None, cursor=None): """Returns PathInfos that have corresponding HashBlobReferences.""" path_infos = {client_path: None for client_path in client_paths} path_id_components = {} for client_path in client_paths: path_id_components[client_path.path_id] = client_path.components params = [] query = """ SELECT t.client_id, t.path_type, t.path_id, UNIX_TIMESTAMP(t.timestamp), s.stat_entry, h.hash_entry FROM (SELECT h.client_id, h.path_type, h.path_id, MAX(h.timestamp) AS timestamp FROM client_path_hash_entries AS h INNER JOIN hash_blob_references AS b ON b.hash_id = h.sha256 WHERE {conditions} GROUP BY client_id, path_type, path_id) AS t LEFT JOIN client_path_stat_entries AS s ON s.client_id = t.client_id AND s.path_type = t.path_type AND s.path_id = t.path_id AND s.timestamp = t.timestamp LEFT JOIN client_path_hash_entries AS h ON h.client_id = t.client_id AND h.path_type = t.path_type AND h.path_id = t.path_id AND h.timestamp = t.timestamp """ path_conditions = [] for client_path in client_paths: path_conditions.append(""" (client_id = %s AND path_type = %s AND path_id = %s) """) params.append(db_utils.ClientIDToInt(client_path.client_id)) params.append(int(client_path.path_type)) params.append(client_path.path_id.AsBytes()) conditions = " OR ".join(path_conditions) if max_timestamp is not None: conditions = "({}) AND UNIX_TIMESTAMP(timestamp) <= %s".format( conditions) params.append(mysql_utils.RDFDatetimeToTimestamp(max_timestamp)) cursor.execute(query.format(conditions=conditions), params) for row in cursor.fetchall(): # pyformat: disable (client_id, path_type, path_id_bytes, timestamp, stat_entry_bytes, hash_entry_bytes) = row # pyformat: enable path_id = rdf_objects.PathID.FromBytes(path_id_bytes) components = path_id_components[path_id] if stat_entry_bytes is not None: stat_entry = rdf_client_fs.StatEntry.FromSerializedString( stat_entry_bytes) else: stat_entry = None hash_entry = rdf_crypto.Hash.FromSerializedString(hash_entry_bytes) client_path = db.ClientPath( client_id=db_utils.IntToClientID(client_id), path_type=path_type, components=path_id_components[path_id]) path_info = rdf_objects.PathInfo( path_type=path_type, components=components, stat_entry=stat_entry, hash_entry=hash_entry, timestamp=mysql_utils.TimestampToRDFDatetime(timestamp)) path_infos[client_path] = path_info return path_infos
def ReadPathInfosHistories(self, client_id, path_type, components_list, cursor=None): """Reads a collection of hash and stat entries for given paths.""" # MySQL does not handle well empty `IN` clauses so we guard against that. if not components_list: return {} path_infos = {components: [] for components in components_list} path_id_components = {} for components in components_list: path_id = rdf_objects.PathID.FromComponents(components) path_id_components[path_id] = components # MySQL does not support full outer joins, so we emulate them with a union. query = """ SELECT s.path_id, s.stat_entry, UNIX_TIMESTAMP(s.timestamp), h.path_id, h.hash_entry, UNIX_TIMESTAMP(h.timestamp) FROM client_path_stat_entries AS s LEFT JOIN client_path_hash_entries AS h ON s.client_id = h.client_id AND s.path_type = h.path_type AND s.path_id = h.path_id AND s.timestamp = h.timestamp WHERE s.client_id = %(client_id)s AND s.path_type = %(path_type)s AND s.path_id IN %(path_ids)s UNION SELECT s.path_id, s.stat_entry, UNIX_TIMESTAMP(s.timestamp), h.path_id, h.hash_entry, UNIX_TIMESTAMP(h.timestamp) FROM client_path_hash_entries AS h LEFT JOIN client_path_stat_entries AS s ON h.client_id = s.client_id AND h.path_type = s.path_type AND h.path_id = s.path_id AND h.timestamp = s.timestamp WHERE h.client_id = %(client_id)s AND h.path_type = %(path_type)s AND h.path_id IN %(path_ids)s """ params = { "client_id": db_utils.ClientIDToInt(client_id), "path_type": int(path_type), "path_ids": [path_id.AsBytes() for path_id in path_id_components] } cursor.execute(query, params) for row in cursor.fetchall(): # pyformat: disable (stat_entry_path_id_bytes, stat_entry_bytes, stat_entry_timestamp, hash_entry_path_id_bytes, hash_entry_bytes, hash_entry_timestamp) = row # pyformat: enable path_id_bytes = stat_entry_path_id_bytes or hash_entry_path_id_bytes path_id = rdf_objects.PathID.FromBytes(path_id_bytes) components = path_id_components[path_id] timestamp = stat_entry_timestamp or hash_entry_timestamp if stat_entry_bytes is not None: stat_entry = rdf_client_fs.StatEntry.FromSerializedString( stat_entry_bytes) else: stat_entry = None if hash_entry_bytes is not None: hash_entry = rdf_crypto.Hash.FromSerializedString( hash_entry_bytes) else: hash_entry = None path_info = rdf_objects.PathInfo( path_type=path_type, components=components, stat_entry=stat_entry, hash_entry=hash_entry, timestamp=mysql_utils.TimestampToRDFDatetime(timestamp)) path_infos[components].append(path_info) for components in components_list: path_infos[components].sort( key=lambda path_info: path_info.timestamp) return path_infos
def ReadHuntResults(self, hunt_id, offset, count, with_tag=None, with_type=None, with_substring=None, with_timestamp=None, cursor=None): """Reads hunt results of a given hunt using given query options.""" hunt_id_int = db_utils.HuntIDToInt(hunt_id) query = ("SELECT client_id, flow_id, hunt_id, payload, type, " "UNIX_TIMESTAMP(timestamp), tag " "FROM flow_results " "FORCE INDEX(flow_results_hunt_id_flow_id_timestamp) " "WHERE hunt_id = %s ") args = [hunt_id_int] if with_tag: query += "AND tag = %s " args.append(with_tag) if with_type: query += "AND type = %s " args.append(with_type) if with_substring: query += "AND payload LIKE %s " args.append("%" + db_utils.EscapeWildcards(with_substring) + "%") if with_timestamp: query += "AND timestamp = FROM_UNIXTIME(%s) " args.append(mysql_utils.RDFDatetimeToTimestamp(with_timestamp)) query += "ORDER BY timestamp ASC LIMIT %s OFFSET %s" args.append(count) args.append(offset) cursor.execute(query, args) ret = [] for ( client_id_int, flow_id_int, hunt_id_int, serialized_payload, payload_type, timestamp, tag, ) in cursor.fetchall(): if payload_type in rdfvalue.RDFValue.classes: payload = rdfvalue.RDFValue.classes[ payload_type].FromSerializedBytes(serialized_payload) else: payload = rdf_objects.SerializedValueOfUnrecognizedType( type_name=payload_type, value=serialized_payload) result = rdf_flow_objects.FlowResult( client_id=db_utils.IntToClientID(client_id_int), flow_id=db_utils.IntToFlowID(flow_id_int), hunt_id=hunt_id, payload=payload, timestamp=mysql_utils.TimestampToRDFDatetime(timestamp)) if tag is not None: result.tag = tag ret.append(result) return ret
def Now(self, cursor: MySQLdb.cursors.Cursor) -> rdfvalue.RDFDatetime: cursor.execute("SELECT UNIX_TIMESTAMP(NOW(6))") [(timestamp,)] = cursor.fetchall() return mysql_utils.TimestampToRDFDatetime(timestamp)
def _CronJobRunFromRow(self, row): serialized_run, timestamp = row res = rdf_cronjobs.CronJobRun.FromSerializedBytes(serialized_run) res.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp) return res
def _AuditEntryFromRow(details, timestamp): entry = rdf_objects.APIAuditEntry.FromSerializedBytes(details) entry.timestamp = mysql_utils.TimestampToRDFDatetime(timestamp) return entry
def ReadPathInfosHistories( self, client_id: Text, path_type: rdf_objects.PathInfo.PathType, components_list: Iterable[Sequence[Text]], cutoff: Optional[rdfvalue.RDFDatetime] = None, cursor: Optional[MySQLdb.cursors.Cursor] = None ) -> Dict[Sequence[Text], Sequence[rdf_objects.PathInfo]]: """Reads a collection of hash and stat entries for given paths.""" # MySQL does not handle well empty `IN` clauses so we guard against that. if not components_list: return {} path_infos = {components: [] for components in components_list} path_id_components = {} for components in components_list: path_id = rdf_objects.PathID.FromComponents(components) path_id_components[path_id] = components params = { "client_id": db_utils.ClientIDToInt(client_id), "path_type": int(path_type), } for path_id in path_id_components: params["path_id_%s" % path_id.AsHexString()] = path_id.AsBytes() path_id_placeholders = ", ".join([ "%(path_id_{})s".format(path_id.AsHexString()) for path_id in path_id_components ]) if cutoff is not None: stat_entry_timestamp_condition = """ AND s.timestamp <= FROM_UNIXTIME(%(cutoff)s) """ hash_entry_timestamp_condition = """ AND h.timestamp <= FROM_UNIXTIME(%(cutoff)s) """ params["cutoff"] = mysql_utils.RDFDatetimeToTimestamp(cutoff) else: stat_entry_timestamp_condition = "" hash_entry_timestamp_condition = "" # MySQL does not support full outer joins, so we emulate them with a union. query = """ SELECT s.path_id, s.stat_entry, UNIX_TIMESTAMP(s.timestamp), h.path_id, h.hash_entry, UNIX_TIMESTAMP(h.timestamp) FROM client_path_stat_entries AS s LEFT JOIN client_path_hash_entries AS h ON s.client_id = h.client_id AND s.path_type = h.path_type AND s.path_id = h.path_id AND s.timestamp = h.timestamp WHERE s.client_id = %(client_id)s AND s.path_type = %(path_type)s AND s.path_id IN ({path_id_placeholders}) {stat_entry_timestamp_condition} UNION SELECT s.path_id, s.stat_entry, UNIX_TIMESTAMP(s.timestamp), h.path_id, h.hash_entry, UNIX_TIMESTAMP(h.timestamp) FROM client_path_hash_entries AS h LEFT JOIN client_path_stat_entries AS s ON h.client_id = s.client_id AND h.path_type = s.path_type AND h.path_id = s.path_id AND h.timestamp = s.timestamp WHERE h.client_id = %(client_id)s AND h.path_type = %(path_type)s AND h.path_id IN ({path_id_placeholders}) {hash_entry_timestamp_condition} """.format( stat_entry_timestamp_condition=stat_entry_timestamp_condition, hash_entry_timestamp_condition=hash_entry_timestamp_condition, path_id_placeholders=path_id_placeholders) cursor.execute(query, params) for row in cursor.fetchall(): # pyformat: disable (stat_entry_path_id_bytes, stat_entry_bytes, stat_entry_timestamp, hash_entry_path_id_bytes, hash_entry_bytes, hash_entry_timestamp) = row # pyformat: enable path_id_bytes = stat_entry_path_id_bytes or hash_entry_path_id_bytes path_id = rdf_objects.PathID.FromSerializedBytes(path_id_bytes) components = path_id_components[path_id] timestamp = stat_entry_timestamp or hash_entry_timestamp if stat_entry_bytes is not None: stat_entry = rdf_client_fs.StatEntry.FromSerializedBytes( stat_entry_bytes) else: stat_entry = None if hash_entry_bytes is not None: hash_entry = rdf_crypto.Hash.FromSerializedBytes(hash_entry_bytes) else: hash_entry = None path_info = rdf_objects.PathInfo( path_type=path_type, components=components, stat_entry=stat_entry, hash_entry=hash_entry, timestamp=mysql_utils.TimestampToRDFDatetime(timestamp)) path_infos[components].append(path_info) for components in components_list: path_infos[components].sort(key=lambda path_info: path_info.timestamp) return path_infos