Question

Aurena Logins

  • 28 October 2020
  • 4 replies
  • 71 views

Badge +1

Hi,

One of our customers is asking the following.  Is this possible in Aurena?

“Is there a view to look at and/or extract that shows IFS Aurena logins, date, and person in IFS?  Want to use it as a KPI for tracking account managers use of IFS Aurena CRM. “

Thanks,

 

Jo


4 replies

Badge +5

We created a logon trigger that inserts each log on  into a user table.   See below     then we created an view that matches that data up with dba_users and fnd_user      this gives us the info we need to see the login frequencies. 

 

 

CREATE OR REPLACE TRIGGER logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into cb_user_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST', 30),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
COMMIT;
END;
 

Allan 

Badge +1

Thank you for this...much appreciated.  Can you elaborate a little more on how to do this?  How do you create the table to insert this info?  Am I understanding this correctly?

Thanks,

Jo

Badge +5

We did create a table it is called cb_user_log     (CB) is our normal prefix to identify our items.    It has the fields User, Session id, Host, Logon_day,  and Logon_time.       We then activated the trigger to capture this info at login time.    We then created an IAL that connects this data with also the views (Oracle_account and fnd_user) so that we can get further information.   We then have quick reports in IFS that allow us to see the information. 

Allan

Badge +5

here is the table definition for what we created

-- Create table
create table CB_USER_LOG
(
  user_id    VARCHAR2(60),
  session_id NUMBER(20),
  host       VARCHAR2(60),
  logon_day  DATE,
  logon_time VARCHAR2(40)
)
tablespace IFSAPP_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on CB_USER_LOG to IFSINFO with grant option;
grant select on CB_USER_LOG to IFS_READ_ONLY;

 

 

Remember that the CB_USER_Log is what  We called the file in our system.

 

Also that grants to IFS Read Only is a security role on our system    You would need your own query role to assign it to.  

Allan

Reply