Simplify. Use auditing. It has all the information you need.
Jim
"David" <dt_146_at_yahoo.co.in> wrote in message
news:d5840ebd.0402210240.416ec33d_at_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 - 06:54:25 CST
