Hello,
When creating a custom Ad Hoc Report I get the following error:
Exception executing database query. Please contact your administrator. See application log for additional details. 42S02 (conn=152057) Table 'ca_uim.vn' doesn't exist
I have tried the fix in here :
CABI Ad Hoc Report Error with Oracle backend database, ORA-00942 , but it does not work.
My database is MySQL 5.7, commercial version. Is there a fix for MySQL?
UIM version is 20.3.3 with June patch. uim_core_dashboards_pack is version 2.46 .
jaspersoft.log shows:
2021-07-20T16:14:07,667 ERROR AdhocAjaxController,https-jsse-nio-443-exec-10:1041 - ad hoc controller exception: Exception executing database query. Please contact your administrator. See application log for additional details. 42S02 (conn=152057) Table 'ca_uim.vn' doesn't exist com.jaspersoft.jasperserver.api.CacheDatasetException: exception getting dataset from cache
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : null sql : 'select avg(vn.samplevalue) as sample_value,
FROM_UNIXTIME(CEILING(UNIX_TIMESTAMP(vn.sampletime)/(15*60))*(15*60)) as sample_time,
ccs.name as cs_name,
ccs.cs_id as cs_id,
cg.name as grp_name,
cg.grp_id as grp_id,
cci.ci_name as ci_name,
cci.ci_id as ci_id,
ccimd.met_description as met_description,
ccimd.unit_type as unit_type,
ccimd.met_type as met_type,
sqd.probe as probe,
sqd.origin as origin,
sqd.qos as qos
from VN_QOS_DATA_0006 vn
join s_qos_data sqd on vn.table_id = sqd.table_id and sqd.qos IN ('QOS_VMWARE_VM_GUEST_DISK_FREE_PERCENT','QOS_DISK_SIZE')
AND sqd.r_table = REPLACE('VN_QOS_DATA_0006', 'VN_', 'RN_')
JOIN S_QOS_SNAPSHOT sqs ON sqd.table_id = sqs.table_id AND sqs.sampletime >= DATE_ADD(NOW(), INTERVAL -24 HOUR)
join cm_configuration_item_metric ccim on sqd.ci_metric_id = ccim.ci_metric_id
and ccim.ci_metric_type IN ('1.1:14','1.1:9')
join cm_configuration_item_metric_definition ccimd on ccim.ci_metric_type = ccimd.met_type
join cm_configuration_item cci on ccim.ci_id = cci.ci_id
join cm_device cd on cci.dev_id = cd.dev_id
join cm_computer_system ccs on cd.cs_id = ccs.cs_id and ccs.cs_id IN (select distinct cs_id from CM_GROUP_MEMBER where grp_id = 6)
join cm_group_member cgm on ccs.cs_id = cgm.cs_id and cgm.grp_id = 6
join cm_group cg on cgm.grp_id = cg.grp_id
where vn.samplevalue != -987654321
AND vn.sampletime >= DATE_ADD(NOW(), INTERVAL -24 HOUR)
Group By
FROM_UNIXTIME(CEILING(UNIX_TIMESTAMP(vn.sampletime)/(15*60))*(15*60)),
ccs.name,
ccs.cs_id,
cg.name,
cg.grp_id,
cci.ci_name,
cci.ci_id,
ccimd.met_description,
ccimd.unit_type,
ccimd.met_type,
sqd.probe,
sqd.origin,
sqd.qos
union
select avg(vn.samplevalue) as sample_value,
FROM_UNIXTIME(CEILING(UNIX_TIMESTAMP(vn.sampletime)/(15*60))*(15*60)) as sample_time,
ccs.name as cs_name,
ccs.cs_id as cs_id,
cg.name as grp_name,
cg.grp_id as grp_id,
cci.ci_name as ci_name,
cci.ci_id as ci_id,
ccimd.met_description as met_description,
ccimd.unit_type as unit_type,
ccimd.met_type as met_type,
sqd.probe as probe,
sqd.origin as origin,
sqd.qos as qos
from vn
join s_qos_data sqd on vn.table_id = sqd.table_id and sqd.qos IN ('QOS_VMWARE_VM_GUEST_DISK_FREE_PERCENT','QOS_DISK_SIZE')
AND sqd.r_table = REPLACE('', 'VN_', 'RN_')
JOIN S_QOS_SNAPSHOT sqs ON sqd.table_id = sqs.table_id AND sqs.sampletime >= DATE_ADD(NOW(), INTERVAL -24 HOUR)
join cm_configuration_item_metric ccim on sqd.ci_metric_id = ccim.ci_metric_id
and ccim.ci_metric_type IN ('1.1:14','1.1:9')
join cm_configuration_item_metric_definition ccimd on ccim.ci_metric_type = ccimd.met_type
join cm_configuration_item cci on ccim.ci_id = cci.ci_id
join cm_device cd on cci.dev_id = cd.dev_id
join cm_computer_system ccs on cd.cs_id = ccs.cs_id and ccs.cs_id IN (select distinct cs_id from CM_GROUP_MEMBER where grp_id = 6)
join cm_group_member cgm on ccs.cs_id = cgm.cs_id and cgm.grp_id = 6
join cm_group cg on cgm.grp_id = cg.grp_id
where vn.samplevalue != -987654321
AND vn.sampletime >= DATE_ADD(NOW(), INTERVAL -24 HOUR)
Group By
FROM_UNIXTIME(CEILING(UNIX_TIMESTAMP(vn.sampletime)/(15*60))*(15*60)),
ccs.name,
ccs.cs_id,
cg.name,
cg.grp_id,
cci.ci_name,
cci.ci_id,
ccimd.met_description,
ccimd.unit_type,
ccimd.met_type,
sqd.probe,
sqd.origin,
sqd.qos', parameters : []
Thank you!
Marius