Find a specific record based on time

Ok, basically I have a list of start times, stop times, usernames, and
ip's in a database, I currently have code in place that will take in a
date and ip address and return a list of people that had that IP =
address,
with the time that they acquired the ip and the time that they released
the ip.

What I need to do is input a date & time and find out the exact user =
that
had the ip address at that time.

So I have tried Date::Manip and it does most of the work for me for my
other calculations  but it doesn't seem to give me a way to find out =
what
I need.

Any ideas, or point to a better module to use would be great.

Chris Hood=A0

On 4/19/05, christopher.l.hood@verizon.com
<christopher.l.hood@verizon.com> wrote:
> Ok, basically I have a list of start times, stop times, usernames, and
> ip's in a database, I currently have code in place that will take in a
> date and ip address and return a list of people that had that IP address,
> with the time that they acquired the ip and the time that they released
> the ip.
>=20
> What I need to do is input a date & time and find out the exact user that
> had the ip address at that time.
>=20
> So I have tried Date::Manip and it does most of the work for me for my
> other calculations  but it doesn't seem to give me a way to find out what
> I need.
>=20
> Any ideas, or point to a better module to use would be great.
>=20
> Chris Hood
>=20

Well, what does your current code to do this look like, and where is
it going wrong for you?

--jay

Well here is what I have so far to get the previous day, the current =
day,
next day, and this works fine but could return 25 results, I am not even
sure how to go about getting only the one that the offense falls within.

The database entries have a date/time that the user stopped having that
IP. The start time is gotten by subtracting the session_time (amount of
time online in seconds), and other information relative to the radius
logs.


The scenario is : we get complaint that joespammer is spamming, from ip
111.222.333.444 on mar 3 2005 at 15:01:05. We run this script to find =
out
what users had that IP on Mar 3 and the previous, and next day, then =
find
specific user by human eye that had that ip during the specified time.

I want to script the finding who had the ip based on start and stop time
and the time of spam.

Thank you very much for you help,



#!/usr/bin/perl
=20
=20
use DBI;
use Date::Manip;
=20
# Get parameters from the user.
print "Please enter the date of the offense (ex: yyyy-mm-dd):\n";
our $offdate =3D <STDIN>;
chop $offdate;
=20
our @dayformat =3D "%Y-%m-%d";
our $prevday =3D DateCalc("$offdate","- 1 day",\$err);
$prevday =3D UnixDate($prevday, @dayformat);
our $nextday =3D DateCalc("$offdate","+ 1 day",\$err);
$nextday =3D UnixDate($nextday, @dayformat);
=20
print "Please enter the IP address of the offender (ex:
111.111.111.111)\n";
our $ip =3D <STDIN>;
chop $ip;
=20
print "Please enter the affiliate code (ex: VZD / PRTC )\n";
our $customer =3D <STDIN>;
chop $customer;
=20
# Get matching information from the database.
if ( ($customer eq "PRTC") || ($customer eq "prtc") ) {
&PrintPRTCTable;
} elsif ( ($customer eq "VZD") || ($customer eq "vzd") ) {
&PrintVZDTable;
} else {
print "Something went horribly wrong!!";
}
=20
### Sub to print PRTC Table ###
sub PrintPRTCTable {
my @row;
my $rsltid;
my $rsltdate;
my $rslttime;
my $rsltrecord_type;
my $rsltfullname;
my $rsltframed_ip_address;
my $tableline;
my $rsltuser_name;
my $rsltrecord_time;
my $rsltevent_timestamp;
my $sth;
my $start_time;
my $stop_time;
my $start_time_secs;
my $session_time;
my $dbh =3D DBI-> connect('DBI:mysql:database_name','user'
,'password') or
die("Cannot Connect: $DBI::errstr");
=20
### Start 6 way union ###
my $sql =3D qq(
(
Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time,
PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name,
PRTC_DIALUP.Framed_IP_Address, PRTC_DIALUP.Acct_Session_Time
from PRTC_DIALUP
Where PRTC_DIALUP.Framed_IP_Address =3D '$ip'
AND PRTC_DIALUP.Date =3D '$offdate'
)
UNION
(
Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time,
PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name,
PRTC_DIALUP.Framed_IP_Address, PRTC_DIALUP.Acct_Session_Time
from PRTC_DIALUP
Where PRTC_DIALUP.Framed_IP_Address =3D '$ip'
AND PRTC_DIALUP.Date =3D '$prevday'
)
UNION
(
Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time,
PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name,
PRTC_DIALUP.Framed_IP_Address, PRTC_DIALUP.Acct_Session_Time
from PRTC_DIALUP
Where PRTC_DIALUP.Framed_IP_Address =3D '$ip'
AND PRTC_DIALUP.Date =3D '$nextday'
)
UNION
(
Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time,
PRTC_DSL.Record_Type,  PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address,
PRTC_DSL.Acct_Session_Time
from PRTC_DSL
Where PRTC_DSL.Framed_IP_Address =3D '$ip'
and PRTC_DSL.Date =3D '$offdate'
)
UNION
(
Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time,
PRTC_DSL.Record_Type,  PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address,
PRTC_DSL.Acct_Session_Time
from PRTC_DSL
Where PRTC_DSL.Framed_IP_Address =3D '$ip'
and PRTC_DSL.Date =3D '$prevday'
)
UNION
(
Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time,
PRTC_DSL.Record_Type,  PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address,
PRTC_DSL.Acct_Session_Time
from PRTC_DSL
Where PRTC_DSL.Framed_IP_Address =3D '$ip'
and PRTC_DSL.Date =3D '$nextday'
)
ORDER BY  Full_Name, Time;
);
=20
### End 6 way union ###
=20
#pass sql query to database handle...
$sth =3D $dbh->prepare($sql);
=20
#execute the query...
$sth->execute();
=20
format PRTC_TOP =3D
Id        |     Start Time     |     Stop Time     |       Full Name
|       IP address
.
=20
while(@row =3D $sth->fetchrow_array) {
$rsltid =3D $row[0];
$rsltdate =3D $row[1];
$stop_time =3D $row[2];
$rsltrecord_type =3D $row[3];
$rsltfullname =3D $row[4];
s/^\s+//, s/\s+$// for $rsltfullname;
$rsltframed_ip_address =3D $row[5];
$session_time =3D $row[6];
$start_time =3D DateCalc("$rsltdate $stop_time","- $session_time
seconds",\$err);
my @format =3D "%Y-%m-%d %H:%M:%S";
$start_time =3D UnixDate($start_time, @format);
=20
=20
format PRTCOUT =3D
@<<<<<<<<< @<<<<<<<<<<<<<<<<<<< @<<<<<<<<< @<<<<<<<<<<
@<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<
$rsltid, $start_time,   $rsltdate $stop_time, $rsltfullname,
$rsltframed_ip_address
.
$^ =3D "PRTC_TOP";
$~ =3D "PRTCOUT";
write;
=20
}
=20
}
=20
### Sub to Print VZD Table ###
sub PrintVZDTable {
my @row;
my $rsltid;
my $rsltdate;
my $rslttime;
my $rsltrecord_type;
my $rsltfullname;
my $rsltframed_ip_address;
my $tableline;
my $rsltuser_name;
my $rsltrecord_time;
my $rsltevent_timestamp;
my $sth;
my $start_time;
my $stop_time;
my $start_time_secs;
my $session_time;
my $rsltdslam;
my $rsltport;
my @rsltDSLinfo;
my $dbh =3D DBI-> connect('dbi:mysql:database_name','user'
,'password') or
die("Cannot Connect: $DBI::errstr");
=20
### Start 6 way union ###
my $sql =3D qq(
(
Select ALL VZD_DIALUP.Id, VZD_DIALUP.Date, VZD_DIALUP.Record_Time,
VZD_DIALUP.User_Name, VZD_DIALUP.Framed_IP_Address,
VZD_DIALUP.Acct_Session_Time, VZD_DIALUP.Acct_Session_Id
from VZD_DIALUP
Where VZD_DIALUP.Framed_IP_Address =3D '$ip'
AND VZD_DIALUP.Date =3D '$offdate'
)
UNION
(
Select ALL VZD_DIALUP.Id, VZD_DIALUP.Date, VZD_DIALUP.Record_Time,
VZD_DIALUP.User_Name, VZD_DIALUP.Framed_IP_Address,
VZD_DIALUP.Acct_Session_Time, VZD_DIALUP.Acct_Session_Id
from VZD_DIALUP
Where VZD_DIALUP.Framed_IP_Address =3D '$ip'
AND VZD_DIALUP.Date =3D '$prevday'
)
UNION
(
Select ALL VZD_DIALUP.Id, VZD_DIALUP.Date, VZD_DIALUP.Record_Time,
VZD_DIALUP.User_Name, VZD_DIALUP.Framed_IP_Address,
VZD_DIALUP.Acct_Session_Time, VZD_DIALUP.Acct_Session_Id
from VZD_DIALUP
Where VZD_DIALUP.Framed_IP_Address =3D '$ip'
AND VZD_DIALUP.Date =3D '$nextday'
)
UNION
(
Select ALL VZD_DSL.Id, VZD_DSL.Date, VZD_DSL.Record_Time,
VZD_DSL.User_Name, VZD_DSL.Framed_IP_Address, VZD_DSL.Acct_Session_Time,
VZD_DSL.Acct_Session_Id
from VZD_DSL
Where VZD_DSL.Framed_IP_Address =3D '$ip'
and VZD_DSL.Date =3D '$offdate'
)
UNION
(
Select ALL VZD_DSL.Id, VZD_DSL.Date, VZD_DSL.Record_Time,
VZD_DSL.User_Name, VZD_DSL.Framed_IP_Address, VZD_DSL.Acct_Session_Time,
VZD_DSL.Acct_Session_Id
from VZD_DSL
Where VZD_DSL.Framed_IP_Address =3D '$ip'
and VZD_DSL.Date =3D '$prevday'
)
UNION
(
Select ALL VZD_DSL.Id, VZD_DSL.Date, VZD_DSL.Record_Time,
VZD_DSL.User_Name, VZD_DSL.Framed_IP_Address, VZD_DSL.Acct_Session_Time,
VZD_DSL.Acct_Session_Id
from VZD_DSL
Where VZD_DSL.Framed_IP_Address =3D '$ip'
and VZD_DSL.Date =3D '$nextday'
)
ORDER BY  User_Name, Record_Time;
);
=20
### End 6 way union ###
=20
#pass sql query to database handle...
$sth =3D $dbh->prepare($sql);
=20
#execute the query...
$sth->execute();
=20
format VZD_TOP =3D
Id        |     Start Time     |     Stop Time     |       Full Name
|       IP address      |   DSLAM  |  PORT
.
=20
=20
while(@row =3D $sth->fetchrow_array) {
$rsltid =3D $row[0];
$rsltdate =3D $row[1];
$rsltrecord_time =3D $row[2];
$rsltuser_name =3D $row[3];
s/^\s+//, s/\s+$// for $rsltuser_name;
$rsltframed_ip_address =3D $row[4];
$session_time =3D $row[5];
if($row[6] =3D~ /\//){
@rsltDSLinfo =3D split(/\//, $row[6]);
$rsltdslam =3D $rsltDSLinfo[0];
$rsltport =3D $rsltDSLinfo[2];
}else{
$rsltdslam =3D "";
$rsltport =3D "";
}
$start_time =3D DateCalc("$rsltdate $rsltrecord_time","-
$session_time seconds",\$err);
my @format =3D "%Y-%m-%d %H:%M:%S";
$start_time =3D UnixDate($start_time, @format);
=20
=20
=20
format VZDOUT =3D
@<<<<<<<<< @<<<<<<<<<<<<<<<<<<< @<<<<<<<<< @<<<<<<<<<<
@<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<< @<<<<<<<< @<<<<<
$rsltid, $start_time,   $rsltdate $rsltrecord_time, $rsltuser_name,
$rsltframed_ip_address, $rsltdslam, $rsltport
.
=20
$^ =3D "VZD_TOP";
$~ =3D "VZDOUT";
=20
write;
}
=20
}

Chris Hood=A0


-----Original Message-----
From: Jay Savage [mailto:daggerquill@gmail.com]=20
Sent: Tuesday, April 19, 2005 10:57 AM
To: Christopher L. Hood; beginners perl
Subject: Re: Find a specific record based on time

On 4/19/05, christopher.l.hood@verizon.com
<christopher.l.hood@verizon.com> wrote:
> Ok, basically I have a list of start times, stop times, usernames, and
> ip's in a database, I currently have code in place that will take in a
> date and ip address and return a list of people that had that IP
address,
> with the time that they acquired the ip and the time that they =
released
> the ip.
>=20
> What I need to do is input a date & time and find out the exact user
that
> had the ip address at that time.
>=20
> So I have tried Date::Manip and it does most of the work for me for my
> other calculations  but it doesn't seem to give me a way to find out
what
> I need.
>=20
> Any ideas, or point to a better module to use would be great.
>=20
> Chris Hood
>=20

Well, what does your current code to do this look like, and where is
it going wrong for you?

--jay

--=20
To unsubscribe, e-mail: beginners-unsubscribe@perl.org
For additional commands, e-mail: beginners-help@perl.org
<http://learn.perl.org/> <http://learn.perl.org/first-response>