WITH question as (select * from (
select question[1] as question, data[1] as answer_ref, data[2] as answer_text from rule r
    join risk_action_rule ar on r.id = ar.rule_id
               join risk_action a on a.id = ar.risk_action_id
             join condition_rule cr on r.id = cr.rule_id
             join condition c on c.id = cr.condition_id,
              string_to_array(ar.value,'_::_') asdata,
       string_to_array(cr.value,'_::_') as question
               where c.ref = 'CONDITION_COMPONENT_QUESTION_GROUP_EXISTS'
                 and a.ref = 'INSERT_COMPONENT_QUESTION') as relationship
                  join (
    select distinctdata[1] as ref, data[3] astextfrom risk_action_rule rar
                                                             join risk_action ra on rar.risk_action_id = ra.id, string_to_array(rar.value,'_::_') asdata
    where ra.ref = 'INSERT_COMPONENT_QUESTION_GROUP') as question on relationship.question = question.ref),
bu_nested AS ( select p.name as project, array_remove(array_agg(bu.name), NULL) as bus from project p
          left join projectugroup pu on pu.project_id = p.id
          left join ugroup bu on pu.ugroup_id = bu.id
          where p.type = 'STANDARD'
          andnotexists (select1from project_version where project_id = p.id)
          group by p.name)
select q.answer_text as answer_text, q.text as question_text, bu.bus as business_unit , c.name as component, p.name as project
from question as q
join component_wizard_question as cwq on q.answer_ref = cwq.ref
join component as c on cwq.component_id = c.id
join project as p on c.project_id = p.id
  join bu_nested bu on bu.project  = p.name

WITH last_diagram_change as (
  select p.id as project_id, ar.content, max(timestamp) as last_diagram_modified_date
  from artifact as ar
  inner join project as p on p.id = ar.project_id
  where ar.show=true
  and p.type = 'STANDARD'
  group by ar.content, p.id
),
last_state_change as (select project_id, project_name, state, ws_name, max(date) as date_of_completion from (
  select pa.id as project_id,p.name as project_name, pa.workflow_state_id asstate,
  ws.name as ws_name,
  rev.rev_timestamp asdate,
  LAG (pa.workflow_state_id, 1)
  OVER (
      PARTITIONBY pa.id
      ORDER BY rev.rev_timestamp
      ) previous_state
  from project_audit pa
  join revinfo as rev on pa.rev_id = rev.rev
  join workflow_state ws on ws.id = pa.workflow_state_id
  join project p on p.id = pa.id and p.workflow_state_id = pa.workflow_state_id
  where  p.type = 'STANDARD'
) as historic
  wherestateisdistinctfrom previous_state
  group by project_id,project_name, state, previous_state, ws_name),
total_threats_project as (
  select
  count(t.id) as total_threats,
  p.id as project
  from threat t
  join component u on t.component_id = u.id
  join project p on u.project_id = p.id
  wherenotexists (select1from project_version where project_id = p.id)
  and p.type = 'STANDARD'
  group by project
),
total_threats_mitigated_project as (
  select
  count(t.id) as total_threats_mitigated,
  max(tce.timestamp) as last_remediation_date,
  p.id as project
  from threat t
  join component u on t.component_id = u.id
  join project p on u.project_id = p.id
  join threat_change_event tce on t.id = tce.threat_id and tce.state='MITIGATE'and tce.project_id = p.id
  wherenotexists (select1from project_version where project_id = p.id)
  and p.type = 'STANDARD'
  and t.state='MITIGATE'
  group by project
)
select
lsc.project_name as project_name,
ws_name as workflow_state,
to_timestamp(date_of_completion/ 1000)  as date_of_completion,
last_diagram_modified_date as last_diagram_modified_date,
content as content,
total_threats,
total_threats_mitigated
,last_remediation_date
from last_state_change lsc
left join total_threats_project ttp on ttp.project = lsc.project_id
left join total_threats_mitigated_project ttmp on ttmp.project = ttp.project
left join last_diagram_change ldc on ldc.project_id = lsc.project_id

select
content as Diagram Created,
last_diagram_modified_date asLast Diagram Modified Date,
project_name as Project Name,
workflow_state as Work flow state,
date_of_completion asLastSecurity Performed Date,
CASE
  WHEN total_threats > 0
  THEN total_threats
    ELSE0
  ENDAS'Total threats',
CASE
  WHEN total_threats_mitigated > 0
  THEN total_threats_mitigated
    ELSE0
  ENDAS'Total threats mitigated',
last_remediation_date asLast Remediation Date
;
select *

Extracting your data

Under normal circumstances, then you would want to access the information generated by your query via the Analytics Dashboard, in which case you would create a widget/visulation and include that within your dashboard(s). But if you want to collect the data and re-use it within another tool, then you need to utilize the Analytics Engine API.

 https://<your.analytics.instance>.iriusrisk.com/analytics/docs/managementAPI.html

Enable API access

from your Account Settings; if you do not have access to this, then you need to speak to your administrator.

Both the Client ID and Client Secret will be provisioned as a UUID (obfuscated here), you will need these both to be able to create a session to be able to access the analytics module data.

Login

curl -i -d "client_id=<your client id>&client_secret=<your client secret>" -X POST <https://<your analytics instance>.iriusrisk.com/analytics/api/1.0/login

This will return your bearer token, which you can use within the session to access the analytics data, you do not need to re-use the client id or secret. The access token will change every time you login to the service, by default the session lasts 3600 seconds (1 hour). You should use the logout API call to terminate the session upon completion of your activities, this is good security practice.

{"access_token":"<your session access token>","token_type":"Bearer","expires_in":3600}

Identify your Query

Each query has many unique attributes with which to identify them, entityName, identifier. This information can be retrieved from the UI or via the API, using the bearer token (session access token from the login API call above).

curl -s -H "Authorization: Bearer <your session access token>" "https://<your analytics instance>.iriusrisk.com/analytics/api/1.0/queries"

this returns raw json, which is a list of the data sets (queries) that you as a user have access to. If you “pretty print the output”, each query is represented by the following content:

 Json Snippet of all Queries

In this example the entityName and the identifier representing the query, is:

Create our Data Set

In order to create the required data set, you need to identify the query that you need to run (from the above output), either the entityName:

curl -i -H "Authorization: Bearer <your session access token>" -G "https://<your analytics instance>.iriusrisk.com/analytics/api/1.0/datasets" --data-urlencode "entityName=dD - Deleted Projects"

or the identifier:

curl -s -H "Authorization: Bearer <your session access token>" "https://<your analytics instance>.iriusrisk.com/analytics/api/1.0/datasets?identifier=BNmR4z"

By default the output is json, you can force the format using either:

curl -i -H "Authorization: Bearer <your session access token>" -G "https://<your analytics instance>.iriusrisk.com/analytics/api/1.0/datasets?exportFormat=csv" --data-urlencode "entityName=dD - Deleted Projects"

or:

curl -s -H "Authorization: Bearer <your session access token>" "https://<your analytics instance>.iriusrisk.com/analytics/api/1.0/datasets?identifier=BNmR4z&exportFormat=csv"

where exportFormat can be one of csv or json.

returning the results of a query (identifying the deleted projects from the audit log).

Running the Query Deleted Projects (based on the audit_event table) from earlier results in the following output (json):

[
{"id":204902,"details":"rgarciap","event_type":"PROJECT_DELETED","project_ref":null,"username":"analyticsdemo","version_id":null,"timestamp":1661435499230},
{"id":204920,"details":"David Doughty","event_type":"PROJECT_DELETED","project_ref":null,"username":"ddoughty","version_id":null,"timestamp":1661503971332},
{"id":205129,"details":"rg - to delete","event_type":"PROJECT_DELETED","project_ref":null,"username":"analyticsdemo","version_id":null,"timestamp":1661838985511},
{"id":220329,"details":"CloudApp","event_type":"PROJECT_DELETED","project_ref":null,"username":"kchau","version_id":null,"timestamp":1662129175733}
]

(CSV):

id,details,event_type,project_ref,username,version_id,timestamp
204902,rgarciap,PROJECT_DELETED,,analyticsdemo,,"08/25/2022 13:51:39"
204920,David Doughty,PROJECT_DELETED,,ddoughty,,"08/26/2022 08:52:51"
205129,rg - to delete,PROJECT_DELETED,,analyticsdemo,,"08/30/2022 05:56:25"
220329,CloudApp,PROJECT_DELETED,,kchau,,"09/02/2022 14:32:55"