qualysetl 0.6.45

Qualys API Best Practices Series - ETL Blueprint Example Code within Python Virtual Environment

Qualys API Best Practices Series

BETA Release of Example code from the Qualys API Best Practices Series enhanced with some packaging and operational capability.

An accompanying video will be available from Qualys by June 25th, 2021, Part 3 - Host List Detection.
Hold off on using until the Part 3 video is released as part of Qualys API Best Practices Series See Roadmap for additonal details.

Table of contents

• Quick Start
  • Install
  • Uninstall
• Qualys API Best Practices Series
  • Component Diagram
  • Blueprint
  • Technologies
• ETL Examples
  • ETL KnowledgeBase
  • ETL Host List
  • ETL Host List Detection
• Application Manager and Data
  • Host List Detection SQLite Database
  • Data Formats
  • Host List Tag Requirements
  • Logging
  • Application Monitoring
• Securing Your Application in the Data Center
  • Password Vault
• Example Run Logs
  • Uninstall and Install qetl
  • qetl_setup_python_env
  • qetl_manage_user
  • qetl_manage_user Add User
  • qetl_manage_user ETL KnowledgeBase
  • Review ETL KnowledgeBase Data
  • Crontab ETL KnowledgeBase Run Hourly
• Roadmap
• License

Quick Start

Hold off on running until in beta >0.8.0 release. Contact David Gregory for details.

Install

Install /opt/qetl Example. Precreate your /opt/qetl directory.

mkdir /opt/qetl   # Create as root and chown/chgrp of qetl directory to user running qualysetl
sudo apt-get install python3-venv   
sudo apt install python3-pip
deactivate  # If you are in a python virtual environment
python3 -m pip install qualysetl
cd ~/.local/bin
./qetl_setup_python_venv /opt/qetl
cd /opt/qetl/qetl_venv/bin 
source activate 
./qetl_manager_user   # Will print help screen.

Uninstall

deactivate  # If you are in a python virtual environment
python3 -m pip uninstall qualysetl
cd /opt/qetl/
rm -ir qetl_venv 

Additional Installation Instructions

• Jump to Installation
• Jump to ETL Examples to transform Qualys data into CSV, JSON and SQLite Databases.

Qualys API Best Practices Series

The example code from the Qualys API Best Practices Series is being hosted here to help customers with an example blueprint to automate transformation of data into their corporate data systems, further enhancing the visibility of outlier systems that are vulnerable.

This example code has been enhanced with some exception processing, logging, and a single point of execution creating an operational context within which to test/develop the code so customers can build automation into their remediation program.

Component Diagram

The component diagram depicts major system interoperability components that deliver data into the enterprise.

Component	Color	Purpose
Execution Environment	Blue	Host and Cloud where this application operates
Application	Grey	Application context to identify Local Docker, Python Application, Host and/or Filesystems
Input	Orange	Qualys data consumed by application
Execution	Green	Execution ETL of Qualys Data through various methods. (The Python Execution Environment on Docker or Traditional Host)
Data	Yellow	Host Data Folders that separate Application, and Subscription Data Users along with distribution pipelines representing the distribution of data to external sources, Cloud, Client, Other
Future	Black	TBD Future State Components such as GraphQL Server.

Blueprint

Customer have many options for Qualys API integration today. Some customers realize they need to develop their own internal code to transform complex data, to make data more accessible within their organizations for metrics and custom reporting.

As a result, Qualys decided on creating the API Best Practices Series to jumpstart clients with a blueprint of example code to help them automate delivery of complex data into their enterprise.

The overarching goal is to simplify our customers security stack and help them significantly reduce cost and complexity.

Key Goals and Solutions of this series are:

Goal	Solution
Automate Vulnerability Data accessibility, transformation of complex data for analysis	JSON, CSV, SQLite Database Formats of Qualys Data readily accessible to Analytical BI Tools for on-demand analysis or for downstream loading into Enterprise Data Storage.
A single query interface to Qualys data	TBD Future GraphQL Server interface to data.
Automate Capturing Vulnerability Data into corporate processes	Blueprint of example code customers can customize to enhance their internal automation "API-First" strategy.
Automate Distribution of Vulnerability Data to Cloud Providers	Optional Distribution methods into cloud systems such as Amazon S3 Bucket
Automate Application Enhancements and Delivery	Docker application instance for reliable CI/CD delivery of enhancements, as well as traditional host execution on Linux Platforms.
Provide Execution Flexibility, Work Load Management, Password Security	Blueprint for enterprise jobstream execution (Ex. Autosys), password vaults (Ex. Hashicorp), or simple command line execution from a Virtual Machine instance of Ubuntu running on a laptop.
Provide Continous Vulnerability Data Pipeline	Blueprint for data transformation pipeline from Qualys to Enterprise Data Stores in various formats ( JSON, CSV, SQLite Database )

Technologies

Project is tested with:

1. Ubuntu version: 20.04
2. SQLite3 version: 3.31.1
3. GNU DBM version: 1.18
4. Python version: 3.8.5
5. Qualys API: latest

Installation

Prerequisites Python Module

1. Ubuntu 20.04 - Note: Other Linux/MacOS may work. Install GNU Home Brew Tools if attempting MacOs Install.
2. Python 3.8.5 or Latest Stable Release
3. Python Virtual Environment - OS will prompt you if you need additional packages.
4. Disk Space on Host.
   • 100,000 hosts, expect ~400 Gigabytes for full copy of VM Data (Confirmed, Potential, Info Gathered)
     • KnowledgeBase - expect ~1 Gigabyte.
     • Host List - expect ~10 Gigabyte for 100K Hosts.
     • Host List Detection - expect ~300-400 Gigabytes for 100K Hosts.

Install from pypi.org

mkdir /opt/qetl   # Create as root and chown/chgrp of qetl directory to user running qualysetl
deactivate  # If you are in a python virtual environment
sudo apt-get install python3-venv  # Ensure you have the python3-venv installed
sudo apt install python3-pip
python3 -m pip install qualysetl
cd ~/.local/bin
./qetl_setup_python_venv /opt/qetl
cd /opt/qetl/qetl_venv/bin 
source activate 
./qetl_manager_user 

ETL Examples

• Create XML, JSON, CSV and SQLite3 Database Formats of Qualys Data.

ETL Configuration

• Configuration file: /opt/qetl/users/[quser]/qetl_home/config/etld_config_settings.yaml
• Ensure you set these configurations:
  1. host_list_detection_concurrency_limit: 2
     Set this to appropriate qualys concurrency limit value after reviewing the [Qualys Limits Guide] https://www.qualys.com/docs/qualys-api-limits.pdf with your TAM for Questions.
  2. host_list_detection_limit_hosts: 50
     Set this to 1000000. It is defaulted to 50 for first run testing.
  3. [kb, host_list, host_list_detection]_csv_truncate_cell_limit variables
     Truncates cells to integer value in both csv and sqlite database. Set to what you are comfortable with. It defaults to 32K to ensure you can read the csv in excel, and it also truncates the cell size sent to the sqlite database.

(qetl_venv) qualysetl@ubuntu:~/.local/bin$ more /opt/qetl/users/tamab_dt4/qetl_home/config/etld_config_settings.yaml 
#
# This file is generated by qetl_manage_user only on first invocation.
# File generated by qetl_manage_user on: 2021-06-17 20:40:59
#
# YAML File of available configuration options for Qualys API Calls and future options.
#
# kb_last_modified_after: 'default'                  # Leave at default.  Knowledgebase is auto-incremental to full knowledgebase.
# kb_export_dir: 'default'                           # Leave at default.  If local directory path is added, the program will copy csv, json, sqlite files there.
# kb_payload_option: 'default'                       # Leave at default until future use is developed.
# kb_csv_truncate_cell_limit: 32000                  # Truncate all cells to integer.  Useful for reading csv in excel. Will also truncate database cells.
#
# host_list_vm_processed_after: 'default'            # Leave at default.  handle dates through qetl_manage_user
# host_list_payload_option: 'notags'                 # Leave at 'notags' until future use is developed.
# host_list_export_dir: 'default'                    # Leave at default.  If local directory path is added, the program will copy the csv, json, sqlite files there.
# host_list_csv_truncate_cell_limit: 32000           # Truncate all cells to integer.  Useful for reading csv in excel. Will also truncate database cells.
#
# host_list_detection_payload_option: 'default'      # Leave at 'default' until future use is developed.
# host_list_detection_export_dir: 'default'          # Leave at default.  If local directory path is added, the program will copy the csv, json, sqlite files there.
# host_list_detection_vm_processed_after: 'default'  # Leave at default.  handle dates through qetl_manage_user
# host_list_detection_concurrency_limit: 2           # Reset based on limits guide:  https://www.qualys.com/docs/qualys-api-limits.pdf
# host_list_detection_multi_proc_batch_size: 750     # Reset to max batch size.
# host_list_detection_limit_hosts: 50                # Reset to max hosts you will download.
# host_list_detection_csv_truncate_cell_limit: 32000 # Truncate all cells to integer.  Useful for reading csv in excel. Will also truncate database cells.

kb_last_modified_after: 'default'
kb_export_dir: 'default'
kb_payload_option: 'default'
kb_csv_truncate_cell_limit: 32000

host_list_vm_processed_after: 'default'
host_list_payload_option: 'notags'
host_list_export_dir: 'default'
host_list_csv_truncate_cell_limit: 32000

host_list_detection_payload_option: 'default'
host_list_detection_export_dir: 'default'
host_list_detection_vm_processed_after: 'default'
host_list_detection_concurrency_limit: 2
host_list_detection_multi_proc_batch_size: 750
host_list_detection_limit_hosts: 50
host_list_detection_csv_truncate_cell_limit: 32000

ETL KnowledgeBase

KnowledgeBase ETL - Incremental Update to Knowledgebase. CSV, JSON, SQLite are full knowledgebase. XML is incremental.

qetl_manage_user -u /opt/qetl/users/quser -e etl_knowledgebase 

ETL Host List

Host List ETL - Download Host List based on date.

qetl_manage_user -u /opt/qetl/users/quser -e etl_host_list -d [YYYY-MM-DDThh:mm:ssZ]

See Application Manager and Data for location of your qetl_home directory.

ETL Host List Detection

Host List Detection ETL - Includes KnowledgeBase and Host List.

qetl_manage_user -u /opt/qetl/users/quser -e etl_host_list_detection -d [YYYY-MM-DDThh:mm:ssZ]

Application Manager and Data

qetl_manage_user application

• qetl_manage_user is your entry point to manage ETL of Qualys Data.

Host List Detection SQLite Database

• qetl_manage_user -e etl_host_list_detection -d [datetime] - Resulting sqlite database ready for distribution.

Host List Detection SQLite Tables

• qetl_manage_user -e etl_host_list_detection -d [datetime] - Resulting sqlite database ready for distribution.

Environment

• Python virtual environment
• Managed by qetl_manage_user
• Example options for qetl Home Directories:
  • Prod: /opt/qetl/users/[user_name]/qetl_home
  • Test: /usr/local/test/opt/qetl/users/[user_name]/qetl_home
  • Dev: $HOME/opt/qetl/users/[user_name]/qetl_home

Application Directories

Path	Description
opt/qetl/users/	Directory of All Users
opt/qetl/users/[user]/qetl_home	Parent directory path for a user
[user]/qetl_home	User Home Directory
qetl_home/bin	User bin directory for customer to host scripts they create.
qetl_home/cred	Credentials Directory
qetl_home/cred/.etld_lib_credentials.yaml	Credentials file in yaml format.
qetl_home/cred/.qualys_cookie	Cookie file used for Qualys session management.
qetl_home/config	Application Options Configuration Directory
qetl_home/config/etld_lib_config_settings.yaml	Application Options
qetl_home/log	Logs Directory
qetl_home/log/kb.log	KnowledgeBase Run Logs
qetl_home/log/host_list.log	Host List Run Logs
qetl_home/log/host_list_detection.log	Host List Detection Run Logs
qetl_home/data	Application Data Directory containing all csv, xml, json, sqlite database data.
qetl_home/data/kb.csv	Cumulative Knowledgebase CSV file.
qetl_home/data/kb_shelve	Cumulative Knowledgebase Python Shelve
qetl_home/data/kb.xml	Incremental update Knowledgebase XML File
qetl_home/data/kb.json	Cumulative Knowledgebase JSON Data
qetl_home/data/kb_sqlite.db	Cumulative Knowledgebase SQLite Database
qetl_home/data/kb_cve_qid_map.csv	Cumulative CVE -> QID List CSV File
qetl_home/data/kb_cve_qid_map_shelve	Cumulative CVE -> QID List Python Shelve
qetl_home/data/host_list.json	vm_last_processed Host List JSON Data
qetl_home/data/host_list_shelve	vm_last_processed Host List Python Shelve
qetl_home/data/host_list_sqlite.db	vm_last_processed Host List SQLite Database
qetl_home/data/host_list.csv	vm_last_processed Host List CSV File
qetl_home/data/host_list_other_file.xml	vm_last_processed Host List XML Data
qetl_home/data/host_list_detection.json	vm_last_processed Host List Detection JSON Data
qetl_home/data/host_list_detection_shelve	vm_last_processed Host List Detection Python Shelve
qetl_home/data/host_list_detection_sqlite.db	vm_last_processed Host List Detection SQLite Database
qetl_home/data/host_list_detection.csv	vm_last_processed Host List Detection CSV File
qetl_home/data/host_list_detection_xml_dir	vm_last_processed Host List Detection XML Data Dir

Data Formats

Data Formats created in qetl_home/data:

Format	Description
JSON	Java Script Object Notation useful for transfer of data between systems
CSV	Comma Separated Values useful for transfer of data between systems Formatted to help import data into various BI or Database Tools: Excel, Apache Open Office, Libre Office, Tableau, Microsoft PowerBI, SQL Database Loader
XML	Extensible Markup Language useful for transfer of data between systems
SQLite Database	SQLite Database: SQLite Database populated with Qualys Data, Useful as a self-contained SQL Database of Qualys Data for Analysis, Useful as an intermediary transformation into your overall Enterprise ETL Process, SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine

Host List Tag Requirements

To operate the host list API, optionally you can create 4 tags to isolate different types of data to be downloaded via API. There is an example script to help create the tags in your subscription.
See etld_templates/etld_create_host_list_extract_cloud_server_tags.py for example of creating tags through API.

Optional First Time Setup Host List Tags

Optional custom tags are qetl-all-hosts, qetl-all-ec2, qetl-all-gcp, qetl-all-azure.

 1) qetl-all-ec2: Type:Dynamic->Rule-Type:Cloud Asset Search->Provider:EC2->Rule Query:aws.ec2.accountId:*
 2) qetl-all-azure: Type:Dynamic->Rule-Type:Cloud Asset Search->Provider:Azure->Rule Query:azure.vm.subscriptionId:*
 3) qetl-all-gcp: Type:Dynamic->Rule-Type:Cloud Asset Search->Provider:GCP->Rule Query:gcp.compute.instanceId:*
 4) qetl-all-hosts: Type:Dynamic->Rule-Type:Groovy Scriptlet->Rule Query:return true;

 "qetl-all-ec2": {"ruleText": "aws.ec2.accountId:*", "ruleType": "CLOUD_ASSET",  "provider": "EC2"},
 "qetl-all-gcp": {"ruleText": "gcp.compute.instanceId:*", "ruleType": "CLOUD_ASSET",  "provider": "GCP"},
 "qetl-all-azure": {"ruleText": "azure.vm.subscriptionId:*", "ruleType": "CLOUD_ASSET",  "provider": "AZURE"},
 "qetl-all-hosts": {"ruleText": "return true;", "ruleType": "GROOVY",  "provider": "NONE"}

Logging

Logging fields are pipe delimited with some formatting for raw readability.

Format	Description
YYYY-MM-DD hh:mm:ss,ms	UTC Date and Time. UTC is used to match internal date and time within Qualys data.
Logging Level	INFO, ERROR, WARNING, etc. Logging levels can be used for troubleshooting or remote monitoring for ERROR/WARNING log entries.
Module Name	Top Level qetl Application Module Name that is executing
User Name	Operating System User executing this application.
Function Name	qetl Application Function Executing.
Message	qetl Application Messages describing actions, providing data.

See Application Directories for details of each log file.

cd qetl_home/log
head -3 kb.log
(qetl_venv) qualysetl@ubuntu:/opt/qetl/qetl_venv/bin$ cat /opt/qetl/users/qualys_user/qetl_home/log/kb.log | nl 
     1	2021-05-28 01:26:03,836 | INFO     | etl_knowledgebase    | dgregory        | setup_logging_stdout                | LOGGING SUCCESSFULLY SETUP FOR STREAMING
     2	2021-05-28 01:26:03,836 | INFO     | etl_knowledgebase    | dgregory        | setup_logging_stdout                | PROGRAM: ['/home/dgregory/opt/qetl/qetl_venv/bin/qetl_manage_user', '-u', '/opt/qetl/users/qualys_user', '-e', 'etl_knowledgebase']
     3	2021-05-28 01:26:03,897 | INFO     | etl_knowledgebase    | dgregory        | check_python_version                | Python version found is: ['3.8.5 (default, Jan 27 2021, 15:41:15) ', '[GCC 9.3.0]']
     4	2021-05-28 01:26:03,897 | INFO     | etl_knowledgebase    | dgregory        | get_sqlite_version                  | SQLite version found is: 3.31.1.
     5	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase    | dgregory        | set_qetl_code_dir                   | parent qetl code dir - /home/dgregory/opt/qetl/qetl_venv/lib/python3.8/site-packages

Application Monitoring

• To monitor the application for issues, the logging format includes a logging level.
• Monitoring for ERROR will help identify issues and tend to the overall health of the applicaiton operation.

Securing Your Application in the Data Center

Follow your corporate procedures for securing your application. A key recommendation is to use a password vault or remote invocation method that passes the credentials at run time so the password isn't stored on the system.

Password Vault

Qualys recommends customers move to a password vault of their chosing to operate this applications credentials. By creating functions to obtain credentials from your corporations password vault, you can improve the security of your application by separating the password from the machine.

One way customers can do this is through a work load management solution, where the external work load management system ( Ex. Autosys ) schedules jobs passing the required keys to this application at runtime. This eliminates the need to store credentials locally on your system.

If you are unfamiliar with password vaults, here is one example from Hashicorp.

• Hashicorp Products Vault
• Hashicorp Getting Started

Roadmap

Capability	Target	Description
KnowledgeBase	June 2021	Automate download and transform of KnowledgeBase into CSV, JSON and SQLite Database
Host List	June 2021	Automate download and transform of Host List into CSV, JSON and SQLite Database
Host List Detection	June 2021	Automate download and transform of Host List Detection into CSV, JSON and SQLite Database
Python Virtual Environment Package	June 2021	Encapsulate qetl Application into Python Virtual Environment at installation.
Docker Image	July 2021	Encapsulate Python Application into distributable docker image for ease os operation and upgrade.
Other Modules	TBD Q3/Q4 2021	Global IT Asset Inventory, Policy Compliance, distribution methods S3 Bucket, etc...

Example Run Logs

Uninstall and Install qetl

Uninstall

• Make sure you are not in your Python Virtual Environment when running uninstall. Notice the command prompt does not include (qetl_env). That means you have deactivated the Python3 Virtual Environment

(qetl_venv) qualysetl@ubuntu:~$ deactivate

qualysetl@ubuntu:~/.local/bin$ python3 -m pip uninstall qualysetl
Found existing installation: qualysetl 0.6.30
Uninstalling qualysetl-0.6.30:
  Would remove:
    /home/dgregory/.local/bin/qetl_setup_python_venv
    /home/dgregory/.local/lib/python3.8/site-packages/qualys_etl/*
    /home/dgregory/.local/lib/python3.8/site-packages/qualysetl-0.6.30.dist-info/*
Proceed (y/n)? y
  Successfully uninstalled qualysetl-0.6.30
qualysetl@ubuntu:~/.local/bin$ 

Install

• Make sure you are not in your Python Virtual Environment when installing this software. Notice the command prompt does not include (qetl_env).

(qetl_env) qualysetl@ubuntu:~$ deactivate
qualysetl@ubuntu:~$ python3 -m pip install qualysetl
Collecting qualysetl
  Downloading qualysetl-0.6.30-py3-none-any.whl (79 kB)
     |████████████████████████████████| 79 kB 1.8 MB/s 
Installing collected packages: qualysetl
Successfully installed qualysetl-0.6.30
qualysetl@ubuntu:~$ 

qetl_setup_python_env

qualysetl@ubuntu:~/.local/bin$ ./qetl_setup_python_venv /opt/qetl
Start qetl_setup_python_venv - Thu 17 Jun 2021 08:39:41 PM PDT
  1) test_os_for_required_commands
  2) test_for_pip_connectivity
  3) prepare_opt_qetl_env_dirs

    usage:        qetl_setup_python_venv [/path/to/dir]

    description:

        Create a python3 virtual environment, and install the qualysetl application into that environment for usage.
        This isolates the qualysetl application dependencies to the python3 virtual environment.

            If you plan to setup your python virtual environment outside of your default home directory,
            please ensure you have authorization to create directories in the target location.

    examples:
            1) qetl_setup_python_venv /opt/qetl
               - will work if you pre-create /opt/qetl as you have to be root to write to /opt
            2) qetl_setup_python_venv /usr/local
               - will result in /usr/local/opt/qetl if you have authorization to write to /usr/local
            3) qetl_setup_python_venv
               - Default will be your /home/qualysetl/opt/qetl directory.


Create qetl Python Environment? /opt/qetl/qetl_venv
Do you want to create your python3 virtual environment for qetl? ( yes or no ) yes

ok, creating python3 virtual /opt/qetl/qetl_venv


  4) create_qetl_python_venv - will run for about 1-2 minutes

     1	    Package         Version  
     2	    --------------- ---------
     3	    boto3           1.17.97  
     4	    botocore        1.20.97  
     5	    certifi         2021.5.30
     6	    chardet         4.0.0    
     7	    idna            2.10     
     8	    jmespath        0.10.0   
     9	    oschmod         0.3.12   
    10	    pip             20.0.2   
    11	    pkg-resources   0.0.0    
    12	    python-dateutil 2.8.1    
    13	    PyYAML          5.4.1    
    14	    qualysetl       0.6.35   
    15	    requests        2.25.1   
    16	    s3transfer      0.4.2    
    17	    setuptools      57.0.0   
    18	    six             1.16.0   
    19	    urllib3         1.26.5   
    20	    wheel           0.36.2   
    21	    xmltodict       0.12.0   


     1	    Name: qualysetl
     2	    Version: 0.6.35
     3	    Summary: Qualys API Best Practices Series - ETL Blueprint Example Code within Python Virtual Environment
     4	    Home-page: https://dg-cafe.github.io/qualysetl/
     5	    Author: David Gregory
     6	    Author-email: [e-mail], [e-mail]
     7	    License: Apache
     8	    Location: /opt/qetl/qetl_venv/lib/python3.8/site-packages
     9	    Requires: 
    10	    Required-by: 

   Success! Your python virtual environment for qetl is: /opt/qetl/qetl_venv

   Your python3 venv separates your base python installation from the qetl python requirements
   and is your entry to executing the qetl_manage_user application.  Your base qetl installation has
   moved to your python virtual environment: /opt/qetl/qetl_venv

   !!! save these commands as they are your entry to run the qetl application
   
       1) source /opt/qetl/qetl_venv/bin/activate
       2) /opt/qetl/qetl_venv/bin/qetl_manage_user  ( Your entry to executing qetl etl )

   Next steps:

    Enter your python3 virtual environment and begin testing qualys connectivity.

       1) source /opt/qetl/qetl_venv/bin/activate
       2) /opt/qetl/qetl_venv/bin/qetl_manage_user

End   qetl_setup_python_venv - Thu 17 Jun 2021 08:40:04 PM PDT
qualysetl@ubuntu:~/.local/bin$

qetl_manage_user

You can execute qetl_manage_user to see options available. To operate the qetl_manage_user application you'll first enter the python3 virtual environment, then execute qetl_manage_user.

(qetl_venv) qualysetl@ubuntu:~/.local/bin$ qetl_manage_user 
    
Please enter -u [ your opt/qetl/users/ user home directory path ]
    Note: opt/qetl/users/newuser is the root directory for your qetl userhome directory, 
         enter a new path including the opt/qetl/users/newuser 
         in the path you have authorization to write to.
         the prefix to your user directory opt/qetl/users is required.
         Examples:
            1) /usr/local/opt/qetl/users/newuser
            2) /opt/qetl/users/newuser
            3) /somepath/opt/qetl/users/newuser


        
    usage: qetl_manage_user [-h] -u qetl_USER_HOME_DIR [-e execute etl module] [-c] [-t] [-d] [-r] [-l]
    
    Setup and execute etl module for your qualys qetl users.  
    
    optional arguments:
      -h, --help                show this help message and exit
      -u Home Directory Path, --qetl_user_home_dir Home directory Path
                                   Include prefix opt/qetl/users/[user dir] 
                                   Examples:
                                   1) /home/dgregory/opt/qetl/users/q_username 
                                   2) /opt/qetl/users/q_username
      -e etl module name,     --execute_etl_module module name
                                  Ex.  -e etl_knowledgebase or -e etl_host_list or -e etl_host_list_detection
      -d YYMMDDThh:mm:ssZ,    --datetime        YYYY-MM-DDThh:mm:ssZ UTC. Get All Data On or After Date. 
      -c, --credentials       update qualys api user credentials: qualys uername, password or api_fqdn_server
      -t, --test              test qualys credentials
      -l, --logs              detailed logs sent to stdout
      -r, --report            brief report of the users directory structure.

qetl_manage_user Add User

To add a new user, execute qetl_manage_user -u [opt/users/your_new_user]. See example run log below.

qualysetl@ubuntu:~$ source /opt/qetl/qetl_venv/bin/activate
(qetl_venv) qualysetl@ubuntu:~$ qetl_manage_user

        
    
Please enter -u [ your opt/qetl/users/ user home directory path ]
    Note: opt/qetl/users/newuser is the root directory for your qetl userhome directory,
         enter a new path including the opt/qetl/users/newuser
         in the path you have authorization to write to.
         the prefix to your user directory opt/qetl/users is required.
         Examples:
            1) /usr/local/opt/qetl/users/newuser
            2) /opt/qetl/users/newuser
            3) /somepath/opt/qetl/users/newuser


        
    usage: qetl_manage_user [-h] -u qetl_USER_HOME_DIR [-e execute etl module] [-c] [-t] [-d] [-r] [-l]
    
    Setup and execute etl module for your qualys qetl users.
    
    optional arguments:
      -h, --help                show this help message and exit
      -u Home Directory Path, --qetl_user_home_dir Home directory Path
                                   Include prefix opt/qetl/users/[user dir]
                                   Examples:
                                   1) /home/dgregory/opt/qetl/users/q_username
                                   2) /opt/qetl/users/q_username
      -e etl module name,     --execute_etl_module module name
                                  Ex.  -e etl_knowledgebase or -e etl_host_list or -e etl_host_list_detection
      -d YYMMDDThh:mm:ssZ,    --datetime        YYYY-MM-DDThh:mm:ssZ UTC. Get All Data On or After Date.
      -c, --credentials       update qualys api user credentials: qualys uername, password or api_fqdn_server
      -t, --test              test qualys credentials
      -l, --logs              detailed logs sent to stdout
      -r, --report            brief report of the users directory structure.
     
    
(qetl_venv) qualysetl@ubuntu:~$ qetl_manage_user -u /opt/qetl/users/qqusr_dt4

qetl_user_home_dir does not exist: /opt/qetl/users/qqusr_dt4/qetl_home
Create new qetl_user_home_dir? /opt/qetl/users/qqusr_dt4/qetl_home ( yes or no ): yes

qetl_user_home_dir created: /opt/qetl/users/qqusr_dt4/qetl_home


Current username: initialuser in config: /opt/qetl/users/qqusr_dt4/qetl_home/cred/.etld_cred.yaml
Update Qualys username? ( yes or no ): yes
Enter new Qualys username: qqusr_dt4
Current api_fqdn_server: qualysapi.qualys.com
Update api_fqdn_server? ( yes or no ): no
Update password for username: qqusr_dt4
Update password? ( yes or no ): yes
Enter your Qualys password:
You have updated your credentials.
  Qualys Username: qqusr_dt4
  Qualys api_fqdn_server: qualysapi.qualys.com


Would you like to test login/logout of Qualys? ( yes or no ): yes

Qualys Login Test for qqusr_dt4 at api_fqdn_server: qualysapi.qualys.com

Testing Qualys Login for qqusr_dt4 Succeeded at qualysapi.qualys.com
    with HTTPS Return Code: 200.

Thank you, exiting.

(qetl_venv) qualysetl@ubuntu:~/opt/qetl/qetl_venv/bin$ 

qetl_manage_user ETL KnowledgeBase

(qetl_venv) qualysetl@ubuntu:~/opt/qetl/qetl_venv/bin$ qetl_manage_user -u /opt/qetl/users/qualys_user -e etl_knowledgebase
Starting etl_knowledgebase.  For progress see your /opt/qetl/users/qualys_user/qetl_home log directory
End      etl_knowledgebase.  For progress see your /opt/qetl/users/qualys_user/qetl_home log directory

(qetl_venv) qualysetl@ubuntu:~/opt/qetl/qetl_venv/bin$ cat /opt/qetl/users/qualys_user/qetl_home/log/kb.log | nl 
     1	2021-05-28 01:26:03,836 | INFO     | etl_knowledgebase    | dgregory        | setup_logging_stdout                | LOGGING SUCCESSFULLY SETUP FOR STREAMING
     2	2021-05-28 01:26:03,836 | INFO     | etl_knowledgebase    | dgregory        | setup_logging_stdout                | PROGRAM: ['/home/dgregory/opt/qetl/qetl_venv/bin/qetl_manage_user', '-u', '/opt/qetl/users/qualys_user', '-e', 'etl_knowledgebase']
     3	2021-05-28 01:26:03,897 | INFO     | etl_knowledgebase    | dgregory        | check_python_version                | Python version found is: ['3.8.5 (default, Jan 27 2021, 15:41:15) ', '[GCC 9.3.0]']
     4	2021-05-28 01:26:03,897 | INFO     | etl_knowledgebase    | dgregory        | get_sqlite_version                  | SQLite version found is: 3.31.1.
     5	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase    | dgregory        | set_qetl_code_dir                   | parent qetl code dir - /home/dgregory/opt/qetl/qetl_venv/lib/python3.8/site-packages
     6	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase    | dgregory        | set_qetl_code_dir                   | child qetl code dir  - /home/dgregory/opt/qetl/qetl_venv/lib/python3.8/site-packages/qualys_etl
     7	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase    | dgregory        | set_qetl_code_dir                   | etld_lib              - /home/dgregory/opt/qetl/qetl_venv/lib/python3.8/site-packages/qualys_etl/etld_lib
     8	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase    | dgregory        | set_qetl_code_dir                   | etld_templates        - /home/dgregory/opt/qetl/qetl_venv/lib/python3.8/site-packages/qualys_etl/etld_templates
     9	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase    | dgregory        | set_qetl_code_dir                   | etld_knowledgebase    - /home/dgregory/opt/qetl/qetl_venv/lib/python3.8/site-packages/qualys_etl/etld_knowledgebase
    10	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase    | dgregory        | set_qetl_code_dir                   | etld_host_list        - /home/dgregory/opt/qetl/qetl_venv/lib/python3.8/site-packages/qualys_etl/etld_host_list
    11	2021-05-28 01:26:03,900 | INFO     | etl_knowledgebase    | dgregory        | setup_user_home_directories         | parent user app dir  - /opt/qetl/users/qualys_user
    12	2021-05-28 01:26:03,900 | INFO     | etl_knowledgebase    | dgregory        | setup_user_home_directories         | user home directory  - /opt/qetl/users/qualys_user/qetl_home
    13	2021-05-28 01:26:03,900 | INFO     | etl_knowledgebase    | dgregory        | setup_user_home_directories         | qetl_user_root_dir   - User root dir       - /opt/qetl/users
    14	2021-05-28 01:26:03,900 | INFO     | etl_knowledgebase    | dgregory        | setup_user_home_directories         | qetl_user_home_dir   - qualys user         - /opt/qetl/users/qualys_user/qetl_home
    15	2021-05-28 01:26:03,900 | INFO     | etl_knowledgebase    | dgregory        | setup_user_home_directories         | qetl_user_data_dir   - xml,json,csv,sqlite - /opt/qetl/users/qualys_user/qetl_home/data
    16	2021-05-28 01:26:03,900 | INFO     | etl_knowledgebase    | dgregory        | setup_user_home_directories         | qetl_user_log_dir    - log files           - /opt/qetl/users/qualys_user/qetl_home/log
    17	2021-05-28 01:26:03,900 | INFO     | etl_knowledgebase    | dgregory        | setup_user_home_directories         | qetl_user_config_dir - yaml configuration  - /opt/qetl/users/qualys_user/qetl_home/config
    18	2021-05-28 01:26:03,900 | INFO     | etl_knowledgebase    | dgregory        | setup_user_home_directories         | qetl_user_cred_dir   - yaml credentials    - /opt/qetl/users/qualys_user/qetl_home/cred
    19	2021-05-28 01:26:03,900 | INFO     | etl_knowledgebase    | dgregory        | setup_user_home_directories         | qetl_user_bin_dir    - etl scripts         - /opt/qetl/users/qualys_user/qetl_home/bin
    20	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase    | dgregory        | load_etld_lib_config_settings_yaml       | etld_config_settings.yaml - kb_last_modified_after: default 
    21	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase    | dgregory        | load_etld_lib_config_settings_yaml       | etld_config_settings.yaml - kb_export_dir: default 
    22	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase    | dgregory        | load_etld_lib_config_settings_yaml       | etld_config_settings.yaml - host_list_vm_processed_after: default 
    23	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase    | dgregory        | load_etld_lib_config_settings_yaml       | etld_config_settings.yaml - host_list_payload_option: notags 
    24	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase    | dgregory        | setup_kb_vars                       | knowledgeBase config - /opt/qetl/users/qualys_user/qetl_home/config/etld_config_settings.yaml
    25	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase    | dgregory        | setup_kb_vars                       | kb_export_dir is direct from yaml
    26	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase    | dgregory        | setup_kb_vars                       | kb_last_modified_after utc.now minus 7 days - 2021-05-21T00:00:00Z
    27	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase    | dgregory        | setup_host_list_vars                | host list config - /opt/qetl/users/qualys_user/qetl_home/config/etld_config_settings.yaml
    28	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase    | dgregory        | setup_host_list_vars                | host_list_vm_processed_after utc.now minus 7 days - 2021-05-27T00:00:00Z
    29	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase    | dgregory        | setup_host_list_vars                | host_list_payload_option yaml - notags
    30	2021-05-28 01:26:03,906 | INFO     | etl_knowledgebase    | dgregory        | spawn_etl_in_background                             | Job PID 247944 kb_etl_workflow job running in background.
    31	2021-05-28 01:26:03,907 | INFO     | etl_knowledgebase    | dgregory        | kb_start_wrapper                    | __start__ kb_etl_workflow ['/home/dgregory/opt/qetl/qetl_venv/bin/qetl_manage_user', '-u', '/opt/qetl/users/qualys_user', '-e', 'etl_knowledgebase']
    32	2021-05-28 01:26:03,907 | INFO     | etl_knowledgebase    | dgregory        | kb_start_wrapper                    | data directory: /opt/qetl/users/qualys_user/qetl_home/data
    33	2021-05-28 01:26:03,907 | INFO     | etl_knowledgebase    | dgregory        | kb_start_wrapper                    | config file:    /opt/qetl/users/qualys_user/qetl_home/config/etld_config_settings.yaml
    34	2021-05-28 01:26:03,907 | INFO     | etl_knowledgebase    | dgregory        | kb_start_wrapper                    | cred yaml file: /opt/qetl/users/qualys_user/qetl_home/cred/.etld_cred.yaml
    35	2021-05-28 01:26:03,907 | INFO     | etl_knowledgebase    | dgregory        | kb_start_wrapper                    | cookie file:    /opt/qetl/users/qualys_user/qetl_home/cred/.etld_cookie
    36	2021-05-28 01:26:03,907 | INFO     | etl_knowledgebase    | dgregory        | kb_extract_wrapper                  | start knowledgebase_extract xml from qualys with kb_last_modified_after=2021-05-21T00:00:00Z
    37	2021-05-28 01:26:03,907 | INFO     | etl_knowledgebase    | dgregory        | knowledgebase_extract                          | start
    38	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase    | dgregory        | get_cred                            | Found your subscription credentials file:  /opt/qetl/users/qualys_user/qetl_home/cred/.etld_cred.yaml
    39	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase    | dgregory        | get_cred                            |      username:         quays93
    40	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase    | dgregory        | get_cred                            |      api_fqdn_server:  qualysapi.qg2.apps.qualys.com
    41	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase    | dgregory        | get_cred                            |  ** Warning: Ensure Credential File permissions are correct for your company.
    42	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase    | dgregory        | get_cred                            |  ** Warning: Credentials File: /opt/qetl/users/qualys_user/qetl_home/cred/.etld_cred.yaml
    43	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase    | dgregory        | get_cred                            |  ** Permissions are: -rw------- for /opt/qetl/users/qualys_user/qetl_home/cred/.etld_cred.yaml
    44	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase    | dgregory        | knowledgebase_extract                          | api call    - https://qualysapi.qg2.apps.qualys.com/api/2.0/fo/knowledge_base/vuln/
    45	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase    | dgregory        | knowledgebase_extract                          | api options - {'action': 'list', 'details': 'All', 'show_disabled_flag': '1', 'show_qid_change_log': '1', 'show_supported_modules_info': '1', 'show_pci_reasons': '1', 'last_modified_after': '2021-05-21T00:00:00Z'}
    46	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase    | dgregory        | knowledgebase_extract                          | cookie      - False
    47	2021-05-28 01:26:05,717 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | input file - https://qualysapi.qg2.apps.qualys.com/api/2.0/fo/knowledge_base/vuln/ size:  change time: 
    48	2021-05-28 01:26:05,718 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | output file - /opt/qetl/users/qualys_user/qetl_home/data/kb.xml size: 728.51 kilobytes change time: 2021-05-27 21:26:05 local timezone
    49	2021-05-28 01:26:05,718 | INFO     | etl_knowledgebase    | dgregory        | knowledgebase_extract                          | end
    50	2021-05-28 01:26:05,718 | INFO     | etl_knowledgebase    | dgregory        | kb_extract_wrapper                  | end knowledgebase_extract xml from qualys
    51	2021-05-28 01:26:05,719 | INFO     | etl_knowledgebase    | dgregory        | kb_to_shelve_wrapper           | start kb_shelve xml to shelve
    52	2021-05-28 01:26:05,719 | INFO     | etl_knowledgebase    | dgregory        | kb_to_shelve_wrapper           | input file:  /opt/qetl/users/qualys_user/qetl_home/data/kb.xml
    53	2021-05-28 01:26:05,719 | INFO     | etl_knowledgebase    | dgregory        | kb_to_shelve_wrapper           | output file: /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    54	2021-05-28 01:26:05,719 | INFO     | etl_knowledgebase    | dgregory        | kb_shelve                           | start
    55	2021-05-28 01:26:05,744 | INFO     | etl_knowledgebase    | dgregory        | log_dbm_info                        | dbm type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    56	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase    | dgregory        | kb_shelve                           | count qualys qid added to shelve: 137 for /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    57	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | input file - /opt/qetl/users/qualys_user/qetl_home/data/kb.xml size: 728.51 kilobytes change time: 2021-05-27 21:26:05 local timezone
    58	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase    | dgregory        | log_dbm_info                        | dbm type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    59	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | output file - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve size: 632.00 kilobytes change time: 2021-05-27 21:26:05 local timezone
    60	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase    | dgregory        | kb_shelve                           | end
    61	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase    | dgregory        | kb_to_shelve_wrapper           | end   kb_shelve xml to shelve
    62	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase    | dgregory        | kb_to_json_wrapper                  | start kb_load_json transform Shelve to JSON
    63	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase    | dgregory        | kb_to_json_wrapper                  | input file:   /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    64	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase    | dgregory        | kb_to_json_wrapper                  | output File:  /opt/qetl/users/qualys_user/qetl_home/data/kb.json
    65	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase    | dgregory        | kb_load_json                             | start
    66	2021-05-28 01:26:05,840 | INFO     | etl_knowledgebase    | dgregory        | kb_load_json                             | count qid loaded to json: 137
    67	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | input file - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve size: 632.00 kilobytes change time: 2021-05-27 21:26:05 local timezone
    68	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase    | dgregory        | log_dbm_info                        | dbm type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    69	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | output file - /opt/qetl/users/qualys_user/qetl_home/data/kb.json size: 645.81 kilobytes change time: 2021-05-27 21:26:05 local timezone
    70	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase    | dgregory        | kb_load_json                             | end
    71	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase    | dgregory        | kb_to_json_wrapper                  | end   kb_load_json transform Shelve to JSON
    72	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase    | dgregory        | kb_to_csv_wrapper                   | start kb_load_csv - shelve to csv
    73	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase    | dgregory        | kb_to_csv_wrapper                   | input file:   /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    74	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase    | dgregory        | kb_to_csv_wrapper                   | output file: /opt/qetl/users/qualys_user/qetl_home/data/kb.csv
    75	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase    | dgregory        | kb_to_csv_wrapper                   | output file: /opt/qetl/users/qualys_user/qetl_home/data/kb_cve_qid_map.csv  cve -> qid map in csv format
    76	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase    | dgregory        | kb_create_csv_from_shelve           | start
    77	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase    | dgregory        | kb_create_csv_from_shelve           | count rows written to csv: 137
    78	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | input file - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve size: 632.00 kilobytes change time: 2021-05-27 21:26:05 local timezone
    79	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase    | dgregory        | log_dbm_info                        | dbm type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    80	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | output file - /opt/qetl/users/qualys_user/qetl_home/data/kb.csv size: 387.65 kilobytes change time: 2021-05-27 21:26:05 local timezone
    81	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase    | dgregory        | kb_create_csv_from_shelve           | end
    82	2021-05-28 01:26:05,867 | INFO     | etl_knowledgebase    | dgregory        | kb_create_cve_qid_shelve            | count rows written to cve to qid shelve: 334
    83	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | input file - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve size: 632.00 kilobytes change time: 2021-05-27 21:26:05 local timezone
    84	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase    | dgregory        | log_dbm_info                        | dbm type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    85	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | output file - /opt/qetl/users/qualys_user/qetl_home/data/kb_cve_qid_map_shelve size: 44.00 kilobytes change time: 2021-05-27 21:26:05 local timezone
    86	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase    | dgregory        | kb_to_csv_wrapper                   | end   kb_load_csv - shelve to csv
    87	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase    | dgregory        | kb_to_cve_qid_csv_wrapper           | start kb_load_cve_qid_csv transform Shelve to CSV
    88	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase    | dgregory        | kb_to_cve_qid_csv_wrapper           | input file:  /opt/qetl/users/qualys_user/qetl_home/data/kb_cve_qid_map_shelve
    89	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase    | dgregory        | kb_to_cve_qid_csv_wrapper           | output file: /opt/qetl/users/qualys_user/qetl_home/data/kb_cve_qid_map.csv
    90	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase    | dgregory        | kb_cve_qid_csv_report               | Start
    91	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase    | dgregory        | kb_cve_qid_csv_report               | Count of CVE rows written: 334
    92	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase    | dgregory        | kb_cve_qid_csv_report               | End
    93	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase    | dgregory        | kb_to_cve_qid_csv_wrapper           | end   kb_load_cve_qid_csv transform Shelve to CSV
    94	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase    | dgregory        | kb_to_sqlite_wrapper                | start kb_load_sqlite transform Shelve to Sqlite3 DB
    95	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase    | dgregory        | kb_to_sqlite_wrapper                | input file:   /opt/qetl/users/qualys_user/qetl_home/data/kb.csv
    96	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase    | dgregory        | kb_to_sqlite_wrapper                | output file: /opt/qetl/users/qualys_user/qetl_home/data/kb_load_sqlite.db
    97	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase    | dgregory        | kb_load_sqlite                           | start
    98	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase    | dgregory        | bulk_insert_csv_file                | Count rows added to table: 137
    99	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | input file - /opt/qetl/users/qualys_user/qetl_home/data/kb.csv size: 387.65 kilobytes change time: 2021-05-27 21:26:05 local timezone
   100	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase    | dgregory        | log_file_info                       | output file - /opt/qetl/users/qualys_user/qetl_home/data/kb_load_sqlite.db size: 520.00 kilobytes change time: 2021-05-27 21:26:05 local timezone
   101	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase    | dgregory        | kb_load_sqlite                           | end
   102	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase    | dgregory        | kb_to_sqlite_wrapper                | end   kb_load_sqlite transform Shelve to Sqlite3 DB
   103	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase    | dgregory        | kb_distribution_wrapper             | start kb_distribution
   104	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase    | dgregory        | kb_dist                             | start
   105	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase    | dgregory        | copy_results_to_external_target     | no actions taken.  etld_config_settings.yaml kb_export_dir set to: default
   106	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase    | dgregory        | kb_dist                             | end
   107	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase    | dgregory        | kb_distribution_wrapper             | end   kb_distribution
   108	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase    | dgregory        | kb_end_wrapper                      | runtime for kb_etl_workflow in seconds: 1.9780801669985522
   109	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase    | dgregory        | kb_end_wrapper                      | __end__ kb_etl_workflow ['/home/dgregory/opt/qetl/qetl_venv/bin/qetl_manage_user', '-u', '/opt/qetl/users/qualys_user', '-e', 'etl_knowledgebase']

Review ETL KnowledgeBase Data

(qetl_venv) qualysetl@ubuntu:/opt/qetl/users/qualys_user/qetl_home/data$ cd /opt/qetl/users/qualys_user/qetl_home/data/
(qetl_venv) qualysetl@ubuntu:/opt/qetl/users/qualys_user/qetl_home/data$ ls | nl
     1	kb.csv
     2	kb_cve_qid_map.csv
     3	kb_cve_qid_map_shelve
     4	kb.json
     5	kb_shelve
     6	kb_load_sqlite.db
     7	kb.xml
(qetl_venv) qualysetl@ubuntu:/opt/qetl/users/qualys_user/qetl_home/data$ head -20 kb.json
[{
    "QID": "238914",
    "VULN_TYPE": "Vulnerability",
    "SEVERITY_LEVEL": "4",
    "TITLE": "Red Hat Update for Red Hat JBoss Enterprise Application Platform 7.3.4 (RHSA-2020:5341)",
    "CATEGORY": "RedHat",
    "LAST_SERVICE_MODIFICATION_DATETIME": "2021-05-21T07:20:40Z",
    "PUBLISHED_DATETIME": "2020-12-11T12:36:56Z",
    "PATCHABLE": "1",
    "SOFTWARE_LIST": {
        "SOFTWARE": [
            {
                "PRODUCT": "jboss_enterprise_application_platform",
                "VENDOR": "redhat"
            },

License

Apache License

Copyright 2021  David Gregory and Qualys Inc.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.