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
