Logon Trigger for getting info of IP Address,etc into a table

From: David <dt_146_at_yahoo.co.in>
Date: 21 Feb 2004 02:40:56 -0800
Message-ID: <d5840ebd.0402210240.416ec33d@posting.google.com>

Hi everyone,

With regards to getting the client's information like IP Address,Host Name etc. ,
I created the following table "CLIENT_INFO":

Sql> connect system/manager
sql>CREATE TABLE CLIENT_INFO(client_id varchar2(20),client_name VARCHAR2(30),
work_station varchar2(40),host_name varchar2(30),database_server varchar2(40),
db_domain varchar2(40),ip_address varchar2(30)
,network_protocol varchar2(60));

sql>grant select,insert on client_info to public; sql >create public synonym client_info for client_info;

Then I create a LOGON trigger in order to store the required Information of all the clients into my table "CLIENT_INFO": LOGON Trigger:

SQl> Connect system/manager
Sql>create or replace trigger logon_trigger after logon ON database
begin

insert into client_info(client_id
,client_name,work_station,host_name,database_server,
db_domain,ip_address,network_protocol)
select SYS_CONTEXT('USERENV','CURRENT_USERID'),SYS_CONTEXT('USERENV','CURRENT_USER'),

SYS_CONTEXT('USERENV','OS_USER'),SYS_CONTEXT('USERENV','HOST'),
SYS_CONTEXT('USERENV','DB_NAME'),SYS_CONTEXT('USERENV','DB_DOMAIN'),
SYS_CONTEXT('USERENV','IP_ADDRESS'),SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

FROM DUAL;
COMMIT;
end if;

END;
/

I created the above Logon trigger,so every session when logon to database, insert its own environment info. into the client_info table.

But when I run the query >select * from client_info;

It displays the information for only two users(system,sys).However there are several other sessions connected to the database like scott,test etc.
And also there is null value for IP_Address column,why?

 for example:

CLIENT_ID CLIENT_NAME WORK_STATION HOST_NAME DATABASE_SERVER DB_DOMAIN IP_ADDRESS

5           SYSTEM      SAMAD\Administrator   RUSH\SAMAD     TEST
0           SYS         SAMAD\Administrator   RUSH\SAMAD     TEST
5           SYSTEM      SAMAD\Administrator   RUSH\SAMAD     TEST
0           SYS         SAMAD\Administrator   RUSH\SAMAD     TEST
5           SYSTEM      SAMAD\Administrator   RUSH\SAMAD     TEST
0           SYS         SAMAD\Administrator   RUSH\SAMAD     TEST

6 rows selected.

How should I get the required info from my historical table "CLIENT_INFO" for each and every user.

I am looking forward to your suggestion.

Thanks

David Received on Sat Feb 21 2004 - 04:40:56 CST