path: root/bin/cve_checker/srtool_cvechecker.py

#!/usr/bin/env python3
#
# ex:ts=4:sw=4:sts=4:et
# -*- tab-width: 4; c-basic-offset: 4; indent-tabs-mode: nil -*-
#
# Security Response Tool Commandline Tool
#
# Copyright (C) 2023       Wind River Systems
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License version 2 as
# published by the Free Software Foundation.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

import os
import sys
import argparse
import json
import subprocess
import logging
import pytz

# load the srt.sqlite schema indexes
dir_path = os.path.dirname(os.path.dirname(os.path.realpath(__file__)))
sys.path.insert(0, dir_path)
from common.srt_schema import ORM
from common.srtool_sql import *
from common.srtool_progress import *
from common.srtool_common import log_error

# Setup:
logger = logging.getLogger("srt")

SRT_BASE_DIR = os.environ.get('SRT_BASE_DIR','.')
SRT_REPORT_DIR = f"{SRT_BASE_DIR}/reports"

# data/cve_checker/yocto-metrics/cve-check/master/1697871310.json
REMOTE_URL = 'git://git.yoctoproject.org/yocto-metrics'
REMOTE_PATH = ''
LOCAL_DIR = 'data/cve_checker/yocto-metrics'
BRANCH = ''

# Import Channel support
CK_LOCAL_DIR = 'data/cve_checker'


# From lib/cve_check/views.py
CK_UNDEFINED = 0
CK_UNPATCHED = 1
CK_IGNORED = 2
CK_PATCHED = 3

verbose = False
test = False
cmd_count = 0
cmd_skip = 0
force_update = False

#################################
# Helper methods
#

# quick development/debugging support
def _log(msg):
    DBG_LVL =  os.environ['SRTDBG_LVL'] if ('SRTDBG_LVL' in os.environ) else 2
    DBG_LOG =  os.environ['SRTDBG_LOG'] if ('SRTDBG_LOG' in os.environ) else '/tmp/srt_dbg.log'
    if 1 == DBG_LVL:
        print(msg)
    elif 2 == DBG_LVL:
        f1=open(DBG_LOG, 'a')
        f1.write("|" + msg + "|\n" )
        f1.close()

def srtsetting_get(conn,key,default_value,is_dict=True):
    cur = SQL_CURSOR(conn)
    # Fetch the key for SrtSetting
    sql = f"""SELECT * FROM orm_srtsetting WHERE `name` = ?"""
    try:
        srtsetting = SQL_EXECUTE(cur, sql,(key,)).fetchone()
        if srtsetting:
            if is_dict:
                return(srtsetting['value'])
            else:
                return(srtsetting[ORM.SRTSETTING_VALUE])
    except Exception as e:
        print(f"ERROR:{e}")
    return(default_value)

def srtsetting_set(conn,key,value,is_dict=True):
    cur = SQL_CURSOR(conn)
    # Set the key value for SrtSetting
    sql = f"""SELECT * FROM orm_srtsetting WHERE `name` = ?"""
    srtsetting = SQL_EXECUTE(cur, sql,(key,)).fetchone()
    if not srtsetting:
        sql = ''' INSERT INTO orm_srtsetting (name, helptext, value) VALUES (?,?,?)'''
        SQL_EXECUTE(cur, sql, (key,'',value))
        if verbose: print(f"INSERT:{key}:{value}:")
    else:
        if verbose: print(f"UPDATE[{srtsetting[ORM.SRTSETTING_ID]}]:{key}:{value}:")
        sql = ''' UPDATE orm_srtsetting
                  SET value=?
                  WHERE id=?'''
        if is_dict:
            SQL_EXECUTE(cur, sql, (value,srtsetting['id']))
        else:
            SQL_EXECUTE(cur, sql, (value,srtsetting[ORM.SRTSETTING_ID]))
    SQL_COMMIT(conn)

def do_chdir(newdir,delay=0.200):
    os.chdir(newdir)
    # WARNING: we need a pause else the chdir will break
    # susequent commands (e.g. 'git clone' and 'git checkout')
    time.sleep(delay)

def do_makedirs(newdir,delay=0.200):
    try:
        os.makedirs(newdir)
    except:
        # dir already exists
        pass
    # WARNING: we need a pause else the makedirs could break
    # susequent commands (e.g. 'git clone' and 'git checkout')
    time.sleep(delay)

#
# Sub Process calls
# Enforce that all scripts run from the SRT_BASE_DIR context (re:WSGI)
#
def execute_process(*args):
    # Only string-type parameters allowed
    cmd_list = []
    for arg in args:
        if not arg: continue
        if isinstance(arg, (list, tuple)):
            # Flatten all the way down
            for a in arg:
                if not a: continue
                cmd_list.append(str(a))
        else:
            cmd_list.append(str(arg))

    result = subprocess.run(cmd_list, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    return(result.returncode,result.stdout.decode('utf-8'),result.stderr.decode('utf-8'))

def execute_commmand(cmnd,path=''):
    print(f"FOO1:EXECUTE_COMMMAND:{cmnd}:{path}:")
    cwd = os.getcwd()
    if path:
        do_chdir(path,0.4)
    print(f"FOO2:EXECUTE_COMMMAND:{os.getcwd()}:")
    result_returncode,result_stdout,result_stderr = execute_process(cmnd)
    if 0 != result_returncode:
        print(f"execute_commmand[{os.getcwd()}]|{cmnd}|{result_stdout}|")
        print(f"ERROR({result_returncode}):{result_stderr}")
        return(1)
    if verbose:
        print(f"execute_commmand[{os.getcwd()}]|{cmnd}|{result_stdout}|")
    if path:
        do_chdir(cwd)

# Insure the git repo is cloned and available
def prepare_git(repo_dir,repo_url,branch):
    if True or verbose: print(f"prepare_git:({repo_dir},{repo_url})")
    if not os.path.isdir(repo_dir):
        repo_parent_dir = os.path.dirname(repo_dir)
        do_makedirs(repo_parent_dir)
        print(f"= Clone '{REMOTE_URL}' ... =")
        cmnd=['git','clone',REMOTE_URL]
        execute_commmand(cmnd,repo_parent_dir)
    else:
        print(f"= Clone '{REMOTE_URL}' skip ... =")

    if branch:
        print("= Checkout branch '{BRANCH}' ... =")
        cmnd=['git','-C',repo_dir,'checkout',branch]
        execute_commmand(cmnd)

    # Get the latest data with a safety pull
    print("= Pull  ... =")
    cmnd=['git','-C',repo_dir,'pull']
    execute_commmand(cmnd)

# Compute a sortable CVE name
def get_name_sort(cve_name):
    try:
        a = cve_name.split('-')
        cve_name_sort = '%s-%s-%07d' % (a[0],a[1],int(a[2]))
    except:
        cve_name_sort = cve_name
    return cve_name_sort

def score2cve_score(score):
    try:
        return(float(score))
    except:
        return(0.0)

def cve_score2severity(score):
    score_num = score2cve_score(score)
    if score_num < 2.5:
        severity = "Low"
    elif score_num < 5.0:
        severity = "Medium"
    elif score_num < 7.5:
        severity = "High"
    else :
        severity = "Critical"
    return(severity)

def cve_scores2priority(score_v2,score_v3):
    score_num = max(score2cve_score(score_v2),score2cve_score(score_v3))
    if score_num < 2.5:
        priority = ORM.PRIORITY_LOW
    elif score_num < 5.0:
        priority = ORM.PRIORITY_MEDIUM
    elif score_num < 7.5:
        priority = ORM.PRIORITY_HIGH
    else :
        priority = ORM.PRIORITY_CRITICAL
    return(priority)

def status2orm_ck(status):
    if 'Unpatched' == status:
        return (CK_UNPATCHED,ORM.STATUS_VULNERABLE)
    elif 'Patched' == status:
        return (CK_PATCHED,ORM.STATUS_NOT_VULNERABLE)
    elif 'Ignored' == status:
        return (CK_IGNORED,ORM.STATUS_NOT_VULNERABLE)
    else:
        return (CK_UNDEFINED,ORM.STATUS_NEW)

def count_ck_records(cur):
    def count_rows(table_name):
        cur.execute(f"SELECT COUNT(*) FROM {table_name}")
        return(cur.fetchone()[0])
    Ck_Audit_cnt            = count_rows('cve_checker_Ck_Audit')
    Ck_Package_cnt          = count_rows('cve_checker_Ck_Package')
    Ck_Product_cnt          = count_rows('cve_checker_Ck_Product')
    Ck_Layer_cnt            = count_rows('cve_checker_Ck_Layer')
    CkPackage2Cve_cnt       = count_rows('cve_checker_CkPackage2Cve')
    CkPackage2CkProduct_cnt = count_rows('cve_checker_CkPackage2CkProduct')
    return(Ck_Audit_cnt, Ck_Package_cnt, Ck_Product_cnt, Ck_Layer_cnt, CkPackage2Cve_cnt, CkPackage2CkProduct_cnt)

#################################
# Check Auto Builder CVE_Checker output files
#
# e.g. https://git.yoctoproject.org/yocto-metrics/tree/cve-check/master/1697612432.json
#
# Unit tests:
#   bin/cve_checker/srtool_cvechecker.py --validate-cvechk-ab master -v
#   bin/cve_checker/srtool_cvechecker.py --validate-cvechk-ab dunfell -v
#

def validate_cvechk_ab(release):
    repo_dir = os.path.join(srtool_basepath,LOCAL_DIR)
    LOCAL_PATH = f'cve-check/{release}'

    # Insure that the repo is present and updated
    prepare_git(repo_dir,REMOTE_URL,BRANCH)

    # Find the JSON file
    json_dir = os.path.join(repo_dir,LOCAL_PATH)
    file_list = []
    for root, dirs, files in os.walk(json_dir):
        for i,file in enumerate(files):
            if not file.endswith('.json'):
                continue
            file_list.append(file)
    print(f"CVKCHK JSON file count = {len(file_list)}")

    progress_set_max(len(file_list))
    # Scan the JSON files
    print(f"Release = {release}")
    for i,json_file in enumerate(file_list):

        # Debugging support
        if cmd_skip and (i < cmd_skip):
            continue
        if cmd_count and (i > (cmd_skip + cmd_count)):
            continue

        with open(os.path.join(json_dir,json_file)) as json_data:
            progress_show(json_file)
            try:
                dct = json.load(json_data)
            except Exception as e:
                print(f"ERROR:JSON_FILE_LOAD:{json_file}:{e}", file=sys.stderr)
                continue

            if 0 == (i % 20): print(f"{i:4}\r",end='',flush=True)

            for elem in dct:
                print(f"TOP ELEM:{elem}")

            multiple_products = []
            mismatch_products = []
            mismatch_iscves = []

            elem_packages = dct['package']
            print(f"PACKAGE COUNT:{len(elem_packages)}")
            for package in elem_packages:
                name = package['name']
                short_name = name.replace('-native','')

                package_products = package['products']
                if 1 != len(package_products):
                    s = f"{name}={len(package_products)}"
                    for product in package_products:
                        s += f":{product['product']}"
                    multiple_products.append(s)

                is_cves = ''
                for product in package_products:
                    if not is_cves:
                        is_cves = product['cvesInRecord']
                    if short_name != product['product']:
                        mismatch_products.append(f"{name}!={product['product']}")
                    if is_cves != product['cvesInRecord']:
                        mismatch_iscves.append(f"{name}:{is_cves} != {product['cvesInRecord']}")

    print(f"multiple_products:{len(multiple_products)}")
    for i,mp in enumerate(multiple_products):
        print(f"  {mp}")
        if i > 5: break
    print(f"mixed_products:{len(mismatch_products)}")
    for i,mp in enumerate(mismatch_products):
        print(f"  {mp}")
        if i > 5: break
    print(f"mixed_iscves:{len(mismatch_iscves)}")
    for i,mp in enumerate(mismatch_iscves):
        print(f"  {mp}")
        if i > 5: break
    progress_done('Done')

#################################
# Import Auto Builder CVE_Checker output files
#
# e.g. https://git.yoctoproject.org/yocto-metrics/tree/cve-check/master/1697612432.json
#
# Unit tests:
#   bin/cve_checker/srtool_cvechecker.py --import-cvechk 7,nanbield,nanbield -v   (7 = AB repo)
#   bin/cve_checker/srtool_cvechecker.py --import-cvechk 6,master,<none> -v     (6 = SSH import)
#

def import_cvechk(key,audit_name):
    conn = SQL_CONNECT(column_names=True)
    cur = SQL_CURSOR(conn)

    ck_import_id,ck_audit_key,ck_import_select = key.split(',')

    _log("Prepare Import channel")
    sql = """SELECT * FROM cve_checker_CkUploadManager WHERE id = ?"""
    ck_import = SQL_EXECUTE(cur, sql, (ck_import_id,)).fetchone()
    if not ck_import:
        print(f"ERROR: ck_import not found '{ck_import_id}'")
        exit(1)

    ck_json_list = []
    if 'Repo' == ck_import['import_mode']:
        # Isolate the repo's directory name from the local path (first dir)
        repo_dir_name = ck_import['path']
        pos = repo_dir_name.find('/')
        if pos > 0:
            repo_dir_name = repo_dir_name[0:pos]
        repo_dir = os.path.join(srtool_basepath,CK_LOCAL_DIR,repo_dir_name)
        repo_url = ck_import['repo']
        repo_branch = ck_import['branch']

        # Insure that the repo is present and updated
        _log("Prepare repo")
        print(f"FOO:prepare_git({repo_dir},{repo_url},{repo_branch})")
        prepare_git(repo_dir,repo_url,repo_branch)

        # Is the selector a file?
        if ck_import_select.endswith('.json'):
            ck_json_list.append(os.path.join(srtool_basepath,CK_LOCAL_DIR,ck_import['path'],ck_import_select))
        else:
            # Gather files from this sub-directory
            json_dir = os.path.join(srtool_basepath,CK_LOCAL_DIR,ck_import['path'],ck_import_select)
            for root, dirs, files in os.walk(json_dir):
                for i,file in enumerate(files):
                    if not file.endswith('.json'):
                        continue
                    ck_json_list.append(os.path.join(json_dir,file))
            if not ck_json_list:
                print(f"ERROR: no JSON files found in '{json_dir}'")
                exit(1)
            else:
                print(f"CVKCHK JSON file count = {len(ck_json_list)}")
    elif 'SSL' == ck_import['import_mode']:
        host,path = ck_import['path'].split(':')
        path = os.path.join(path,ck_import_select)
        ck_ssl_cp_list = []
        if path.endswith('.json'):
            ck_ssl_cp_list.append(path)
        else:
            cmnd = ['ssh','-i', ck_import['pem'], host, 'ls', path+'/*.json']
            exec_returncode,exec_stdout,exec_stderr = execute_process(*cmnd)
            for i,line in enumerate(exec_stdout.splitlines()):
                line = line.strip()
                ck_ssl_cp_list.append(line)
        print(f"FOUND_SSL_JSON={ck_ssl_cp_list}:")
        local_import_dir = os.path.join(srtool_basepath,'data/cve_checker/ssl')
        do_makedirs(local_import_dir)
        cmnd = ['scp','-i', ck_import['pem'], f"{host}:{path}"+"/*", local_import_dir]
        exec_returncode,exec_stdout,exec_stderr = execute_process(*cmnd)
        for file in ck_ssl_cp_list:
            ck_json_list.append(os.path.join(local_import_dir,os.path.basename(file)))

    elif 'Upload' == ck_import['import_mode']:
        print(f"FOO:UPLOAD:{ck_import_select}:")
        # Is the selector a file?
        if ck_import_select.endswith('.json'):
            print(f"FOO1:{ck_import_select}")
            ck_json_list.append(ck_import_select)
        else:
            print(f"ERROR: Upload: not a JSON file '{ck_import_select}'")
            exit(1)

    elif 'File' == ck_import['import_mode']:
        print(f"FOO:{ck_import['path']}:{ck_import_select}:")
        # Is the selector a file?
        if ck_import['path'].endswith('.json'):
            print(f"FOO1:{ck_import['path']}")
            ck_json_list.append(ck_import['path'])
        else:
            # Gather files from this sub-directory
            json_dir = os.path.join(ck_import['path'],ck_import_select)
            print(f"FOO2:CHECK:{json_dir}")
            for root, dirs, files in os.walk(json_dir):
                for i,file in enumerate(files):
                    if not file.endswith('.json'):
                        continue
                    ck_json_list.append(os.path.join(json_dir,file))
            if not ck_json_list:
                print(f"ERROR: no JSON files found in '{json_dir}'")
                exit(1)
            else:
                print(f"CVKCHK JSON file count = {len(ck_json_list)}")
    else:
        print(f"ERROR: import mode not recognized '{ck_import['import_mode']}'")
        exit(1)
    print(f"FOUND_JSON={ck_json_list}:")

    _log("Prepare ORM Products")
    sql = """SELECT * FROM orm_product WHERE `key` = ?"""
    orm_product = SQL_EXECUTE(cur, sql, (ck_audit_key,)).fetchone()
    if not orm_product:
        print(f"ERROR: release not found '{ck_audit_key}'")
        exit(1)

    # Find or create audit, just one per day per release
    _log("Prepare Audit record")
    audit_date = datetime.now()
    if not audit_name:
        audit_name = f"audit_{audit_date.strftime('%Y%m%d')}_{orm_product['key']}_"
    sql = f"""SELECT * FROM cve_checker_ck_audit WHERE `name` = ?"""
    found_audit = SQL_EXECUTE(cur, sql, (audit_name,)).fetchone()
    if found_audit:
        ck_audit_id = int(found_audit['id'])
        # Preclear audit's packages and their indexes
        sql = f"""SELECT * FROM cve_checker_ck_package WHERE ck_audit_id = ?"""
        for ck_package in SQL_EXECUTE(cur, sql, params=(ck_audit_id,)).fetchall():
            sql = f"""DELETE FROM cve_checker_ckpackage2cve WHERE ck_package_id = ?"""
            SQL_EXECUTE(cur, sql, params=(ck_package['id'],))
            sql = f"""DELETE FROM cve_checker_ckpackage2ckproduct WHERE ck_package_id = ?"""
            SQL_EXECUTE(cur, sql, params=(ck_package['id'],))
            sql = f"""DELETE FROM cve_checker_ck_package WHERE id = ?"""
            SQL_EXECUTE(cur, sql, params=(ck_package['id'],))
            SQL_COMMIT(conn)
    else:
        # Create a parent audit record
        sql = ''' INSERT INTO cve_checker_ck_audit (name, orm_product_id,create_time) VALUES (?, ?, ?)'''
        SQL_EXECUTE(cur, sql, (audit_name,orm_product['id'],audit_date,))
        ck_audit_id = SQL_GET_LAST_ROW_INSERTED_ID(cur)
        SQL_COMMIT(conn)
    if verbose: print(f"ck_audit_id={ck_audit_id}")

    # Scan the JSON files
    print(f"Release = {ck_audit_key}")
    if verbose:
        Ck_Audit_org, Ck_Package_org, Ck_Product_org, Ck_Layer_org, CkPackage2Cve_org, CkPackage2CkProduct_org = count_ck_records(cur)

    layer_id_cache = {}
    product_id_cache = {}
    cve_id_cache = {}
    layer_id_cache_hit = 0
    product_id_cache_hit = 0
    cve_id_cache_hit = 0
    added_cve = 0
    issue_cnt = 0

    # Prefetch the existing CVE IDs
    _log("Prepare CVE pre-fetch")
    print(f"Prefetch CVE IDs ...")
    sql = f"""SELECT id,name FROM orm_cve"""
    orm_cves = SQL_EXECUTE(cur, sql, ).fetchall()
    for orm_cve in orm_cves:
        layer_id_cache[orm_cve['name']] = orm_cve['id']

    for json_file in ck_json_list:
        with open(json_file) as json_data:
            try:
                dct = json.load(json_data)
            except Exception as e:
                print(f"ERROR:JSON_FILE_LOAD:{json_file}:{e}", file=sys.stderr)
                continue

            elem_packages = dct['package']
            print(f"PACKAGE COUNT:{len(elem_packages)}")
            progress_set_max(len(elem_packages))
            for i,package in enumerate(elem_packages):
                # Debugging support
                if cmd_skip and (i < cmd_skip):
                    continue
                if cmd_count and (i > (cmd_skip + cmd_count)):
                    continue
                if 0 == (i % 20): print(f"{i:4}\r",end='',flush=True)

                #
                # Extract the ck_package records
                #

                package_name = package['name']
                package_version = package['version']
                ck_layer_name = package['layer']
                progress_show(package_name)

                # Fetch or create the ck_layer
                ck_layer_id = 0
                if ck_layer_name in layer_id_cache:
                    ck_layer_id = layer_id_cache[ck_layer_name]
                    layer_id_cache_hit += 1
                if not ck_layer_id:
                    sql = f"""SELECT * FROM cve_checker_ck_layer WHERE "name" = ?"""
                    ck_layer = SQL_EXECUTE(cur, sql, params=(ck_layer_name,)).fetchone()
                    if ck_layer:
                        ck_layer_id = ck_layer['id']
                if not ck_layer_id:
                    # Create layer record
                    sql = ''' INSERT INTO cve_checker_ck_layer (name) VALUES (?)'''
                    SQL_EXECUTE(cur, sql, (ck_layer_name,))
                    ck_layer_id = SQL_GET_LAST_ROW_INSERTED_ID(cur)
                    SQL_COMMIT(conn)
                layer_id_cache[ck_layer_name] = ck_layer_id

                # Create ck_package record
                sql = ''' INSERT INTO cve_checker_ck_package (name,version,ck_layer_id,unpatched_cnt,ignored_cnt,patched_cnt,ck_audit_id) VALUES (?, ?, ?, ?, ?, ?, ?)'''
                params = (package_name,package_version,ck_layer_id,0,0,0,ck_audit_id)
                SQL_EXECUTE(cur, sql, params)
                ck_package_id = SQL_GET_LAST_ROW_INSERTED_ID(cur)
                SQL_COMMIT(conn)

                # Fetch or create the ck_products
                for product in package['products']:
                    ck_product_name = product['product']
                    ck_cvesInRecord = product['cvesInRecord']

                    ck_product_id = 0
                    if ck_product_name in product_id_cache:
                        ck_product_id = product_id_cache[ck_product_name]
                        product_id_cache_hit += 1
                    if not ck_product_id:
                        sql = f"""SELECT * FROM cve_checker_ck_product WHERE "name" = ?"""
                        ck_product = SQL_EXECUTE(cur, sql, params=(ck_product_name,)).fetchone()
                        if ck_product:
                            ck_product_id = ck_product['id']
                    if not ck_product_id:
                        # Create layer record
                        sql = ''' INSERT INTO cve_checker_ck_product (name) VALUES (?)'''
                        SQL_EXECUTE(cur, sql, (ck_product_name,))
                        ck_product_id = SQL_GET_LAST_ROW_INSERTED_ID(cur)
                        SQL_COMMIT(conn)
                        sql = f"""SELECT * FROM cve_checker_ck_product WHERE "name" = ?"""
                        ck_product = SQL_EXECUTE(cur, sql, params=(ck_product_name,)).fetchone()
                    product_id_cache[ck_product_name] = ck_product_id

                    # Create CkPackage2CkProduct
                    sql = ''' INSERT INTO cve_checker_ckpackage2ckproduct (ck_package_id,ck_product_id,cvesInRecord) VALUES (?, ?, ?)'''
                    params = (ck_package_id,ck_product_id,('Yes'==ck_cvesInRecord))
                    SQL_EXECUTE(cur, sql, params)
                    SQL_COMMIT(conn)

                # Fetch or create CVE records for issues
                unpatched_cnt =  0
                ignored_cnt =  0
                patched_cnt =  0
                for issue in package['issue']:
                    issue_cnt += 1
                    issue_id = issue['id']
                    ck_status,orm_status = status2orm_ck(issue['status'])
                    orm_comments = ''
                    orm_packages = ''
                    srtool_today = datetime.now()
                    print(f"CVE={issue_id}:Package={package_name}")

                    # increment status sums
                    if CK_UNPATCHED == ck_status:
                        unpatched_cnt += 1
                    elif CK_IGNORED == ck_status:
                        ignored_cnt += 1
                    elif CK_UNPATCHED == ck_status:
                        patched_cnt += 1

                    orm_cve_id = 0
                    if issue_id in cve_id_cache:
                        orm_cve_id = cve_id_cache[issue_id]
                        cve_id_cache_hit += 1
                    if not orm_cve_id:
                        sql = f"""SELECT * FROM orm_cve WHERE "name" = ?"""
                        orm_cve = SQL_EXECUTE(cur, sql, params=(issue_id,)).fetchone()
                        if orm_cve:
                            orm_cve_id = orm_cve['id']
                    if not orm_cve_id:
                        # Create a placehold CVE record until is it published and imported from NVD
                        sql_elements = [
                            'name',
                            'name_sort',
                            'priority',
                            'status',
                            'comments',
                            'comments_private',
                            'tags',
                            'cve_data_type',
                            'cve_data_format',
                            'cve_data_version',
                            'public',
                            'publish_state',
                            'publish_date',
                            'acknowledge_date',
                            'description',
                            'publishedDate',
                            'lastModifiedDate',
                            'recommend',
                            'recommend_list',
                            'cvssV3_baseScore',
                            'cvssV3_baseSeverity',
                            'cvssV2_baseScore',
                            'cvssV2_severity',
                            'packages',
                            'srt_updated',
                            'srt_created',
                            ]
                        sql_qmarks = []
                        for i in range(len(sql_elements)):
                            sql_qmarks.append('?')
                        sql_values = (
                            issue_id,
                            get_name_sort(issue_id),
                            cve_scores2priority(issue['scorev2'],issue['scorev3']),
                            orm_status,
                            orm_comments,
                            '',
                            '',
                            '',
                            '',
                            '',
                            True,
                            ORM.PUBLISH_UNPUBLISHED,
                            '',
                            None,
                            issue['summary'],
                            '',
                            '',
                            '',
                            '',
                            issue['scorev3'],
                            cve_score2severity(issue['scorev3']),
                            issue['scorev2'],
                            cve_score2severity(issue['scorev2']),
                            orm_packages,
                            srtool_today,
                            srtool_today
                        )
                        sql, params = 'INSERT INTO orm_cve (%s) VALUES (%s)' % (','.join(sql_elements),','.join(sql_qmarks)),sql_values
                        SQL_EXECUTE(cur, sql, params)
                        orm_cve_id = SQL_GET_LAST_ROW_INSERTED_ID(cur)
                        added_cve += 1
                        # Commit the new CVE and history
                        SQL_COMMIT(conn)

                        # Update package status sums
                        update_comment = "%s {%s}" % (ORM.UPDATE_CREATE_STR % ORM.UPDATE_SOURCE_CVE,'Created from CVE Checker')
                        sql = '''INSERT INTO orm_cvehistory (cve_id, comment, date, author) VALUES (?,?,?,?)'''
                        SQL_EXECUTE(cur, sql, (orm_cve_id,update_comment,srtool_today.strftime(ORM.DATASOURCE_DATE_FORMAT),ORM.USER_SRTOOL_NAME,) )
                        SQL_COMMIT(conn)

                    cve_id_cache[issue_id] = orm_cve_id

                    # Create CkPackage2Cve
                    sql = ''' INSERT INTO cve_checker_ckpackage2cve (ck_package_id,orm_cve_id,ck_status,ck_audit_id) VALUES (?,?,?,?)'''
                    SQL_EXECUTE(cur, sql, (ck_package_id,orm_cve_id,ck_status,ck_audit_id,))

                    # Update counts in the CK_Package
                    sql = ''' UPDATE cve_checker_ck_package
                              SET unpatched_cnt = ?, ignored_cnt = ?, patched_cnt = ?
                              WHERE id=?'''
                    SQL_EXECUTE(cur, sql, (unpatched_cnt,ignored_cnt,patched_cnt,ck_package_id))

                    # Commit these records
                    SQL_COMMIT(conn)


    if verbose:
        Ck_Audit_cnt, Ck_Package_cnt, Ck_Product_cnt, Ck_Layer_cnt, CkPackage2Cve_cnt, CkPackage2CkProduct_cnt = count_ck_records(cur)
        print(f"Packages                 = {len(elem_packages)}")
        print(f"Ck_Audit diff            = {Ck_Audit_cnt - Ck_Audit_org}")
        print(f"Ck_Package diff          = {Ck_Package_cnt - Ck_Package_org}")
        print(f"Ck_Product diff          = {Ck_Product_cnt - Ck_Product_org}")
        print(f"Ck_Layer diff            = {Ck_Layer_cnt - Ck_Layer_org}")
        print(f"CkPackage2Cve diff       = {Ck_Audit_cnt - Ck_Layer_org}")
        print(f"CkPackage2CkProduct diff = {Ck_Audit_cnt - Ck_Audit_org}")
        print(f"Issue count              = {issue_cnt}")
        print(f"Added Orm_CVE records    = {added_cve}")
        print(f"layer_id_cache_hit       = {layer_id_cache_hit}")
        print(f"product_id_cache_hit     = {product_id_cache_hit}")
        print(f"cve_id_cache_hit         = {cve_id_cache_hit}")

    progress_done('Done')
    SQL_COMMIT(conn)
    SQL_CLOSE_CUR(cur)
    SQL_CLOSE_CONN(conn)

#################################
# update_imports
#

def update_imports():
    conn = SQL_CONNECT(column_names=True)
    cur = SQL_CURSOR(conn)
    now = datetime.now(pytz.utc)

    _log("Update Import channel lists")
    sql = """SELECT * FROM cve_checker_CkUploadManager"""
    for ck_import in SQL_EXECUTE(cur, sql, ).fetchall():
        # 2023-11-20T07:19:47.033Z
        select_refresh = ck_import['select_refresh'][:26]
        print(f"FOO1:{select_refresh}")
        select_refresh = datetime.strptime(select_refresh,'%Y-%m-%d %H:%M:%S.%f')
        select_refresh = select_refresh.replace(tzinfo=pytz.utc)
        # Update no more that every 10 minutes
        delta = now - select_refresh
        print(f"FOO2:{delta} = {now} - {select_refresh}")
        if (1 > delta.days) and ((10 * 60) > delta.seconds) and (not force_update):
            continue

        ck_list = []
        if 'Repo' == ck_import['import_mode']:
            # Isolate the repo's directory name from the local path (first dir)
            repo_dir_name = ck_import['path']
            pos = repo_dir_name.find('/')
            if pos > 0:
                repo_dir_name = repo_dir_name[0:pos]
            repo_dir = os.path.join(srtool_basepath,CK_LOCAL_DIR,repo_dir_name)
            repo_url = ck_import['repo']
            repo_branch = ck_import['branch']

            # Insure that the repo is present and updated
            _log("Prepare repo")
            print(f"FOO:prepare_git({repo_dir},{repo_url},{repo_branch})")
            prepare_git(repo_dir,repo_url,repo_branch)

            # Is the selector a file?
            if ck_import['path'].endswith('.json'):
                pass
            else:
                # Gather files from this sub-directory
                json_dir = os.path.join(srtool_basepath,CK_LOCAL_DIR,ck_import['path'])
                for root, dirs, files in os.walk(json_dir,topdown=True):
                    print(f"BAR:{dirs}:{files}:")
                    for i,dir in enumerate(dirs):
                        ck_list.append(dir)
                    for i,file in enumerate(files):
                        if file.endswith('.json'):
                            ck_list.append(file)
                    # Only the first level
                    break

        elif 'SSL' == ck_import['import_mode']:
            host,path = ck_import['path'].split(':')
            cmnd = ['ssh','-i', ck_import['pem'], host, 'ls', path]
            exec_returncode,exec_stdout,exec_stderr = execute_process(*cmnd)
            for i,line in enumerate(exec_stdout.splitlines()):
                line = line.strip()
                ck_list.append(line)

        elif 'File' == ck_import['import_mode']:
            # Is the selector a file?
            if ck_import['path'].endswith('.json'):
                # Put the file's name in the list
                ck_list.append(os.path.basename(ck_import['path']))
            else:
                # Gather files from this sub-directory
                json_dir = ck_import['path']
                for root, dirs, files in os.walk(json_dir,topdown=True):
                    print(f"BAR:{dirs}:{files}:")
                    for i,dir in enumerate(dirs):
                        ck_list.append(dir)
                    for i,file in enumerate(files):
                        if file.endswith('.json'):
                            ck_list.append(file)
                    # Only the first level
                    break

        if ck_list:
            ck_list.sort()
            print(f"FOUND_SELECTS[{ck_import['id']}]={ck_list}:")
            sql = ''' UPDATE cve_checker_CkUploadManager
                      SET select_list=?, select_refresh = ?
                      WHERE id=?'''
            SQL_EXECUTE(cur, sql, ('|'.join(ck_list),now,ck_import['id']))


    SQL_COMMIT(conn)
    SQL_CLOSE_CUR(cur)
    SQL_CLOSE_CONN(conn)

#################################
# new_to_historical
#
# For boot strapping an installation,
#  set triage CVE set going forward
#
# Range is either 'all', or all CVEs
# before a given end published date
#

def new_to_historical(end_date):
    conn = SQL_CONNECT(column_names=True)
    cur = SQL_CURSOR(conn)

    is_all = True
    if 'all' != end_date:
        is_all = False
        try:
            pub_date = datetime.strptime(end_date,'%Y-%m-%d')
        except:
            print(f"ERROR: pub date not in YYYY-MM-DD: '{end_date}'")
            exit(1)

    # SRTool Status
    HISTORICAL = 0
    NEW = 1
    status_changes = {}

    sql = """SELECT name,publishedDate,id FROM orm_cve where status = ?"""
    for cve in SQL_EXECUTE(cur, sql, (NEW,) ).fetchall():
        name = cve['name']

        cve_year = name[:name.find('-',5)]
        if not cve_year in status_changes:
            status_changes[cve_year] = [0,0,0]
        status_changes[cve_year][0] += 1

        if (not is_all) and (cve['publishedDate'] > end_date):
            status_changes[cve_year][2] += 1
            continue
        status_changes[cve_year][1] += 1

        if not test:
            sql = ''' UPDATE orm_cve
                      SET status=?
                      WHERE id=?'''
            SQL_EXECUTE(cur, sql, (HISTORICAL,cve['id']))

    print("\n Results")
    print("Year        Found Changed    Kept")
    for cve_year in sorted(status_changes.keys()):
        print(f"{cve_year}: {status_changes[cve_year][0]:7} {status_changes[cve_year][1]:7} {status_changes[cve_year][2]:7}")

    if not test:
        SQL_COMMIT(conn)
    else:
        print(f"NOTE: changes not committed due to 'test' flag")

    SQL_CLOSE_CUR(cur)
    SQL_CLOSE_CONN(conn)

#################################
# main loop
#

def main(argv):
    global verbose
    global test
    global force_update
    global cmd_count
    global cmd_skip

    parser = argparse.ArgumentParser(description='srtool_cve_checker.py: CVE Checker results import')

    parser.add_argument('--import-cvechk', '-i', dest='import_cvechk', help='Import an audit channel')
    parser.add_argument('--audit-name', '-n', dest='audit_name', help='Name for audit')
    parser.add_argument('--progress', '-P', action='store_true', dest='do_progress', help='Progress output')

    parser.add_argument('--update-imports', '-u', action='store_true', dest='update_imports', help='Update the import lists')
    parser.add_argument('--new-to-historical', dest='new_to_historical', help="Change 'new' cves to 'historical' for 'all' or since pub date [all|yyyy-mm-dd]")

    # Test
    parser.add_argument('--validate-cvechk-ab', '-V', dest='validate_cvechk_ab', help='Validate the AB cve-checker JSON file')

    # Debugging support
    parser.add_argument('--force', '-f', action='store_true', dest='force_update', help='Force update')
    parser.add_argument('--test', '-t', action='store_true', dest='test', help='Test, dry-run')
    parser.add_argument('--count', dest='count', help='Debugging: short run record count')
    parser.add_argument('--skip', dest='skip', help='Debugging: skip record count')
    parser.add_argument('--verbose', '-v', action='store_true', dest='verbose', help='Verbose debugging')
    parser.add_argument('--local-job', action='store_true', dest='local_job', help='Use local job')
    args = parser.parse_args()

    ret = 0
    verbose = args.verbose
    test = args.test
    force_update = args.force_update
    cmd_count = int(args.count) if args.count else 0
    cmd_skip = int(args.skip) if args.skip else 0
    progress_set_on(args.do_progress)

    if args.validate_cvechk_ab:
        validate_cvechk_ab(args.validate_cvechk_ab)
    elif args.import_cvechk:
        import_cvechk(args.import_cvechk,args.audit_name)
    elif args.update_imports:
        update_imports()
    elif args.new_to_historical:
        new_to_historical(args.new_to_historical)

    elif args.drop_ck_tables:
        drop_ck_tables()

    else:
        print("srtool_cve_checker.py:Command not found")
        ret = 1

    progress_done('Done')
    return(ret)


if __name__ == '__main__':
    srtool_basepath = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(sys.argv[0]))))
    exit( main(sys.argv[1:]) )