Update how unique sessions are tracked for reporting
Replacing #506 (closed), this issue will encompass the move for unique session tracking from the eventlog to a unique table held within the membership database. The previous solution used a heavily used table that creates a lot of lag to query effectively due to the sheer size of the table which also introduced a lot of complexity.
The new table should have the following fields in it to minimally track which organizations access the portal while still tracking the minimum amount of information required for the business case:
Column Name | Type | Nullable | Description |
---|---|---|---|
id | SERIAL | N | Unique ID for the entry that allows for easy identification of columns |
username | varchar(63) | N | The username associated with the access request. With this column we'll be able to capture how many users from an organization requested access for an additional potential data point, as well as provide data when there is no organization present. |
time | datetime | N | The time access was requested. Used to create slices of time for reporting. |
organization | int | Y | The organization ID detected at access time. As users will potentially move orgs, we need to track who they are associated with at time of request. |
We no longer need to track the host as there are multiple databases for us to reference, and there should only be data from the one environment per database.
When a report is requested, we will pull all records within a slice of time, defaulting to the last 6 months, and filter the data in post processing. We will want to aggregate the data to output in the same general format for now, as it will provide easy to consume value without overwhelming amounts of data.