Not sure what you are, but we're OOB.  I use the "log_info" table to get user names from IPs.  

You could get the most recent or first record of a user by ORDERing BY and id field in DESCending or ASCending order.  To only get one, drop a LIMIT 1 on the end of the query.  Here's what I do:

ex: get the log text (which contains the login name) from the log_info table for a give IP
$query = "SELECT log_text,log_time FROM log_info WHERE log_cat = 'Authentication' AND log_text LIKE '%$address%' ORDER BY log_id DESC LIMIT 1";

Similarly, you could use the dm_report table to do what you're looking for.  Look on your cam for pg_createtable.sql for the whole breakdown of their tables

Regards,

Alex Lanstein
Senior Software Engineer, Transitional Data Services
Help Desk/Network Junkie, Connecticut College
Chief Coffee Drinker, LBCCHosting
860-625-4277
[log in to unmask]



-----Original Message-----
From: Cisco Clean Access Users and Administrators on behalf of Cal Frye
Sent: Fri 1/26/2007 10:33 AM
To: [log in to unmask]
Subject: SQL query and table choices
 
What I know of SQL could be written on the head of a pin. With some
help, we created the following little scripts to draw the userid, IP,
MAC and lease time from Clean Access. We have other scripts that combine
this with LDAP for ID number, and with our Residence database to
ultimately relate IP address to a dorm and floor the student lives on.
These scripts all run weekly, to reflect the most current information on
student residence and IP assignments.

This is done for a research project involving a competition between
dorms. The project wants to capture the IP address one uses when
visiting the contest web page, and relate that to location of the
viewer. We give them only the final relationship of IP and dorm/floor,
to protect privacy. Sounds vastly complicated, but using DHCP, I know of
no other way. We have three big subnets, covering both halves of ResNet
and the academic campus buildings, that's all. There's no systematic
relationship of IP address to location.

That said, I think I'm querying either the wrong table or I need more
conditions. What we get in return, in our nacextract.csv file, includes
multiple registrations on IP address, not just the most current one. And
in some cases, it appears we don't have the most current one in this
table. The discrepancies are frequent enough to bollix the project. Can
anyone help direct me either to the proper table within Clean Access or
which attribute could lead me to the proper information?

cron script:
psql -f /var/local/scripts/nacextract.psql -h 127.0.0.1 controlsmartdb
-U postgres

nacextract.psql:
-- Script to extract user info from NAC database
\o /var/local/scripts/nacextract.csv
\f ,
\pset format unaligned
select user_name, ip_address, mac_address, start_time from mac_filter;
\q

Many, many thanks in advance for your pointers...
-- 
Regards,
-- Cal Frye, Network Administrator, Oberlin College
   www.calfrye.com,  www.pitalabs.com

"Smash forehead on keyboard to continue..."