DX Infrastructure Manager

Expand all | Collapse all

CABI Ad Hoc Report Error with MySQL backend database

Jump to Best Answer
  • 1.  CABI Ad Hoc Report Error with MySQL backend database

    Posted 07-20-2021 09:44 AM
    Edited by Marius Nitu 07-20-2021 09:50 AM
    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


  • 2.  RE: CABI Ad Hoc Report Error with MySQL backend database

    Broadcom Employee
    Posted 07-21-2021 02:24 PM
    This may help.
    https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?GroupId=1315&MessageKey=2d962e08-ae92-4862-8d46-653f681fa44f&CommunityKey=170eb4e5-a593-4af2-ad1d-f7655e31513b&tab=digestviewer

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 3.  RE: CABI Ad Hoc Report Error with MySQL backend database

    Broadcom Employee
    Posted 07-22-2021 01:37 PM
    Not sure it will help you but found out some more. The VN_ tables show up in the database under VIEWS, and System Views, instead of TABLES, and are used by cabi. They are created during installation by the data_engine running in your case mysql_dataengine_create.sql, there is another for MS SQL and Oracle.

    ------------------------------
    Support Engineer
    Broadcom
    ------------------------------



  • 4.  RE: CABI Ad Hoc Report Error with MySQL backend database
    Best Answer

    Broadcom Employee
    Posted 07-27-2021 04:07 PM

    Hi Marius,

    Please follow the instructions on KB 220436

    https://knowledge.broadcom.com/external/article?articleId=220436

    Thanks,

    Samer




  • 5.  RE: CABI Ad Hoc Report Error with MySQL backend database

    Posted 08-03-2021 06:17 AM
    Hello Samer,

    I implemented the instructions in the KB and I got rid of the error.

    I now have another error, but I will make another thread about it.

    Thank you!

    Marius