Thread

1. look for latest user login in one table from a list of users in a nother

   Sill-II, Stephen <stephen.sill-ii@ost.dot.gov> — 2003-03-14T16:35:14Z

   I'm trying to come up with an efficient way to do the following task, but I
   haven't found the correct join syntax to do it.
   
   I have table "users" for keeping a list of users I need to check logins for.
   It contains the following fields:
   
   id,Full-Name,User-Name
   
   I have table called "logs" that contains the actual radius log data.  The
   three fields I am concerned with are:
   
   User-Name,Date,Time
   
   I have gotten thus far almost what I want with the following query.
   
   SELECT users."User-Name",max(logs."Date") as login_date,max(logs."Time") as
   login_time FROM logs where logs."User-Name"=users."User-Name" GROUP BY
   users."User-Name";
   
   This gives me the User-Name, date, and time of people WHO HAVE LOGGED IN.
   I'm looking to have a query that returns all of the users in the first
   table, including those who have not logged in.  I have an external perl
   script that generates a nice html report for the manager.  I have a script
   that parses the raw .csv files, but I'm trying to move it entirely to
   postgres, including if possible stored-procedures in plperl.  I already have
   a perl script that imports the raw log files into the logs table.
   
   Am I on the right track?
   
   Thanks,
   
   Stephen Sill II
   
   
   
   

2. Re: look for latest user login in one table from a list of users

   Jean-Luc Lachance <jllachan@nsd.ca> — 2003-03-14T17:13:35Z

   Try:
   
   select users."User-Name", ss."Date", ss."Time" from users left outer
   join (
     select distinct on ("User-Name") "User-Name", "Date", "Time" order by
   1, 2 DESC, 3 DESC) as ss
    on ( users."User-Name" = ss."User-Name")
   order by 1;
   
   It help if yo uhave an index on logs( "User-Name", "Date" desc, "Time"
   desc).
   
   JL
   
   PS Can't you convert "Date" || "Time"  to timestamp?
   
   
   "Sill-II, Stephen" wrote:
   > 
   > I'm trying to come up with an efficient way to do the following task, but I
   > haven't found the correct join syntax to do it.
   > 
   > I have table "users" for keeping a list of users I need to check logins for.
   > It contains the following fields:
   > 
   > id,Full-Name,User-Name
   > 
   > I have table called "logs" that contains the actual radius log data.  The
   > three fields I am concerned with are:
   > 
   > User-Name,Date,Time
   > 
   > I have gotten thus far almost what I want with the following query.
   > 
   > SELECT users."User-Name",max(logs."Date") as login_date,max(logs."Time") as
   > login_time FROM logs where logs."User-Name"=users."User-Name" GROUP BY
   > users."User-Name";
   > 
   > This gives me the User-Name, date, and time of people WHO HAVE LOGGED IN.
   > I'm looking to have a query that returns all of the users in the first
   > table, including those who have not logged in.  I have an external perl
   > script that generates a nice html report for the manager.  I have a script
   > that parses the raw .csv files, but I'm trying to move it entirely to
   > postgres, including if possible stored-procedures in plperl.  I already have
   > a perl script that imports the raw log files into the logs table.
   > 
   > Am I on the right track?
   > 
   > Thanks,
   > 
   > Stephen Sill II
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 6: Have you searched our list archives?
   > 
   > http://archives.postgresql.org
   
   

3. Re: look for latest user login in one table from a list of

   Achilleas Mantzios <achill@matrix.gatewaynet.com> — 2003-03-14T21:14:44Z

   On Fri, 14 Mar 2003, Sill-II, Stephen wrote:
   
   > I'm trying to come up with an efficient way to do the following task, but I
   > haven't found the correct join syntax to do it.
   > 
   > I have table "users" for keeping a list of users I need to check logins for.
   > It contains the following fields:
   > 
   > id,Full-Name,User-Name
   > 
   > I have table called "logs" that contains the actual radius log data.  The
   > three fields I am concerned with are:
   > 
   > User-Name,Date,Time
   > 
   > I have gotten thus far almost what I want with the following query.
   > 
   > SELECT users."User-Name",max(logs."Date") as login_date,max(logs."Time") as
   > login_time FROM logs where logs."User-Name"=users."User-Name" GROUP BY
   > users."User-Name";
   
   Try
   foo=# SELECT users.name,someq.mlast from users left outer join (select 
   id,max(last) as mlast from log group by id) as someq using(id);
   
   substituting with your names.
   
   > 
   > This gives me the User-Name, date, and time of people WHO HAVE LOGGED IN.
   > I'm looking to have a query that returns all of the users in the first
   > table, including those who have not logged in.  I have an external perl
   > script that generates a nice html report for the manager.  I have a script
   > that parses the raw .csv files, but I'm trying to move it entirely to
   > postgres, including if possible stored-procedures in plperl.  I already have
   > a perl script that imports the raw log files into the logs table.
   > 
   > Am I on the right track?
   > 
   > Thanks,
   > 
   > Stephen Sill II
   > 
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 6: Have you searched our list archives?
   > 
   > http://archives.postgresql.org
   > 
   
   -- 
   ==================================================================
   Achilleus Mantzios
   S/W Engineer
   IT dept
   Dynacom Tankers Mngmt
   Nikis 4, Glyfada
   Athens 16610
   Greece
   tel:    +30-210-8981112
   fax:    +30-210-8981877
   email:  achill@matrix.gatewaynet.com
           mantzios@softlab.ece.ntua.gr