Copy the SQL below into a new SQL report in the K1000
Software Catalog - Publisher True Up (Adobe Systems, Inc.)
--------------------------------------------------------------------------------------------------------------------------
SELECT CATALOG.SAM_CATALOG.PUBLISHER,
CATALOG.SAM_CATALOG.PRODUCT_NAME AS 'Product',
CATALOG.SAM_CATALOG.MAJOR_VERSION AS 'Version',
CATALOG.SAM_CATALOG.SOFTWARE_CATEGORY AS 'Category',
SUM(DISTINCT FIELD_1) AS 'Licenses',
SAM_COUNT.INSTALLED_ON AS 'Installed',
SUM(DISTINCT IF(DATEDIFF(NOW(),ASSET_DATA_7.FIELD_7) > 0, FIELD_1, 0)) AS 'Expired'
FROM CATALOG.SAM_CATALOG
JOIN SAM_COUNT ON SAM_COUNT.SAM_CATALOG_ID = CATALOG.SAM_CATALOG.ID
LEFT JOIN ASSET_CATALOG_ASSOCIATION ON ASSET_CATALOG_ASSOCIATION.ASSOCIATED_CATALOG_ID = CATALOG.SAM_CATALOG.ID
LEFT JOIN ASSET ON ASSET.ID = ASSET_CATALOG_ASSOCIATION.ASSET_ID
LEFT JOIN ASSET_DATA_7 ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID
LEFT JOIN ASSET_FIELD_DEFINITION ON ASSET_FIELD_DEFINITION.ID = ASSET_CATALOG_ASSOCIATION.ASSET_FIELD_ID
WHERE CATALOG.SAM_CATALOG.SAM_TYPE in ('TITLED_APPLICATION' , 'TITLED_SUITE')
AND (CATALOG.SAM_CATALOG.SOFTWARE_CATEGORY_ID <> 32)
AND CATALOG.SAM_CATALOG.PUBLISHER = 'Adobe Systems, Inc.'
GROUP BY CATALOG.SAM_CATALOG.ID
ORDER BY PUBLISHER, CATEGORY
--------------------------------------------------------------------------------------------------------------------------
Software Catalog - Publisher True Up (Microsoft Corporation.)
--------------------------------------------------------------------------------------------------------------------------
SELECT CATALOG.SAM_CATALOG.PUBLISHER,
CATALOG.SAM_CATALOG.PRODUCT_NAME AS 'Product',
CATALOG.SAM_CATALOG.MAJOR_VERSION AS 'Version',
CATALOG.SAM_CATALOG.SOFTWARE_CATEGORY AS 'Category',
SUM(DISTINCT FIELD_1) AS 'Licenses',
SAM_COUNT.INSTALLED_ON AS 'Installed',
SUM(DISTINCT IF(DATEDIFF(NOW(),ASSET_DATA_7.FIELD_7) > 0, FIELD_1, 0)) AS 'Expired'
FROM CATALOG.SAM_CATALOG
JOIN SAM_COUNT ON SAM_COUNT.SAM_CATALOG_ID = CATALOG.SAM_CATALOG.ID
LEFT JOIN ASSET_CATALOG_ASSOCIATION ON ASSET_CATALOG_ASSOCIATION.ASSOCIATED_CATALOG_ID = CATALOG.SAM_CATALOG.ID
LEFT JOIN ASSET ON ASSET.ID = ASSET_CATALOG_ASSOCIATION.ASSET_ID
LEFT JOIN ASSET_DATA_7 ON ASSET_DATA_7.ID = ASSET.ASSET_DATA_ID
LEFT JOIN ASSET_FIELD_DEFINITION ON ASSET_FIELD_DEFINITION.ID = ASSET_CATALOG_ASSOCIATION.ASSET_FIELD_ID
WHERE CATALOG.SAM_CATALOG.SAM_TYPE in ('TITLED_APPLICATION' , 'TITLED_SUITE')
AND (CATALOG.SAM_CATALOG.SOFTWARE_CATEGORY_ID <> 32)
AND CATALOG.SAM_CATALOG.PUBLISHER = 'Microsoft Corporation'
GROUP BY CATALOG.SAM_CATALOG.ID
ORDER BY PUBLISHER, CATEGORY
--------------------------------------------------------------------------------------------------------------------------
Comments