Alright to reply to myself I can return the information but have been unable
to return the last row... instead it always returns the first row. Tried
max, tried min, tried converting the datetime to a timestamp with the same
results...
mysql> SELECT da_userinfo.UserName, da_userinfo.Name, radacct.AcctStartTime
as LoginTime, radacct.AcctStopTime as LogoutTime
-> FROM radacct, da_userinfo
-> WHERE da_userinfo.Username = radacct.Username
-> AND `NASIPAddress` = '172.17.6.100'
-> GROUP BY radacct.`UserName`
-> HAVING MAX( radacct.`AcctStartTime` );
+--------------+-------------------------+---------------------+------------
---------+
| UserName | Name | LoginTime | LogoutTime
|
+--------------+-------------------------+---------------------+------------
---------+
| 0010E70A8004 | User1 | 2009-02-09 09:16:24 | 2009-02-10
04:42:08 |
| 0010E70A80A8 | User2 | 2009-02-09 14:31:20 |
2009-02-10 09:57:42 |
| 0010E70A812D | User3 | 2009-02-09 13:19:51 |
2009-02-10 07:43:08 |
| 0010E70A8336 | User4 | 2009-02-05 14:10:41 |
2009-02-10 02:36:41 |
| 0010E70A833B | User5 | 2009-02-06 17:45:15 |
2009-02-09 20:27:21 |
| 0010E72A2258 | User6 | 2009-01-29 13:40:42 | 2009-01-29
14:03:29 |
| 0010E72A2957 | User7 | 2009-02-09 15:16:06 | 2009-02-09
16:02:56 |
| 0010E72AA91C | User8 | 2009-02-03 09:45:36 | 2009-02-10
07:41:22 |
| 0010E7C2F6AF | 07B | 2009-01-28 16:13:44 | 2009-01-28
16:15:43 |
+--------------+-------------------------+---------------------+------------
---------+
16 rows in set (0.00 sec)
mysql> select UserName,NASIPAddress as Host,AcctStartTime as
LoginTime,AcctStopTime as LogoutTime from radacct where UserName =
'0010E70A8004';
+--------------+--------------+---------------------+---------------------+
| UserName | Host | LoginTime | LogoutTime |
+--------------+--------------+---------------------+---------------------+
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:16:24 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:17:02 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:17:12 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:18:30 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:39:21 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:44:42 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:12:34 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:14:05 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:18:51 | 2009-02-10 04:42:08 |
| 0010E70A8004 | 172.17.6.100 | 2009-02-10 04:42:08 | 0000-00-00 00:00:00 |
+--------------+--------------+---------------------+---------------------+
10 rows in set (0.00 sec)
mysql> select UserName,NASIPAddress as Host,AcctStartTime as
LoginTime,AcctStopTime as LogoutTime from radacct where UserName =
'0010E70A8004' HAVING MAX( AcctStartTime );
+--------------+--------------+---------------------+---------------------+
| UserName | Host | LoginTime | LogoutTime |
+--------------+--------------+---------------------+---------------------+
| 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:16:24 | 2009-02-10 04:42:08 |
+--------------+--------------+---------------------+---------------------+
1 row in set (0.00 sec)
-----Original Message-----
From: Ben Wiechman [mailto:b...@meltel.com]
Sent: Tuesday, February 10, 2009 11:32 AM
To: mysql@lists.mysql.com
Subject: Query Help
I keep hacking at this but haven't been able to get it right yet.
I have two tables
Userinfo contains a login, User's Name, Group Name
Log contains login, host, datetime of last login
What I need to do is return user information (userinfo.name/groupname) of
users that have logged into a particular host last. i.e. A user can log into
any of the hosts but I want to know which one they were on last. Each time
they log the username, host and datetime of login are recorded in the log
table. I've been able to return the last login to the host but I'm at a loss
to get the list of all users that used the host last or all users that have
ever logged into that host.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
