How to Query The Number of users logged in a server during a period of time

How to Query The Number of users logged in a server during a period of time
         
            I need to get the number of users connected to a server during a period of time.Note:its not the number of users connected at a time or during the time of query.        
BALU_R_SAsked:
Who is Participating?
 
WadhwaCommented:

Hi Balu RS,

Here are some disadvantage of auditing  and Advantage of Database triggers.

1. It causes a SYSTEM OVERHEAD that is why people do not use it frequently .
2. Since the AUDIT tables are in SYS USERS and SYSTEM tablespace , By adding the data and deleting it causes the fragementation in SYSTEM tablespace which is not desiable at all .
3. Only DBA can delete the data from AUDIT tables , if DBA wants he can give the privilges to the specific users to delete the data from AUDIT table but this is security risk and people avoid this.
4. You can also enable auditing at SYSTEM level , means audit record generate in the OS and if you have windows OS you can monitor this with EVENT VIEWER but the disadvantage is you have to keep track of the location where audit records are generating.
5. If you wants to create a reports if the audit data with the help of third party tool , it is not easy , DBA has to give grant to select data of these table from the other user or you have to allows to read you system table from third party tool.


The advantage of above trigger is as follows :-

1. The Table (log_info) in above case can be owned by any user. It is not necessary to be owned by SYSTEM/SYS.
2. As it can be owned by XYZ user and any tablespace other then SYSTEM, deletion, inserting in this table does not cause SYSTEM OVERHEAD and  fragmentation in the SYSTEM TABLESPACE.
3. You can keep history of a USER in this table without worrying about filling of SYSTEM TABLESPACE and whenever you need you can truncate/delete it.
4. You can easily export/import of this table as it is difficult for audit table. 5. Whenever you want to disable logging feature , you can easily disable/drop the trigger.
6. Logon trigger does not cause any SYSTEM OVERHEAD AND KEEPS INFORMATION in the SYSTEM TABLESPACE and hence it is fast , recommended and desirable.

Now decesion is your which way you want to go.

Goodluck
Sam..
0
 
UsamaMunirCommented:
I think the only way to do it is Enable the auditing, or write a database on-logon trigger to keep on incrementing for each logon.

0
 
FarazMSyedCommented:
hi there ...

we have an Oracle Dictionary view V$SESSION in which u can find number of users connected to the database ...

connect to your system user account and run the following command in SQLPLUS.
select count(username) from v$session;

basically this query will return number of session of different users connected to the database ...

Regards
Faraz
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BALU_R_SAuthor Commented:
hi Faraz,

But this doesnt query the number of users connected during a period of time ....

ive already tried this using the v$session....but no use
0
 
dbrowerCommented:
try this

select count(username)
from v$session
where status = 'ACTIVE';

This should give you a count of the current active sessions. Or try

select count(distinct username)
from v$session;

This should give you the number of distinct users connected.

Hope this helps
0
 
mathavraCommented:
I would recommend creating a "Database logon trigger" and insert (record) the date and time into another table with user name. Later you can query this to get the information you need.
0
 
WadhwaCommented:
Hi Balu RS,

You asked
"How to Query The Number of users logged in a server during a period of time"

There is no way that you can capture this statistics online from oracle data dictionary. There is a view v$session which keep information of the logged user. Once the user has disconnected that information is no longer there.

If you have a oracle server version 8.x then you can create a database login trugger as already suggested by  UsamaMunir.

Here is a way to create a logon trigger

connect with sys or system and proceed with the following steps

Step 1:-
Create a table log_info as

create table SYSTEM.log_info( logindatetime date,LogoffDateTime Date,LoggedUser Varchar2(30),SESSIONID Number );

Step 2:-

CREATE OR REPLACE TRIGGER logon_audit AFTER LOGON ON DATABASE
BEGIN
 insert into system.log_info values(sysdate,null,user,sys_context('USERENV','SESSIONID') );
END;
/

CREATE OR REPLACE TRIGGER logoff_audit Before LOGOFF ON DATABASE
BEGIN
 insert into system.log_info values(Null,sysdate,user,sys_context('USERENV','SESSIONID') );
END;
/

This will create trigger name logon_audit,logoff_audit in the database

Step 3:
Shutdown normal and startup the database.


All set now , whenever any user logged in to the database , the login time and logoff time is recorded in the system.log_info table.

Now you can write down a simple sql , to know how many time particular user logged , at what time he/she logged, How long user was connected with the database etc.

I made a t1.sql for you .
t1.sql
select to_char(logindatetime,'DD-MON hh:mi') logon,
to_char(logoffdatetime,'DD-MON hh:mi') logoff,loggeduser,sessionid from system.log_info
where sessionid != 0
/
SVRMGR> @t1
LOGON        LOGOFF       LOGGEDUSER                     SESSIONID
------------ ------------ ------------------------------ ----------
30-AUG 03:49              SCOTT                               16209
30-AUG 03:49              SCOTT                               16210
30-AUG 03:51              SCOTT                               16211
             30-AUG 03:51 SCOTT                               16210
             30-AUG 03:51 SCOTT                               16209


The sessionid will tell you when a particular user has logged in and logged out.


Hope this is waht you want

Let me know if there is any doubt about this

Sam..
0
 
myerpzCommented:
There should be no need to write your own trigger.
Instead, you can make use of Oracle's own standard session auditing functionality.

Ask your DBA, or someone with AUDIT SYSTEM privilege, to issue the SQL command :-
   AUDIT BY SESSION;

If you just want to audit successful connections, the command is :-
   AUDIT BY SESSION WHENEVER SUCCESSFUL;

Each time a connection is made to the database, a record is written to the view "sys.dba_audit_session".

Amongst other things, this view shows the username of whoever logged in, when the connection was made and how long they were logged on for.

You can then write your own queries based on this view to give you what you need.

Before all this will work, however, you need the following line in your database's "init.ora" file :-

   audit_trail = true

If it's not there, your DBA will need to amend the file and bounce the database.

Hope this helps
0
 
BALU_R_SAuthor Commented:
hi all,

thank u for every sugestions....Hope this will work...
any way i'll try both the options & say the result within 2 days...the next 2 days im going for a holiday...
thanku any way.....great response

regards,
Balu
0
 
MindphaserCommented:
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
MoondancerCommented:
Zero response from anyone, finalized.
Moondancer - EE Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.