/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Build-in " Unapproved Software Installation" and >1 Software Catalog linked to license

01/08/2016 1188 views

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


Be the first to answer this question

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ