def _set_response(self):
# type: () -> None
'''
Updates the user response for this task.
'''
SQLEngine.execute(SET_RESPONSE,
(self.comment, self.username, self.performed,
self.authenticated, self.hash))
def _set_status(self, status):
# type: (int) -> None
'''
Sets the status of a task in the DB.
Args:
status (int): The new status to use.
'''
SQLEngine.execute(SET_STATUS, (status, self.hash))
def remove(self, name):
# type: (str) -> None
'''
Removes a name to the blacklist.
Args:
name (str): The name to remove from the blacklist.
'''
self._blacklist.remove(name)
SQLEngine.execute('DELETE FROM blacklist WHERE ldap = %s', (name, ))
def add(self, name):
# type: (str) -> None
'''
Adds a name to the blacklist.
Args:
name (str): The name to add to the blacklist.
'''
self._blacklist.add(name)
SQLEngine.execute('INSERT INTO blacklist (ldap) VALUES (%s)', (name, ))
def create_securitybot_task(search_name, hash, username, description, reason,
url):
'''
Creates a new Maniphest task with the securitybot tag so that the bot can
reach out to the relevant people.
'''
logging.info('Creating new task about {} for {}'.format(
description, username))
# Check for collision
rows = SQLEngine.execute('SELECT title FROM alerts WHERE hash=UNHEX(%s)',
(hash, ))
if rows:
raise CollisionException('''We found a collision with {0} for {1}.
Most likely the Splunk alert with configured incorrectly.
However, if this is a geniune collision, then you have a paper to write. Good luck.
'''.format(rows, hash))
# Insert that into the database as a new alert
create_new_alert(search_name,
username,
description,
reason,
url,
user=hash)
def find_on_hash(hash):
# type: (str) -> Sequence[Any]
match = SQLEngine.execute('SELECT comment, performed, authenticated FROM user_responses WHERE hash=%s', (hash,))
if len(match) != 1:
# This catches collisions too, which is probably (hopefully) overkill
return None
item = match[0]
return item[0], bool(item[1]), bool(item[2])
def __init__(self):
# type: () -> None
'''
Creates a new blacklist tied to a table named "blacklist".
'''
# Load from table
names = SQLEngine.execute('SELECT * FROM blacklist')
# Break tuples into names
self._blacklist = {name[0] for name in names}
def ignore_task(username, title, reason, ttl):
# type: (str, str, str, timedelta) -> None
'''
Adds a task with the given title to the ignore list for the given
amount of time. Additionally adds an optional message to specify the
reason that the alert was ignored.
Args:
username (str): The username of the user to ignore the given alert for.
title (str): The title of the alert to ignore.
ttl (Timedelta): The amount of time to ignore the alert for.
msg (str): An optional string specifying why an alert was ignored
'''
expiry_time = datetime.now(tz=pytz.utc) + ttl
# NB: Non-standard MySQL specific query
SQLEngine.execute(
'''INSERT INTO ignored (ldap, title, reason, until)
VALUES (%s, %s, %s, %s)
ON DUPLICATE KEY UPDATE reason=VALUES(reason), until=VALUES(until)
''', (username, title, reason, expiry_time.strftime('%Y-%m-%d %H:%M:%S')))
def _get_tasks(self, level):
# type: (int) -> List[Task]
'''
Gets all tasks of a certain level.
Args:
level (int): One of STATUS_LEVELS
Returns:
List of SQLTasks.
'''
alerts = SQLEngine.execute(GET_ALERTS, (level, ))
return [SQLTask(*alert) for alert in alerts]
def _get_tasks(self, level):
# type: (int) -> List[Task]
'''
Gets all tasks of a certain level.
Args:
level (int): One of STATUS_LEVELS
Returns:
List of SQLTasks.
'''
alerts = SQLEngine.execute(GET_ALERTS, (level, ))
tasks = [SQLTask(*alert) for alert in alerts]
for task in tasks:
task.escalation = []
escalation_list = SQLEngine.execute(GET_ESCALATION, (task.hash, ))
for escalation_tuple in escalation_list:
task.escalation.append(Escalation(*escalation_tuple))
logging.debug("Fetched task from DB: {0}".format(task))
return tasks
def create_new_alert(title, ldap, description, reason, url='N/A', key=None):
# type: (str, str, str, str, str, str) -> None
'''
Creates a new alert in the SQL DB with an optionally random hash.
'''
# Generate random key if none provided
if key is None:
key = binascii.hexlify(os.urandom(32))
# Insert that into the database as a new alert
SQLEngine.execute(
'''
INSERT INTO alerts (hash, ldap, title, description, reason, url, event_time)
VALUES (UNHEX(%s), %s, %s, %s, %s, %s, NOW())
''', (key, ldap, title, description, reason, url))
SQLEngine.execute(
'''
INSERT INTO user_responses (hash, comment, performed, authenticated)
VALUES (UNHEX(%s), '', false, false)
''', (key, ))
SQLEngine.execute(
'INSERT INTO alert_status (hash, status) VALUES (UNHEX(%s), 0)',
(key, ))
def create_new_alert(title,
ldap,
description,
reason,
url=None,
key=None,
escalation_list=None):
# type: (str, str, str, str, str, str) -> None
'''
Creates a new alert in the SQL DB with an optionally random hash.
'''
# Generate random key if none provided
if key is None:
key = binascii.hexlify(os.urandom(32))
if url is None:
# currently url field cannot be NULL
url = ''
# Insert that into the database as a new alert
SQLEngine.execute(
'''
INSERT INTO alerts (hash, ldap, title, description, reason, url, event_time)
VALUES (UNHEX(%s), %s, %s, %s, %s, %s, NOW())
''', (key, ldap, title, description, reason, url))
SQLEngine.execute(
'''
INSERT INTO user_responses (hash, ldap, comment, performed, authenticated, updated_at)
VALUES (UNHEX(%s), ldap, '', false, false, NOW())
''', (key, ))
if escalation_list is not None and isinstance(escalation_list, list):
for escalation in escalation_list:
SQLEngine.execute(
'INSERT INTO escalation (hash, ldap, delay_in_sec, escalated_at) VALUES (UNHEX(%s), %s, %s, NULL)',
(key, escalation.ldap, escalation.delay_in_sec))
SQLEngine.execute(
'INSERT INTO alert_status (hash, status) VALUES (UNHEX(%s), 0)',
(key, ))
logging.info("Created new alert: {}".format({
'title': title,
'ldap': ldap,
'description': description,
'reason': reason,
'url': url,
'escalation': escalation_list
}))
def get_ignored(username):
# type: (str) -> Dict[str, str]
'''
Returns a dictionary of ignored alerts to reasons why
the ignored are ignored.
Args:
username (str): The username of the user to retrieve ignored alerts for.
Returns:
Dict[str, str]: A mapping of ignored alert titles to reasons
'''
__update_ignored_list()
rows = SQLEngine.execute(
'''SELECT title, reason FROM ignored WHERE ldap = %s''', (username, ))
return {row[0]: row[1] for row in rows}
def ignored(**kwargs):
# type: (**Any) -> Dict[str, Any]
'''
Makes a call to the ignored database.
Content:
{
"ignored": List[Dict]: list of dictionaries representing ignored alerts
}
Each item in "ignored" has fields in IGNORED_FIELDS
'''
response = build_response()
args = kwargs
build_arguments(DEFAULT_IGNORED_ARGUMENTS, args, response)
query = IGNORED_QUERY
params = [] # type: List[Any]
if args['ldap'] is not None:
query += build_where(build_in(LDAP_IN, len(args['ldap'])), False)
params.extend(args['ldap'])
query += IGNORED_ORDER_BY
query += LIMIT
params.append(args['limit'])
try:
raw_results = SQLEngine.execute(query, params)
except SQLEngineException:
response['error'] = 'Invalid parameters'
return response
results = build_query_dict(IGNORED_FIELDS, raw_results)
# Convert datetimes to timestamps
for ignored in results:
ignored['until'] = int(ignored['until'].strftime('%s'))
response['content']['ignored'] = results
response['ok'] = True
return response
def blacklist(**kwargs):
# type: (**Any) -> Dict[str, Any]
'''
Makes a call to the ignored database.
Content:
{
"blacklist": List[Dict]: list of dictionaries representing ignored alerts
}
Each item in "blacklist" has only an ldap
'''
response = build_response()
args = kwargs
build_arguments(DEFAULT_BLACKLIST_ARGUMENTS, args, response)
try:
raw_results = SQLEngine.execute(BLACKLIST_QUERY, (args['limit'], ))
except SQLEngineException:
response['error'] = 'Invalid parameters'
return response
results = build_query_dict(BLACKLIST_FIELDS, raw_results)
response['content']['blacklist'] = results
response['ok'] = True
return response
def alerts(args):
# type: (Any) -> Sequence[Any]
params = [] # type: List[Any]
query = MAIN_QUERY
# Prepare for possible limited status
if args.status is not None:
query += build_where(STATUS_WHERE)
params += args.status
# Prepare for possible limited performed boolean
if args.performed is not None:
query += build_where(PERFORMED_WHERE)
params += args.performed
# Prepare for possible title restrictions
if args.titles is not None:
query += build_where(build_in(len(args.titles)))
params.extend(args.titles)
# Add time bounding
if args.before is not None:
query += build_where(BEFORE)
params += [parse_time(args.before[0])]
if args.after is not None:
query += build_where(AFTER)
params += [parse_time(args.after[0])]
# Append limit restriction and order by
query += build_order_by(args.order[0]) + '\n'
query += LIMIT
params += args.limit
# Perform query
raw_results = SQLEngine.execute(query, params)
results = build_query_dict(raw_results)
to_remove = [] # type: List[str]
# Set extra fields to remove
if args.drop is not None:
to_remove.extend(args.drop)
# Remove hashes if not specified
if not args.hash:
to_remove.append('hash')
# Remove status if one was specified earlier
if args.status is not None:
to_remove.append('status')
# Remove time if not specified
if not args.time:
to_remove.append('event_time')
# Remove URL if not specified
if not args.url:
to_remove.append('splunk_url')
# Anonymize if specified
if args.anon:
to_remove.append('ldap')
# Remove columns
for row in results:
for col in to_remove:
row.pop(col, None)
# Grab list of fields and convert to list of lists
fields = [field for field in QUERY_FIELDS if field not in to_remove]
matrix = [[row[field] for field in fields] for row in results]
return fields, matrix
def ignored(alerts):
# type: (Any) -> Sequence[Any]
results = SQLEngine.execute(IGNORED_QUERY)
return IGNORED_FIELDS, [list(row) for row in results]
def set_escalated(self, escalation):
escalation.set_notified()
SQLEngine.execute(
SET_ESCALATED,
(self.hash, escalation.ldap, escalation.delay_in_sec))
def __update_ignored_list():
# type: () -> None
'''
Prunes the ignored table of old ignored alerts.
'''
SQLEngine.execute('''DELETE FROM ignored WHERE until <= NOW()''')
def query(**kwargs):
# type: (**Any) -> Dict[str, Any]
'''
Queries the alerts database.
Args:
**kwargs: Arguments to the API endpoint.
Content:
{
"alerts": List[Dict]: list of dictionaries representing alerts in the database
}
Each alert has all of the fields in QUERY_FIELDS.
'''
response = build_response()
args = kwargs
build_arguments(DEFAULT_QUERY_ARGUMENTS, args, response)
# Build query
query = ALERTS_QUERY
params = [] # type: List[Any]
has_where = False
# Add possible where statements
if args['status'] is not None:
query += build_where(STATUS_WHERE, has_where)
params.append(args['status'])
has_where = True
if args['performed'] is not None:
query += build_where(PERFORMED_WHERE, has_where)
params.append(args['performed'])
has_where = True
if args['titles'] is not None:
query += build_where(build_in(TITLE_IN, len(args['titles'])),
has_where)
params.extend(args['titles'])
has_where = True
if args['ldap'] is not None:
query += build_where(build_in(LDAP_IN, len(args['ldap'])), has_where)
params.extend(args['ldap'])
has_where = True
# Add time bounds
if args['before'] is not None:
query += build_where(BEFORE, has_where)
params.append(args['before'])
has_where = True
if args['after'] is not None:
query += build_where(AFTER, has_where)
params.append(args['after'])
has_where = True
# Add limit
query += 'ORDER BY event_time DESC\n'
query += LIMIT
params.append(args['limit'])
# Make SQL query
try:
raw_results = SQLEngine.execute(query, params)
except SQLEngineException:
response['error'] = 'Invalid parameters'
return response
results = build_query_dict(ALERTS_FIELDS, raw_results)
# Convert datetimes to unix time
for alert in results:
alert['event_time'] = int(alert['event_time'].strftime('%s'))
response['content']['alerts'] = results
response['ok'] = True
return response
def init():
# type: () -> None
SQLEngine('localhost', 'root', '', 'securitybot')
def blacklist(args):
# type: (Any) -> Sequence[Any]
fields = ['ldap']
results = SQLEngine.execute(BLACKLIST_QUERY)
return fields, [list(row) for row in results]
def main(args):
# type: (Any) -> None
SQLEngine('localhost', 'root', '', 'securitybot')
create_new_alert('custom_alert', args.name[0], args.title[0], args.reason[0])
