Build-in " Unapproved Software Installation" and >1 Software Catalog linked to license
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
Answers (0)
Please log in to answer
Be the first to answer this question
