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
use DBI;
use Date::Manip;
# Get parameters from the user.
print "Please enter the date of the offense (ex: yyyy-mm-dd):\n";
our $offdate = <STDIN>;
chop $offdate;
our @dayformat = "%Y-%m-%d";
our $prevday = DateCalc("$offdate","- 1 day",\$err);
$prevday = UnixDate($prevday, @dayformat);
our $nextday = DateCalc("$offdate","+ 1 day",\$err);
$nextday = UnixDate($nextday, @dayformat);
print "Please enter the IP address of the offender (ex:
111.111.111.111)\n";
our $ip = <STDIN>;
chop $ip;
print "Please enter the affiliate code (ex: VZD / PRTC )\n";
our $customer = <STDIN>;
chop $customer;
# 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!!";
}
### 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 = DBI->connect('DBI:mysql:database_name','user','password') or
die("Cannot Connect: $DBI::errstr");
### Start 6 way union ###
my $sql = 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 = '$ip'
AND PRTC_DIALUP.Date = '$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 = '$ip'
AND PRTC_DIALUP.Date = '$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 = '$ip'
AND PRTC_DIALUP.Date = '$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 = '$ip'
and PRTC_DSL.Date = '$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 = '$ip'
and PRTC_DSL.Date = '$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 = '$ip'
and PRTC_DSL.Date = '$nextday'
)
ORDER BY Full_Name, Time;
);
### End 6 way union ###
#pass sql query to database handle...
$sth = $dbh->prepare($sql);
#execute the query...
$sth->execute();
format PRTC_TOP =
Id | Start Time | Stop Time | Full Name
| IP address
.
while(@row = $sth->fetchrow_array) {
$rsltid = $row[0];
$rsltdate = $row[1];
$stop_time = $row[2];
$rsltrecord_type = $row[3];
$rsltfullname = $row[4];
s/^\s+//, s/\s+$// for $rsltfullname;
$rsltframed_ip_address = $row[5];
$session_time = $row[6];
$start_time = DateCalc("$rsltdate $stop_time","- $session_time
seconds",\$err);
my @format = "%Y-%m-%d %H:%M:%S";
$start_time = UnixDate($start_time, @format);
format PRTCOUT =
@<<<<<<<<< @<<<<<<<<<<<<<<<<<<< @<<<<<<<<< @<<<<<<<<<<
@<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<
$rsltid, $start_time, $rsltdate $stop_time, $rsltfullname,
$rsltframed_ip_address
.
$^ = "PRTC_TOP";
$~ = "PRTCOUT";
write;
}
}
### 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 = DBI->connect('dbi:mysql:database_name','user','password') or
die("Cannot Connect: $DBI::errstr");
### Start 6 way union ###
my $sql = 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 = '$ip'
AND VZD_DIALUP.Date = '$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 = '$ip'
AND VZD_DIALUP.Date = '$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 = '$ip'
AND VZD_DIALUP.Date = '$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 = '$ip'
and VZD_DSL.Date = '$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 = '$ip'
and VZD_DSL.Date = '$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 = '$ip'
and VZD_DSL.Date = '$nextday'
)
ORDER BY User_Name, Record_Time;
);
### End 6 way union ###
#pass sql query to database handle...
$sth = $dbh->prepare($sql);
#execute the query...
$sth->execute();
format VZD_TOP =
Id | Start Time | Stop Time | Full Name
| IP address | DSLAM | PORT
.
while(@row = $sth->fetchrow_array) {
$rsltid = $row[0];
$rsltdate = $row[1];
$rsltrecord_time = $row[2];
$rsltuser_name = $row[3];
s/^\s+//, s/\s+$// for $rsltuser_name;
$rsltframed_ip_address = $row[4];
$session_time = $row[5];
if($row[6] =~ /\//){
@rsltDSLinfo = split(/\//, $row[6]);
$rsltdslam = $rsltDSLinfo[0];
$rsltport = $rsltDSLinfo[2];
}else{
$rsltdslam = "";
$rsltport = "";
}
$start_time = DateCalc("$rsltdate $rsltrecord_time","-
$session_time seconds",\$err);
my @format = "%Y-%m-%d %H:%M:%S";
$start_time = UnixDate($start_time, @format);
format VZDOUT =
@<<<<<<<<< @<<<<<<<<<<<<<<<<<<< @<<<<<<<<< @<<<<<<<<<<
@<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<< @<<<<<<<< @<<<<<
$rsltid, $start_time, $rsltdate $rsltrecord_time, $rsltuser_name,
$rsltframed_ip_address, $rsltdslam, $rsltport
.
$^ = "VZD_TOP";
$~ = "VZDOUT";
write;
}
}
Chris Hood
-----Original Message-----
From: Jay Savage [mailto:[EMAIL PROTECTED]
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, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> 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.
>
> 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
>
Well, what does your current code to do this look like, and where is
it going wrong for you?
--jay
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
