Eramba bulk reviews with SQL queries

We utilise the open GRC source tool, Eramba; some instances of which are the community edition which does not have an API interface. In some scenarios it is desirable make bulk updates/completions of reviews (and potentially update status of audits).

Doing this is slightly less trivial than expected… steps are:

• Generate a list of review ids and foreign keys (maps review objects to model object [Asset, SecurityPolicy, ThirdPartyRisk, Risk]
• Update the relevant review objects with completion date, comments, completed status
• Create new reviews entries for next cycle
• Update the model object [Asset, SecurityPolicy, ThirdPartyRisk, Risk] to reference the new, next review date
• Update the object status mapping for the updated reviews (expired and current statuses in this case)
• Validate your updated via the web interface
  • No clearing of cache of waiting for jobs is required

-- Generate a list of review ids and foreign keys (in this case the foreign keys are for the associated Assets being reviewed):

SELECT CONCAT_WS(',',Model,id,foreign_key) from reviews where planned_date = '2022-04-19' and model = 'Asset';

-- Update the relevant review objects as desired:

update reviews set actual_date = '2022-04-20',user_id = 2, description = 'No changes to store [components, customer, deployment etc, all same] added to ISMF Agenda ', completed = 1, modified = now(), edited = now()  where id in (select id from reviews where planned_date = '2022-04-19' and model = 'Asset');

-- Create new reviews (this is usually done by the app when completing the review via the web interface, review objects need a Model [Asset, SecurityPolicy, ThirdPartyRisk, Risk] (other models have audits):

INSERT INTO 
	reviews(model, foreign_key, planned_date, completed, created, modified, deleted)
VALUES
('Asset',115,'2023-04-19',0,now(),now(),0),
('Asset',116,'2023-04-19',0,now(),now(),0),
...;

-- Update the Asset object to reference the new, next review date (get asset ID from query used to get review id + foreign_key)
update asset set review = '2023-04-19', expired_reviews = 0 where id in (select foreign_key from reviews where planned_date = '2022-04-19' and model =  'Asset');

-- Eramba has object statuses, the list of available statuses is defined in the object_status_statuses table; the mapping of objects to statuses is in the table: object_status_object_statuses
-- Note there will be better, safer queries to do this..:

-- Check the results select query so we know what we are updating
select * from object_status_object_statuses where foreign_key in (select id from reviews where planned_date = '2022-04-19' and model =  'Asset') and model = 'AssetReview' and name = 'expired';

-- Update the object status mappings for relevant items, in this case there are two statuses that need to be updated, the expired status (now should be 0_ and the current status (now should be 1)

update object_status_object_statuses set status = 0 where id in (select id from object_status_object_statuses 
where foreign_key in (select id from reviews where planned_date = '2022-04-19' and model =  'Asset') and model = 'AssetReview' and name = 'expired');

update object_status_object_statuses set status = 1 where id in (select id from object_status_object_statuses 
where foreign_key in (select id from reviews where planned_date = '2022-04-19' and model =  'Asset') and model = 'AssetReview' and name = 'current_review');