Systems Management

Are anyone know how are linked relations between Software Catalog and Licenses in „ Approved for Software Catalog „ Field in assets License ? Right now i work on report that is based on builded Dell Kace Report (  " Unapproved Software Installation" ). The DELL report works good if license has only 1  Software Catalog linked - when i link more > 1 Software Catalog then Report list summary only for 1 linked Software  Catalog in "unapproved_count" and "machine" fields - below example:

I have 1 License (name: Adobe Photoshop ) with 2 Software Catalog linked to it :

Adobe Photoshop Windows ( this software catalog has 2 unapproved install counts on devices : a-machine, b-machine )

and

Adobe Photoshop Mac ( this software catalog has 3 unapproved install counts on devices : c machine, d-machine,e-machine)

and when i generate that report i have the summary : 

Unapproved install counts : 2 , 

Machines : a-machine, b-machine

but i would expect to get sum of all the linked software catalog like : 

Unapproved install counts: 5, 

Machines: a-machine, b-machine, c-machine, d-machinea-machine, e-machine

Is there any way to get it ? Below i add a SQL code of that report ( in bold the most important field that i am saying before ) . Thank for support and help !

SQL code

-------------------------------------

SELECT MID(GROUP_CONCAT(DISTINCT IF(ISNULL(A2.NAME),SVTS.NAME,A2.NAME) SEPARATOR '\n'), 1, LENGTH(GROUP_CONCAT(DISTINCT IF(ISNULL(A2.NAME),SVTS.NAME,A2.NAME) SEPARATOR '\n'))) AS NAME, 

A21.NAME AS VENDOR,

ASSET.NAME AS LICENSES, 

IF(A2.MAPPED_ID,

(SELECT COUNT(*)

 FROM MACHINE_SOFTWARE_JT MSJ

 WHERE SOFTWARE_ID = A2.MAPPED_ID AND

 EXISTS (SELECT 1

                                               FROM  ASSET_ASSOCIATION AJ3 

                                               LEFT JOIN ASSET COMPUTER ON

                                               COMPUTER.ID = AJ3.ASSOCIATED_ASSET_ID AND

                                               COMPUTER.ASSET_TYPE_ID = 5

                                               WHERE AJ3.ASSET_ID = ASSET.ID AND COMPUTER.MAPPED_ID = MACHINE_ID)),

(SELECT COUNT(DISTINCT MACHINE_ID)

FROM SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE

WHERE SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.ID = ASSOCIATED_CATALOG_ID AND

EXISTS (SELECT 1

                        FROM  ASSET_ASSOCIATION AJ3 

                                               LEFT JOIN ASSET COMPUTER ON

                                               COMPUTER.ID = AJ3.ASSOCIATED_ASSET_ID AND

                                               COMPUTER.ASSET_TYPE_ID = 5

                                               WHERE AJ3.ASSET_ID = ASSET.ID AND COMPUTER.MAPPED_ID = SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.MACHINE_ID))) AS APPROVED_COUNT,

IF(A2.MAPPED_ID,

(SELECT COUNT(*)

 FROM MACHINE_SOFTWARE_JT MSJ

 WHERE SOFTWARE_ID = A2.MAPPED_ID AND

 NOT EXISTS (SELECT 1

                                               FROM  ASSET_ASSOCIATION AJ3 

                                               LEFT JOIN ASSET COMPUTER ON

                                               COMPUTER.ID = AJ3.ASSOCIATED_ASSET_ID AND

                                               COMPUTER.ASSET_TYPE_ID = 5

                                               WHERE AJ3.ASSET_ID = ASSET.ID AND COMPUTER.MAPPED_ID = MACHINE_ID)),

(SELECT COUNT(DISTINCT MACHINE_ID)

FROM SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE

WHERE SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.ID = ASSOCIATED_CATALOG_ID AND

NOT EXISTS (SELECT 1

                        FROM  ASSET_ASSOCIATION AJ3 

                                               LEFT JOIN ASSET COMPUTER ON

                                               COMPUTER.ID = AJ3.ASSOCIATED_ASSET_ID AND

                                               COMPUTER.ASSET_TYPE_ID = 5

                                               WHERE AJ3.ASSET_ID = ASSET.ID AND COMPUTER.MAPPED_ID = SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.MACHINE_ID))) AS UNAPPROVED_COUNT,

IF(A2.MAPPED_ID,

(SELECT GROUP_CONCAT(DISTINCT M.NAME)

 FROM MACHINE_SOFTWARE_JT MSJ

 INNER JOIN MACHINE M ON

 MSJ.MACHINE_ID = M.ID

 WHERE SOFTWARE_ID = A2.MAPPED_ID AND

 NOT EXISTS (SELECT 1

                                               FROM  ASSET_ASSOCIATION AJ3 

                                               LEFT JOIN ASSET COMPUTER ON

                                               COMPUTER.ID = AJ3.ASSOCIATED_ASSET_ID AND

                                               COMPUTER.ASSET_TYPE_ID = 5

                                               WHERE AJ3.ASSET_ID = ASSET.ID AND COMPUTER.MAPPED_ID = MACHINE_ID)),

(SELECT GROUP_CONCAT(DISTINCT M.NAME)

FROM SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE

INNER JOIN MACHINE M ON

M.ID = MACHINE_ID

WHERE SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.ID = ASSOCIATED_CATALOG_ID AND

NOT EXISTS (SELECT 1

                        FROM  ASSET_ASSOCIATION AJ3 

                                               LEFT JOIN ASSET COMPUTER ON

                                               COMPUTER.ID = AJ3.ASSOCIATED_ASSET_ID AND

                                               COMPUTER.ASSET_TYPE_ID = 5

                                               WHERE AJ3.ASSET_ID = ASSET.ID AND COMPUTER.MAPPED_ID = SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.MACHINE_ID))) AS MACHINES

FROM ASSET_DATA_7 

LEFT JOIN ASSET ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=7 

LEFT JOIN ASSET_ASSOCIATION J3 ON J3.ASSET_ID = ASSET.ID AND J3.ASSET_FIELD_ID=3

LEFT JOIN ASSET A3 ON A3.ID = J3.ASSOCIATED_ASSET_ID 

LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2

LEFT JOIN ASSET A2 ON A2.ID = J2.ASSOCIATED_ASSET_ID 

LEFT JOIN ASSET_ASSOCIATION JX2 ON JX2.ASSET_ID = ASSET.ID AND JX2.ASSET_FIELD_ID=2

LEFT JOIN ASSET AX2 ON AX2.ID = JX2.ASSOCIATED_ASSET_ID       

LEFT JOIN ASSET_ASSOCIATION J21 ON J21.ASSET_ID = ASSET.ID AND J21.ASSET_FIELD_ID=8

LEFT JOIN ASSET A21 ON A21.ID = J21.ASSOCIATED_ASSET_ID 

LEFT JOIN ASSET_CATALOG_ASSOCIATION ACA ON ACA.ASSET_ID = ASSET.ID

LEFT JOIN SAM_VIEW_ALL_SOFTWARE SVTS ON SVTS.ID = ASSOCIATED_CATALOG_ID

GROUP BY ASSET_DATA_7.ID 

ORDER BY 1