Task events for scheduler

[jpbruinsslot]

Related issues:

• Show job duration on task lists #1161

Currently, we only have created_at and modified_at timestamp information for tasks. Status changes can only be derived from the status and the modified_at, any other mutations and events are lost.

It would be helpful to know how long a task was queued, and how long this task took when it was picked up and finished.

The most straightforward solutions seems to be adding more datetime columns for these state changes or adding an events table with a 1:m relation between a task and task events that then can be referenced from a task.

[underdarknl]

Future work on this could also include which runner-instance executed the job, and how many resources where consumed (cpu, ram, network-io), as those could be useful for the scheduler to rank on for future jobs on similar objects.

[jpbruinsslot]

During a discussion with Donny about the current implementation, we condensed the following considerations:

Considerations

• If we want, we can still opt to use columns with the runtime, queued, and duration fields instead of adding this to an events table, however this can limit the extension of using additional information like cpu, memory, network i/o load, unless adding additional database columns.
• Usage of postgres trigger, could be considered as a bit of obfuscation, and a bit of magic that is done in the 'background'. We probably want to us Python instead, on tasks updates from the api
• Additionally we probably want to move away from those triggers if the task runner creates events like cpu, memory load etc.
• Scalability, when considering high loads we can end up with a large events table, and perhaps partitioning should be considered, or compacting on date.
• The queries for duration, queued, and runtime should only be done when requested. It should not be done for the task list endpoint has been implemented
• The fields type, context, and events can superfluous, and could be combined into 1 field and perhaps with dot notation

@underdarknl thoughts?

[underdarknl]

• Adding columns would allow us to more easily add indices for events, however I'm not sure we have a good usecase for them. Having a single json blob with extensible fields is more likely to result in flexibility in consuming metrics as deliverable by the various plugin runners (eg, clouds have other resource counters, compared to local execution).
• Database triggers should not be used in OpenKAT I'd say, as it splits the business logic over two domains. Deterministic view logic (eg, combining tables, or calculating averages), is something that I would consider to be something you'd want to do in database land since it can be obviously named columns.
• the main usecase for these metrics is twofold:
  ** Show the user what resources a specific tool has consumed. This would cover all historical data, but is incidental and does not suffer from scalability issue as much.
  ** Calculate what a proposed Job is likely to consume based on 'recent similar jobs'. This means that we don't need all the data, just a most recent set. Maybe we can have a daily job that trims this materialized view into a two week or N items window to keep the math required for this calculation reasonable?
• If we introduce blob fields, lets stick to something postgres has querying tools for, like json/jsonb.

[jpbruinsslot]

• Adding columns would allow us to more easily add indices for events, however I'm not sure we have a good usecase for them. Having a single json blob with extensible fields is more likely to result in flexibility in consuming metrics as deliverable by the various plugin runners (eg, clouds have other resource counters, compared to local execution).
• Database triggers should not be used in OpenKAT I'd say, as it splits the business logic over two domains. Deterministic view logic (eg, combining tables, or calculating averages), is something that I would consider to be something you'd want to do in database land since it can be obviously named columns.
• the main usecase for these metrics is twofold:
  ** Show the user what resources a specific tool has consumed. This would cover all historical data, but is incidental and does not suffer from scalability issue as much.
  ** Calculate what a proposed Job is likely to consume based on 'recent similar jobs'. This means that we don't need all the data, just a most recent set. Maybe we can have a daily job that trims this materialized view into a two week or N items window to keep the math required for this calculation reasonable?
• If we introduce blob fields, lets stick to something postgres has querying tools for, like json/jsonb.

Awesome, thanks for the feedback! In regards to indices, we still have that possibility to add indices to specific fields in the jsonb column if we wanted to, which is really great.

[underdarknl]

If we can store the metrics in the boefje meta we might not even need the 'archived' storage, since for most lookups on historical jobs we would already load the boefje meta from bytes anyway. This would simplify things to hold just the data the scheduler needs for its own priority calculations.
Depending on how much data we'd want for bayesian like predictions this would be less useful route though, as reading lots of meta files from bytes is much more expensive than using a un-indexed database table.

[stephanie0x00]

Checklist for QA:

• I have checked out this branch, and successfully ran a fresh make reset.
• I confirmed that there are no unintended functional regressions in this branch:
  • I have managed to pass the onboarding flow
  • Objects and Findings are created properly
  • Tasks are created and completed properly
• I confirmed that the PR's advertised feature or hotfix works as intended.

What works:

From a UI point of view everything still works as it's supposed to be. The onboarding flow works, additional boefjes are scheduled as intended and they complete.

What doesn't work:

n/a.

Bug or feature?:

n/a

[jpbruinsslot]

TLDR

Potential solutions:

• Creation of event table with a 1:m relation between task and events that track status change events
  • @dekkers will potentially create a lot of records and might not be scalable with the expected high throughput
  • @jpbruinsslot perhaps other stream processing solutions will be better suited for this
• Use available created_at, modfied_at to calculate the time from creation to completion
  • Does not necessarily communicate the running time of the task runner
• Add duration field to a Task as a 32-bit integer @dekkers
  • This only tracks the running time of a task runner and does not expose information on the duration of other statuses, unless additional duration fields are added for individual statuses and then we need to consider if we want to have those status durations in the task table or to normalize in order to not clutter the task table. @jpbruinsslot
  • Rather see timestamps instead of durations @Donnype: this allows for sorting on start_at, easily calculate the "total lifetime" of a task (queued -> done) and queuing time (queued -> start_at),

Other discussions:

• CPU/mem usage metrics seem specific towards a boefje runner and might not necessarily relate to other task runners. Additionally, we're not considering other metrics like disk usage and network usage which might not be relevant to other task runners. Considering the agnostic nature of the scheduler we then we need to evaluate whether to normalize those metrics fields as well. @jpbruinsslot
  • We concluded that this needs to be discussed in another ticket since it also begs questions about ux: how is this going to be used, queried and visualized, and if other tools that track these metrics are better suited than to integrate this into the scheduler. i.e. cluster metrics about individual tasks runners in containers

[jpbruinsslot]

@underdarknl fyi see the comment above this one for the latest updates on this discussion