SELECT 
obj_main.isys_obj__id AS '__id__', 
 (SELECT CONCAT(isys_obj__title, ' {', isys_obj__id, '}')
                            FROM isys_obj  WHERE isys_obj__id = obj_main.isys_obj__id   ) 'isys_cmdb_dao_category_s_net_ip_addresses__ip_address_link' , 
 (SELECT (CASE WHEN IS_IPV6(isys_cats_net_list__address) THEN INET6_NTOA(INET6_ATON(isys_cats_net_list__address))
                                  WHEN IS_IPV4(isys_cats_net_list__address) THEN CONCAT('<span data-sort="', LPAD(isys_cats_net_list__address_long, 10, '0'), '">', isys_cats_net_list__address, '</span>')
                                  ELSE '-' END)
                            FROM isys_cats_net_list  WHERE isys_cats_net_list__isys_obj__id = obj_main.isys_obj__id  AND isys_cats_net_list__status = 2   ) 'isys_cmdb_dao_category_s_net__address' , 
 (j5.isys_net_type__title   ) 'isys_cmdb_dao_category_s_net__type' , 
 (SELECT REPLACE(CONCAT('<ul><li>', GROUP_CONCAT((CASE
                                WHEN IS_IPV6(isys_cats_net_list__address) THEN CONCAT(INET6_NTOA(INET6_ATON(isys_cats_net_list__address)), ' / ', isys_cats_net_list__cidr_suffix)
                                WHEN IS_IPV4(isys_cats_net_list__address) THEN CONCAT(INET_NTOA(INET_ATON(isys_cats_net_list__address)), ' / ', isys_cats_net_list__cidr_suffix)
                                ELSE '-' END) SEPARATOR '</li><li>'), '</li></ul>'), '<ul><li></li></ul>', '')
                            FROM isys_cats_net_list  WHERE isys_cats_net_list__isys_obj__id = obj_main.isys_obj__id  AND isys_cats_net_list__status = 2   GROUP BY isys_cats_net_list__isys_obj__id  ) 'isys_cmdb_dao_category_s_net__address_with_suffix' , 
 (SELECT REPLACE(CONCAT('<ul><li>', GROUP_CONCAT((CASE
                                WHEN IS_IPV4(isys_cats_net_list__address_range_from) THEN
                                    CONCAT(isys_cats_net_list__address_range_from, ' - ', isys_cats_net_list__address_range_to)
                                WHEN IS_IPV6(isys_cats_net_list__address_range_from) THEN
                                    CONCAT(INET6_NTOA(INET6_ATON(isys_cats_net_list__address_range_from)), ' - ', INET6_NTOA(INET6_ATON(isys_cats_net_list__address_range_to)))
                             END) SEPARATOR '</li><li>'), '</li></ul>'), '<ul><li></li></ul>', '')
                            FROM isys_cats_net_list  WHERE isys_cats_net_list__isys_obj__id = obj_main.isys_obj__id  AND isys_cats_net_list__status = 2   GROUP BY isys_cats_net_list__isys_obj__id  ) 'isys_cmdb_dao_category_s_net__address_range' , 
j3.isys_cats_net_list__mask 'isys_cmdb_dao_category_s_net__netmask', 
 (SELECT CONCAT((SELECT COUNT(isys_cats_net_ip_addresses_list__id)
                                FROM isys_cats_net_ip_addresses_list LEFT JOIN isys_catg_ip_list
                                ON isys_catg_ip_list__isys_cats_net_ip_addresses_list__id = isys_cats_net_ip_addresses_list__id
                                WHERE isys_cats_net_ip_addresses_list__isys_obj__id = isys_cats_net_list__isys_obj__id 
                                  AND isys_catg_ip_list__id IS NOT NULL 
                                  AND isys_catg_ip_list__status = 2),
                                (CASE WHEN isys_cats_net_list__address_range_to_long = 0 AND isys_cats_net_list__address_range_from_long = 0 THEN '' ELSE ' / ' END),
                                (CASE
                                  WHEN isys_cats_net_list__address_range_to_long = 0 AND isys_cats_net_list__address_range_from_long = 0 THEN ''
                                  WHEN isys_cats_net_list__address_range_to_long >= isys_cats_net_list__address_range_from_long THEN
                                    (1 + isys_cats_net_list__address_range_to_long - isys_cats_net_list__address_range_from_long)
                                  WHEN isys_cats_net_list__address_range_from_long >= isys_cats_net_list__address_range_to_long THEN
                                    (1 + isys_cats_net_list__address_range_from_long - isys_cats_net_list__address_range_to_long)
                                  ELSE
                                    'LC__CMDB__CATG__IP__INVALID_IP_RANGE'
                                  END)
                              )
                            FROM isys_cats_net_list  WHERE isys_cats_net_list__isys_obj__id = obj_main.isys_obj__id  AND isys_cats_net_list__status = 2   ) 'isys_cmdb_dao_category_s_net__free_addresses' , 
 (SELECT CONCAT("{#", isys_cmdb_status__color, "} ", isys_cmdb_status__title) FROM isys_cmdb_status  WHERE obj_main.isys_obj__isys_cmdb_status__id = isys_cmdb_status__id   ) 'isys_cmdb_dao_category_g_global__cmdb_status'  

FROM isys_obj AS obj_main 
 LEFT JOIN isys_cats_net_list j3 ON j3.isys_cats_net_list__isys_obj__id = obj_main.isys_obj__id AND j3.isys_cats_net_list__status = 2 
 LEFT JOIN isys_net_type j5 ON j3.isys_cats_net_list__isys_net_type__id = j5.isys_net_type__id 

WHERE TRUE 
AND obj_main.isys_obj__isys_obj_type__id = 31 
