hi thanks for your reply. I have been familiarizing myself with some concepts that should be used with DBs and Perl. I also found this piece of code that I can use as basis to do mine. I believe this code (from my limited perl understanding) extracts the data and updates the DB. I am only meant to be extracting data from the 'LES' and 'Twenty' databases and find the differences. From what I have been informed that the term 'point_to_twenty' is the old term for 'LES'. Also i think this code is only concerned with the 'LES' DB. I am very confused... Here is the code below, thank you in advance
#!perl
my $DEFAULT_TWENTY_SITE_UID = -1;
# to compile application
# ppm install DBD-Oracle
#perlapp --add=DBD::Oracle --add=DBI --dependent rtf.pl
# requires module: DBD-Oracle
#declare all the mods required
use DBI;
use DBD::Oracle;
use Getopt::Std;
use strict;
#my indicates that the variables are private to a function to prevent
+them from gettting overwritten
my $implementation = "";
my %cfg;
#process the config file which is the input file and check that it ope
+ns correctly
sub ProcessConfigFile {
my ($configFile) = @_;
my $cantOpenFile = 0;
open(FILEIN, $configFile) or $cantOpenFile = 1;
if ($cantOpenFile == 1) {
logger("ERROR", "Can't open $configFile\n");
exit;
}
#log files present their data in a special format. they allow one to m
+ake the distinction between stateful and statelss data. the entries i
+n the log file need to be connected/associated in some way ie. assign
+ing a PK
my @logData = <FILEIN>;
#declare a private variable 'line'
my $line;
foreach $line (@logData) {
#chomp gets rid of any new line character that is read in from the inp
+ut file
chomp($line);
if ($line !~ /\s.\#/) {
$line =~ /(\S+)\s+(\S+)/;
my $var = $1;
my $val = $2;
if ($var =~ /LES_USER/) {
$cfg{LES_USER} = $val;
} elsif ($var =~ /LES_PSWD/) {
$cfg{LES_PSWD} = $val;
} elsif ($var =~ /LES_SERVER/) {
$cfg{LES_SERVER} = $val;
} elsif ($var =~ /LES_PORT/) {
$cfg{LES_PORT} = $val;
} elsif ($var =~ /LES_SID/) {
$cfg{LES_SID} = $val;
}
}
}
close(FILEIN);
#checking and verifying the username and password
if (length ($cfg{LES_USER}) == 0) { print ("ERROR: ", "No LES_USER
+ Specified in config file.\n"); }
if (length ($cfg{LES_PSWD}) == 0) { print ("ERROR: ", "No LES_PSWD
+ Specified in config file.\n"); }
if (length ($cfg{LES_SERVER}) == 0) { print ("ERROR: ", "No LES_SE
+RVER Specified in config file.\n"); }
if (length ($cfg{LES_PORT}) == 0) { print ("ERROR: ", "No LES_PORT
+ Specified in config file.\n"); }
if (length ($cfg{LES_SID}) == 0) { print ("ERROR: ", "No LES_SID S
+pecified in config file.\n"); }
}
######################################################################
+#####
# connect_point_to_TWENTY
#
# inputs
#
# returns
#
# Algorithm
#
######################################################################
+#####
sub connect_point_db
{
my ($server, $port, $sid, $user, $passwd) = @_;
my ($dbh);
$dbh = DBI->connect("dbi:Oracle:host=$server;port=$port;sid=$sid",
+ $user, $passwd);
$dbh->{LongReadLen} = 1000;
$dbh->{LongTruncOk} = 1;
return $dbh;
}
sub create_from_sql
{
my ($sql, $name, $dbh) = @_;
my ($sth, $row);
$sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute;
my $MAX_ROWS = 100;
my $rows = []; # cache for batches of rows
my $cols = []; # cache for binded columns
my $i;
for ($i=1; $i<=$sth->{NUM_OF_FIELDS}; $i++) {
$sth->bind_col($i, \@$cols[$i]);
}
#fetch a sigle row at a time
while($row=$sth->fetchrow_hashref) {
my $i;
print OUTFILE "\t<$name>\n";
for ( $i = 1 ; $i <= $sth->{NUM_OF_FIELDS} ; $i++ ) {
my $fieldName = $sth->{NAME}->[$i-1];
my $fieldValue = @$cols[$i]; #$row->{$fieldName};
$fieldValue =~ s/&/&/g;
$fieldValue =~ s/'/'/g;
$fieldValue =~ s/"/"/g;
$fieldValue =~ s/</</g;
$fieldValue =~ s/>/>/g;
$fieldValue =~ s/\x96/\x39/g; #replace ` (non xml char) wit
+h '
$fieldValue =~ s/[\x0-\x1f]||[\xa0-\xff]//g; #remove any no
+n xml characters
print OUTFILE "\t\t<$fieldName>$fieldValue</$fieldName>\n";
}
print OUTFILE "\t</$name>\n\n";
}
$sth->finish;
}
sub main {
my ($impl) = @_;
$implementation = $impl;
my $numArgs = $#ARGV + 1;
if (!($numArgs == 3 || $numArgs == 4)) {
print "point to file: Extracts data from a point (LES) databas
+e to a file for either MCC or TWENTY Import\n";
print "rtdf = point To TWENTY File\n";
print "rtcf = point To MCC File\n";
print "Usage: rtcf|rtdf configFile.cfg fileName.xml timezone_o
+ffset [company]\n";
exit(0);
}
ProcessConfigFile($ARGV[0]);
my $server = $cfg{LES_SERVER};
my $port = $cfg{LES_PORT};
my $sid = $cfg{LES_SID};
my $user = $cfg{LES_USER};
my $passwd = $cfg{LES_PSWD};
my $file = $ARGV[1];
my $d_dbh = connect_point_db($server, $port, $sid, $user, $passwd)
+;
# my $siteValue = "datacenters.dc_id";
# my $companyValue = "business.business_ID";
my $companyCondition = "";
my $includeCompaniesAndSites;
if ($numArgs == 4) {
$includeCompaniesAndSites = 1;
my @companies = split(/,/, $ARGV[3]);
$companyCondition = "";
foreach my $company(@companies) {
#print "$company\n";
if ($companyCondition eq "") {
$companyCondition = "AND ((business.business_name = $c
+ompany)"
}
else {
$companyCondition = $companyCondition . "OR (business.
+business_name = $company)";
}
}
if ($companyCondition ne "") {
$companyCondition = $companyCondition . ")";
}
}
if (($implementation eq "TWENTY") && ($includeCompaniesAndSites !=
+ 1)) {
$siteValue = $DEFAULT_TWENTY_SITE_UID;
}
my $devicesql = "SELECT distinct(server.system_id) masterid, serv
+er.primary_name hostname, server.arpa_domain domainname,
server.os_type os, datacenters.dc_id as sit
+e,
business.business_id as company,datacenters
+.dc_timezone as timezone,
'nnmserver1.mydomain.com' as PrimaryNNM, 'n
+nmserver1.mydomain.com' as SecondaryNNM, 'itoserver1.mydomain.com' as
+ PrimaryITO, 'itoserver1.mydomain.com' as SecondaryITO,
server.status as ServiceStatus, '24x7' as S
+erviceWindow, server.mgmt_region as Region, 'N/A' as HardwareSupport
+, '0' as RadiaManaged, nvl(monlvl, 0) as EventNotify, TO_CHAR(prod_da
+te, 'YYYY-MM-DD hh:mm:ss') as DateProduction, TO_CHAR(decomm_date, 'Y
+YYY-MM-DD hh:mm:ss') as DateObsolete, '1' as Reported, '$server' as M
+asteredBy,
ip_name, nvl(substr(ip_name, 0, instr(ip_na
+me, '.', 1)-1), ip_name) as NatHostName,
substr(ip_name, instr(ip_name, '.', 1)+1) a
+s NatDomainName
FROM LES.server,
LES.datacenters,
LES.server_customer,
LES.business,
LES.status_dates,
(select * from LES.ip_info where ip_type =
+'NAT') ip_info,
(select '1' as monlvl, system_id from LES.s
+rs ml where ml.par_cd = 'MON_LEVEL' and ml.SRS_VALUE <> 'None') ml
WHERE (ml.system_id(+) = server.system_id)
AND (datacenters.dc_id = server.dc_id)
AND (server.system_id = server_customer.sy
+stem_id)
AND (business.business_id = server_custome
+r.business_id)
AND (ip_info.system_id(+) = server.system_
+id)
AND (server.system_type = 'server')
AND (status_dates.system_id = server.syste
+m_id)
AND (server.mgmt_region = 'Asia Pacific')
AND (monlvl = 1)";
my $tzsql = "SELECT unique(DC_TIMEZONE) as display, DC_TIMEZO
+NE as displaySTD, TO_NUMBER(NVL(substr(DC_TIMEZONE, 4, 5), '0')) * 60
+ as bias
FROM LES.datacenters";
my $regionsql = "SELECT unique(region) FROM ($devicesql) devices"
+;
# my $devicetypesql = "";
my $ossql = "SELECT unique(os) FROM ($devicesql) devices";
# my $hwssql = "";
my $ptsql = "SELECT unique(PlatformType) FROM ($devicesql) d
+evices";
# my $svcwinsql = "SELECT SvcWinDesc from tblSvcWindow";
### limit compaines to specified companies to stop companies which
+ are linked to shared sites from being imported which will cause prob
+lems when switching to all device import
my $companysql = "SELECT distinct(business_id) as masterid, SUB_BU
+SINESS_NAME as Company, 'nnmserver1.mydomain.com' as PrimaryNNM, 'nn
+mserver1.mydomain.com' as SecondaryNNM, 'itoserver1.mydomain.com' as
+PrimaryITO, 'itoserver1.mydomain.com' as SecondaryITO,
'1' as MCCMaster, '1' as MCCProduction
+
FROM LES.business,
($devicesql) devices
WHERE devices.company = business.business_id
$companyCondition";
### link sites to devices because sites that are shared between co
+mpanies will need to be imported
my $sitesql = "SELECT distinct(datacenters.dc_id) as MasterID,
+datacenters.dc_name as Site, datacenters.dc_country as Country,
datacenters.dc_timezone as TimeZone, data
+centers.dc_address as Address, '24x7' as SvcWin, decode(nvl(datacente
+rs.business_name, '-1'), '-1', '-1', devices.company) as Company
FROM LES.datacenters,
($devicesql) devices
WHERE devices.site = datacenters.dc_id";
my $countrysql = "SELECT country_def.country, country_def.c_region
+ as Region
FROM LES.country_def
WHERE (country_def.c_region = 'Asia Pacific')";
# my $mgmtsvrsql = "SELECT distinct(ito_server) as mgmtServerName,
+'ITO' as mgmtServerFunction, '1' as AutoLinkDiscovery,
# '1' as ActiveNode, '1' as AutoInterfaceD
+iscovery, '0' as AllowSecondaryFailure
# FROM LES.server,
# LES.ovo_info
# WHERE (ovo_info.system_id = server.system_id)";
my $computersql = "SELECT distinct(concat(concat(hostname,'.'),dom
+ainname)) as fqdn ,'unknown' as PlatformType
FROM ($devicesql) devices";
my $dbsql = "SELECT distinct(concat(concat(hostname,'.'),dom
+ainname)) as fqdn, ios.instance_name as databasetype, ins_instance_na
+me as DBIdentifier, ins_instance_name as DatabaseName, instance_avail
+ability as SLAuptime,
'HA' as dbslatype, '24x7' as SvcWinDesc,
+ ios.instance_id as MasterID
FROM LES.instance_on_server ios,
LES.instance_definition id,
($devicesql) devices
WHERE (category = 'database')
AND (ios.instance_name = id.instance_name)
AND (devices.masterid = ios.system_id)
AND (ios.ins_instance_name is not null)";
my $dbtypesql = "SELECT distinct databasetype from ($dbsql) dbs";
my $timezone_offset = $ARGV[2];
if (!($timezone_offset < 0 || $timezone_offset > 0 || $timezone_of
+fset == 0)) { #if $timezone_offset is not a number
$timezone_offset = 0;
print "Timezone offset is invalid or not specified. Defaulting
+ export times to GMT\n";
}
my $outages = "SELECT concat(concat(hostname,'.'),domainname) as f
+qdn, TO_CHAR(startdate, 'YYYY-MM-DD hh:mm:ss') as startdt, TO_CHAR(e
+nddate, 'YYYY-MM-DD hh:mm:ss') as enddt, message as SUPpurpose, 'once
+off' as frequency, '2' as headingID, TO_CHAR(startdate, 'HH24') as st
+artdayhour, TO_CHAR(startdate, 'MI') as starthourmin, TO_CHAR(enddate
+, 'HH24') as enddayhour, TO_CHAR(enddate, 'MI') as endhourmin, infobo
+ard.message_id as MasterID
FROM (select message_id, message, timezone, (end_
+date-($timezone_offset/24)+(nvl(to_number(replace(infoboard.timezone,
+'GMT','')),0)/24)) as enddate, (start_date-($timezone_offset/24)+(nvl
+(to_number(replace(infoboard.timezone,'GMT','')),0)/24)) as startdate
+ from LES.infoboard) infoboard,
LES.message_on_system,
($devicesql) devices
WHERE devices.masterid = message_on_system.system_
+id
AND infoboard.message_id = message_on_system.mess
+age_id
AND infoboard.enddate >= (SYSDATE-(1/24)+(nvl(-1+
+to_number(replace(infoboard.timezone,'GMT','')),0)/24))";
open(OUTFILE, ">$file");
print OUTFILE "<$implementation>\n";
#create_from_sql($mgmtsvrsql, "ManagementServer", $d_dbh);
create_from_sql($tzsql, "TimeZone", $d_dbh);
#create_from_sql($regionsql, "Region", $d_dbh);
create_from_sql($countrysql, "Country", $d_dbh);
#create_from_sql($devicetypesql, "DeviceType", $d_dbh);
#create_from_sql($ptsql, "PlatformType", $d_dbh);
#create_from_sql($ossql, "OperatingSystem", $d_dbh);
#create_from_sql($hwssql, "HardwareSupport", $d_dbh);
#create_from_sql($svcwinsql, "ServiceWindow", $d_dbh);
if ($implementation ne "TWENTY" || $includeCompaniesAndSites) {
create_from_sql($companysql, "Company", $d_dbh);
create_from_sql($sitesql, "Site", $d_dbh);
}
create_from_sql($devicesql, "Device", $d_dbh);
create_from_sql($computersql, "Computer", $d_dbh);
create_from_sql($dbtypesql, "DatabaseType", $d_dbh);
create_from_sql($dbsql, "Database", $d_dbh);
if ($implementation eq "TWENTY") {
create_from_sql($outages, "Outages", $d_dbh);
}
print OUTFILE "</$implementation>\n";
close(OUTFILE);
print "Completed Successfully.\n";
}
return 1;
|