By michelle on

I'd like to see a summary of the logs with just the IP, user name (if logged in), and date, grouped by date and sorted by date descending. Before I go delving into the database, I thought I'd see if anyone has done anything like this already. Just a PHP snippit would be fine. I don't need a whole fancy module.

Thanks,

Michelle

⋅ Categories: Drupal 4.6.x, Drupal 4.7.x

Comments

sepeck’s picture

sepeck commented

I haven't seen anything specific to your question, but you might want to look at Bèr Kessels XStatistics module as a starting point. It might have much of the framework already so all you would need would be the specific queries.

-sp
---------
Test site, always start with a test site.
Drupal Best Practices Guide -|- Black Mountain

-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide

Bèr Kessels’s picture

Bèr Kessels commented

And file that as as feature issue to xstatistics I will certainly add it, if its any good. Please add a mockup of how you think it should be, since I really cannot understand your ideas, the way you word them here.
Bèr
---
Professional | Personal
| Sympal: Development and Hosting

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented

I am looking for a summary of the log files. Something like:

DATE         IP                USER ID
2006/01/26   66.249.65.148     Anonymous
2006/01/26   99.149.65.544     Jane Doe
2006/01/26   66.249.65.876     Anonymous
2006/01/25   88.343.54.123     Joe Smith
2006/01/25   66.249.65.148     Anonymous
2006/01/24   66.249.65.148     Anonymous

I just want to be able to tell at a glance who's using the site instead of going thru tons of individual page hits.

Thanks,

Michelle

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented

I did a little research and figured it out. Just in case someone comes along someday wanting to know the same thing, here's the SQL:

SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%m/%d/%Y') AS visited_on, name, hostname 
FROM accesslog LEFT JOIN users ON accesslog.uid = users.uid 
GROUP BY hostname, visited_on 
ORDER BY visited_on DESC, hostname ASC

Michelle

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented

I made the query into a page. I don't know if this is the best way of doing it and it's nothing fancy, but it works.

Michelle


$query = "SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%m/%d/%Y') AS visited_on, name, hostname FROM {accesslog} LEFT JOIN {users} ON {accesslog}.uid = {users}.uid GROUP BY hostname, visited_on ORDER BY visited_on DESC, name DESC, hostname ASC";

$result = db_query($query);

$output .= "<table border='1' cellpadding='4'>";

while ($logline = db_fetch_object($result)) {
  $output .= "<tr>";
  $output .= "<td>" . $logline->visited_on . "</td> " ;
  $output .= "<td>" . $logline->hostname . "</td> " ;
  $output .= "<td>" . $logline->name . "</td> " ;
  $output .= "</tr>";
}

$output .= "</table>";

print $output;