Report-Manager - Extended Rackreport - Capacity in racks with location



  • Hey guys,
    I have to admit directly that my SQL is unfortunately only sufficient for simple SQL selects and such. Therefore I fail to adjust the report "Available capacities of all cabinets".
    I only need one additional column to show the location of the racks.

    SELECT
    isys_obj__id AS '__id__',
    isys_obj__title AS 'Schrank',
    isys_obj__sysid AS 'SYS-ID',
    SUM(
        IF(
            !ISNULL(
                isys_catg_location_list__pos
            )
            AND isys_catg_location_list__pos != 0, 1, 0
        )
    ) AS 'Anzahl Elemente',
    
    CONCAT(
        IF(
            !ISNULL(rack.isys_catg_formfactor_list__rackunits),
            rack.isys_catg_formfactor_list__rackunits - SUM(
                IF(
                    ISNULL(
                        isys_catg_location_list__pos
                    )
                    OR isys_catg_location_list__pos = 0
                    , 0, dev.isys_catg_formfactor_list__rackunits
                )
            ),
            rack.isys_catg_formfactor_list__rackunits
        )
        , ' HE'
    ) AS 'Verbleibende Kapazitaet'
    
    FROM isys_obj
    INNER JOIN isys_obj_type ON isys_obj_type__id = isys_obj__isys_obj_type__id
    INNER JOIN isys_catg_formfactor_list rack ON rack.isys_catg_formfactor_list__isys_obj__id = isys_obj__id
    LEFT JOIN isys_catg_location_list ON isys_catg_location_list__parentid = isys_obj__id
    LEFT JOIN isys_catg_formfactor_list dev ON dev.isys_catg_formfactor_list__isys_obj__id = isys_catg_location_list__isys_obj__id
    WHERE isys_obj_type__const = 'C__OBJTYPE__ENCLOSURE'
    AND isys_obj__status =2
    GROUP BY isys_obj__id
    

    Would be great if someone could provide the cue. Thanks for the support and kind regards.

    Ulli


  • i-doIT Team

    Hi @um3n
    I'm working on a solution 😉
    If I'm ready, I will let you know und publish the solution.

    Cheers,
    Phil


Log in to reply
 


Datenschutz / Privacy Policy