Useful FTS database queries.

Command Line Access to the Database.

$ . /opt/glite/yaim/etc/site-info.def
$  sqlplus $FTA_GLOBAL_DB_USER/"$FTA_GLOBAL_DB_PASSWORD@$FTA_GLOBAL_DB_CONNECTSTRING"
SQL>

rlwap

$  rlwrap sqlplus $FTA_GLOBAL_DB_USER/"$FTA_GLOBAL_DB_PASSWORD@$FTA_GLOBAL_DB_CONNECTSTRING"
SQL>

List of Active Users on a Channel

SQL> select distinct user_dn, count(*) from t_job where channel_name = 'STAR-CERN' and 
             submit_time >= TIMESTAMP '2007-03-19 00:00:00 +00:00' group by user_dn ;
USER_DN
--------------------------------------------------------------------------------
  COUNT(*)
----------
/C=CH/O=CERN/OU=GRID/CN=XXXXXXXXXXXXX
       548

/DC=ch/DC=cern/OU=Organic Units/OU=Users/CN=YYYYYYYYYYYYYYYYYYYYY
       974
SQL> 

Check which VO a DN is in.

SQL> select distinct vo_name,count(*) from t_job where user_dn='/C=RU/O=RDIG/OU=users/OU=sinp.msu.ru/CN=XXXXX' 
               group by vo_name;

VO_NAME                                              COUNT(*)
-------------------------------------------------- ----------
cms                                                      3348

-- SteveTraylen - 26 Mar 2007