Thread: [Phpip-support] Add Exsisting Data to phpip

Brought to you by: trilexcom

phpip-support

[Phpip-support] Add Exsisting Data to phpip

From: mrgenius <mrg...@gm...> - 2005-10-12 05:04:35

hi all
 i've Question here. I am interested in using your software .. and it looks
really good to me.
 from past 5 years we've been mantaining IP Schema of our organization on
Excel sheets. Which have similar Colmns as "  IP MASK DESCRIPTION CLIENT
 and your software also generates output in same format. So is there any
possbility that i can import that IP Schema from Excel to this software???
bcos there are around 8000 IP adressse and it will be real pain to re-enter
that manually..
 And also. In search page is it possible to include Search in ANY field?
 Waiting for response.
 -Geni.

Re: [Phpip-support] Add Exsisting Data to phpip

From: Michael E. <mi...@mi...> - 2005-10-12 12:44:56

Can you export the Excel to cvs format?  I have a perl script that will
parse and insert the data into sql.

-- 
Michael Earls

http://www.vermeer.org
http://www.vermeer.org/pgp
http://www.phpip.net

PGP Info: KeyID 0x168CBA4E
Fingerprint 14DD 0899 8FE4 1C16 9561 FA2A 1ACB CA91 168C BA4E


> hi all
>  i've Question here. I am interested in using your software .. and it
> looks
> really good to me.
>  from past 5 years we've been mantaining IP Schema of our organization on
> Excel sheets. Which have similar Colmns as "  IP MASK DESCRIPTION CLIENT
>  and your software also generates output in same format. So is there any
> possbility that i can import that IP Schema from Excel to this software???
> bcos there are around 8000 IP adressse and it will be real pain to
> re-enter
> that manually..
>  And also. In search page is it possible to include Search in ANY field?
>  Waiting for response.
>  -Geni.
>

Re: [Phpip-support] Add Exsisting Data to phpip

From: mrgenius <mrg...@gm...> - 2005-10-12 13:02:42

Yeah I can export it as CSV.
 i've already created cidr's and prefixes.. only needed to import all host
addresses.
 Hope you'r script will help me.. please send me that script
  Regards,

-Geni

 On 10/12/05, Michael Earls <mi...@mi...> wrote:
>
> Can you export the Excel to cvs format? I have a perl script that will
> parse and insert the data into sql.
>
> --
> Michael Earls
>
> http://www.vermeer.org
> http://www.vermeer.org/pgp
> http://www.phpip.net
>
> PGP Info: KeyID 0x168CBA4E
> Fingerprint 14DD 0899 8FE4 1C16 9561 FA2A 1ACB CA91 168C BA4E
>
>
> > hi all
> > i've Question here. I am interested in using your software .. and it
> > looks
> > really good to me.
> > from past 5 years we've been mantaining IP Schema of our organization o=
n
> > Excel sheets. Which have similar Colmns as " IP MASK DESCRIPTION CLIENT
> > and your software also generates output in same format. So is there any
> > possbility that i can import that IP Schema from Excel to this
> software???
> > bcos there are around 8000 IP adressse and it will be real pain to
> > re-enter
> > that manually..
> > And also. In search page is it possible to include Search in ANY field?
> > Waiting for response.
> > -Geni.
> >
>
>

Re: [Phpip-support] Add Exsisting Data to phpip

From: Michael E. <mi...@mi...> - 2005-10-12 13:51:08

The perl script was written by Vijay Avarachen back in 2003.  Let me know
if this still works.

Michael

cat ipmanager_csv_import.pl

#!/usr/bin/perl -w
# We were using a simple excel document to keep track of ip's
# After I implemented phpIPMANAGER I had to import the data
# from the excel file to MySQL.
# This perl script reads from a csv file of format
# HOSTNAME,IP,COMMENT<EOL> and inputs the data in
# phpIPMANAGER utility (http://www.phpip.net)
# Written by Vijay Avarachen (vi...@av...)
# April 02 2003
# ver 1.0

use strict;
use DBI;
my $server = 'localhost';
my $db = 'ipmanager';
my $table = 'addresses';
my $username = 'ipmanager';
my $password = 'PaSsWoRd';
my $phone = 'x6651';
my $email = 'vav...@in...';
my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password);

# Hash to find the gateway
my %subnet = ('11', '192.200.11.1',
           '40', '192.200.40.1',
           '41', '192.200.41.1',
           '81', '192.200.81.1',
           '82', '192.200.82.1',
           '86', '192.200.86.1',
           '98', '192.168.98.1');

while(<>){
# Spliting the csv input
# CSV file should be in the format HOSTNAME,xxx.xxx.xxx.xxx,COMMENT
my ($hostname,$ip,$comment)=split /\,/;
# chomp did not work, so I was forced to use chop twice to get rid of

# I have a feeling it was something to do with excel export feature
# end of line character
chop($comment);
chop($comment);

# spliting $ip to get the subnet
my ($oct1,$oct2,$oct3,$oct4)=split (/\./,$ip);

# Run the third octate through subnet hash to get the right gateway
my $gateway=$subnet{$oct3};
# build the query string to update ipmanager
my $query="UPDATE $table SET mask='255.255.255.0', gateway='$gateway',
description='$comment', phone='$phone', email='$email' WHERE ip='$ip';";
# print ("$query\n");
my $sth = $dbh->prepare($query);
$sth->execute();
}
$dbh->disconnect;


-- 
Michael Earls

http://www.vermeer.org
http://www.vermeer.org/pgp
http://www.phpip.net

PGP Info: KeyID 0x168CBA4E
Fingerprint 14DD 0899 8FE4 1C16 9561 FA2A 1ACB CA91 168C BA4E


> Yeah I can export it as CSV.
>  i've already created cidr's and prefixes.. only needed to import all host
> addresses.
>  Hope you'r script will help me.. please send me that script
>   Regards,
>
> -Geni
>
>  On 10/12/05, Michael Earls <mi...@mi...> wrote:
>>
>> Can you export the Excel to cvs format? I have a perl script that will
>> parse and insert the data into sql.
>>
>> --
>> Michael Earls
>>
>> http://www.vermeer.org
>> http://www.vermeer.org/pgp
>> http://www.phpip.net
>>
>> PGP Info: KeyID 0x168CBA4E
>> Fingerprint 14DD 0899 8FE4 1C16 9561 FA2A 1ACB CA91 168C BA4E
>>
>>
>> > hi all
>> > i've Question here. I am interested in using your software .. and it
>> > looks
>> > really good to me.
>> > from past 5 years we've been mantaining IP Schema of our organization
>> on
>> > Excel sheets. Which have similar Colmns as " IP MASK DESCRIPTION
>> CLIENT
>> > and your software also generates output in same format. So is there
>> any
>> > possbility that i can import that IP Schema from Excel to this
>> software???
>> > bcos there are around 8000 IP adressse and it will be real pain to
>> > re-enter
>> > that manually..
>> > And also. In search page is it possible to include Search in ANY
>> field?
>> > Waiting for response.
>> > -Geni.
>> >
>>
>>
>

Re: [Phpip-support] Add Exsisting Data to phpip

From: mrgenius <mrg...@gm...> - 2005-10-17 05:34:30

Yes it is workign to some extent.
 its not importing clien'ts column..
 and one mroe thing is that its setting subnet to
255.255.255.0<http://255.255.255.0>against all ips.. I've subnets
masks defiend in excel sheet. isn't it
possible that It just sees for example
 192.168.0.5 <http://192.168.0.5> from Excel and then updates whatever is
present against this IP
 like i said my excel is in the form .. IP Address, Subnet MASK, Client,
Description.
 so apparent we can search record via IP address and then replaces its item=
s
to what are present in excel.
 please advice
 Regadsm

 On 10/12/05, Michael Earls <mi...@mi...> wrote:
>
> The perl script was written by Vijay Avarachen back in 2003. Let me know
> if this still works.
>
> Michael
>
> cat ipmanager_csv_import.pl
>
> #!/usr/bin/perl -w
> # We were using a simple excel document to keep track of ip's
> # After I implemented phpIPMANAGER I had to import the data
> # from the excel file to MySQL.
> # This perl script reads from a csv file of format
> # HOSTNAME,IP,COMMENT<EOL> and inputs the data in
> # phpIPMANAGER utility (http://www.phpip.net)
> # Written by Vijay Avarachen (vi...@av...)
> # April 02 2003
> # ver 1.0
>
> use strict;
> use DBI;
> my $server =3D 'localhost';
> my $db =3D 'ipmanager';
> my $table =3D 'addresses';
> my $username =3D 'ipmanager';
> my $password =3D 'PaSsWoRd';
> my $phone =3D 'x6651';
> my $email =3D 'vav...@in...';
> my $dbh =3D DBI->connect("dbi:mysql:$db:$server", $username, $password);
>
> # Hash to find the gateway
> my %subnet =3D ('11', '192.200.11.1 <http://192.200.11.1>',
> '40', '192.200.40.1 <http://192.200.40.1>',
> '41', '192.200.41.1 <http://192.200.41.1>',
> '81', '192.200.81.1 <http://192.200.81.1>',
> '82', '192.200.82.1 <http://192.200.82.1>',
> '86', '192.200.86.1 <http://192.200.86.1>',
> '98', '192.168.98.1 <http://192.168.98.1>');
>
> while(<>){
> # Spliting the csv input
> # CSV file should be in the format HOSTNAME,xxx.xxx.xxx.xxx,COMMENT
> my ($hostname,$ip,$comment)=3Dsplit /\,/;
> # chomp did not work, so I was forced to use chop twice to get rid of
>
> # I have a feeling it was something to do with excel export feature
> # end of line character
> chop($comment);
> chop($comment);
>
> # spliting $ip to get the subnet
> my ($oct1,$oct2,$oct3,$oct4)=3Dsplit (/\./,$ip);
>
> # Run the third octate through subnet hash to get the right gateway
> my $gateway=3D$subnet{$oct3};
> # build the query string to update ipmanager
> my $query=3D"UPDATE $table SET mask=3D'255.255.255.0 <http://255.255.255.=
0>',
> gateway=3D'$gateway',
> description=3D'$comment', phone=3D'$phone', email=3D'$email' WHERE ip=3D'=
$ip';";
> # print ("$query\n");
> my $sth =3D $dbh->prepare($query);
> $sth->execute();
> }
> $dbh->disconnect;
>
>
> --
> Michael Earls
>
> http://www.vermeer.org
> http://www.vermeer.org/pgp
> http://www.phpip.net
>
> PGP Info: KeyID 0x168CBA4E
> Fingerprint 14DD 0899 8FE4 1C16 9561 FA2A 1ACB CA91 168C BA4E
>
>
> > Yeah I can export it as CSV.
> > i've already created cidr's and prefixes.. only needed to import all
> host
> > addresses.
> > Hope you'r script will help me.. please send me that script
> > Regards,
> >
> > -Geni
> >
> > On 10/12/05, Michael Earls <mi...@mi...> wrote:
> >>
> >> Can you export the Excel to cvs format? I have a perl script that will
> >> parse and insert the data into sql.
> >>
> >> --
> >> Michael Earls
> >>
> >> http://www.vermeer.org
> >> http://www.vermeer.org/pgp
> >> http://www.phpip.net
> >>
> >> PGP Info: KeyID 0x168CBA4E
> >> Fingerprint 14DD 0899 8FE4 1C16 9561 FA2A 1ACB CA91 168C BA4E
> >>
> >>
> >> > hi all
> >> > i've Question here. I am interested in using your software .. and it
> >> > looks
> >> > really good to me.
> >> > from past 5 years we've been mantaining IP Schema of our organizatio=
n
> >> on
> >> > Excel sheets. Which have similar Colmns as " IP MASK DESCRIPTION
> >> CLIENT
> >> > and your software also generates output in same format. So is there
> >> any
> >> > possbility that i can import that IP Schema from Excel to this
> >> software???
> >> > bcos there are around 8000 IP adressse and it will be real pain to
> >> > re-enter
> >> > that manually..
> >> > And also. In search page is it possible to include Search in ANY
> >> field?
> >> > Waiting for response.
> >> > -Geni.
> >> >
> >>
> >>
> >
>
>
>