Solved

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

Posted on 2001-08-30
11
1,409 Views
Last Modified: 2008-03-10
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.        
0
Comment
Question by:BALU_R_S
11 Comments
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6439997
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
 

Expert Comment

by:FarazMSyed
ID: 6440132
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
 

Author Comment

by:BALU_R_S
ID: 6440382
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
 
LVL 1

Expert Comment

by:dbrower
ID: 6440557
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
 
LVL 3

Expert Comment

by:mathavra
ID: 6442176
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:Wadhwa
ID: 6442257
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
 
LVL 3

Expert Comment

by:myerpz
ID: 6444397
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
 
LVL 3

Accepted Solution

by:
Wadhwa earned 50 total points
ID: 6444940

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
 

Author Comment

by:BALU_R_S
ID: 6447385
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
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7042910
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
 
LVL 1

Expert Comment

by:Moondancer
ID: 7058220
Zero response from anyone, finalized.
Moondancer - EE Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now