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.
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.
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.
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 |
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 |
| 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 |
| 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 |
| 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_DETECT | PATCH_ATTRIBUTE.LANGUAGE |
| KBSYS.PATCHLINK_PATCH.LANGUAGE_ID | Same as above | PATCH_ATTRIBUTE.LANGUAGE |
| IS_DOWNLOADED | PATCH_PAYLOAD_DOWNLOAD_STATUS.IS_DEPLOY_DOWNLOADED |
