Perl MySQL Driver Support
-
Hi,
I've got a captive portal up and running using a radius database, I run lightsquid to monitor the internet usage and sites visited.
My lightsquid.cfg has the realname option enabled which gives me this when I look at the report:
Which is fine, however I want more information; specifically the "Real Name" field to be populated.
From a bit of searching I've found one way to do this; edit the /usr/local/etc/lightsquid/realname.cfg file, manually putting in the ip addresses followed by names. I guess this is fine for smaller networks but there are far too many users on my network for me to waste time putting in 100+ names everyday.
So with more searching I came across this post on a blog:
http://itguest.blogspot.co.uk/2012/03/show-user-freeradius-from-lightsquid.html
Which sounded perfect, as I am using freeradius (using a MySQL database) to authenticate my CP users. A little tweaking of the script (it didn't look quite right) gave me this:
#!/usr/bin/perl use DBI; use Socket; #use file; ($sec_now, $min_now, $hour_now, $date_now, $mon_now, $year_now, $dayno_now) = localtime(); $mon_now++; $year_now += 1900; $todayni= sprintf("%04d-%02d-%02d",$year_now, $mon_now, $date_now); $dsn = "DBI:mysql:database=radius;host=IPADDRESS"; $dbh = DBI->connect($dsn,"USER","PASSWORD"); #USER is user for mysql, PASSWORD is password for mysql $sth = $dbh->prepare("select * from radacct,radcheck where radacct.AcctStartTime >= '".$todayni." 00:00:00' and radacct.AcctStartTime <= '".$todayni." 23:59:59' and radacct.UserName = ' r$ $sth->execute()"); #print "Content-type:text/html\n\n"; open(FILE, ">/usr/local/etc/lightsquid/realname.cfg"); while (my $ref = $sth->fetchrow_hashref()) { #print FILE "$ref->{'FramedIPAddress'} $ref->{'firstname'} $ref->{'lastname'}\n"; print FILE "$ref->{'framedipaddress'} $ref->{'username'} $ref->{'name'}\n"; } close(FILE); $sth->finish(); $dbh->disconnect();
(I'm not any good at scripting, but that looked correct to me, if its not please tell me)
I placed the script in /usr/local/www/lightsquid/realname.pl, made it executable with chmod +x and ran it from the pfsense shell.
It failed.
Turns out I need to install DBI support for perl, easy enough with some searching turns out I need to run:
pkg_add -r http://files.pfsense.org/packages/amd64/8/All/p5-DBI-1.616_1.tbz
(I'm running 64bit)
It seemed to work a bit better after that, I no longer got the same error message.
However its now replaced with this error:
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/local/lib/perl5/5.12.4/BSDPAN /usr/local/lib/perl5/site_perl/5.12.4/mach /usr/local/lib/perl5/site_perl/5.12.4 /usr/local/lib/perl5/5.12.4/mach /usr/local/lib/perl5/5.12.4 .) at (eval 5) line 3. Perhaps the DBD::mysql perl module hasn't been fully installed, or perhaps the capitalisation of 'mysql' isn't right. Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge. at realname.pl line 14
I can't find a p5-DBD-mysql module in the pfsense package lists all I see is this:
http://files.pfsense.org/packages/amd64/8/All/p5-DBD-SQLite-1.35.tbz
I've added it as a package just to be sure but it still doesn't work I still get the same error.
What do I do now?
I'm sure loads of people would find this useful if we crack it.
Any help would be greatly appreciated!
-
Try to change dbi to sqlite instead of mysql. IIRC query sintax on perl dbi will be the same.
-
OK Thanks, that works a little better.
Now I get this error:
DBD::SQLite::db prepare failed: unrecognized token: "' r->execute()" at realname.pl line 15. Can't call method "fetchrow_hashref" on an undefined value at realname.pl line 18.
-
Check a sqlite sample on cpan.
using DBD
http://search.cpan.org/~msergeant/DBD-SQLite-0.31/lib/DBD/SQLite.pm
http://mailliststock.wordpress.com/2007/03/01/sqlite-examples-with-bash-perl-and-python/using DB
http://search.cpan.org/~vxx/SQLite-DB-0.04/lib/SQLite/DB.pmRemember to create the sqlite database first
att,
Marcello Coutinho