monitoring/PerlModules/NP/OracleDB/NPRecords.pm
| 37 +++++-----
monitoring/SatConfig/bootstrap-server/Bootstrap.pm
| 10 +-
monitoring/SatConfig/generator/GenerateConfig.pm
| 16 ++--
schema/spacewalk/common/views/rhn_host_monitoring.sql
| 10 +-
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/095-rhn_host_monitoring.sql
| 24 ++++++
5 files changed, 64 insertions(+), 33 deletions(-)
New commits:
commit 4c9e1707d1add19b132229e959b2a657002c5308
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Sep 30 10:28:20 2011 +0200
741782 - subquery in FROM must have an alias
fixing
ERROR: subquery in FROM must have an alias
LINE 3: from (
^
HINT: For example, FROM (SELECT ...) [AS] foo.
diff --git a/monitoring/SatConfig/generator/GenerateConfig.pm
b/monitoring/SatConfig/generator/GenerateConfig.pm
index df70e4d..b36eee3 100644
--- a/monitoring/SatConfig/generator/GenerateConfig.pm
+++ b/monitoring/SatConfig/generator/GenerateConfig.pm
@@ -145,7 +145,7 @@ sub synch_nolog {
where check_probe.probe_id = probe.recid
and check_probe.sat_cluster_id = ?
and host.recid = check_probe.host_id
- )
+ ) X
};
$sth = CSDBRecord->DoSql($sql, $sat_cluster_id);
};
commit f6fd33d8b1856cdf66c9ca8bf0ee9341440bc8c7
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Sep 30 10:28:20 2011 +0200
741782 - upgrade script for rhn_host_monitoring
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/095-rhn_host_monitoring.sql
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/095-rhn_host_monitoring.sql
new file mode 100644
index 0000000..6123c99
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/095-rhn_host_monitoring.sql
@@ -0,0 +1,24 @@
+drop view rhn_host_monitoring;
+create or replace view rhn_host_monitoring
+(
+ recid,
+ ip,
+ name,
+ description,
+ customer_id,
+ os_id,
+ asset_id,
+ last_update_user,
+ last_update_date
+) as
+select s.id as recid,
+ rhn_server.get_ip_address(s.id) as ip,
+ s.name as name,
+ s.description as description,
+ s.org_id as customer_id,
+ 4 as os_id,
+ to_number(null,null) as asset_id,
+ cast(null as char) as last_update_user,
+ cast(null as date) as last_update_date
+from rhnServer s
+;
commit 025dbb54979af7a769d0edabc1aa6794486a18f4
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Sep 30 10:28:20 2011 +0200
741782 - fixed data types in view
fixing
"ERROR: failed to find conversion function from unknown to numeric"
STATEMENT:
insert into rhn_deployed_probe(recid, probe_type,
description, customer_id, command_id, contact_group_id, notify_critical,
notify_warning, notify_recovery, notify_unknown, notification_interval_minutes,
check_interval_minutes, retry_interval_minutes, max_attempts, last_update_user,
last_update_date, sat_cluster_id, os_id)
select recid, probe_type, description,
customer_id, command_id, contact_group_id, notify_critical, notify_warning,
notify_recovery, notify_unknown, notification_interval_minutes,
check_interval_minutes, retry_interval_minutes, max_attempts, last_update_user,
last_update_date, sat_cluster_id, os_id
from (
select probe.recid, probe.probe_type,
probe.description, probe.customer_id, probe.command_id, probe.contact_group_id,
probe.notify_critical, probe.notify_warning, probe.notify_recovery,
probe.notify_unknown, probe.notification_interval_minutes,
probe.check_interval_minutes, probe.retry_interval_minutes, probe.max_attempts,
probe.last_update_user, probe.last_update_date, check_probe.sat_cluster_id,
host.os_id
from rhn_probe as probe,
rhn_check_probe as check_probe, rhn_host_monitoring as host
where check_probe.probe_id = probe.recid
and check_probe.sat_cluster_id = $1
and host.recid = check_probe.host_id
) SUB
But it should be:
insert into rhn_deployed_probe(recid, probe_type, description, customer_id,
command_id, contact_group_id, notify_critical, notify_warning, notify_recovery,
notify_unknown, notification_interval_minutes, check_interval_minutes,
retry_interval_minutes, max_attempts, last_update_user, last_update_date,
sat_cluster_id, os_id)
select recid::numeric, probe_type::text,
description::text, customer_id::numeric, command_id::numeric,
contact_group_id::numeric, notify_critical::text, notify_warning::text,
notify_recovery::text, notify_unknown::text,
notification_interval_minutes::numeric, check_interval_minutes::numeric,
retry_interval_minutes::numeric, max_attempts::numeric, last_update_user::text,
last_update_date::timestamp, sat_cluster_id::numeric, os_id::numeric
from (
select probe.recid::numeric,
probe.probe_type::text, probe.description::text, probe.customer_id::numeric,
probe.command_id::numeric, probe.contact_group_id::numeric,
probe.notify_critical::text, probe.notify_warning::text,
probe.notify_recovery::text, probe.notify_unknown::text,
probe.notification_interval_minutes::numeric,
probe.check_interval_minutes::numeric, probe.retry_interval_minutes::numeric,
probe.max_attempts::numeric, probe.last_update_user::text,
probe.last_update_date::timestamp, check_probe.sat_cluster_id::numeric,
host.os_id::text
from rhn_probe as probe,
rhn_check_probe as check_probe, rhn_host_monitoring as host
where check_probe.probe_id = probe.recid
and check_probe.sat_cluster_id = 1
and host.recid = check_probe.host_id
) SUB
diff --git a/schema/spacewalk/common/views/rhn_host_monitoring.sql
b/schema/spacewalk/common/views/rhn_host_monitoring.sql
index 1b1e5e3..2478525 100644
--- a/schema/spacewalk/common/views/rhn_host_monitoring.sql
+++ b/schema/spacewalk/common/views/rhn_host_monitoring.sql
@@ -1,5 +1,5 @@
--
--- Copyright (c) 2008 Red Hat, Inc.
+-- Copyright (c) 2008--2011 Red Hat, Inc.
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
@@ -34,10 +34,10 @@ select s.id as recid,
s.name as name,
s.description as description,
s.org_id as customer_id,
- '4' as os_id,
- null as asset_id,
- null as last_update_user,
- null as last_update_date
+ 4 as os_id,
+ to_number(null,null) as asset_id,
+ cast(null as char) as last_update_user,
+ cast(null as date) as last_update_date
from rhnServer s
;
commit 475bd021f86a714d630401e66d10421b2e12dc6e
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Sep 30 08:59:04 2011 +0200
741782 - replaced aliases with table names
diff --git a/monitoring/PerlModules/NP/OracleDB/NPRecords.pm
b/monitoring/PerlModules/NP/OracleDB/NPRecords.pm
index ab0058c..069ca0d 100644
--- a/monitoring/PerlModules/NP/OracleDB/NPRecords.pm
+++ b/monitoring/PerlModules/NP/OracleDB/NPRecords.pm
@@ -117,7 +117,9 @@ sub LoadForSatellite
SELECT $probe_col_str, /* Checks */
check_probe.sat_cluster_id as netsaint_id,
$host_col_str
- FROM probe, check_probe, host
+ FROM rhn_probe probe,
+ rhn_check_probe check_probe,
+ rhn_host_monitoring host
WHERE check_probe.probe_id = probe.recid
AND check_probe.host_id = host.recid
AND check_probe.sat_cluster_id = ?
@@ -129,7 +131,7 @@ sub LoadForSatellite
$class->InstanceCount > 0 or return 0;
# Fetch OS IDs and names for later mapping
- CFDBRecord->LoadFromSql("SELECT recid, os_name FROM os", 'RECID');
+ CFDBRecord->LoadFromSql("SELECT recid, os_name FROM rhn_os",
'RECID');
my $os = CFDBRecord->Instances;
CFDBRecord->ReleaseAllInstances;
@@ -184,8 +186,8 @@ sub LoadForSatellite
m.snmp_host,
m.snmp_port,
m.sender_sat_cluster_id
- FROM contact_groups g, contact_methods m,
- contact_group_members map
+ FROM rhn_contact_groups g, rhn_contact_methods m,
+ rhn_contact_group_members map
WHERE map.contact_group_id = g.recid
AND map.member_contact_method_id = m.recid
AND g.recid in (".join(', ',
@placeholder).')';
@@ -211,7 +213,7 @@ sub LoadForSatellite
# Now %snmpdests contains a list of SNMP destinatios for each
# contact group.
$sql = "SELECT recid,contact_group_name,customer_id
- FROM contact_groups
+ FROM rhn_contact_groups
WHERE recid in (".join(', ', @placeholder).")";
CFDBRecord->LoadFromSqlWithBind($sql, \@contactGroupIds, 'RECID');
$class->Map(
@@ -249,26 +251,26 @@ sub LoadForSatellite
# Fetch service probes and host probes for all sats/scouts ...
my $probeSubquery = "
SELECT probe_id
- FROM host_probe
+ FROM rhn_host_probe
WHERE sat_cluster_id = ?
UNION
SELECT probe_id
- FROM check_probe
+ FROM rhn_check_probe
WHERE sat_cluster_id = ?
UNION
SELECT probe_id
- FROM sat_cluster_probe
+ FROM rhn_sat_cluster_probe
WHERE sat_cluster_id = ?
UNION
SELECT probe_id
- FROM url_probe
+ FROM rhn_url_probe
WHERE sat_cluster_id = ?
";
# Load up the commands table and add the command group name to the probe records
CommandRecord->LoadFromSqlWithBind('
select c.recid, c.name, c.description, c.command_class, c.group_name
- from command c, probe p
+ from rhn_command c, rhn_probe p
where p.command_id = c.recid
and p.recid in ('.$probeSubquery.')',
[$satClusterId, $satClusterId, $satClusterId, $satClusterId],
@@ -290,7 +292,7 @@ sub LoadForSatellite
CommandMetricRecord->LoadFromSqlWithBind("
select m.command_class, m.metric_id, m.label, m.description,
u.unit_label, u.description as unit_description
- from metrics m, units u, command c, probe p
+ from rhn_metrics m, rhn_units u, rhn_command c, rhn_probe p
where m.command_class = c.command_class
and m.storage_unit_id = u.unit_id
and p.command_id = c.recid
@@ -303,7 +305,7 @@ sub LoadForSatellite
select cp.command_id, cp.param_name, cp.param_type, cp.mandatory,
cp.description,
command.command_class,
'NA' as threshold_type_name, 'NA' as
threshold_metric_id
- from command_parameter cp, probe p, command
+ from rhn_command_parameter cp, rhn_probe p, rhn_command command
where cp.param_type = 'config'
and p.command_id = cp.command_id
and p.recid in ($probeSubquery)
@@ -312,7 +314,8 @@ sub LoadForSatellite
select cp.command_id, cp.param_name, cp.param_type, cp.mandatory,
cp.description,
command.command_class,
ct.threshold_type_name, ct.threshold_metric_id
- from command_parameter cp, command_parameter_threshold ct, probe p, command
+ from rhn_command_parameter cp, rhn_command_parameter_threshold ct,
+ rhn_probe p, rhn_command command
where cp.param_type = 'threshold'
and p.command_id = cp.command_id
and ct.command_id = cp.command_id
@@ -326,7 +329,7 @@ sub LoadForSatellite
# Load the probe parameter values.
ProbeParamValueRecord->LoadFromSqlWithBind('
select probe_id, command_id, param_name, value
- from probe_param_value
+ from rhn_probe_param_value
where probe_id in ('.$probeSubquery.')',
[$satClusterId, $satClusterId, $satClusterId, $satClusterId],
('PROBE_ID', 'PARAM_NAME'));
@@ -456,7 +459,7 @@ sub LoadAll
my ($class) = @_;
$class->LoadFromSql("
select recid, name, description, command_class, group_name, for_host_probe
- from command", 'RECID');
+ from rhn_command", 'RECID');
}
############################################
@@ -504,7 +507,7 @@ sub LoadForSatCluster
# Fetch service probes and host probes for all sats/scouts ...
my $alertquery = "
SELECT *
- FROM snmp_alert
+ FROM rhn_snmp_alert
WHERE sender_cluster_id = $satClusterId";
# Just Do Me
@@ -530,7 +533,7 @@ sub ClearForSatCluster
# Delete SNMP alerts for $satClusterId up to and including $lastRecid
my $alertquery = "
- DELETE from snmp_alert
+ DELETE from rhn_snmp_alert
WHERE sender_cluster_id = $satClusterId
AND recid <= $lastRecid";
diff --git a/monitoring/SatConfig/generator/GenerateConfig.pm
b/monitoring/SatConfig/generator/GenerateConfig.pm
index c077cf5..df70e4d 100644
--- a/monitoring/SatConfig/generator/GenerateConfig.pm
+++ b/monitoring/SatConfig/generator/GenerateConfig.pm
@@ -141,7 +141,7 @@ sub synch_nolog {
select $sel_probe_cols, check_probe.sat_cluster_id, host.os_id
from rhn_probe probe,
rhn_check_probe check_probe,
- rhn_host host
+ rhn_host_monitoring host
where check_probe.probe_id = probe.recid
and check_probe.sat_cluster_id = ?
and host.recid = check_probe.host_id
commit b28339716a873ec082c6997202478c24b50bce31
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Sep 30 08:59:04 2011 +0200
741782 - replaced aliases with table names
diff --git a/monitoring/SatConfig/generator/GenerateConfig.pm
b/monitoring/SatConfig/generator/GenerateConfig.pm
index f7c77c7..c077cf5 100644
--- a/monitoring/SatConfig/generator/GenerateConfig.pm
+++ b/monitoring/SatConfig/generator/GenerateConfig.pm
@@ -27,7 +27,9 @@ sub handler {
location_name as physical_location_name,
sat_node.max_concurrent_checks, sat_node.sched_log_level,
sat_node.sput_log_level, sat_node.dq_log_level
- from sat_cluster, sat_node, physical_location
+ from rhn_sat_cluster sat_cluster,
+ rhn_sat_node sat_node,
+ rhn_physical_location physical_location
where sat_cluster.recid = sat_node.sat_cluster_id
and sat_cluster.physical_location_id = physical_location.recid
and sat_cluster.recid = ?
@@ -46,7 +48,7 @@ sub handler {
# Get the auto_update flag
CFDBRecord->LoadFromSqlWithBind(q{
select recid, auto_update
- from customer
+ from rhn_customer_monitoring
where recid = ?
},
[$customerId], 'RECID');
@@ -112,7 +114,7 @@ sub synch_nolog {
my $sth;
eval {
$sth = CSDBRecord->DoSql(q{
- delete from deployed_probe
+ delete from rhn_deployed_probe
where sat_cluster_id = ?
or probe_type = 'url'
},
@@ -133,11 +135,13 @@ sub synch_nolog {
eval {
my $sql = qq{
- insert into deployed_probe($ins_probe_cols, sat_cluster_id, os_id)
+ insert into rhn_deployed_probe($ins_probe_cols, sat_cluster_id, os_id)
select $ins_probe_cols, sat_cluster_id, os_id
from (
select $sel_probe_cols, check_probe.sat_cluster_id, host.os_id
- from probe, check_probe, host
+ from rhn_probe probe,
+ rhn_check_probe check_probe,
+ rhn_host host
where check_probe.probe_id = probe.recid
and check_probe.sat_cluster_id = ?
and host.recid = check_probe.host_id
commit 7d3a88353f57ef9657853f69ab53cc499997dd7a
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Sep 30 09:05:52 2011 +0200
741782 - replaced ll_netsaint alias with table name
diff --git a/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
b/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
index 2e7de57..966b603 100644
--- a/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
+++ b/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
@@ -66,7 +66,7 @@ sub handler
my $satid = $satrec->get_RECID;
my $scout_shared_key = $satrec->get_SCOUT_SHARED_KEY;
my $clustrec = SatClusterRecord->LoadOneFromSql("select
recid,description,customer_id from rhn_sat_cluster where recid =
'$clustid'");
- my $isll = CFDBRecord->LoadOneFromSql("select netsaint_id from ll_netsaint where
netsaint_id = '$clustid'") ? 1 : 0;
+ my $isll = CFDBRecord->LoadOneFromSql("select netsaint_id from rhn_ll_netsaint
where netsaint_id = '$clustid'") ? 1 : 0;
CFDBRecord->ReleaseAllInstances;
# Store the satellite public key if one was provided
commit 604e5374f8f31d0a1cb327ed5dc9f8eb5fd21756
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Sep 30 09:05:11 2011 +0200
741782 - replaced sat_cluster alias with table name
diff --git a/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
b/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
index f86f2e7..2e7de57 100644
--- a/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
+++ b/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
@@ -65,17 +65,17 @@ sub handler
my $clustid = $satrec->get_SAT_CLUSTER_ID;
my $satid = $satrec->get_RECID;
my $scout_shared_key = $satrec->get_SCOUT_SHARED_KEY;
- my $clustrec = SatClusterRecord->LoadOneFromSql("select
recid,description,customer_id from sat_cluster where recid = '$clustid'");
+ my $clustrec = SatClusterRecord->LoadOneFromSql("select
recid,description,customer_id from rhn_sat_cluster where recid =
'$clustid'");
my $isll = CFDBRecord->LoadOneFromSql("select netsaint_id from ll_netsaint where
netsaint_id = '$clustid'") ? 1 : 0;
CFDBRecord->ReleaseAllInstances;
# Store the satellite public key if one was provided
if (defined($key)) {
- CFDBRecord->DatabaseConnection->prepare("update sat_cluster set
public_key = '$key' where recid = $clustid")->execute;
+ CFDBRecord->DatabaseConnection->prepare("update rhn_sat_cluster set
public_key = '$key' where recid = $clustid")->execute;
CFDBRecord->Commit;
}
if (defined($cert)) {
- CFDBRecord->DatabaseConnection->prepare("update sat_cluster set
pem_public_key = '$cert', pem_public_key_hash = '$certHash' where recid =
$clustid")->execute;
+ CFDBRecord->DatabaseConnection->prepare("update rhn_sat_cluster set
pem_public_key = '$cert', pem_public_key_hash = '$certHash' where recid =
$clustid")->execute;
CFDBRecord->Commit;
}
commit ff8c6378fcfbf310270713eec30d881f7548aadd
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Sep 30 08:59:04 2011 +0200
741782 - replaced alias with table name
diff --git a/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
b/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
index c87c23b..f86f2e7 100644
--- a/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
+++ b/monitoring/SatConfig/bootstrap-server/Bootstrap.pm
@@ -56,7 +56,7 @@ sub handler
my $satrec;
if (defined($ssk)) {
- $satrec = SatNodeRecord->LoadOneFromSql("select
recid,ip,sat_cluster_id,scout_shared_key from sat_node where scout_shared_key =
'$ssk'");
+ $satrec = SatNodeRecord->LoadOneFromSql("select
recid,ip,sat_cluster_id,scout_shared_key from rhn_sat_node where scout_shared_key =
'$ssk'");
if (! defined($satrec)) {
return &Reject($request, "Couldn't look up record for Scout Shared Key
$ssk: $@");
}