Cal,

IIRC, the mac_filter table is certified users.  I tend to use the  
user_info table, which is online users.  It uses IP + CCA server as  
the primary key, so you should never have a duplicate IP.

Michael Grinnell
Network Security Administrator
The American University

On Jan 26, 2007, at 10:48 AM, Lanstein, Alex C wrote:

> 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..."