-
Title
Updating Custom SQL Queries for 10.0 -
Description
The KACE Systems Management Appliance (SMA) was recently updated to version 10.0. This version provides the new KACE Patch Catalog, but also comes with multiple patch table changes. When using the SMA wizards to create smart labels, reports, custom views, and ticket rules, these changes should be made during the upgrade.
Note: Due to the significant changes, all patching Smart Labels, Reports, Custom Views, and Ticket Rules which were created using a built-in wizard should each be individually reviewed after the upgrade to ensure the automatic changes still produce the desired results.
When it comes to custom SQL queries used in Smart Labels, Reports, Custom Views, and Ticket Rules, these will need to be repaired by hand. It is possible to use the KACE Patching Migration Report to help identify these custom queries.
This article should provide a few direct mappings from old tables to new tables, but note not all tables have direct mapping. For a full list of removed and added tables, please see the KACE SMA 10.0 Database Schema Changes.
-
Resolution
Below is specific information which pertains to an already existing table or new table. The information is provided in attempts to assist with updating custom SQL. Please feel free to review a full list of the KACE SMA 10.0 Database Schema Changes.
KACE recommends using the built-in wizards for labels, reports, custom ticket rules, and custom views.
KACE Support is only able to assist with basic structure or possible break-fix situations when it comes to custom SQL. For assistance with structure or break-fix, please contact technical support. For assistance with writing custom SQL, please contact Professional Services. This is a fee-based service.
TYPE to CLASSIFICATION Mapping:
In 10.0, TYPE was removed and this has now been mapped to CLASSIFICATION.
OLD PATCHING (TYPE) NEW PATCHING (CLASSIFICATION) Security Security Updates Software Installer Full Software Non-Security Definition Updates, Feature Packs, Critical Updates, Tools, Update Rollups, Updates, Upgrades, Hotfix IMPACT to SEVERITY Mapping:
In 10.0, IMPACT was removed and this has now been mapped to SEVERITY.
OLD PATCHING (IMPACT) NEW PATCHING (SEVERITY) Critical Critical Recommended Important, Low, Moderate, Recommended KBSYS (System level table changes):
OLD PATCHING NEW PATCHING NOTES PATCHLINK_ERROR_CODE PATCH_ERROR_CODE PATCHLINK_IMPACT PATCH_SEVERITY Previously Patch Impact. Stores Severity name and ID PATCHLINK_LANGUAGE PATCH_LANGUAGE Patch Catalog metadata PATCHLINK_OS_TYPE PATCH_OS Patch Catalog metadata PATCHLINK_PATCH PATCH Patch Catalog metadata PATCHLINK_PATCH_PRODUCT PATCH_PRODUCT Patch Catalog metadata PATCHLINK_PATCH_SUPERCEDES PATCH_SUPERCEDES_JT Patch Catalog metadata PATCHLINK_UPDATE_STATUS PATCH_CATALOG_RUN_STATUS PATCH_CATALOG_UPDATE_STATUS Stores information on the patch catalog files that are downloaded on to the K1 PATCH_CLASSIFICATION Previously Patch Type. Stores Classification name and ID PATCH_FILE_TYPE Information about the different kind of patch file types and their purpose PATCH_PACKAGE Patch Catalog metadata PATCH_ATTRIBUTE Patch Catalog metadata PATCH_PRODUCT_JT Patch Catalog metadata PATCH_PRODUCT_OS_JT Patch Catalog metadata PATCH_PRODUCT_TITLED_APPLICATION_JT Patch Catalog metadata PATCH_PRODUCT_TITLED_SUITE_JT Patch Catalog metadata PATCH_PAYLOAD_DOWNLOAD_STATUS Stores information about patches downloaded onto the K1 PATCH_RESOURCE Stores information about patches downloaded onto the K1 ORGx (ORG level table changes):
OLD PATCHING NEW PATCHING NOTES PATCHLINK_MACHINE_STATUS PATCH_MACHINE_STATUS Stores aggregated detect/deploy/rollback status for a patch-machine combo. This table can be queried to get accurate information on whether a patch is installed on a machine. PATCHLINK_PATCH_COUNT PATCH_COUNT PATCHLINK_PATCH_LABEL_JT PATCH_LABEL_JT PATCHLINK_PATCH_STATUS PATCH_STATUS PATCHLINK_SCHEDULE PATCH_SCHEDULE PATCHLINK_SCHEDULE_DEPLOY_LABEL_JT PATCH_SCHEDULE_DEPLOY_LABEL_JT PATCHLINK_SCHEDULE_DETECT_LABEL_JT PATCH_SCHEDULE_DETECT_LABEL_JT PATCHLINK_SCHEDULE_LABEL_JT PATCH_SCHEDULE_LABEL_JT PATCHLINK_SCHEDULE_MACHINE_STATUS PATCH_SCHEDULE_MACHINE_STATUS PATCHLINK_SCHEDULE_OS_JT PATCH_SCHEDULE_OS_JT PATCHLINK_SCHEDULE_ROLLBACK_LABEL_JT PATCH_SCHEDULE_ROLLBACK_LABEL_JT PATCHLINK_SCHEDULE_RUN PATCH_SCHEDULE_RUN PATCHLINK_SCHEDULE_RUN_COUNTS PATCH_SCHEDULE_RUN_COUNTS PATCHLINK_SCHEDULE_RUN_LOG PATCH_SCHEDULE_RUN_LOG PATCHLINK_SCHEDULE_RUN_MACHINE PATCH_SCHEDULE_RUN_MACHINE PATCH_MACHINE_REMEDIATION_STATUS Stores raw information about patch detect/deploy/rollback results. This is strictly for the K1 to use. Customers are not advised to query this table. PATCH_UPGRADE_FAILURE Stores information about 9.1 patches that were not mapped to the new 10.0 system. PATCH_SETTINGS PATCH_SETTINGS PATCH_FILTER PATCH_FILTER New Joins:
Old Column Name New Join Where Clause KBSYS.PATCHLINK_PATCH.IS_APP LEFT JOIN KBSYS.PATCH_PRODUCT_JT ON PATCH.ID = PATCH_PRODUCT_JT.PATCH_ID
LEFT JOIN KBSYS.PATCH_PRODUCT ON PATCH_PRODUCT_JT.PATCH_PRODUCT_ID = PATCH_PRODUCT.ID
LEFT JOIN KBSYS.PATCH_PRODUCT_OS_JT ON PATCH_PRODUCT_OS_JT.PATCH_PRODUCT_ID = PATCH_PRODUCT.ID
LEFT JOIN KBSYS.PATCH_OS ON PATCH_OS.ID = PATCH_PRODUCT_OS_JT.PATCH_OS_ID
LEFT JOIN KBSYS.PATCH_PACKAGE ON PATCH_PACKAGE_ID = PATCH_PACKAGE.ID(CASE KBSYS.PATCH_PACKAGE.PACKAGE_TYPE WHEN 'APP' THEN 1 ELSE 0 END) KBSYS.PATCHLINK_PATCH.PLATFORM_ID Same as above PATCH_OS.SOFTWARE_PLATFORM O.ID Same as above PATCH_OS.ID L.ARCHITECTURE_ID Same as above KBSYS.PATCH_PRODUCT.PRODUCT_ARCHITECTURE KBSYS.PATCHLINK_PATCH_LANGUAGE_JT.LANGUAGE_ID LEFT JOIN KBSYS.PATCH_ATTRIBUTE ON PATCH_ATTRIBUTE.PATCH_ID = KBSYS.PATCH.ID
LEFT JOIN KBSYS.PATCH_PAYLOAD_DOWNLOAD_STATUS on PATCH_PAYLOAD_DOWNLOAD_STATUS.PATCH_ID = PATCH_ATTRIBUTE.PATCH_ID
and PATCH_PAYLOAD_DOWNLOAD_STATUS.PATCH_DETECT = PATCH_ATTRIBUTE.PATCH_DETECTPATCH_ATTRIBUTE.LANGUAGE KBSYS.PATCHLINK_PATCH.LANGUAGE_ID Same as above PATCH_ATTRIBUTE.LANGUAGE IS_DOWNLOADED PATCH_PAYLOAD_DOWNLOAD_STATUS.IS_DEPLOY_DOWNLOADED