Skip to content

User Audit

Welcome, developers to User Audit system Techincal documentation, where we outline the structured process for auditing user activities.

User Audit structure:

ID: Primary Key auto-generated. Email Address: Email of a user. User_Type: (if needed) Audit_Type: Total 3 Types: UserNoLongerInPlatform, UserNotEnabledInAnyPlatform, UserNotLoggedInPlatform. Action: values: NONE, DISABLE_IN_COGNITO, ENABLE_IN_COGNITO, DELETE_FROM_COGNITO, DISABLE_IN_DB, DELETE_FROM_DB, DELETE_FROM_COGNITO_AND_DB, REMINDER_SENT, SEND_REMINDER. Initially, for UserNoLongerInPlatform and UserNotEnabledInAnyPlatform, it's set to NONE, otherwise, it's set to SEND_REMINDER. AWS_Status: Integer with values: 0 (disabled), 1 (enabled), or null (not identified/not found). DB_Status: Integer field with values: 0 (disabled), 1 (enabled), or null (not found). Last_Login: Date/time when the user last logged in to the system. Null in case of UserNoLongerInPlatform. Default Platform: The default platform is set to CSC. Role Definition: Platform roles within CBP are exclusively determined by technical domain names. User Identification: User identification utilizes either email addresses or AWS keys, serving as single sign-on keys. Platform: User platform, default is 'CSC'. If null, default to 'CSC'. Created_Date: Date/time when a record was created. Updated_Date: Date/ time when a record was updated. Initially, both created and updated time are the same. Username: used by Cognito. Role: User role in the system. Null in case of UserNoLongerInPlatform. CBP: CBP in the system. Null in case of UserNoLongerInPlatform.

CROSS SSO DATA Checking

Endpoint: /get-user-details Details: this endpoint will accept list of emails and then return data based on this query (Refer to Query 1) Cross-platform configuration is required to get data from other platforms like if scheduler running in US the this cofiguration (sso.cross_platform_user_details) must hold uk sso url and vice versa.

UserNoLongerInPlatform

Preparation: Retrieve AWS data and remove email mentioned in the unsubscribed list. Get users' of all configured platforms and remove email addresses mentioned in the unsubscribed list. Retrieve users' from other platforms using the provided endpoint.

Processing:

Remove all users from the DB user list where the email address is null or empty. Iterate through the AWS user list: Get the email from the AWS user list. Check if the email exists in the list of users from other platforms. If not found in other platforms and the email does not exist in the DataBase, and the email is not already captured in the user audit: Create a record in the audit with the following details: Email:from the AWS list Username: from the AWS list Audit Type: USER_NOT_IN_PLATFORM Action: None AWS Status: User's current status in AWS DB Status: Null Platform: If mentioned in AWS, then use it; otherwise, default to 'CSC' Remaining fields will stay null or default value as per data type. Send email notification if new record is saved in the user audit. This process ensures that users who are no longer in the platform are identified and recorded in the user audit.

UserNotEnabledInAnyPlatform

Preparation: Retrieve AWS data and remove email mentioned in the unsubscribed list (List1). Get users' data of all platforms, remove email mentioned in the unsubscribed list, and pick only disabled users (List2). Retrieve users' from other platforms using the provided endpoint (List3).

Processing

Iterate through the DB disabled user list: Check if the user exists in List1 (AWS) and if the user exists and is enabled in other platforms (List2). If the user is not active in any platform: Check if the email id is not already caught in the audit. Get the CBP using the provided query (Query 3). Create a record in the audit with the following details: Email: Email from List1 (AWS list) Username: Username from List1 (AWS list) Audit Type: USER_DISABLED_IN_PLATFORM Action: None AWS Status: User's current status in AWS DB Status: False Last Login: Last login from the DB Platforms: All platforms where the user is disabled (if more than one) The rest of the fields will remain null or default values depending on the data type. Send an email notification if a new record is saved in the user's audit.

UserNotLoggedInPlatform

Preparation: Get data of active users of all configured platforms and remove email addresses mentioned in the unsubscribed list. Remove data where the last login date is null or empty (List1).

Processing:

Iterate the DB disabled user list: Skip users wherestatus is "REMINDER SENT" in the user audit. For each user: Check if the user's last login is 6 months old and no entry is already captured. If so, create an entry with status "SEND_REMINDER" in the user audit. Perform audit will pick the entry and send a reminder, then change the status to "REMINDER_SENT". Check if the user's last login is 12 months old. If so, set the status to "DISABLE_IN_DB" in the user audit. The Perform Audit task will select this entry, update the DB status to Disabled, and delete the entry from the user's audit. The same record should reappear in the user audit when UserNotEnabledInPlatform is run.

Queries Documentation:

This query retrieves user details (platform_id, enabled, last_login, group_roll_id, reseller_id, emailaddress) based on provided email addresses from the user table.

SELECT platform_id, enabled, last_login, group_roll_id, reseller_id, emailaddress from user where emailaddress in (?1)

Get Users Data of all configured platforms .(emailaddress, enabled, last_login, fname, lname, platform_id, cust_det_id, last_update, group_roll_id) excluding null email addresses and filtering by platform_id.

select a.emailaddress, a.enabled, a.last_login, a.fname , a.lname, a.platform_id, a.cust_det_id, a.last_update, a.group_roll_id from aws_user_view a where a.emailaddress is not null and a.platform_id in(?1)

This query retrieves the CBP from XXX_reseller_profile_view based on a subquery filtering by email ids from the user table.

select reseller_id from XXX_reseller_profile_view where reseller_id = template_nr and template_nr in (select ur.auth_id from vuser_realm_view ur, user u where ur.type_value = 'r' and u.cust_det_id = ur.cust_det_id and u.emailaddress = ?1) limit 1