MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Ben Wiechman Date:February 10 2009 6:58pm
Subject:RE: Query Help
View as plain text  
Awesome... that works. Had to add a where clause to limit it to a specific
host.

The explain for that looks... interesting. 

Thanks

-----Original Message-----
From: ddevaudreuil@stripped [mailto:ddevaudreuil@stripped] 
Sent: Tuesday, February 10, 2009 12:47 PM
To: Ben Wiechman
Cc: mysql@stripped
Subject: RE: Query Help


"Ben Wiechman" <benw@stripped> wrote on 02/10/2009 01:30:14 PM:

> Thanks for the input! That is close to what I need, however not exactly.
It
> will give me the last time a user logged into the host in question but I
> want to prune users who have since logged into a different host.
Basically
> find out how many users are logged into a given host or who are not
> currently logged in but have not logged into a different host since they
> logged out of the target.

Figure out the last time each user logged in to any host:

SELECT login, MAX(datetime)as lastlogindate
FROM Log
GROUP BY login

So use that query as a derived table to get the rest of the info (untested
SQL):

SELECT Userinfo.Username, Userinfo.GroupName, Log.hostname, Log.datetime
FROM Userinfo
INNER JOIN
      (SELECT login, MAX(datetime)as lastlogindate
      FROM Log
      GROUP BY login) AS lastlogin
      ON Userinfo.login=lastlogin.login
INNER JOIN Log  ON lastlogin.login=Log.login AND
lastlogin.lastlogindate=Log.datetime

Hope that helps.

Donna




Thread
Query HelpBen Wiechman10 Feb
  • RE: Query HelpBen Wiechman10 Feb
RE: Query HelpBen Wiechman10 Feb
  • RE: Query Helpddevaudreuil10 Feb
    • RE: Query HelpBen Wiechman10 Feb