Hello everyone

I need help with builded Dell Kace Report ( that has name " Unapproved Software Installation" ). It 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. Thank for support and help !













SQL
-------------------------------------
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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity