I was asked for a report that would gather Software Compliance across all ORG's. If created at the System level of an ORG-enabled K1000, this will show the Software licenses owned vs installed broken down by ORG. This assumes the licenses and software catalog associations have already been created in each of the ORG's. Create a new SQL Report in the System ORG with the following details:

Title: Software Catalog Compliance (All ORGs)

Category: Compliance

Break on Columns: ORGANIZATION_NAME

SQL:

#BEGIN SQL

SELECT '<ORGANIZATION_NAME>' AS ORGANIZATION_NAME,

GROUP_CONCAT(DISTINCT SAM_VIEW_DISCOVERED_SOFTWARE.PUBLISHER SEPARATOR '\n') AS PUBLISHER,

GROUP_CONCAT(DISTINCT SAM_VIEW_DISCOVERED_SOFTWARE.PRODUCT_NAME SEPARATOR '\n') AS 'Product',

GROUP_CONCAT(DISTINCT SAM_VIEW_DISCOVERED_SOFTWARE.MAJOR_VERSION SEPARATOR '\n') AS 'Version',

SUM(DISTINCT FIELD_1) AS 'Licenses',

GROUP_CONCAT(DISTINCT SAM_VIEW_DISCOVERED_SOFTWARE.INSTALLED_ON SEPARATOR '\n') AS 'Installed'

FROM <ORGANIZATION_DB>.SAM_VIEW_DISCOVERED_SOFTWARE LEFT JOIN <ORGANIZATION_DB>.SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE ON SAM_VIEW_DISCOVERED_SOFTWARE.ID

= SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.ID

LEFT JOIN <ORGANIZATION_DB>.MACHINE ON MACHINE.ID = SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.MACHINE_ID

LEFT JOIN <ORGANIZATION_DB>.ASSET_CATALOG_ASSOCIATION ON ASSET_CATALOG_ASSOCIATION.ASSOCIATED_CATALOG_ID =

SAM_VIEW_DISCOVERED_SOFTWARE.ID

LEFT JOIN <ORGANIZATION_DB>.ASSET ON ASSET.ID = ASSET_CATALOG_ASSOCIATION.ASSET_ID

LEFT JOIN <ORGANIZATION_DB>.ASSET_DATA_7 ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID

LEFT JOIN <ORGANIZATION_DB>.ASSET_FIELD_DEFINITION ON ASSET_FIELD_DEFINITION.ID = ASSET_CATALOG_ASSOCIATION.ASSET_FIELD_ID

WHERE  FIELD_1 > 0

GROUP BY SAM_VIEW_DISCOVERED_SOFTWARE.ID ORDER BY PUBLISHER

#END SQL

NOTE: Be sure to check “All Organizations”