# Copyright 2014 Netflix, Inc.

#

# Licensed under the Apache License, Version 2.0 (the "License");

# you may not use this file except in compliance with the License.

# You may obtain a copy of the License at

#

# http://www.apache.org/licenses/LICENSE-2.0

#

# Unless required by applicable law or agreed to in writing, software

# distributed under the License is distributed on an "AS IS" BASIS,

# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

# See the License for the specific language governing permissions and

# limitations under the License.

"""

.. module: security_monkey.datastore

:platform: Unix

:synopsis: Contains the SQLAlchemy models and a few helper methods.

.. version:: $$VERSION$$

.. moduleauthor:: Patrick Kelley <pkelley@netflix.com> @monkeysecurity

"""

from flask_security.core import UserMixin, RoleMixin

from flask_security.signals import user_registered

from sqlalchemy import BigInteger

from .auth.models import RBACUserMixin

from security_monkey import db, app

from sqlalchemy.dialects.postgresql import JSON

from sqlalchemy import Column, Integer, String, DateTime, Boolean, Unicode, Text

from sqlalchemy.dialects.postgresql import CIDR

from sqlalchemy.schema import ForeignKey, UniqueConstraint

from sqlalchemy.orm import relationship, backref, column_property

from sqlalchemy.ext.hybrid import hybrid_property

from sqlalchemy import select, func

from sqlalchemy.orm import deferred

from copy import deepcopy

import dpath.util

from dpath.exceptions import PathNotFound

from security_monkey.common.utils import sub_dict

import datetime

import json

import hashlib

import traceback

association_table = db.Table(

'association',

Column('user_id', Integer, ForeignKey('user.id'), primary_key=True),

Column('account_id', Integer, ForeignKey('account.id'), primary_key=True)

)

class AccountType(db.Model):

"""

Defines the type of account based on where the data lives, e.g. AWS.

"""

__tablename__ = "account_type"

id = Column(Integer, primary_key=True)

name = db.Column(db.String(80), unique=True)

accounts = relationship("Account", backref="account_type")

class Account(db.Model):

"""

Meant to model AWS accounts.

"""

__tablename__ = "account"

id = Column(Integer, primary_key=True)

active = Column(Boolean())

third_party = Column(Boolean())

name = Column(String(50), index=True, unique=True)

notes = Column(String(256))

identifier = Column(String(256), unique=True) # Unique id of the account, the number for AWS.

items = relationship("Item", backref="account", cascade="all, delete, delete-orphan")

issue_categories = relationship("AuditorSettings", backref="account")

account_type_id = Column(Integer, ForeignKey("account_type.id"), nullable=False)

custom_fields = relationship("AccountTypeCustomValues", lazy="immediate", cascade="all, delete, delete-orphan")

unique_const = UniqueConstraint('account_type_id', 'identifier')

# 'lazy' is required for the Celery scheduler to reference the type:

type = relationship("AccountType", backref="account_type", lazy="immediate")

exceptions = relationship("ExceptionLogs", backref="account", cascade="all, delete, delete-orphan")

def getCustom(self, name):

for field in self.custom_fields:

if field.name == name:

return field.value

return None

class AccountTypeCustomValues(db.Model):

"""

Defines the values for custom fields defined in AccountTypeCustomFields.

"""

__tablename__ = "account_type_values"

id = Column(Integer, primary_key=True)

name = Column(db.String(64))

value = db.Column(db.String(256))

account_id = Column(Integer, ForeignKey("account.id"), nullable=False)

unique_const = UniqueConstraint('account_id', 'name')

class Technology(db.Model):

"""

meant to model AWS primitives (elb, s3, iamuser, iamgroup, etc.)

"""

__tablename__ = 'technology'

id = Column(Integer, primary_key=True)

name = Column(String(32), index=True, unique=True) # elb, s3, iamuser, iamgroup, etc.

items = relationship("Item", backref="technology")

issue_categories = relationship("AuditorSettings", backref="technology")

ignore_items = relationship("IgnoreListEntry", backref="technology")

exceptions = relationship("ExceptionLogs", backref="technology", cascade="all, delete, delete-orphan")

roles_users = db.Table(

'roles_users',

db.Column('user_id', db.Integer(), db.ForeignKey('user.id'), primary_key=True),

db.Column('role_id', db.Integer(), db.ForeignKey('role.id'), primary_key=True)

)

class Role(db.Model, RoleMixin):

"""

Used by Flask-Login / the auth system to check user permissions.

"""

id = db.Column(db.Integer(), primary_key=True)

name = db.Column(db.String(80), unique=True)

description = db.Column(db.String(255))

class User(UserMixin, db.Model, RBACUserMixin):

"""

Used by Flask-Security and Flask-Login.

Represents a user of Security Monkey.

"""

id = db.Column(db.Integer, primary_key=True)

email = db.Column(db.String(255), unique=True)

password = db.Column(db.String(255))

active = db.Column(db.Boolean())

confirmed_at = db.Column(db.DateTime())

daily_audit_email = Column(Boolean())

change_reports = Column(String(32)) # All, OnlyWithIssues, None

# Flask-Security SECURITY_TRACKABLE

last_login_at = Column(DateTime())

current_login_at = Column(DateTime())

login_count = Column(Integer)

# Why 45 characters for IP Address ?

# See http://stackoverflow.com/questions/166132/maximum-length-of-the-textual-representation-of-an-ipv6-address/166157#166157

last_login_ip = Column(db.String(45))

current_login_ip = Column(db.String(45))

accounts = relationship("Account", secondary=association_table)

item_audits = relationship("ItemAudit", uselist=False, backref="user")

revision_comments = relationship("ItemRevisionComment", backref="user")

item_comments = relationship("ItemComment", backref="user")

roles = db.relationship('Role', secondary=roles_users,

backref=db.backref('users', lazy='dynamic'))

role = db.Column(db.String(30), default="View")

def __str__(self):

return '<User id=%s email=%s>' % (self.id, self.email)

issue_item_association = db.Table('issue_item_association',

Column('super_issue_id', Integer, ForeignKey('itemaudit.id'), primary_key=True),

Column('sub_item_id', Integer, ForeignKey('item.id'), primary_key=True)

)

class ItemAudit(db.Model):

"""

Meant to model an issue attached to a single item.

"""

__tablename__ = "itemaudit"

id = Column(Integer, primary_key=True)

score = Column(Integer)

issue = Column(String(512))

notes = Column(String(1024))

action_instructions = Column(Text(), nullable=True)

background_info = Column(Text(), nullable=True)

origin = Column(Text(), nullable=True)

origin_summary = Column(Text(), nullable=True)

class_uuid = Column(String(32), nullable=True)

fixed = Column(Boolean, default=False, nullable=False)

justified = Column(Boolean)

justified_user_id = Column(Integer, ForeignKey("user.id"), nullable=True, index=True)

justification = Column(String(512))

justified_date = Column(DateTime(), default=datetime.datetime.utcnow, nullable=True)

item_id = Column(Integer, ForeignKey("item.id"), nullable=False, index=True)

auditor_setting_id = Column(Integer, ForeignKey("auditorsettings.id"), nullable=True, index=True)

sub_items = relationship("Item", secondary=issue_item_association, back_populates="issues")

item = relationship("Item") # TODO: Remove this when the issue system is refactored.

def __str__(self):

return "Issue: [{issue}] Score: {score} Fixed: {fixed} Justified: {justified}\nNotes: {notes}\n".format(

issue=self.issue,

score=self.score,

fixed=self.fixed,

justified=self.justified,

notes=self.notes)

def __repr__(self):

return self.__str__()

def sub_ids(self):

item_ids = []

for sub_item in self.sub_items:

item_ids.append(sub_item.id)

return str(item_ids.sort())

def key(self):

return '{issue} -- {notes} -- {score} -- {subids}'.format(

issue=self.issue,

notes=self.notes,

score=self.score,

subids=self.sub_ids())

def copy_unlinked(self):

"""

Used to address SQLAlchemy annoyances when the auditor saves issues. For some reason... if we don't

make a copy of this object, SQLAlchemy complains that it's already attached to item...

"""

return ItemAudit(score=self.score, issue=self.issue, notes=self.notes,

action_instructions=self.action_instructions,

background_info=self.background_info, origin=self.origin,

origin_summary=self.origin_summary, class_uuid=self.class_uuid,

fixed=self.fixed, justified=self.justified, justified_user_id=self.justified_user_id,

justification=self.justification, justified_date=self.justified_date,

auditor_setting_id=self.auditor_setting_id)

class AuditorSettings(db.Model):

"""

This table contains auditor disable settings.

"""

__tablename__ = "auditorsettings"

id = Column(Integer, primary_key=True)

disabled = Column(Boolean(), nullable=False)

issue_text = Column(String(512), nullable=True)

auditor_class = Column(String(128))

issues = relationship("ItemAudit", backref="auditor_setting", cascade="all, delete, delete-orphan")

tech_id = Column(Integer, ForeignKey("technology.id"), index=True)

account_id = Column(Integer, ForeignKey("account.id"), index=True)

unique_const = UniqueConstraint('account_id', 'issue_text', 'tech_id')

class Item(db.Model):

"""

Meant to model a specific item, like an instance of a security group.

"""

__tablename__ = "item"

id = Column(Integer, primary_key=True)

region = Column(String(32), index=True)

# Max AWS name = 255 chars. Add 48 chars for ' (sg-12345678901234567 in vpc-12345678901234567)'

name = Column(String(303), index=True)

arn = Column(Text(), nullable=True, index=True, unique=True)

latest_revision_complete_hash = Column(String(32), index=True)

latest_revision_durable_hash = Column(String(32), index=True)

tech_id = Column(Integer, ForeignKey("technology.id"), nullable=False, index=True)

account_id = Column(Integer, ForeignKey("account.id"), nullable=False, index=True)

latest_revision_id = Column(Integer, nullable=True)

comments = relationship("ItemComment", backref="revision", cascade="all, delete, delete-orphan",

order_by="ItemComment.date_created")

revisions = relationship("ItemRevision", backref="item", cascade="all, delete, delete-orphan",

order_by="desc(ItemRevision.date_created)", lazy="dynamic")

cloudtrail_entries = relationship("CloudTrailEntry", backref="item", cascade="all, delete, delete-orphan",

order_by="CloudTrailEntry.event_time")

issues = relationship("ItemAudit", secondary=issue_item_association, back_populates="sub_items",

single_parent=True, cascade="all, delete, delete-orphan")

exceptions = relationship("ExceptionLogs", backref="item", cascade="all, delete, delete-orphan")

@hybrid_property

def score(self):

return db.session.query(

func.cast(

func.sum(ItemAudit.score),

Integer)

).filter(

ItemAudit.item_id == self.id,

ItemAudit.auditor_setting_id == AuditorSettings.id,

ItemAudit.fixed == False,

AuditorSettings.disabled == False).one()[0] or 0

@score.expression

def score(cls):

return select([func.sum(ItemAudit.score)]). \

where(ItemAudit.item_id == cls.id). \

where(ItemAudit.auditor_setting_id == AuditorSettings.id). \

where(ItemAudit.fixed == False). \

where(AuditorSettings.disabled == False). \

label('item_score')

@hybrid_property

def unjustified_score(self):

return db.session.query(

func.cast(

func.sum(ItemAudit.score),

Integer)

).filter(

ItemAudit.item_id == self.id,

ItemAudit.justified == False,

ItemAudit.fixed == False,

ItemAudit.auditor_setting_id == AuditorSettings.id,

AuditorSettings.disabled == False).one()[0] or 0

@unjustified_score.expression

def unjustified_score(cls):

return select([func.sum(ItemAudit.score)]). \

where(ItemAudit.item_id == cls.id). \

where(ItemAudit.justified == False). \

where(ItemAudit.fixed == False). \

where(ItemAudit.auditor_setting_id == AuditorSettings.id). \

where(AuditorSettings.disabled == False). \

label('item_unjustified_score')

issue_count = column_property(

select([func.count(ItemAudit.id)])

.where(ItemAudit.item_id == id)

.where(ItemAudit.fixed == False)

.where(ItemAudit.auditor_setting_id == AuditorSettings.id)

.where(AuditorSettings.disabled == False),

deferred=True

)

@hybrid_property

def latest_config(self):

"""Returns the config from the latest item revision."""

return db.session.query(ItemRevision).filter(ItemRevision.id == self.latest_revision_id).one().config

class ItemComment(db.Model):

"""

The Web UI allows users to add comments to items.

"""

__tablename__ = "itemcomment"

id = Column(Integer, primary_key=True)

user_id = Column(Integer, ForeignKey('user.id'), nullable=False, index=True)

item_id = Column(Integer, ForeignKey('item.id'), nullable=False, index=True)

date_created = Column(DateTime(), default=datetime.datetime.utcnow, nullable=False)

text = Column(Unicode(1024))

def __str__(self):

return "User [{user}]({date}): {text}".format(

user=self.user.email,

date=str(self.date_created),

text=self.text

)

def __repr__(self):

return self.__str__()

class ItemRevision(db.Model):

"""

Every new configuration for an item is saved in a new ItemRevision.

"""

__tablename__ = "itemrevision"

id = Column(Integer, primary_key=True)

active = Column(Boolean())

config = deferred(Column(JSON))

date_created = Column(DateTime(), default=datetime.datetime.utcnow, nullable=False, index=True)

date_last_ephemeral_change = Column(DateTime(), nullable=True, index=True)

item_id = Column(Integer, ForeignKey("item.id"), nullable=False, index=True)

comments = relationship("ItemRevisionComment", backref="revision", cascade="all, delete, delete-orphan",

order_by="ItemRevisionComment.date_created")

cloudtrail_entries = relationship("CloudTrailEntry", backref="revision", cascade="all, delete, delete-orphan",

order_by="CloudTrailEntry.event_time")

class CloudTrailEntry(db.Model):

"""

Bananapeel (the security_monkey rearchitecture) will use this table to

correlate CloudTrail entries to item revisions.

"""

__tablename__ = 'cloudtrail'

id = Column(Integer, primary_key=True)

event_id = Column(String(36), index=True, unique=True)

request_id = Column(String(36), index=True)

event_source = Column(String(64), nullable=False)

event_name = Column(String(64), nullable=False)

event_time = Column(DateTime(), default=datetime.datetime.utcnow, nullable=False, index=True)

request_parameters = deferred(Column(JSON))

responseElements = deferred(Column(JSON))

source_ip = Column(String(45))

user_agent = Column(String(300))

full_entry = deferred(Column(JSON))

user_identity = deferred(Column(JSON))

user_identity_arn = Column(String(300), index=True)

revision_id = Column(Integer, ForeignKey('itemrevision.id'), nullable=False, index=True)

item_id = Column(Integer, ForeignKey('item.id'), nullable=False, index=True)

class ItemRevisionComment(db.Model):

"""

The Web UI allows users to add comments to revisions.

"""

__tablename__ = "itemrevisioncomment"

id = Column(Integer, primary_key=True)

user_id = Column(Integer, ForeignKey('user.id'), nullable=False, index=True)

revision_id = Column(Integer, ForeignKey('itemrevision.id'), nullable=False, index=True)

date_created = Column(DateTime(), default=datetime.datetime.utcnow, nullable=False)

text = Column(Unicode(1024))

class NetworkWhitelistEntry(db.Model):

"""

This table contains user-entered CIDR's that security_monkey

will not alert on.

"""

__tablename__ = "networkwhitelist"

id = Column(Integer, primary_key=True)

name = Column(String(512))

notes = Column(String(512))

cidr = Column(CIDR)

class IgnoreListEntry(db.Model):

"""

This table contains user-entered prefixes that security_monkey

will ignore when slurping the AWS config.

"""

__tablename__ = "ignorelist"

id = Column(Integer, primary_key=True)

prefix = Column(String(512))

notes = Column(String(512))

tech_id = Column(Integer, ForeignKey("technology.id"), nullable=False, index=True)

class ExceptionLogs(db.Model):

"""

This table stores all exceptions that are encountered, and provides metadata and context

around the exceptions.

"""

__tablename__ = "exceptions"

id = Column(BigInteger, primary_key=True)

source = Column(String(256), nullable=False, index=True)

occurred = Column(DateTime, default=datetime.datetime.utcnow(), nullable=False)

ttl = Column(DateTime, default=(datetime.datetime.utcnow() + datetime.timedelta(days=10)), nullable=False)

type = Column(String(256), nullable=False, index=True)

message = Column(String(512))

stacktrace = Column(Text)

region = Column(String(32), nullable=True, index=True)

tech_id = Column(Integer, ForeignKey("technology.id", ondelete="CASCADE"), index=True)

item_id = Column(Integer, ForeignKey("item.id", ondelete="CASCADE"), index=True)

account_id = Column(Integer, ForeignKey("account.id", ondelete="CASCADE"), index=True)

class ItemAuditScore(db.Model):

"""

This table maps scores to audit methods, allowing for configurable scores.

"""

__tablename__ = "itemauditscores"

id = Column(Integer, primary_key=True)

technology = Column(String(128), nullable=False)

method = Column(String(256), nullable=False)

score = Column(Integer, nullable=False)

disabled = Column(Boolean, default=False)

account_pattern_scores = relationship("AccountPatternAuditScore", backref="itemauditscores",

cascade="all, delete, delete-orphan")

__table_args__ = (UniqueConstraint('technology', 'method'), )

def add_or_update_pattern_score(self, account_type, field, pattern, score):

db_pattern_score = self.get_account_pattern_audit_score(account_type, field, pattern)

if db_pattern_score is not None:

db_pattern_score.score = score

else:

db_pattern_score = AccountPatternAuditScore(account_type=account_type,

account_field=field,

account_pattern=pattern,

score=score)

self.account_pattern_scores.append(db_pattern_score)

def get_account_pattern_audit_score(self, account_type, field, pattern):

for db_pattern_score in self.account_pattern_scores:

if db_pattern_score.account_field == field and \

db_pattern_score.account_pattern == pattern and db_pattern_score.account_type == account_type:

return db_pattern_score

class AccountPatternAuditScore(db.Model):

"""

This table allows the value(s) of an account field to be mapped to scores, allowing for

configurable scores by account.

"""

__tablename__ = "accountpatternauditscore"

id = Column(Integer, primary_key=True)

account_type = Column(String(80), nullable=False)

account_field = Column(String(128), nullable=False)

account_pattern = Column(String(128), nullable=False)

score = Column(Integer, nullable=False)

itemauditscores_id = Column(Integer, ForeignKey("itemauditscores.id"), nullable=False)

class WatcherConfig(db.Model):

"""

Defines watcher configurations for interval and active

"""

__tablename__ = "watcher_config"

id = Column(Integer, primary_key=True)

index = Column(db.String(80), unique=True)

interval = Column(Integer, nullable=False)

active = Column(Boolean(), nullable=False)

class Datastore(object):

def __init__(self, debug=False):

pass

def durable_hash(self, item, ephemeral_paths):

"""

Remove all ephemeral paths from the item and return the hash of the new structure.

:param item: dictionary, representing an item tracked in security_monkey

:return: hash of the sorted json dump of the item with all ephemeral paths removed.

"""

durable_item = deepcopy(item)

for path in ephemeral_paths:

try:

dpath.util.delete(durable_item, path, separator='$')

except PathNotFound:

pass

return self.hash_config(durable_item)

def hash_config(self, config):

"""

Finds the hash for a config.

Calls sub_dict, which is a recursive method which sorts lists which may be buried in the structure.

Dumps the config to json with sort_keys set.

Grabs an MD5 hash.

:param config: dict describing item

:return: 32 character string (MD5 Hash)

"""

item = sub_dict(config)

item_str = json.dumps(item, sort_keys=True)

item_hash = hashlib.md5(item_str) # nosec: not used for security

return item_hash.hexdigest()

def get_all_ctype_filtered(self, tech=None, account=None, region=None, name=None, include_inactive=False):

"""

Returns a list of Items joined with their most recent ItemRevision,

potentially filtered by the criteria above.

"""

item_map = {}

query = Item.query

if tech:

query = query.join((Technology, Item.tech_id == Technology.id)).filter(Technology.name == tech)

if account:

query = query.join((Account, Item.account_id == Account.id)).filter(Account.name == account)

filter_by = {'region': region, 'name': name}

for k, v in filter_by.items():

if not v:

del filter_by[k]

query = query.filter_by(**filter_by)

attempt = 1

while True:

try:

items = query.all()

break

except Exception as e:

app.logger.warn("Database Exception in Datastore::get_all_ctype_filtered. "

"Sleeping for a few seconds. Attempt {}.".format(attempt))

app.logger.debug("Exception: {}".format(e))

import time

time.sleep(5)

attempt = attempt + 1

if attempt > 5:

raise Exception("Too many retries for database connections.")

for item in items:

if not item.latest_revision_id:

app.logger.debug("There are no itemrevisions for this item: {}".format(item.id))

continue

most_recent = ItemRevision.query.get(item.latest_revision_id)

if not most_recent.active and not include_inactive:

continue

item_map[item] = most_recent

return item_map

def get(self, ctype, region, account, name):

"""

Returns a list of all revisions for the given item.

"""

item = self._get_item(ctype, region, account, name)

return item.revisions

def get_audit_issues(self, ctype, region, account, name):

"""

Returns a list of ItemAudit objects associated with a given Item.

"""

item = self._get_item(ctype, region, account, name)

return item.issues

def store(self, ctype, region, account, name, active_flag, config, arn=None, new_issues=None, ephemeral=False,

source_watcher=None):

"""

Saves an itemrevision. Create the item if it does not already exist.

"""

new_issues = new_issues if new_issues else []

item = self._get_item(ctype, region, account, name)

if arn:

duplicate_arns = Item.query.filter(Item.arn == arn).all()

for duplicate_item in duplicate_arns:

if duplicate_item.id != item.id:

duplicate_item.arn = None

app.logger.info("Moving ARN {arn} from {duplicate} to {item}".format(

arn=arn,

duplicate=duplicate_item.name,

item=item.name

))

db.session.add(duplicate_item)

db.session.commit()

if arn:

item.arn = arn

item.latest_revision_complete_hash = self.hash_config(config)

if source_watcher and source_watcher.honor_ephemerals:

ephemeral_paths = source_watcher.ephemeral_paths

else:

ephemeral_paths = []

item.latest_revision_durable_hash = self.durable_hash(

config,

ephemeral_paths)

if ephemeral:

item_revision = item.revisions.first()

item_revision.config = config

item_revision.date_last_ephemeral_change = datetime.datetime.utcnow()

else:

item_revision = ItemRevision(active=active_flag, config=config)

item.revisions.append(item_revision)

# Add new issues

for new_issue in new_issues:

nk = "{}/{}".format(new_issue.issue, new_issue.notes)

if nk not in ["{}/{}".format(old_issue.issue, old_issue.notes) for old_issue in item.issues]:

item.issues.append(new_issue)

db.session.add(new_issue)

# Delete old issues

for old_issue in item.issues:

ok = "{}/{}".format(old_issue.issue, old_issue.notes)

if ok not in ["{}/{}".format(new_issue.issue, new_issue.notes) for new_issue in new_issues]:

db.session.delete(old_issue)

db.session.add(item)

db.session.add(item_revision)

db.session.commit()

self._set_latest_revision(item)

return item

def _set_latest_revision(self, item):

latest_revision = item.revisions.first()

item.latest_revision_id = latest_revision.id

db.session.add(item)

db.session.commit()

#db.session.close()

def _delete_duplicate_item(self, items):

"""

Given a list of identical items (account, name, region, technology), delete the duplicate, and return

the most current item back out.

:param items:

:return:

"""

last_item = items.pop()

for i in items:

if last_item.latest_revision_id > i.latest_revision_id:

db.session.delete(i)

else:

db.session.delete(last_item)

last_item = i

db.session.commit()

return last_item

def _get_item(self, technology, region, account, name):

"""

Returns the first item with matching parameters.

Creates item if it doesn't exist.

"""

account_result = Account.query.filter(Account.name == account).first()

if not account_result:

raise Exception("Account with name [{}] not found.".format(account))

item = Item.query.join((Technology, Item.tech_id == Technology.id)) \

.join((Account, Item.account_id == Account.id)) \

.filter(Technology.name == technology) \

.filter(Account.name == account) \

.filter(Item.region == region) \

.filter(Item.name == name) \

.all()

if len(item) > 1:

app.logger.error("[?] Duplicate items have been detected: {a}/{t}/{r}/{n}. Removing duplicate...".format(

a=account, t=technology, r=region, n=name))

item = self._delete_duplicate_item(item)

app.logger.info("[-] Duplicate items removed: {a}/{t}/{r}/{n}...".format(a=account, t=technology,

r=region, n=name))

elif len(item) == 1:

item = item[0]

else:

item = None

if not item:

technology_result = Technology.query.filter(Technology.name == technology).first()

if not technology_result:

technology_result = Technology(name=technology)

db.session.add(technology_result)

db.session.commit()

app.logger.info("Creating a new Technology: {} - ID: {}"

.format(technology, technology_result.id))

item = Item(tech_id=technology_result.id, region=region, account_id=account_result.id, name=name)

db.session.add(item)

db.session.commit()

db.session.refresh(item)

return item

def store_exception(source, location, exception, ttl=None):

"""

Method to store exceptions in the database.

:param source:

:param location:

:param exception:

:param ttl:

:return:

"""

try:

app.logger.debug("Logging exception from {} with location: {} to the database.".format(source, location))

message = str(exception)[:512]

exception_entry = ExceptionLogs(source=source, ttl=ttl, type=type(exception).__name__,

message=message, stacktrace=traceback.format_exc())

if location:

if len(location) == 4:

item = Item.query.filter(Item.name == location[3]).first()

if item:

exception_entry.item_id = item.id

if len(location) >= 3:

exception_entry.region = location[2]

if len(location) >= 2:

account = Account.query.filter(Account.name == location[1]).one()

if account:

exception_entry.account_id = account.id

if len(location) >= 1:

technology = Technology.query.filter(Technology.name == location[0]).first()

if not technology:

technology = Technology(name=location[0])

db.session.add(technology)

db.session.commit()

db.session.refresh(technology)

app.logger.info("Creating a new Technology: {} - ID: {}".format(technology.name, technology.id))

exception_entry.tech_id = technology.id

db.session.add(exception_entry)

db.session.commit()

app.logger.debug("Completed logging exception to database.")

except Exception as e:

app.logger.error("Encountered exception while logging exception to database:")

app.logger.exception(e)

def clear_old_exceptions():

exc_list = ExceptionLogs.query.filter(ExceptionLogs.ttl <= datetime.datetime.utcnow()).all()

for exc in exc_list:

db.session.delete(exc)

db.session.commit()