Topic
This article describes how to use DELETE queries to reduce DB size and how to obtain Console Summary Tab results using SQL queris
Instructions
IMPORTANT NOTE : Always perform a full DB backup before making any changes to the DB.
| Query Description | SQL Query |
| Delete tasks from tasks history to reduce database size (Only applicable from 21.4) Clear redis and restart unattended-ui-svc thereafter to update the console. | DECLARE @date date; SET @date = '<cutoff date in yyyy-mm-dd format>' DELETE FROM [history].[tasks_history] WHERE created_at < @date DELETE FROM [history].[tasks_summary] WHERE created_at < @date DELETE FROM [tasks].[tasks_task_data] WHERE task_allocation_time < @date DELETE FROM [queues].[queues_task_data] WHERE task_create_time < @date DELETE FROM LeoLogInHistory WHERE LogInTime < @date DELETE FROM LeoStatistics WHERE Time < @date DELETE FROM [history].[user_runtime_action_history] WHERE created_at < @date |
| Console Summary Tab result from DB on monthly basis - (Only applicable from 21.10) | SELECT A.[task_name] TaskName,A.[state] Summary,B.[Name] InitiatedBy,C.Title Wizard,D.[name] Robot,A.[start_time] StartedAt,A.[end_time] EndedAt ,A.[duration_sec] DurationInSecFROM [Kryon].[history].[tasks_summary] As A Left JOIN [Kryon].[triggers].[TriggerInstances] As B on A.producer_id=convert(varchar(70), b.id ) Left JOIN Kryon.dbo.LeoScripts As C on A.workflow_id=C.ScriptID Left JOIN Kryon.robots.robot_data As D on A.robot_id=D.id Profile WHERE A.[start_time] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)AND A.[start_time] < EOMONTH(DATEADD(MONTH,-1,GETDATE())) |
| Gather a total overview of all the tasks that should be done tomorrow/today/yesterday for example. It extracts all tasks that are being added to the queue and processed on it. Including ones which already ended and the ones that are due to be executed. The lifecycle of a task in the DB is visible in its status as per the DB and most often it is in the following sequence: 1. Created 2. Publishing 3. Published 4. Processed 5. Allocated 6. Started 7. Ended The last column 'ConsoleStatus' represents the console Status if the task has already finished, if it is not finished it will return a NULL value. | SELECT tasks_history.created_at AS 'Timestamp', tasks_history.task_id AS 'TaskID', tasks_history.task_name AS 'TaskName', tasks_history.task_state AS 'Status', tasks_history.producer_type AS 'TriggerType', tasks_history.event_details_json AS 'Details', tasks_summary.state AS 'ConsoleStatus' FROM history.tasks_history LEFT JOIN history.tasks_summary ON tasks_summary.task_id = tasks_history.task_id ORDER BY tasks_history.created_at desc; |