Report-Manager Erweiterung des Reports um Lokation - Verfügbare Kapazitäten aller Schränke



  • Hallo zusammen,

    ich muss direkt zugeben, dass mein SQL leider nur für einfache SQL Selects und sowas ausrreicht. Deshalb scheitere ich daran den Report "Verfügbare Kapazitäten aller Schränke" anzupassen.
    Ich benötige lediglich noch eine Spalte zusätzlich um die Lokation der Racks auszugeben.

    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
    

    Wäre super wenn jemand den erlösenden Hinweis liefern könnte. Danke für die Unterstützung und freundliche Grüße.

    Ulli


  • i-doIT Team

    Hi @um3n
    ich arbeite aktuell an der passenden Query 😉
    Sobald ich da fertig bin, werde ich die Lösung anhängen.

    VG
    Phil



  • Ich habe es mal zusammengeklickt:

    SELECT 
    obj_main.isys_obj__id AS '__id__', 
    obj_main.isys_obj__title AS 'Schrank',
    obj_main.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',
    j2.isys_catg_location_list__parentid AS 'isys_cmdb_dao_category_g_location::dynamic_property_callback_location_path::isys_catg_location_list__parentid::LC__CMDB__CATG__LOCATION_PATH' 
    
     FROM isys_obj AS obj_main
    INNER JOIN isys_cmdb_status AS obj_main_status ON obj_main_status.isys_cmdb_status__id = obj_main.isys_obj__isys_cmdb_status__id
    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 AS j2 ON obj_main.isys_obj__id = j2.isys_catg_location_list__isys_obj__id 
    LEFT JOIN isys_obj AS j3 ON j3.isys_obj__id = j2.isys_catg_location_list__isys_obj__id 
    LEFT JOIN isys_obj_type AS j4 ON j4.isys_obj_type__id = obj_main.isys_obj__isys_obj_type__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 TRUE 
     AND  ( (j4.isys_obj_type__id != '60' ) AND (j4.isys_obj_type__id != '63' ) )
     ORDER BY obj_main.isys_obj__title DESC
    

Log in to reply
 


Datenschutz / Privacy Policy