Dashboard 'Technology Issue Scores' and 'High Score Items' not loading

[Martin-Who]

Does anybody have any ideas why I get this error in the dashboard view?
Everything else is working perfectly.

main.dart.js:40376 GET https://URL/api/1/items?accounts=PD-AWS-Live&accounttypes=&active=true&count=1000000000&names=&page=1&regions=&summary=true&technologies= 500 (INTERNAL SERVER ERROR)
main.dart.js:43764 HTTP 500: {"message": "Internal Server Error"}
STACKTRACE:
null

[mikegrima]

@Martin-Who You will need to provide the logs from the API server for us to debug.

[barkayw]

I installed the SM as production, two UI servers under ALB, one scheduler server, and two workers servers.
I've got the same problem
"Dashboard 'Technology Issue Scores' and 'High Score Items' not loading."

where I can find the API logs ?

[macedogm]

Hi. I am also facing the same problem. Every menu inside the SM UI works great, but the 'Technology Issue Scores' and 'High Score Items' dashboards does not.

When the UI requests the API:

/api/1/items?accounts=REDACTED&accounttypes=&active=true&count=1000000000&names=&page=1&regions=&summary=true&technologies=

It receives an HTTP 500 error with the following message:

{"message": "Internal Server Error"}

The error that is throw in the logs is:

2018-11-22 09:19:58,058 ERROR: Exception on /api/1/items [GET] [in /usr/local/src/security_monkey/venv/lib/python2.7/site-packages/Flask-0.12.4-py2.7.egg/flask/app.py:1560] Traceback (most recent call last): File "/usr/local/src/security_monkey/venv/lib/python2.7/site-packages/Flask-0.12.4-py2.7.egg/flask/app.py", line 1612, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/src/security_monkey/venv/lib/python2.7/site-packages/Flask-0.12.4-py2.7.egg/flask/app.py", line 1598, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/usr/local/src/security_monkey/venv/lib/python2.7/site-packages/Flask_RESTful-0.3.6-py2.7.egg/flask_restful/__init__.py", line 480, in wrapper resp = resource(*args, **kwargs) File "/usr/local/src/security_monkey/venv/lib/python2.7/site-packages/Flask-0.12.4-py2.7.egg/flask/views.py", line 84, in view return self.dispatch_request(*args, **kwargs) File "/usr/local/src/security_monkey/venv/lib/python2.7/site-packages/Flask_RESTful-0.3.6-py2.7.egg/flask_restful/__init__.py", line 595, in dispatch_request resp = meth(*args, **kwargs) File "/usr/local/src/security_monkey/security_monkey/views/item.py", line 270, in get items = query.paginate(page, count) File "/usr/local/src/security_monkey/venv/lib/python2.7/site-packages/Flask_SQLAlchemy-1.0-py2.7.egg/flask_sqlalchemy/__init__.py", line 405, in paginate items = self.limit(per_page).offset((page - 1) * per_page).all() File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2773, in all return list(self) File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2925, in __iter__ return self._execute_and_instances(context) File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2948, in _execute_and_instances result = conn.execute(querycontext.statement, self._params) File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 948, in execute return meth(self, multiparams, params) File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement compiled_sql, distilled_params File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1200, in _execute_context context) File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info File "build/bdist.linux-x86_64/egg/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1193, in _execute_context context) File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 507, in do_execute cursor.execute(statement, parameters) ProgrammingError: (psycopg2.ProgrammingError) table name "account" specified more than once [SQL: 'SELECT anon_1.item_id AS anon_1_item_id, anon_1.item_region AS anon_1_item_region, anon_1.item_name AS anon_1_item_name, anon_1.item_arn AS anon_1_item_arn, anon_1.item_latest_revision_complete_hash AS anon_1_item_latest_revision_complete_hash, anon_1.item_latest_revision_durable_hash AS anon_1_item_latest_revision_durable_hash, anon_1.item_tech_id AS anon_1_item_tech_id, anon_1.item_account_id AS anon_1_item_account_id, anon_1.item_latest_revision_id AS anon_1_item_latest_revision_id, anon_1.itemrevision_date_created AS anon_1_itemrevision_date_created, itemaudit_1.id AS itemaudit_1_id, itemaudit_1.score AS itemaudit_1_score, itemaudit_1.issue AS itemaudit_1_issue, itemaudit_1.notes AS itemaudit_1_notes, itemaudit_1.action_instructions AS itemaudit_1_action_instructions, itemaudit_1.background_info AS itemaudit_1_background_info, itemaudit_1.origin AS itemaudit_1_origin, itemaudit_1.origin_summary AS itemaudit_1_origin_summary, itemaudit_1.class_uuid AS itemaudit_1_class_uuid, itemaudit_1.fixed AS itemaudit_1_fixed, itemaudit_1.justified AS itemaudit_1_justified, itemaudit_1.justified_user_id AS itemaudit_1_justified_user_id, itemaudit_1.justification AS itemaudit_1_justification, itemaudit_1.justified_date AS itemaudit_1_justified_date, itemaudit_1.item_id AS itemaudit_1_item_id, itemaudit_1.auditor_setting_id AS itemaudit_1_auditor_setting_id, technology_1.id AS technology_1_id, technology_1.name AS technology_1_name, account_1.id AS account_1_id, account_1.active AS account_1_active, account_1.third_party AS account_1_third_party, account_1.name AS account_1_name, account_1.notes AS account_1_notes, account_1.identifier AS account_1_identifier, account_1.account_type_id AS account_1_account_type_id \nFROM (SELECT item.id AS item_id, item.region AS item_region, item.name AS item_name, item.arn AS item_arn, item.latest_revision_complete_hash AS item_latest_revision_complete_hash, item.latest_revision_durable_hash AS item_latest_revision_durable_hash, item.tech_id AS item_tech_id, item.account_id AS item_account_id, item.latest_revision_id AS item_latest_revision_id, itemrevision.date_created AS itemrevision_date_created \nFROM item JOIN itemrevision ON item.latest_revision_id = itemrevision.id JOIN account ON account.id = item.account_id JOIN account ON account.id = item.account_id \nWHERE account.name IN (%(name_1)s) AND itemrevision.active = true AND account.active = true ORDER BY itemrevision.date_created DESC \n LIMIT %(param_1)s OFFSET %(param_2)s) AS anon_1 LEFT OUTER JOIN (issue_item_association AS issue_item_association_1 JOIN itemaudit AS itemaudit_1 ON itemaudit_1.id = issue_item_association_1.super_issue_id) ON anon_1.item_id = issue_item_association_1.sub_item_id LEFT OUTER JOIN technology AS technology_1 ON technology_1.id = anon_1.item_tech_id LEFT OUTER JOIN account AS account_1 ON account_1.id = anon_1.item_account_id ORDER BY anon_1.itemrevision_date_created DESC'] [parameters: {'name_1': '<REDACTED>', 'param_1': 1000000000, 'param_2': 0}] (Background on this error at: http://sqlalche.me/e/f405)

Apparently this is the main error:

ProgrammingError: (psycopg2.ProgrammingError) table name "account" specified more than once (...)

[macedogm]

@mikegrima

Hi again.
I believe that I might have found the issue. The stack trace complains about an error in:

"/usr/local/src/security_monkey/security_monkey/views/item.py", line 270, in get items = query.paginate(page, count) File

Looking for the query it is possible to identify that this join is made three times in the query (lines 228, 232 and 251):

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

I have commented out lines 232 and 251 and kept line 228, then restarted SM and the two dashboards loaded normally.
One note: the query appears to be big and runs slowly, at least using a local Postgres only for testing.

Could someone, please, review this to see if it will not break any other functionality in SM?

[barkayw]

Thanks, works
If I find some issue, I'll let you know.

[mikegrima]

Please submit a PR with the fix, and I will merge it in.

[mikegrima]

Merged in #1153 . Please test that things are working now.

[macedogm]

Will test and let you know. Thanks for your time.

[macedogm]

Hi @mikegrima, just to let you know, it is working for me.