Project

General

Profile

PCRefresh Implementation

User documentation
10/27/2016

PC Refresh Internals -- Overview:
=================================
Goal: Provide recommendations for computer replacements when they reach end-of-life,
as well as operational reports grouped together by "normalized hardware" or "normalized OS" 
information.

Implementation Overview:
========================
Tables involved in the process:
    cmdb_ci_computer                -- systems, with an OS and OS Service Pack and underlying asset
    u_sunflower_info                -- assets, with Manufacturer and Model and Official Name

    u_computer_normalized_data      -- per cmdb_ci_computer, its normalized OS and OS service pack
    u_sunflower_normalized_data     -- per sunflower asset, its normalized Mfr/model/official name
    u_computer_normalization        -- map of raw values to user-entered "normalized" values for
                                        u_computer_normalized_data fields and
                                        u_sunflower_normalized_data fields (could be extended to
                                        include other normalizations in the future)

    u_upgrade_recommendation        -- contains one record for each pair of normalized OS and
                                        normalized MODEL, mapping it to the suggested
                                        item to purchase as a replacement when the time comes
                                        to replace this computer and its underlying asset.

Database views involved in the process:
    u_pc_refresh_recommendation     -- view joining u_sunflower_info (the hardware asset),
                                        u_sunflower_normalized_data (its normalized model),
                                        cmdb_ci_computer (the system running on that hardware),
                                        u_computer_normalized_data (its normalized OS),
                                        and u_upgrade_recommendation (the recommended replacement
                                        for systems running this normalized OS on this normalized
                                        model)

Unique Indexes:
    In order for the u_pc_refresh_recommendation view to contain ONE and ONLY ONE
    record per computer, we must ensure uniqueness of the fields in the joins of
    the driving tables.  Operationally, this must be done via UNIQUE INDEXES
    (see HI incident INT3060241).  The tables and fields with unique indices are:
        u_computer_normalized_data:     u_computer
        u_sunflower_normalized_data:    u_sunflower_info
        u_computer_normalization:       u_field_table, u_field, u_field_value
        u_upgrade_recommendation:       u_model, u_operating_system

Script Includes:
    ComputerNormalization   - handles the business rules involved in populating
                                the u_computer_normalized_data, u_sunflower_normalized_data,
                                u_computer_normalization tables

    PCRefresh               - handles the business rules involved in populating
                                the u_upgrade_recommendation table

===========================================================================================
Implementation Details:
=======================

The hardware layer is populated first and maintained regularly through various integrations.

    cmdb_ci_computer - holds cmdb information for "computers",
        updated regularly, contains computer sys_id, OS, OS Service Pack;
        may hold a reference to an underlying asset (.u_sunflower_info)

        BR, 50: Create Normalized Data on Insert, Create Normalized Data on Update:
          call script include ComputerNormalization().createNormalizedDataRecord
          to ensure that there is a record in the u_computer_normalized_data table
          with a reference to this computer, and columns to hold the value for
          the normalized OS and OS Service Pack on this computer.
          There should be ONE and ONLY ONE record in the u_computer_normalized_data
          table per computer.
          The cmdb_ci_computer instance has a backward pointer to the normalized data record
          (doubly-linked list).

        BR, 100: Insert Computer Normalization on Insert, Insert Computer Normalization on Update:
          call script include ComputerNormalization().insertNormalization passing the
           current os, to ensure that the u_computer_normalization table contains a
           record for this raw OS, wherein an authorized person may later configure its
           "normalized" value.  There should be ONE and ONLY one record in this
           table for field_table='cmdb_ci_computer', field='OS', field_value=<current os>.

          call script include ComputerNormalization().insertNormalization passing the
            current os_service_pack, to ensure that the u_computer_normalization table
            contains a record for this raw OS Service Pack, wherein an authorized person may
            later configure its "normalized" value.  There should be ONE and ONLY ONE
            record in this table for field_table='cmdb_ci_computer', field='OS Service Pack',
            field_value=<current os_service_pack>.

        BR, 150: Set Normalized Data on Insert, Set Normalized Data on Update
            call script include ComputerNormalization().setNormalizedData to fill in the
            columns for normalized OS and normalized OS Service Pack in the
            u_computer_normalized_data table for this computer (which we ensured exists
            back at step BR,50).

    u_sunflower_info - holds asset information for the "hardware",
        updated regularly, contains asset sys_id, manufacturer, model number, official name;
        may hold a reference to a "containing" computer (.u_cmdb_ci)

        Similar BR rules at 50, 100, 150 doing similar things (and calling the
        same code functions but passing slightly different parameters) to:

          50: Create Normalized Data on Insert, Create Normalized Data on Update:
            call ComputerNormalization().createNormalizedDataRecord to
            ensure one record per asset (doubly linked list) in the u_sunflower_normalized_data
            table, with columms to hold the normalized values of Manufacturer, Model number, Official name
            of this asset

          100: Insert Sunflower Normalization on Insert, Insert Sunflower Normalization on Update:
            call ComputerNormalization().insertNormalization 3 times, to ensure that table u_computer_normalization
            contains exactly one record for
                field_table='u_sunflower_info', field='Manufacturer', field_value=<current manufacturer>
            and exactly one record for
                field_table='u_sunflower_info', field='Model number', field_value=<current model>
            and exactly one record for
                field_table='u_sunflower_info', field='Official Name', field_value=<current official name>
            wherein an authorized person may later configure a "normalized" value for each of these.

          150: Set Normalized Data on Insert, Set Normalized Data on Update:
            call ComputerNormalization().setNormalizedData to fill in the columns for
            normalized manufacturer, model, and official name for this sunflower asset (which
            we ensured exists back at step BR,50)

Later a suitably authorized person fills in the "normalized" data values, for example:

    Column              Sample Raw Data                             Sample Normalized Data
    ---------------------------------------------------------------------------------------
    OS                  Windows 10 Enterprise 2015 LTSB             Windows 10
    OS Service Pack     Scientific Linux SL release 5.2 (Boron)     SLF 5.2
    Manufacturer        SUPERMAC TECH                               SuperMac
    Model Number        A1297-2800                                  MacBook Pro (17-inch, Mid 2009)
    Official Name       COMPUTER, DESKTOP                           Desktop

When this person enters or updates a normalized data value, we need to update the
appropriate u_computer_normalized_data or u_sunflower_normalized_data table to reflect
the new normalized value of this column for each computer or sunflower asset where the
raw data matches the raw data in the row that was changed.  This is handled by
business rules:

    BR,150: Update Normalized Data on Update:
        call script include ComputerNormalization.updateNormalizedData which will
        loop through all records in either u_computer_normalized_data or u_sunflower_normalized_data
        (depending on the u_field_table in the record that was changed), find all records
        that have the old value for the field that changed, and update to the new value.

Meanwhile in the background are the business rules that handle ensuring that the
u_upgrade_recommendation table has ONE and ONLY ONE record per each normalized OS and
normalized MODEL pair found in "world":

    BR, 500, async:
        Insert Upgrade Recommendation on Insert on u_computer_normalized_data
        Insert Upgrade Recommendation on Insert on u_sunflower_normalized_data
        Insert Upgrade Recommendation on Update on u_computer_normalized_data
        Insert Upgrade Recommendation on Update on u_sunflower_normalized_data

        If the [computer|asset] value of normalized [OS|model] is set or modified,
        get the current value of the [normalized OS and normalized model] that
        applies to this computer/asset, and make sure that there is ONE and ONLY
        ONE record in the u_upgrade_recommendation table.

        This happens in the background asynchronously because it may be triggered
        on a lot of cmdb_ci_computer/u_sunflower_info instances at the same time.

=====================================================================================

Files