Getting a List of Users Connected to a DB

Is there a way to get a list of users connected to a database. I can do a onstat -uthen take all the sessid and pipe them through a onstat -g ses {sessid}then grep that for the database in question. Is this information held in some table(s) that I can query?

Perhaps: gex_vector=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | current_query | query_start -------+------------+---------+----------+----------+---------------+----------- -------------------- 17142 | gex_vector | 15822 | 1 | postgres | <IDLE> | 2005-04-05 22:07:27.849474-07 (1 row) The procpid would tie the process id you see from the OS side of things, I think; usename would show that the user id is ... HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: STEPHEN SCOTT [mailto:dba.lumber@gmail.com] Sent: Tue 4/5/2005 9:06 PM To: ids@iiug.org Cc: Subject: Getting a List of Users Connected to a DB [4671] Is there a way to get a list of users connected to a database. I can do a onstat -uthen take all the sessid and pipe them through a onstat -g ses {sessid}then grep that for the database in question. Is this information held in some table(s) that I can query? !DSPAM:425363b5160926109514939!

Hi, try something like a) echo "select distinct(username) from syssessions;" | dbaccess sysmaster - b) onstat -g ses | awk '/^[0-9]/ {print $2}' | sort -u Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management forum.subscriber@iiug.org wrote on 06.04.2005 06:06:31: > Is there a way to get a list of users connected to a database. > > I can do a > onstat -u> then take all the sessid and pipe them through a > onstat -g ses {sessid}> then grep that for the database in question. > > Is this information held in some table(s) that I can query?

Hi, you can use onstat -g sql (without a session ID). That shows information about all user sessions currently connected to databases - including the database name. Regards, Andreas > -----Ursprüngliche Nachricht----- > Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im > Auftrag von STEPHEN SCOTT > Gesendet: Mittwoch, 06. April 2005 06:07 > An: ids@iiug.org > Betreff: Getting a List of Users Connected to a DB [4671] > > > Is there a way to get a list of users connected to a database. > > I can do a > onstat -u> then take all the sessid and pipe them through a > onstat -g ses {sessid}> then grep that for the database in question. > > Is this information held in some table(s) that I can query? > >

Gregory, I have a script that provides the database name, the username, and the session ID. There is one optional command line parameter: the database name. Without this parameter, all databases in the current instance are included. Here is the script: ---------------------------------------- #!/usr/bin/ksh # dbwho.ksh if [ "$#" -gt 0 ] then export WHERE_CLAUSE="and odb_dbname='${1}'" else export WHERE_CLAUSE="" fi export WORKFILE=/tmp/dbwho.$$ echo " set isolation to dirty read; unload to $WORKFILE delimiter ' ' select odb_dbname, username, odb_sessionid from sysopendb, syssessions where syssessions.sid = sysopendb.odb_sessionid${WHERE_CLAUSE}" \\\\ | dbaccess sysmaster > /dev/null 2>&1 sort $WORKFILE rm -f $WORKFILE ---------------------------------------- Hope this helps. Rob Schmitz Rob.B.Schmitz@mail.sprint.com -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Gregory S. .... Sent: Wednesday, April 06, 2005 12:15 AM To: ids@iiug.org Subject: RE: Getting a List of Users Connected to a DB [4672] Perhaps: gex_vector=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | current_query | query_start -------+------------+---------+----------+----------+---------------+--- ---------------------------- 17142 | gex_vector | 15822 | 1 | postgres | <IDLE> | 2005-04-05 22:07:27.849474-07 (1 row) The procpid would tie the process id you see from the OS side of things, I think; usename would show that the user id is ... HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: STEPHEN SCOTT [mailto:dba.lumber@gmail.com] Sent: Tue 4/5/2005 9:06 PM To: ids@iiug.org Cc: Subject: Getting a List of Users Connected to a DB [4671] Is there a way to get a list of users connected to a database. I can do a onstat -uthen take all the sessid and pipe them through a onstat -g ses {sessid}then grep that for the database in question. Is this information held in some table(s) that I can query? !DSPAM:425363b5160926109514939!