/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


„Approved for Software Catalog’’ field in License and Software Catalog SQL relations problem

01/10/2016 999 views

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

 

0 Comments   [ + ] Show comments

Comments


Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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