Portal

You may want to log user connections to your Semarchy application. The built-in audit trail does not keep track of user connections, but only of user actions (creating / updating records, starting a workflow, ...).
This article explains how to use the Semarchy platform mechanisms to log this information into a table (a regular table or a model entity).

The proposed method relies on model variables to persist this information into the database.
Model variables are typically used to implement security rules or to retrieve information from database or LDAP, when this information does not need to be duplicated within the hub. They are populated at login time.

Since a model variable can be populated by a select statement, you can use this possibility to execute some code, if the select statement uses a PL-SQL function (or its equivalent in any other RDBMS). This example uses an oracle PL-SQL function.

Step-by-step guide

If you want to persist this information into a Semarchy entity rather than in a regular table, to allow application administrators to keep track of user connections, you have to create this entity as follows (if you don't, jump to step 5)

1. Create a basic entity UserConnection(UserConnectionID (sequence), ConnectedUser, ConnectionDate)

2. Create the application components automatically (no edition needed, browsing actions only)

3. Create a job LOAD_USER_CONNECTIONS  that has a single task for the UserConnection entity

4. Create the PL-SQL function in the Hub schema as follows:

create or replace function usp_on_login_trigger(p_user_name varchar2)
 return varchar2 is
 pragma autonomous_transaction;
 currentLoadId number;
 ret number;
begin
 if p_user_name <> 'semadmin' then
 currentLoadId:=SEMARCHY_REPO.integration_load.get_new_loadid('YourDataLocationName', 0, 'Log', 'Log', 'semadmin');
 insert into SA_USER_CONNECTION(B_LOADID, USER_CONNECTION_ID, B_CLASSNAME, B_CREDATE, B_CREATOR, CONNECTED_USER, CONNECTION_DATE)
 values(currentLoadId, SEQ_USER_CONNECTION.NEXTVAL, 'UserConnection', sysdate, 'TRIGGER', p_user_name, sysdate);
 commit;
 ret:=SEMARCHY_REPO.integration_load.submit_load(currentLoadId, 'LOAD_USER_CONNECTIONS', 'semadmin') ;
 end if;
 return 'done';
end;
/

5. Create a new variable value provider that uses the JDBC connection of the data location you want to monitor. How to configure a Variable Value Provider

6. Create a CONNECTION_TRIGGER model variable in the model, that uses the newly created Datasource Variable Provider

Populate it with the following SQL statement

select usp_on_login_trigger(:V_USERNAME) from dual