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');
