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

um3n

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

Philipp Hörselmann

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

VG
Phil

beckmann

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