path: root/python-qualysetl.spec

%global _empty_manifest_terminate_build 0
Name:		python-qualysetl
Version:	0.8.14
Release:	1
Summary:	Qualys API Best Practices Series - ETL Blueprint Example Code within Python Virtual Environment
License:	Apache
URL:		https://pypi.org/project/qualysetl/
Source0:	https://mirrors.nju.edu.cn/pypi/web/packages/ec/6b/57219cf428711c82611b593c778ed3d421bb2f7fa91a4253605e418657a4/qualysetl-0.8.14.tar.gz
BuildArch:	noarch


%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 Env            | June 2021 | Encapsulate qetl Application into Python Virtual Environment at installation.
Asset Inventory(CSAM)         | Oct 2021  | Automate download and transform of GAV/CSAM V2 API into CSV, JSON and SQLite Database
Performance Enhancements      | Jan 2022  | Begin 0.7.x series with performance enhancements.  See change log for details.
Asset Inventory(CSAM)         | Aug 2022  | CSAM API Blog, Video, documentation updates for CSAM, additional edge cases for Qualys Maintenance Windows.
Host List ARS                 | Aug 2022  | Host List Asset Risk Score Added to QualysETL.
Host List Detection QDS       | Aug 2022  | Host List Detection Qualys Detection Score Added to QualysETL.
Web Application Scanning(WAS) | Dec 2022  | Begin 0.8.x series, including WAS Module and Distribution Option, data prepared for database loader.
Policy Compliance             | Apr 2023  | Delayed to include PCRS. Automate download and transform of Policy Compliance Posture, Policy, Controls. 
Asset Tagging API             | Apr 2023  | Automate download and transform of Asset Tagging Information
Docker Image                  | TBD       | Contact your TAM to schedule a call with David Gregory.  Encapsulate Python Application into distributable docker image for ease os operation and upgrade.
Other Modules                 | 2023      | TBD
```
## Technologies
Project tested with:
1. Ubuntu version: 22.04 and 20.04
2. Redhat version: 8.x latest
3. SQLite3 version: 3.31.1
4. Python version: 3.8.5 
5. Qualys API: latest
## 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:
  - 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) 
     https://www.qualys.com/docs/qualys-api-limits.pdf with your TAM for Questions. 
```bash
(qetl_venv) qualysetl@ubuntu:~/.local/bin$ more /opt/qetl/users/qualysetl/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: $DATE
#
# YAML File of available configuration options for Qualys API Calls and future options.
# 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.
#           Note: if you exceed the endpoints concurrency limit,
#                 the application will reset the concurrency limit to X-ConcurrencyLimit-Limit - 1
#
# kb_last_modified_after: 'default'                  # Leave at default.  Knowledgebase is auto-incremental
#                                                      to full knowledgebase.
# kb_export_dir: 'default'                           # Leave at default until future use is developed.
# kb_payload_option: 'default'                       # Leave at default until future use is developed.
# kb_distribution_csv_flag: True                     # True/False Populates qetl_home/data/knowledgebase_distribution_dir
# kb_distribution_csv_max_field_size: 1000000        # Maximum field size allowed in distribution_csv file
#
# host_list_vm_processed_after: 'default'            # Leave at default until future use is developed.
# host_list_payload_option: 'default'                # Leave at default until future use is developed.
# host_list_payload_option: {{'show_ars': '1', 'show_ars_factors': '1'}}  # Contact your TAM to enable TruRisk
# host_list_export_dir: 'default'                    # Leave at default until future use is developed.
# host_list_distribution_csv_flag: True              # True/False Populates qetl_home/data/host_list_distribution_dir
# host_list_distribution_csv_max_field_size: 1000000 # Maximum field size allowed in distribution_csv file
#
# host_list_detection_payload_option: 'default'      # Leave at default until future use is developed.
# host_list_detection_payload_option: {{'show_qds': '1', 'show_qds_factors': '1'}} # Contact your TAM to enable TruRisk.
# host_list_detection_export_dir: 'default'          # Leave at default until future use is developed.
# host_list_detection_vm_processed_after: 'default'  # Leave at default until future use is developed.
# host_list_detection_concurrency_limit: 2           # Reset based on your subscription api concurrency limits
# host_list_detection_multi_proc_batch_size: 1000    # Leave at 1000
# host_list_detection_distribution_csv_flag: True    # True/False Populates qetl_home/data/host_list_detection_distribution_dir
# host_list_detection_distribution_csv_max_field_size: 1000000 # Maximum field size allowed in distribution_csv file
#
# asset_inventory_payload_option: 'default'          # Leave at 'default' until future use is developed.
# asset_inventory_export_dir: 'default'              # Leave at 'default' until future use is developed.
# asset_inventory_asset_last_updated: 'default'      # Leave at 'default' until future use is developed.
# asset_inventory_distribution_csv_flag: True        # True/False Populates qetl_home/data/asset_inventory_distribution_dir
# asset_inventory_distribution_csv_max_field_size: 1000000 # Maximum field size allowed in distribution_csv file
#
# requests_module_tls_verify_status: True            # Recommend leaving at True to protect application against
#                                                      man-in-middle attacks. False will set Python3 requests module
#                                                      verify option to False and requests will accept any TLS
#                                                      certificate presented by the server, and will ignore hostname
#                                                      mismatches and/or expired certificates, which will make your
#                                                      application vulnerable to man-in-the-middle (MitM) attacks
#                                                      This option is useful for development testing only when you
#                                                      are behind a reverse proxy, ex. Data Loss Prevention solution,
#                                                      and you haven't installed the trusted certificates yet.
requests_module_tls_verify_status: True
kb_last_modified_after: 'default'
kb_export_dir: 'default'
kb_payload_option: 'default'
kb_distribution_csv_flag: True
host_list_vm_processed_after: 'default'
host_list_export_dir: 'default'
host_list_distribution_csv_flag: True
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: 1000
host_list_detection_distribution_csv_flag: True
asset_inventory_payload_option: 'default'
asset_inventory_export_dir: 'default'
asset_inventory_asset_last_updated: 'default'
asset_inventory_distribution_csv_flag: True
was_distribution_csv_flag: True
```
### ETL KnowledgeBase
KnowledgeBase ETL - Incremental Update to Knowledgebase.  CSV, JSON, SQLite are full knowledgebase.  XML is incremental.
   - note the knowledgebase will rebuild itself every 30-90 days to ensure gdbm is reorganized.
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_knowledgebase 
```
### ETL Host List
Host List ETL - Download Host List based on date
  - if no date is used, Host List will auto increment from last run 
    ( max LAST_VULN_SCAN_DATETIME ) or if no sqlite database exists
    it download start incremental pull from utc minus 1 day. 
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_host_list -d [YYYY-MM-DDThh:mm:ssZ]
```
See [Application Manager and Data](#application-manager-and-data) for location of your qetl_home directory.
### ETL Host List Detection
Host List Detection ETL - Includes KnowledgeBase and Host List so do not run ETL Host List or ETL KnowledgeBase while Host List Detection ETL is runnning..
  - if no date is used, The Host List Driver will auto increment from last run
      ( max LAST_VULN_SCAN_DATETIME ) or if no sqlite database exists
      it download start incremental pull from utc minus 1 day. 
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_host_list_detection -d [YYYY-MM-DDThh:mm:ssZ]
```
### ETL Asset Inventory
Asset Inventory (GAV/CSAM API) ETL - Includes CyberSecurity Asset Inventory API (CSAM) or its subset Global Asset View API (GAV).
- if no date is used, The Asset Inventory will be pulled from UTC - one day. 
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_asset_inventory -d [YYYY-MM-DDThh:mm:ssZ]
```
### ETL Web Application Scanning Data
Web Application Scanning (WAS API) ETL - Includes Web Applications, Web Application Findings and the Web Application Catalog.
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_was -d [YYYY-MM-DDThh:mm:ssZ]
```
### ETL Test System
Test System ETL - Small system test to validate modules are all working.  
* log/test_system.log will contain all results.  
Executes Programs:
1. etl_knowledgebase - updates knowledgebase up to date. 
2. etl_host_list ( 75 hosts )
3. etl_host_list_detection ( 75 hosts )
4. etl_asset_inventory ( 900 hosts )
4. etl_was ( subset of applications )
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_test_system
```
## Application Manager and Data
### qetl_manage_user application
- qetl_manage_user is your entry point to manage ETL of Qualys Data.
[![](https://user-images.githubusercontent.com/82658653/213870402-d5bf448f-9c2f-4c8a-b36b-54fce35818af.png)](https://user-images.githubusercontent.com/82658653/213870402-d5bf448f-9c2f-4c8a-b36b-54fce35818af.png)
### Host List Detection SQLite Database
- qetl_manage_user -u [userdir] -e etl_host_list_detection -d [datetime] - Resulting sqlite database ready for distribution.
[![](https://user-images.githubusercontent.com/82658653/120927089-a1bb9880-c6ad-11eb-8b83-98c3e7643473.png)](https://user-images.githubusercontent.com/82658653/120927089-a1bb9880-c6ad-11eb-8b83-98c3e7643473.png)
### Host List Detection SQLite Tables
- qetl_manage_user -u [userdir] -e etl_host_list_detection -d [datetime] - Resulting sqlite database ready for distribution.
[![](https://user-images.githubusercontent.com/82658653/190963226-dee51f36-7f32-492a-9cb6-5acb906e4d7d.png)](https://user-images.githubusercontent.com/82658653/190963226-dee51f36-7f32-492a-9cb6-5acb906e4d7d.png)
### 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 of all run logs                                                  |
| qetl_home/log/kb.log                           | LOG KnowledgeBase Run Logs                                                        |
| qetl_home/log/host_list.log                    | LOG - Host List Run Logs                                                          |
| qetl_home/log/host_list_detection.log          | LOG - Host List Detection Run Logs                                                |
| qetl_home/log/asset_inventory.log              | LOG - GAV/CSAM Asset Inventory Run Logs                                           |
| qetl_home/log/was.log                          | LOG - Web Application Scanning(WAS) Run Logs                                      |
| qetl_home/data                                 | Application Data - Directory containing all csv, xml, json, sqlite database data. |
| qetl_home/data/kb_sqlite.db                    | Database - Cumulative Knowledgebase SQLite Database                               |
| qetl_home/data/host_list_sqlite.db             | Database - vm_last_processed Host List SQLite Database                            |
| qetl_home/data/host_list_detection_sqlite.db   | Database - vm_last_processed Host List Detection SQLite Database                  |
| qetl_home/data/asset_inventory_sqlite.db       | Database -lastScanDate Asset Inventory SQLite Database                            |
| qetl_home/data/was_sqlite.db                   | Database - WebApp lastScan.date SQLite Database                                   |
| qetl_home/data/knowledgebase_extract_dir       | Extract - latest *.json.gz, *.xml.gz files                                        |
| qetl_home/data/host_list_extract_dir           | Extract - latest *.json.gz, *.xml.gz files                                        |
| qetl_home/data/host_list_detection_extract_dir | Extract - vm_last_processed Host List Detection XML Data Dir                      |
| qetl_home/data/asset_inventory_extract_dir     | Extract - Asset Inventory Extracts of last scan date of asset in JSON Format.     |
| qetl_home/data/was_extract_dir                 | Extract - Web Application Scanning (WAS) JSON Data Dir                            |
| qetl_home/data/knowledgebase_distribution_dir       | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/host_list_distribution_dir           | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/host_list_detection_distribution_dir | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/asset_inventory_distribution_dir     | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/was_distribution_dir                 | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
### Data Formats 
Data Formats created in qetl_home/data:
| Format          | Description                                                                                                                                                                                                                                                                                                                                                                                   |
|-----------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| JSON            | [Java Script Object Notation](https://datatracker.ietf.org/doc/html/rfc7159) useful for transfer of data between systems                                                                                                                                                                                                                                                                      |
| CSV             | [Comma Separated Values](https://datatracker.ietf.org/doc/html/rfc4180) useful for transfer of data between systems<br>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](https://datatracker.ietf.org/doc/html/rfc3470) useful for transfer of data between systems                                                                                                                                                                                                                                                                       |
| SQLite Database | [SQLite Database](https://www.sqlite.org/about.html): 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 |
### Logging
Logging fields are pipe delimited with some formatting for raw readability.  You can easily import this data into excel, 
 a database for analysis or link this data to a monitoring system.
| 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: YYYYMMDDHHMMSS | Top Level qetl Application Module Name that is executing, along with date to uniquely identify all log entries associated with that job. |
| 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](#application-directories) for details of each log file.
```bash
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: 20210528012603    | dgregory        | setup_logging_stdout                | LOGGING SUCCESSFULLY SETUP FOR STREAMING
     2	2021-05-28 01:26:03,836 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | get_sqlite_version                  | SQLite version found is: 3.31.1.
     5	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase: 20210528012603    | 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
QualysETL provides options to inject credentials at runtime via qetl_manage_user, so your credentials are not stored on disk.  
qetl_manage_user options to inject credentials at runtime are:
1) -p, --prompt-credentials prompt user for credentials, also accepts stdin with credentials piped to program.
2) -m, --memory-credentials get credentials from environment: q_username, q_password, q_api_fqdn_server
3) -s, --stdin-credentials  send credentials in json to stdin. 
         Example:
         {"q_username": "your userid", "q_password": "your password", "q_api_fqdn_server": "api fqdn", "q_gateway_fqdn_server": "gateway api fqdn"}
Qualys recommends customers move to a password vault of their choosing 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, injecting the credentials at runtime.  
One way customers can do this is through a work load management solution, where the external work load management
system ( Ex. Autosys ) schedules jobs injecting the required credentials to QualysETL 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](https://www.hashicorp.com/products/vault)
- [Hashicorp Getting Started](https://learn.hashicorp.com/tutorials/vault/getting-started-intro?in=vault/getting-started)
## Example Run Logs
### Uninstall and Install qetl
#### Uninstall Run Log
- 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
```bash
(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).
```bash
(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
```bash
qualysetl@ubuntu:~/.local/bin$ ./qetl_setup_python_venv /opt/qetl
Start qetl_setup_python_venv - Fri Jan 21 07:07:22 PST 2022
  1) test_os_for_required_commands
  2) test_for_pip_connectivity
  3) prepare_opt_qetl_env_dirs
    usage:       qetl_setup_python_venv [/opt/qetl] [test|prod] [version number]
                 qetl_setup_python_venv [-h] for help
    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.
        See https://pypi.org/project/qualysetl/ for first time setup and
        installation instructions.
    options:
        qetl_setup_python_venv [/opt/qetl] [test|prod] [version number]
        1) [/opt/qetl]        - root directory where application and data
                                will be stored.
                              - You must be root to create this directory.
                              - See https://pypi.org/project/qualysetl/ for
                                first time setup/installation instructions.
        2) [test|prod]        - obtain QualysETL from test or prod pypi
                                instance.
        3) [version number]   - obtain version number of qualysetl.
    examples:
            1) qetl_setup_python_venv /opt/qetl
               - Ensure you have /opt/qetl directory created before running
                 this program.
               - Creates QualysETL Environment.  See directory information
                 below.
            2) qetl_setup_python_venv /opt/qetl prod 0.6.131
               - will install version 0.6.131 of qualysetl from pypi.org into
                 your /opt/qetl/qetl_venv directory.
            3) qetl_setup_python_venv /opt/qetl test 0.6.131
               - will install version 0.6.131 of qualysetl from test.pypi.org
                 into your /opt/qetl/qetl_venv directory.
    directory information:
             /opt/qetl            - root directory for Application and Data
             /opt/qetl/qetl_venv  - application directory for Qualys ETL
                                    Python Virtual Environment
             /opt/qetl/users      - data directory containing results of
                                    QualysETL execution.
    files:
        See https://dg-cafe.github.io/qualysetl/#application-manager-and-data
    container notes:
            1) For container deployment, ex docker, application and data
               are separated for container deployment.
               Container Application - /opt/qetl/qetl_venv should installed into the container image.
               Persistent Data       - /opt/qetl/users should be mapped to the underlying host
                                       system for persistent storage of application data.
Create qetl Python Environment? /opt/qetl/qetl_venv prod:latest
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: dgregory@qualys.com, dave@davidgregory.com
     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 point to operating qualysetl ) 
   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.
```bash
(qetl_venv) qualysetl@ubuntu:~/.local/bin$ qetl_manage_user 
    usage: qetl_manage_user [-h] [-u qetl_USER_HOME_DIR] [-e etl_[module] ] [-e validate_etl_[module] ] [-c] [-t] [-i] [-d] [-r] [-l]
    Command to Extract, Transform and Load Qualys Data into various forms ( CSV, JSON, SQLITE3 DATABASE )
    optional arguments:
      -h, --help                show this help message and exit
      -u Home Directory Path,   --qetl_user_home_dir Home directory Path
                                   Example:
                                   - /opt/qetl/users/q_username
      -e etl_[module],          --execute_etl_[module] execute etl of module name. valid options are:
                                       -e etl_knowledgebase 
                                       -e etl_host_list 
                                       -e etl_host_list_detection
                                       -e etl_asset_inventory
                                       -e etl_was
                                       -e etl_test_system ( for a small system test of all ETL Jobs )
      -e validate_etl_[module], --validate_etl_[module] [test last run of etl_[module]].  valid options are:
                                       -e validate_etl_knowledgebase
                                       -e validate_etl_host_list 
                                       -e validate_etl_host_list_detection
                                       -e validate_etl_asset_inventory
                                       -e validate_etl_was
                                       -e validate_etl_test_system 
      -d YYMMDDThh:mm:ssZ,      --datetime      YYYY-MM-DDThh:mm:ssZ UTC. Get All Data On or After Date. 
                                                Ex. 1970-01-01T00:00:00Z acts as flag to obtain all data.
      -c, --credentials        update qualys api user credentials: qualys username, password or api_fqdn_server
      -t, --test               test qualys credentials
      -i, --initialize_user    For automation, create a /opt/qetl/users/[userhome] directory 
                               without being prompted.
      -l, --logs               detailed logs sent to stdout for testing qualys credentials
      -v, --version            Help and QualysETL version information.
      -r, --report             brief report of the users directory structure.
      -p, --prompt-credentials prompt user for credentials, also accepts stdin with credentials piped to program.
      -m, --memory-credentials get credentials from environment: 
                               Example: q_username="your userid", q_password=your password, q_api_fqdn_server=api fqdn, q_gateway_fqdn_server=gateway api fqdn
      -s, --stdin-credentials  send credentials in json to stdin. 
                               Example:
                               {"q_username": "your userid", "q_password": "your password", "q_api_fqdn_server": "api fqdn", "q_gateway_fqdn_server": "gateway api fqdn"}
      etld_config_settings.yaml notes:
         1. To Enable CSV Distribution, add the following keys to etld_config_settings.yaml and toggle on/off them via True or False
              kb_distribution_csv_flag: True                    # populates qetl_home/data/knowledgebase_distribution_dir
              host_list_distribution_csv_flag: True             # populates qetl_home/data/host_list_distribution_dir
              host_list_detection_distribution_csv_flag: True   # populates qetl_home/data/host_list_detection_distribution_dir
              asset_inventory_distribution_csv_flag: True       # populates qetl_home/data/asset_inventory_distribution_dir
              was_distribution_csv_flag: True                   # populates qetl_home/data/was_distribution_dir
              These files are prepared for database load, tested with mysql.  No headers are present.  
              Contact your Qualys TAM and schedule a call with David Gregory if you need assistance with this option.
         2. To enable TruRisk, ask your TAM to add TruRisk to your subscription, then update the following keys in your etld_config_settings.yaml
            host_list_payload_option: {'show_ars': '1', 'show_ars_factors': '1'}
            host_list_detection_payload_option: {'show_qds': '1', 'show_qds_factors': '1'}
```
### 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.
```bash
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.
         Example:
            1) /opt/qetl/users/newuser
        usage: qetl_manage_user [-h] [-u QETL_USER_HOME_DIR] [-e EXECUTE_ETL_MODULE] [-d DATETIME] [-c] [-t] [-l] [-p] [-s] [-m] [-r]
        Command to Extract, Transform and Load Qualys Data into various forms ( CSV, JSON, SQLITE3 DATABASE )
        optional arguments:
          -h, --help            show this help message and exit
          -u QETL_USER_HOME_DIR, --qetl_user_home_dir QETL_USER_HOME_DIR
                                Please enter -u option
          -e EXECUTE_ETL_MODULE, --execute_etl_module EXECUTE_ETL_MODULE
                                Execute etl_knowledgebase, etl_host_list, etl_host_list_detection, etl_asset_inventory, etl_test_system
          -d DATETIME, --datetime DATETIME
                                YYYY-MM-DDThh:mm:ssZ UTC. Get All Data On or After Date. Ex. 1970-01-01T00:00:00Z acts as flag to obtain all data.
          -c, --credentials     update qualys api user credentials stored on disk: qualys username, password or api_fqdn_server
          -t, --test            test qualys credentials
          -l, --logs            detailed logs sent to stdout for test qualys credentials
          -p, --prompt_credentials
                                prompt user for credentials
          -s, --stdin_credentials
                                read stdin credentials json {"q_username":"your userid", "q_password":"your password", "q_api_fqdn_server":"api fqdn", "q_gateway_fqdn_server":"gateway api fqdn"}
          -m, --memory_credentials
                                Get credentials from environment variables in memory: q_username, q_password, q_api_fqdn_server, and optionally add q_gateway_fqdn_server. Ex. export q_username=myuser
          -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
```bash
(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: 20210528012603    | dgregory        | setup_logging_stdout                | LOGGING SUCCESSFULLY SETUP FOR STREAMING
     2	2021-05-28 01:26:03,836 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | setup_logging_stdout                | PROGRAM: ['/home/dgregory/opt/qetl/qetl_venv/bin/qetl_manage_user', '-u', '/opt/qetl/users/qualys_user', '-e', 'etl_knowledgebase: 20210528012603']
     3	2021-05-28 01:26:03,897 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | get_sqlite_version                  | SQLite version found is: 3.31.1.
     5	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | setup_kb_vars                       | kb_export_dir is direct from yaml
    26	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | setup_host_list_vars                | host_list_payload_option yaml - notags
    30	2021-05-28 01:26:03,906 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603']
    32	2021-05-28 01:26:03,907 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | knowledgebase_extract                          | start
    38	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | get_cred                            |      username:         quays93
    40	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | get_cred                            |      api_fqdn_server:  qualysapi.qg2.apps.qualys.com
    41	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | get_cred                            |  ** Warning: Ensure Credential File permissions are correct for your company.
    42	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | knowledgebase_extract                          | cookie      - False
    47	2021-05-28 01:26:05,717 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | knowledgebase_extract                          | end
    50	2021-05-28 01:26:05,718 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_extract_wrapper                  | end knowledgebase_extract xml from qualys
    51	2021-05-28 01:26:05,719 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_shelve_wrapper           | start kb_shelve xml to shelve
    52	2021-05-28 01:26:05,719 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_shelve                           | start
    55	2021-05-28 01:26:05,744 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    56	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    59	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_shelve                           | end
    61	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_shelve_wrapper           | end   kb_shelve xml to shelve
    62	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_json_wrapper                  | start kb_load_json transform Shelve to JSON
    63	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_json                             | start
    66	2021-05-28 01:26:05,840 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_load_json                             | count qid loaded to json: 137
    67	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    69	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_json                             | end
    71	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_json_wrapper                  | end   kb_load_json transform Shelve to JSON
    72	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_csv_wrapper                   | start kb_load_csv - shelve to csv
    73	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_create_csv_from_shelve           | start
    77	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_create_csv_from_shelve           | count rows written to csv: 137
    78	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    80	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_create_csv_from_shelve           | end
    82	2021-05-28 01:26:05,867 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    85	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_to_csv_wrapper                   | end   kb_load_csv - shelve to csv
    87	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_cve_qid_csv_report               | Start
    91	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_cve_qid_csv_report               | Count of CVE rows written: 334
    92	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_cve_qid_csv_report               | End
    93	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_sqlite                           | start
    98	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | bulk_insert_csv_file                | Count rows added to table: 137
    99	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_sqlite                           | end
   102	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_distribution_wrapper             | start kb_distribution
   104	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_dist                             | start
   105	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_dist                             | end
   107	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_distribution_wrapper             | end   kb_distribution
   108	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_end_wrapper                      | runtime for kb_etl_workflow in seconds: 1.9780801669985522
   109	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603']
```
### Review ETL KnowledgeBase Data
```bash
(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 kb_sqlite.db knowledgebase_extract_dir
     1	kb_sqlite.db
     2  kb_utc_run_datetime_2022-01-13T07:29:49Z_utc_last_modified_after_2021-12-14T00:00:00Z_batch_000001.json.gz  
     3  kb_utc_run_datetime_2022-01-13T07:29:49Z_utc_last_modified_after_2021-12-14T00:00:00Z_batch_000001.xml.gz
```
## License
[Apache License](http://www.apache.org/licenses/LICENSE-2.0)
    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.
## ChangeLog
Beginning with 0.6.98 a change log will be maintained here.
```

%package -n python3-qualysetl
Summary:	Qualys API Best Practices Series - ETL Blueprint Example Code within Python Virtual Environment
Provides:	python-qualysetl
BuildRequires:	python3-devel
BuildRequires:	python3-setuptools
BuildRequires:	python3-pip
%description -n python3-qualysetl
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 Env            | June 2021 | Encapsulate qetl Application into Python Virtual Environment at installation.
Asset Inventory(CSAM)         | Oct 2021  | Automate download and transform of GAV/CSAM V2 API into CSV, JSON and SQLite Database
Performance Enhancements      | Jan 2022  | Begin 0.7.x series with performance enhancements.  See change log for details.
Asset Inventory(CSAM)         | Aug 2022  | CSAM API Blog, Video, documentation updates for CSAM, additional edge cases for Qualys Maintenance Windows.
Host List ARS                 | Aug 2022  | Host List Asset Risk Score Added to QualysETL.
Host List Detection QDS       | Aug 2022  | Host List Detection Qualys Detection Score Added to QualysETL.
Web Application Scanning(WAS) | Dec 2022  | Begin 0.8.x series, including WAS Module and Distribution Option, data prepared for database loader.
Policy Compliance             | Apr 2023  | Delayed to include PCRS. Automate download and transform of Policy Compliance Posture, Policy, Controls. 
Asset Tagging API             | Apr 2023  | Automate download and transform of Asset Tagging Information
Docker Image                  | TBD       | Contact your TAM to schedule a call with David Gregory.  Encapsulate Python Application into distributable docker image for ease os operation and upgrade.
Other Modules                 | 2023      | TBD
```
## Technologies
Project tested with:
1. Ubuntu version: 22.04 and 20.04
2. Redhat version: 8.x latest
3. SQLite3 version: 3.31.1
4. Python version: 3.8.5 
5. Qualys API: latest
## 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:
  - 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) 
     https://www.qualys.com/docs/qualys-api-limits.pdf with your TAM for Questions. 
```bash
(qetl_venv) qualysetl@ubuntu:~/.local/bin$ more /opt/qetl/users/qualysetl/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: $DATE
#
# YAML File of available configuration options for Qualys API Calls and future options.
# 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.
#           Note: if you exceed the endpoints concurrency limit,
#                 the application will reset the concurrency limit to X-ConcurrencyLimit-Limit - 1
#
# kb_last_modified_after: 'default'                  # Leave at default.  Knowledgebase is auto-incremental
#                                                      to full knowledgebase.
# kb_export_dir: 'default'                           # Leave at default until future use is developed.
# kb_payload_option: 'default'                       # Leave at default until future use is developed.
# kb_distribution_csv_flag: True                     # True/False Populates qetl_home/data/knowledgebase_distribution_dir
# kb_distribution_csv_max_field_size: 1000000        # Maximum field size allowed in distribution_csv file
#
# host_list_vm_processed_after: 'default'            # Leave at default until future use is developed.
# host_list_payload_option: 'default'                # Leave at default until future use is developed.
# host_list_payload_option: {{'show_ars': '1', 'show_ars_factors': '1'}}  # Contact your TAM to enable TruRisk
# host_list_export_dir: 'default'                    # Leave at default until future use is developed.
# host_list_distribution_csv_flag: True              # True/False Populates qetl_home/data/host_list_distribution_dir
# host_list_distribution_csv_max_field_size: 1000000 # Maximum field size allowed in distribution_csv file
#
# host_list_detection_payload_option: 'default'      # Leave at default until future use is developed.
# host_list_detection_payload_option: {{'show_qds': '1', 'show_qds_factors': '1'}} # Contact your TAM to enable TruRisk.
# host_list_detection_export_dir: 'default'          # Leave at default until future use is developed.
# host_list_detection_vm_processed_after: 'default'  # Leave at default until future use is developed.
# host_list_detection_concurrency_limit: 2           # Reset based on your subscription api concurrency limits
# host_list_detection_multi_proc_batch_size: 1000    # Leave at 1000
# host_list_detection_distribution_csv_flag: True    # True/False Populates qetl_home/data/host_list_detection_distribution_dir
# host_list_detection_distribution_csv_max_field_size: 1000000 # Maximum field size allowed in distribution_csv file
#
# asset_inventory_payload_option: 'default'          # Leave at 'default' until future use is developed.
# asset_inventory_export_dir: 'default'              # Leave at 'default' until future use is developed.
# asset_inventory_asset_last_updated: 'default'      # Leave at 'default' until future use is developed.
# asset_inventory_distribution_csv_flag: True        # True/False Populates qetl_home/data/asset_inventory_distribution_dir
# asset_inventory_distribution_csv_max_field_size: 1000000 # Maximum field size allowed in distribution_csv file
#
# requests_module_tls_verify_status: True            # Recommend leaving at True to protect application against
#                                                      man-in-middle attacks. False will set Python3 requests module
#                                                      verify option to False and requests will accept any TLS
#                                                      certificate presented by the server, and will ignore hostname
#                                                      mismatches and/or expired certificates, which will make your
#                                                      application vulnerable to man-in-the-middle (MitM) attacks
#                                                      This option is useful for development testing only when you
#                                                      are behind a reverse proxy, ex. Data Loss Prevention solution,
#                                                      and you haven't installed the trusted certificates yet.
requests_module_tls_verify_status: True
kb_last_modified_after: 'default'
kb_export_dir: 'default'
kb_payload_option: 'default'
kb_distribution_csv_flag: True
host_list_vm_processed_after: 'default'
host_list_export_dir: 'default'
host_list_distribution_csv_flag: True
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: 1000
host_list_detection_distribution_csv_flag: True
asset_inventory_payload_option: 'default'
asset_inventory_export_dir: 'default'
asset_inventory_asset_last_updated: 'default'
asset_inventory_distribution_csv_flag: True
was_distribution_csv_flag: True
```
### ETL KnowledgeBase
KnowledgeBase ETL - Incremental Update to Knowledgebase.  CSV, JSON, SQLite are full knowledgebase.  XML is incremental.
   - note the knowledgebase will rebuild itself every 30-90 days to ensure gdbm is reorganized.
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_knowledgebase 
```
### ETL Host List
Host List ETL - Download Host List based on date
  - if no date is used, Host List will auto increment from last run 
    ( max LAST_VULN_SCAN_DATETIME ) or if no sqlite database exists
    it download start incremental pull from utc minus 1 day. 
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_host_list -d [YYYY-MM-DDThh:mm:ssZ]
```
See [Application Manager and Data](#application-manager-and-data) for location of your qetl_home directory.
### ETL Host List Detection
Host List Detection ETL - Includes KnowledgeBase and Host List so do not run ETL Host List or ETL KnowledgeBase while Host List Detection ETL is runnning..
  - if no date is used, The Host List Driver will auto increment from last run
      ( max LAST_VULN_SCAN_DATETIME ) or if no sqlite database exists
      it download start incremental pull from utc minus 1 day. 
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_host_list_detection -d [YYYY-MM-DDThh:mm:ssZ]
```
### ETL Asset Inventory
Asset Inventory (GAV/CSAM API) ETL - Includes CyberSecurity Asset Inventory API (CSAM) or its subset Global Asset View API (GAV).
- if no date is used, The Asset Inventory will be pulled from UTC - one day. 
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_asset_inventory -d [YYYY-MM-DDThh:mm:ssZ]
```
### ETL Web Application Scanning Data
Web Application Scanning (WAS API) ETL - Includes Web Applications, Web Application Findings and the Web Application Catalog.
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_was -d [YYYY-MM-DDThh:mm:ssZ]
```
### ETL Test System
Test System ETL - Small system test to validate modules are all working.  
* log/test_system.log will contain all results.  
Executes Programs:
1. etl_knowledgebase - updates knowledgebase up to date. 
2. etl_host_list ( 75 hosts )
3. etl_host_list_detection ( 75 hosts )
4. etl_asset_inventory ( 900 hosts )
4. etl_was ( subset of applications )
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_test_system
```
## Application Manager and Data
### qetl_manage_user application
- qetl_manage_user is your entry point to manage ETL of Qualys Data.
[![](https://user-images.githubusercontent.com/82658653/213870402-d5bf448f-9c2f-4c8a-b36b-54fce35818af.png)](https://user-images.githubusercontent.com/82658653/213870402-d5bf448f-9c2f-4c8a-b36b-54fce35818af.png)
### Host List Detection SQLite Database
- qetl_manage_user -u [userdir] -e etl_host_list_detection -d [datetime] - Resulting sqlite database ready for distribution.
[![](https://user-images.githubusercontent.com/82658653/120927089-a1bb9880-c6ad-11eb-8b83-98c3e7643473.png)](https://user-images.githubusercontent.com/82658653/120927089-a1bb9880-c6ad-11eb-8b83-98c3e7643473.png)
### Host List Detection SQLite Tables
- qetl_manage_user -u [userdir] -e etl_host_list_detection -d [datetime] - Resulting sqlite database ready for distribution.
[![](https://user-images.githubusercontent.com/82658653/190963226-dee51f36-7f32-492a-9cb6-5acb906e4d7d.png)](https://user-images.githubusercontent.com/82658653/190963226-dee51f36-7f32-492a-9cb6-5acb906e4d7d.png)
### 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 of all run logs                                                  |
| qetl_home/log/kb.log                           | LOG KnowledgeBase Run Logs                                                        |
| qetl_home/log/host_list.log                    | LOG - Host List Run Logs                                                          |
| qetl_home/log/host_list_detection.log          | LOG - Host List Detection Run Logs                                                |
| qetl_home/log/asset_inventory.log              | LOG - GAV/CSAM Asset Inventory Run Logs                                           |
| qetl_home/log/was.log                          | LOG - Web Application Scanning(WAS) Run Logs                                      |
| qetl_home/data                                 | Application Data - Directory containing all csv, xml, json, sqlite database data. |
| qetl_home/data/kb_sqlite.db                    | Database - Cumulative Knowledgebase SQLite Database                               |
| qetl_home/data/host_list_sqlite.db             | Database - vm_last_processed Host List SQLite Database                            |
| qetl_home/data/host_list_detection_sqlite.db   | Database - vm_last_processed Host List Detection SQLite Database                  |
| qetl_home/data/asset_inventory_sqlite.db       | Database -lastScanDate Asset Inventory SQLite Database                            |
| qetl_home/data/was_sqlite.db                   | Database - WebApp lastScan.date SQLite Database                                   |
| qetl_home/data/knowledgebase_extract_dir       | Extract - latest *.json.gz, *.xml.gz files                                        |
| qetl_home/data/host_list_extract_dir           | Extract - latest *.json.gz, *.xml.gz files                                        |
| qetl_home/data/host_list_detection_extract_dir | Extract - vm_last_processed Host List Detection XML Data Dir                      |
| qetl_home/data/asset_inventory_extract_dir     | Extract - Asset Inventory Extracts of last scan date of asset in JSON Format.     |
| qetl_home/data/was_extract_dir                 | Extract - Web Application Scanning (WAS) JSON Data Dir                            |
| qetl_home/data/knowledgebase_distribution_dir       | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/host_list_distribution_dir           | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/host_list_detection_distribution_dir | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/asset_inventory_distribution_dir     | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/was_distribution_dir                 | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
### Data Formats 
Data Formats created in qetl_home/data:
| Format          | Description                                                                                                                                                                                                                                                                                                                                                                                   |
|-----------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| JSON            | [Java Script Object Notation](https://datatracker.ietf.org/doc/html/rfc7159) useful for transfer of data between systems                                                                                                                                                                                                                                                                      |
| CSV             | [Comma Separated Values](https://datatracker.ietf.org/doc/html/rfc4180) useful for transfer of data between systems<br>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](https://datatracker.ietf.org/doc/html/rfc3470) useful for transfer of data between systems                                                                                                                                                                                                                                                                       |
| SQLite Database | [SQLite Database](https://www.sqlite.org/about.html): 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 |
### Logging
Logging fields are pipe delimited with some formatting for raw readability.  You can easily import this data into excel, 
 a database for analysis or link this data to a monitoring system.
| 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: YYYYMMDDHHMMSS | Top Level qetl Application Module Name that is executing, along with date to uniquely identify all log entries associated with that job. |
| 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](#application-directories) for details of each log file.
```bash
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: 20210528012603    | dgregory        | setup_logging_stdout                | LOGGING SUCCESSFULLY SETUP FOR STREAMING
     2	2021-05-28 01:26:03,836 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | get_sqlite_version                  | SQLite version found is: 3.31.1.
     5	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase: 20210528012603    | 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
QualysETL provides options to inject credentials at runtime via qetl_manage_user, so your credentials are not stored on disk.  
qetl_manage_user options to inject credentials at runtime are:
1) -p, --prompt-credentials prompt user for credentials, also accepts stdin with credentials piped to program.
2) -m, --memory-credentials get credentials from environment: q_username, q_password, q_api_fqdn_server
3) -s, --stdin-credentials  send credentials in json to stdin. 
         Example:
         {"q_username": "your userid", "q_password": "your password", "q_api_fqdn_server": "api fqdn", "q_gateway_fqdn_server": "gateway api fqdn"}
Qualys recommends customers move to a password vault of their choosing 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, injecting the credentials at runtime.  
One way customers can do this is through a work load management solution, where the external work load management
system ( Ex. Autosys ) schedules jobs injecting the required credentials to QualysETL 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](https://www.hashicorp.com/products/vault)
- [Hashicorp Getting Started](https://learn.hashicorp.com/tutorials/vault/getting-started-intro?in=vault/getting-started)
## Example Run Logs
### Uninstall and Install qetl
#### Uninstall Run Log
- 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
```bash
(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).
```bash
(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
```bash
qualysetl@ubuntu:~/.local/bin$ ./qetl_setup_python_venv /opt/qetl
Start qetl_setup_python_venv - Fri Jan 21 07:07:22 PST 2022
  1) test_os_for_required_commands
  2) test_for_pip_connectivity
  3) prepare_opt_qetl_env_dirs
    usage:       qetl_setup_python_venv [/opt/qetl] [test|prod] [version number]
                 qetl_setup_python_venv [-h] for help
    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.
        See https://pypi.org/project/qualysetl/ for first time setup and
        installation instructions.
    options:
        qetl_setup_python_venv [/opt/qetl] [test|prod] [version number]
        1) [/opt/qetl]        - root directory where application and data
                                will be stored.
                              - You must be root to create this directory.
                              - See https://pypi.org/project/qualysetl/ for
                                first time setup/installation instructions.
        2) [test|prod]        - obtain QualysETL from test or prod pypi
                                instance.
        3) [version number]   - obtain version number of qualysetl.
    examples:
            1) qetl_setup_python_venv /opt/qetl
               - Ensure you have /opt/qetl directory created before running
                 this program.
               - Creates QualysETL Environment.  See directory information
                 below.
            2) qetl_setup_python_venv /opt/qetl prod 0.6.131
               - will install version 0.6.131 of qualysetl from pypi.org into
                 your /opt/qetl/qetl_venv directory.
            3) qetl_setup_python_venv /opt/qetl test 0.6.131
               - will install version 0.6.131 of qualysetl from test.pypi.org
                 into your /opt/qetl/qetl_venv directory.
    directory information:
             /opt/qetl            - root directory for Application and Data
             /opt/qetl/qetl_venv  - application directory for Qualys ETL
                                    Python Virtual Environment
             /opt/qetl/users      - data directory containing results of
                                    QualysETL execution.
    files:
        See https://dg-cafe.github.io/qualysetl/#application-manager-and-data
    container notes:
            1) For container deployment, ex docker, application and data
               are separated for container deployment.
               Container Application - /opt/qetl/qetl_venv should installed into the container image.
               Persistent Data       - /opt/qetl/users should be mapped to the underlying host
                                       system for persistent storage of application data.
Create qetl Python Environment? /opt/qetl/qetl_venv prod:latest
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: dgregory@qualys.com, dave@davidgregory.com
     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 point to operating qualysetl ) 
   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.
```bash
(qetl_venv) qualysetl@ubuntu:~/.local/bin$ qetl_manage_user 
    usage: qetl_manage_user [-h] [-u qetl_USER_HOME_DIR] [-e etl_[module] ] [-e validate_etl_[module] ] [-c] [-t] [-i] [-d] [-r] [-l]
    Command to Extract, Transform and Load Qualys Data into various forms ( CSV, JSON, SQLITE3 DATABASE )
    optional arguments:
      -h, --help                show this help message and exit
      -u Home Directory Path,   --qetl_user_home_dir Home directory Path
                                   Example:
                                   - /opt/qetl/users/q_username
      -e etl_[module],          --execute_etl_[module] execute etl of module name. valid options are:
                                       -e etl_knowledgebase 
                                       -e etl_host_list 
                                       -e etl_host_list_detection
                                       -e etl_asset_inventory
                                       -e etl_was
                                       -e etl_test_system ( for a small system test of all ETL Jobs )
      -e validate_etl_[module], --validate_etl_[module] [test last run of etl_[module]].  valid options are:
                                       -e validate_etl_knowledgebase
                                       -e validate_etl_host_list 
                                       -e validate_etl_host_list_detection
                                       -e validate_etl_asset_inventory
                                       -e validate_etl_was
                                       -e validate_etl_test_system 
      -d YYMMDDThh:mm:ssZ,      --datetime      YYYY-MM-DDThh:mm:ssZ UTC. Get All Data On or After Date. 
                                                Ex. 1970-01-01T00:00:00Z acts as flag to obtain all data.
      -c, --credentials        update qualys api user credentials: qualys username, password or api_fqdn_server
      -t, --test               test qualys credentials
      -i, --initialize_user    For automation, create a /opt/qetl/users/[userhome] directory 
                               without being prompted.
      -l, --logs               detailed logs sent to stdout for testing qualys credentials
      -v, --version            Help and QualysETL version information.
      -r, --report             brief report of the users directory structure.
      -p, --prompt-credentials prompt user for credentials, also accepts stdin with credentials piped to program.
      -m, --memory-credentials get credentials from environment: 
                               Example: q_username="your userid", q_password=your password, q_api_fqdn_server=api fqdn, q_gateway_fqdn_server=gateway api fqdn
      -s, --stdin-credentials  send credentials in json to stdin. 
                               Example:
                               {"q_username": "your userid", "q_password": "your password", "q_api_fqdn_server": "api fqdn", "q_gateway_fqdn_server": "gateway api fqdn"}
      etld_config_settings.yaml notes:
         1. To Enable CSV Distribution, add the following keys to etld_config_settings.yaml and toggle on/off them via True or False
              kb_distribution_csv_flag: True                    # populates qetl_home/data/knowledgebase_distribution_dir
              host_list_distribution_csv_flag: True             # populates qetl_home/data/host_list_distribution_dir
              host_list_detection_distribution_csv_flag: True   # populates qetl_home/data/host_list_detection_distribution_dir
              asset_inventory_distribution_csv_flag: True       # populates qetl_home/data/asset_inventory_distribution_dir
              was_distribution_csv_flag: True                   # populates qetl_home/data/was_distribution_dir
              These files are prepared for database load, tested with mysql.  No headers are present.  
              Contact your Qualys TAM and schedule a call with David Gregory if you need assistance with this option.
         2. To enable TruRisk, ask your TAM to add TruRisk to your subscription, then update the following keys in your etld_config_settings.yaml
            host_list_payload_option: {'show_ars': '1', 'show_ars_factors': '1'}
            host_list_detection_payload_option: {'show_qds': '1', 'show_qds_factors': '1'}
```
### 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.
```bash
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.
         Example:
            1) /opt/qetl/users/newuser
        usage: qetl_manage_user [-h] [-u QETL_USER_HOME_DIR] [-e EXECUTE_ETL_MODULE] [-d DATETIME] [-c] [-t] [-l] [-p] [-s] [-m] [-r]
        Command to Extract, Transform and Load Qualys Data into various forms ( CSV, JSON, SQLITE3 DATABASE )
        optional arguments:
          -h, --help            show this help message and exit
          -u QETL_USER_HOME_DIR, --qetl_user_home_dir QETL_USER_HOME_DIR
                                Please enter -u option
          -e EXECUTE_ETL_MODULE, --execute_etl_module EXECUTE_ETL_MODULE
                                Execute etl_knowledgebase, etl_host_list, etl_host_list_detection, etl_asset_inventory, etl_test_system
          -d DATETIME, --datetime DATETIME
                                YYYY-MM-DDThh:mm:ssZ UTC. Get All Data On or After Date. Ex. 1970-01-01T00:00:00Z acts as flag to obtain all data.
          -c, --credentials     update qualys api user credentials stored on disk: qualys username, password or api_fqdn_server
          -t, --test            test qualys credentials
          -l, --logs            detailed logs sent to stdout for test qualys credentials
          -p, --prompt_credentials
                                prompt user for credentials
          -s, --stdin_credentials
                                read stdin credentials json {"q_username":"your userid", "q_password":"your password", "q_api_fqdn_server":"api fqdn", "q_gateway_fqdn_server":"gateway api fqdn"}
          -m, --memory_credentials
                                Get credentials from environment variables in memory: q_username, q_password, q_api_fqdn_server, and optionally add q_gateway_fqdn_server. Ex. export q_username=myuser
          -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
```bash
(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: 20210528012603    | dgregory        | setup_logging_stdout                | LOGGING SUCCESSFULLY SETUP FOR STREAMING
     2	2021-05-28 01:26:03,836 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | setup_logging_stdout                | PROGRAM: ['/home/dgregory/opt/qetl/qetl_venv/bin/qetl_manage_user', '-u', '/opt/qetl/users/qualys_user', '-e', 'etl_knowledgebase: 20210528012603']
     3	2021-05-28 01:26:03,897 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | get_sqlite_version                  | SQLite version found is: 3.31.1.
     5	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | setup_kb_vars                       | kb_export_dir is direct from yaml
    26	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | setup_host_list_vars                | host_list_payload_option yaml - notags
    30	2021-05-28 01:26:03,906 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603']
    32	2021-05-28 01:26:03,907 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | knowledgebase_extract                          | start
    38	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | get_cred                            |      username:         quays93
    40	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | get_cred                            |      api_fqdn_server:  qualysapi.qg2.apps.qualys.com
    41	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | get_cred                            |  ** Warning: Ensure Credential File permissions are correct for your company.
    42	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | knowledgebase_extract                          | cookie      - False
    47	2021-05-28 01:26:05,717 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | knowledgebase_extract                          | end
    50	2021-05-28 01:26:05,718 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_extract_wrapper                  | end knowledgebase_extract xml from qualys
    51	2021-05-28 01:26:05,719 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_shelve_wrapper           | start kb_shelve xml to shelve
    52	2021-05-28 01:26:05,719 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_shelve                           | start
    55	2021-05-28 01:26:05,744 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    56	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    59	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_shelve                           | end
    61	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_shelve_wrapper           | end   kb_shelve xml to shelve
    62	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_json_wrapper                  | start kb_load_json transform Shelve to JSON
    63	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_json                             | start
    66	2021-05-28 01:26:05,840 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_load_json                             | count qid loaded to json: 137
    67	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    69	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_json                             | end
    71	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_json_wrapper                  | end   kb_load_json transform Shelve to JSON
    72	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_csv_wrapper                   | start kb_load_csv - shelve to csv
    73	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_create_csv_from_shelve           | start
    77	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_create_csv_from_shelve           | count rows written to csv: 137
    78	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    80	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_create_csv_from_shelve           | end
    82	2021-05-28 01:26:05,867 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    85	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_to_csv_wrapper                   | end   kb_load_csv - shelve to csv
    87	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_cve_qid_csv_report               | Start
    91	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_cve_qid_csv_report               | Count of CVE rows written: 334
    92	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_cve_qid_csv_report               | End
    93	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_sqlite                           | start
    98	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | bulk_insert_csv_file                | Count rows added to table: 137
    99	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_sqlite                           | end
   102	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_distribution_wrapper             | start kb_distribution
   104	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_dist                             | start
   105	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_dist                             | end
   107	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_distribution_wrapper             | end   kb_distribution
   108	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_end_wrapper                      | runtime for kb_etl_workflow in seconds: 1.9780801669985522
   109	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603']
```
### Review ETL KnowledgeBase Data
```bash
(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 kb_sqlite.db knowledgebase_extract_dir
     1	kb_sqlite.db
     2  kb_utc_run_datetime_2022-01-13T07:29:49Z_utc_last_modified_after_2021-12-14T00:00:00Z_batch_000001.json.gz  
     3  kb_utc_run_datetime_2022-01-13T07:29:49Z_utc_last_modified_after_2021-12-14T00:00:00Z_batch_000001.xml.gz
```
## License
[Apache License](http://www.apache.org/licenses/LICENSE-2.0)
    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.
## ChangeLog
Beginning with 0.6.98 a change log will be maintained here.
```

%package help
Summary:	Development documents and examples for qualysetl
Provides:	python3-qualysetl-doc
%description help
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 Env            | June 2021 | Encapsulate qetl Application into Python Virtual Environment at installation.
Asset Inventory(CSAM)         | Oct 2021  | Automate download and transform of GAV/CSAM V2 API into CSV, JSON and SQLite Database
Performance Enhancements      | Jan 2022  | Begin 0.7.x series with performance enhancements.  See change log for details.
Asset Inventory(CSAM)         | Aug 2022  | CSAM API Blog, Video, documentation updates for CSAM, additional edge cases for Qualys Maintenance Windows.
Host List ARS                 | Aug 2022  | Host List Asset Risk Score Added to QualysETL.
Host List Detection QDS       | Aug 2022  | Host List Detection Qualys Detection Score Added to QualysETL.
Web Application Scanning(WAS) | Dec 2022  | Begin 0.8.x series, including WAS Module and Distribution Option, data prepared for database loader.
Policy Compliance             | Apr 2023  | Delayed to include PCRS. Automate download and transform of Policy Compliance Posture, Policy, Controls. 
Asset Tagging API             | Apr 2023  | Automate download and transform of Asset Tagging Information
Docker Image                  | TBD       | Contact your TAM to schedule a call with David Gregory.  Encapsulate Python Application into distributable docker image for ease os operation and upgrade.
Other Modules                 | 2023      | TBD
```
## Technologies
Project tested with:
1. Ubuntu version: 22.04 and 20.04
2. Redhat version: 8.x latest
3. SQLite3 version: 3.31.1
4. Python version: 3.8.5 
5. Qualys API: latest
## 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:
  - 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) 
     https://www.qualys.com/docs/qualys-api-limits.pdf with your TAM for Questions. 
```bash
(qetl_venv) qualysetl@ubuntu:~/.local/bin$ more /opt/qetl/users/qualysetl/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: $DATE
#
# YAML File of available configuration options for Qualys API Calls and future options.
# 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.
#           Note: if you exceed the endpoints concurrency limit,
#                 the application will reset the concurrency limit to X-ConcurrencyLimit-Limit - 1
#
# kb_last_modified_after: 'default'                  # Leave at default.  Knowledgebase is auto-incremental
#                                                      to full knowledgebase.
# kb_export_dir: 'default'                           # Leave at default until future use is developed.
# kb_payload_option: 'default'                       # Leave at default until future use is developed.
# kb_distribution_csv_flag: True                     # True/False Populates qetl_home/data/knowledgebase_distribution_dir
# kb_distribution_csv_max_field_size: 1000000        # Maximum field size allowed in distribution_csv file
#
# host_list_vm_processed_after: 'default'            # Leave at default until future use is developed.
# host_list_payload_option: 'default'                # Leave at default until future use is developed.
# host_list_payload_option: {{'show_ars': '1', 'show_ars_factors': '1'}}  # Contact your TAM to enable TruRisk
# host_list_export_dir: 'default'                    # Leave at default until future use is developed.
# host_list_distribution_csv_flag: True              # True/False Populates qetl_home/data/host_list_distribution_dir
# host_list_distribution_csv_max_field_size: 1000000 # Maximum field size allowed in distribution_csv file
#
# host_list_detection_payload_option: 'default'      # Leave at default until future use is developed.
# host_list_detection_payload_option: {{'show_qds': '1', 'show_qds_factors': '1'}} # Contact your TAM to enable TruRisk.
# host_list_detection_export_dir: 'default'          # Leave at default until future use is developed.
# host_list_detection_vm_processed_after: 'default'  # Leave at default until future use is developed.
# host_list_detection_concurrency_limit: 2           # Reset based on your subscription api concurrency limits
# host_list_detection_multi_proc_batch_size: 1000    # Leave at 1000
# host_list_detection_distribution_csv_flag: True    # True/False Populates qetl_home/data/host_list_detection_distribution_dir
# host_list_detection_distribution_csv_max_field_size: 1000000 # Maximum field size allowed in distribution_csv file
#
# asset_inventory_payload_option: 'default'          # Leave at 'default' until future use is developed.
# asset_inventory_export_dir: 'default'              # Leave at 'default' until future use is developed.
# asset_inventory_asset_last_updated: 'default'      # Leave at 'default' until future use is developed.
# asset_inventory_distribution_csv_flag: True        # True/False Populates qetl_home/data/asset_inventory_distribution_dir
# asset_inventory_distribution_csv_max_field_size: 1000000 # Maximum field size allowed in distribution_csv file
#
# requests_module_tls_verify_status: True            # Recommend leaving at True to protect application against
#                                                      man-in-middle attacks. False will set Python3 requests module
#                                                      verify option to False and requests will accept any TLS
#                                                      certificate presented by the server, and will ignore hostname
#                                                      mismatches and/or expired certificates, which will make your
#                                                      application vulnerable to man-in-the-middle (MitM) attacks
#                                                      This option is useful for development testing only when you
#                                                      are behind a reverse proxy, ex. Data Loss Prevention solution,
#                                                      and you haven't installed the trusted certificates yet.
requests_module_tls_verify_status: True
kb_last_modified_after: 'default'
kb_export_dir: 'default'
kb_payload_option: 'default'
kb_distribution_csv_flag: True
host_list_vm_processed_after: 'default'
host_list_export_dir: 'default'
host_list_distribution_csv_flag: True
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: 1000
host_list_detection_distribution_csv_flag: True
asset_inventory_payload_option: 'default'
asset_inventory_export_dir: 'default'
asset_inventory_asset_last_updated: 'default'
asset_inventory_distribution_csv_flag: True
was_distribution_csv_flag: True
```
### ETL KnowledgeBase
KnowledgeBase ETL - Incremental Update to Knowledgebase.  CSV, JSON, SQLite are full knowledgebase.  XML is incremental.
   - note the knowledgebase will rebuild itself every 30-90 days to ensure gdbm is reorganized.
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_knowledgebase 
```
### ETL Host List
Host List ETL - Download Host List based on date
  - if no date is used, Host List will auto increment from last run 
    ( max LAST_VULN_SCAN_DATETIME ) or if no sqlite database exists
    it download start incremental pull from utc minus 1 day. 
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_host_list -d [YYYY-MM-DDThh:mm:ssZ]
```
See [Application Manager and Data](#application-manager-and-data) for location of your qetl_home directory.
### ETL Host List Detection
Host List Detection ETL - Includes KnowledgeBase and Host List so do not run ETL Host List or ETL KnowledgeBase while Host List Detection ETL is runnning..
  - if no date is used, The Host List Driver will auto increment from last run
      ( max LAST_VULN_SCAN_DATETIME ) or if no sqlite database exists
      it download start incremental pull from utc minus 1 day. 
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_host_list_detection -d [YYYY-MM-DDThh:mm:ssZ]
```
### ETL Asset Inventory
Asset Inventory (GAV/CSAM API) ETL - Includes CyberSecurity Asset Inventory API (CSAM) or its subset Global Asset View API (GAV).
- if no date is used, The Asset Inventory will be pulled from UTC - one day. 
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_asset_inventory -d [YYYY-MM-DDThh:mm:ssZ]
```
### ETL Web Application Scanning Data
Web Application Scanning (WAS API) ETL - Includes Web Applications, Web Application Findings and the Web Application Catalog.
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_was -d [YYYY-MM-DDThh:mm:ssZ]
```
### ETL Test System
Test System ETL - Small system test to validate modules are all working.  
* log/test_system.log will contain all results.  
Executes Programs:
1. etl_knowledgebase - updates knowledgebase up to date. 
2. etl_host_list ( 75 hosts )
3. etl_host_list_detection ( 75 hosts )
4. etl_asset_inventory ( 900 hosts )
4. etl_was ( subset of applications )
```bash
qetl_manage_user -u /opt/qetl/users/quser -e etl_test_system
```
## Application Manager and Data
### qetl_manage_user application
- qetl_manage_user is your entry point to manage ETL of Qualys Data.
[![](https://user-images.githubusercontent.com/82658653/213870402-d5bf448f-9c2f-4c8a-b36b-54fce35818af.png)](https://user-images.githubusercontent.com/82658653/213870402-d5bf448f-9c2f-4c8a-b36b-54fce35818af.png)
### Host List Detection SQLite Database
- qetl_manage_user -u [userdir] -e etl_host_list_detection -d [datetime] - Resulting sqlite database ready for distribution.
[![](https://user-images.githubusercontent.com/82658653/120927089-a1bb9880-c6ad-11eb-8b83-98c3e7643473.png)](https://user-images.githubusercontent.com/82658653/120927089-a1bb9880-c6ad-11eb-8b83-98c3e7643473.png)
### Host List Detection SQLite Tables
- qetl_manage_user -u [userdir] -e etl_host_list_detection -d [datetime] - Resulting sqlite database ready for distribution.
[![](https://user-images.githubusercontent.com/82658653/190963226-dee51f36-7f32-492a-9cb6-5acb906e4d7d.png)](https://user-images.githubusercontent.com/82658653/190963226-dee51f36-7f32-492a-9cb6-5acb906e4d7d.png)
### 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 of all run logs                                                  |
| qetl_home/log/kb.log                           | LOG KnowledgeBase Run Logs                                                        |
| qetl_home/log/host_list.log                    | LOG - Host List Run Logs                                                          |
| qetl_home/log/host_list_detection.log          | LOG - Host List Detection Run Logs                                                |
| qetl_home/log/asset_inventory.log              | LOG - GAV/CSAM Asset Inventory Run Logs                                           |
| qetl_home/log/was.log                          | LOG - Web Application Scanning(WAS) Run Logs                                      |
| qetl_home/data                                 | Application Data - Directory containing all csv, xml, json, sqlite database data. |
| qetl_home/data/kb_sqlite.db                    | Database - Cumulative Knowledgebase SQLite Database                               |
| qetl_home/data/host_list_sqlite.db             | Database - vm_last_processed Host List SQLite Database                            |
| qetl_home/data/host_list_detection_sqlite.db   | Database - vm_last_processed Host List Detection SQLite Database                  |
| qetl_home/data/asset_inventory_sqlite.db       | Database -lastScanDate Asset Inventory SQLite Database                            |
| qetl_home/data/was_sqlite.db                   | Database - WebApp lastScan.date SQLite Database                                   |
| qetl_home/data/knowledgebase_extract_dir       | Extract - latest *.json.gz, *.xml.gz files                                        |
| qetl_home/data/host_list_extract_dir           | Extract - latest *.json.gz, *.xml.gz files                                        |
| qetl_home/data/host_list_detection_extract_dir | Extract - vm_last_processed Host List Detection XML Data Dir                      |
| qetl_home/data/asset_inventory_extract_dir     | Extract - Asset Inventory Extracts of last scan date of asset in JSON Format.     |
| qetl_home/data/was_extract_dir                 | Extract - Web Application Scanning (WAS) JSON Data Dir                            |
| qetl_home/data/knowledgebase_distribution_dir       | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/host_list_distribution_dir           | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/host_list_detection_distribution_dir | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/asset_inventory_distribution_dir     | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
| qetl_home/data/was_distribution_dir                 | Distribution - latest *.csv.gz files if option set in etld_config.settings.yaml   |
### Data Formats 
Data Formats created in qetl_home/data:
| Format          | Description                                                                                                                                                                                                                                                                                                                                                                                   |
|-----------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| JSON            | [Java Script Object Notation](https://datatracker.ietf.org/doc/html/rfc7159) useful for transfer of data between systems                                                                                                                                                                                                                                                                      |
| CSV             | [Comma Separated Values](https://datatracker.ietf.org/doc/html/rfc4180) useful for transfer of data between systems<br>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](https://datatracker.ietf.org/doc/html/rfc3470) useful for transfer of data between systems                                                                                                                                                                                                                                                                       |
| SQLite Database | [SQLite Database](https://www.sqlite.org/about.html): 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 |
### Logging
Logging fields are pipe delimited with some formatting for raw readability.  You can easily import this data into excel, 
 a database for analysis or link this data to a monitoring system.
| 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: YYYYMMDDHHMMSS | Top Level qetl Application Module Name that is executing, along with date to uniquely identify all log entries associated with that job. |
| 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](#application-directories) for details of each log file.
```bash
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: 20210528012603    | dgregory        | setup_logging_stdout                | LOGGING SUCCESSFULLY SETUP FOR STREAMING
     2	2021-05-28 01:26:03,836 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | get_sqlite_version                  | SQLite version found is: 3.31.1.
     5	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase: 20210528012603    | 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
QualysETL provides options to inject credentials at runtime via qetl_manage_user, so your credentials are not stored on disk.  
qetl_manage_user options to inject credentials at runtime are:
1) -p, --prompt-credentials prompt user for credentials, also accepts stdin with credentials piped to program.
2) -m, --memory-credentials get credentials from environment: q_username, q_password, q_api_fqdn_server
3) -s, --stdin-credentials  send credentials in json to stdin. 
         Example:
         {"q_username": "your userid", "q_password": "your password", "q_api_fqdn_server": "api fqdn", "q_gateway_fqdn_server": "gateway api fqdn"}
Qualys recommends customers move to a password vault of their choosing 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, injecting the credentials at runtime.  
One way customers can do this is through a work load management solution, where the external work load management
system ( Ex. Autosys ) schedules jobs injecting the required credentials to QualysETL 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](https://www.hashicorp.com/products/vault)
- [Hashicorp Getting Started](https://learn.hashicorp.com/tutorials/vault/getting-started-intro?in=vault/getting-started)
## Example Run Logs
### Uninstall and Install qetl
#### Uninstall Run Log
- 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
```bash
(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).
```bash
(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
```bash
qualysetl@ubuntu:~/.local/bin$ ./qetl_setup_python_venv /opt/qetl
Start qetl_setup_python_venv - Fri Jan 21 07:07:22 PST 2022
  1) test_os_for_required_commands
  2) test_for_pip_connectivity
  3) prepare_opt_qetl_env_dirs
    usage:       qetl_setup_python_venv [/opt/qetl] [test|prod] [version number]
                 qetl_setup_python_venv [-h] for help
    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.
        See https://pypi.org/project/qualysetl/ for first time setup and
        installation instructions.
    options:
        qetl_setup_python_venv [/opt/qetl] [test|prod] [version number]
        1) [/opt/qetl]        - root directory where application and data
                                will be stored.
                              - You must be root to create this directory.
                              - See https://pypi.org/project/qualysetl/ for
                                first time setup/installation instructions.
        2) [test|prod]        - obtain QualysETL from test or prod pypi
                                instance.
        3) [version number]   - obtain version number of qualysetl.
    examples:
            1) qetl_setup_python_venv /opt/qetl
               - Ensure you have /opt/qetl directory created before running
                 this program.
               - Creates QualysETL Environment.  See directory information
                 below.
            2) qetl_setup_python_venv /opt/qetl prod 0.6.131
               - will install version 0.6.131 of qualysetl from pypi.org into
                 your /opt/qetl/qetl_venv directory.
            3) qetl_setup_python_venv /opt/qetl test 0.6.131
               - will install version 0.6.131 of qualysetl from test.pypi.org
                 into your /opt/qetl/qetl_venv directory.
    directory information:
             /opt/qetl            - root directory for Application and Data
             /opt/qetl/qetl_venv  - application directory for Qualys ETL
                                    Python Virtual Environment
             /opt/qetl/users      - data directory containing results of
                                    QualysETL execution.
    files:
        See https://dg-cafe.github.io/qualysetl/#application-manager-and-data
    container notes:
            1) For container deployment, ex docker, application and data
               are separated for container deployment.
               Container Application - /opt/qetl/qetl_venv should installed into the container image.
               Persistent Data       - /opt/qetl/users should be mapped to the underlying host
                                       system for persistent storage of application data.
Create qetl Python Environment? /opt/qetl/qetl_venv prod:latest
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: dgregory@qualys.com, dave@davidgregory.com
     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 point to operating qualysetl ) 
   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.
```bash
(qetl_venv) qualysetl@ubuntu:~/.local/bin$ qetl_manage_user 
    usage: qetl_manage_user [-h] [-u qetl_USER_HOME_DIR] [-e etl_[module] ] [-e validate_etl_[module] ] [-c] [-t] [-i] [-d] [-r] [-l]
    Command to Extract, Transform and Load Qualys Data into various forms ( CSV, JSON, SQLITE3 DATABASE )
    optional arguments:
      -h, --help                show this help message and exit
      -u Home Directory Path,   --qetl_user_home_dir Home directory Path
                                   Example:
                                   - /opt/qetl/users/q_username
      -e etl_[module],          --execute_etl_[module] execute etl of module name. valid options are:
                                       -e etl_knowledgebase 
                                       -e etl_host_list 
                                       -e etl_host_list_detection
                                       -e etl_asset_inventory
                                       -e etl_was
                                       -e etl_test_system ( for a small system test of all ETL Jobs )
      -e validate_etl_[module], --validate_etl_[module] [test last run of etl_[module]].  valid options are:
                                       -e validate_etl_knowledgebase
                                       -e validate_etl_host_list 
                                       -e validate_etl_host_list_detection
                                       -e validate_etl_asset_inventory
                                       -e validate_etl_was
                                       -e validate_etl_test_system 
      -d YYMMDDThh:mm:ssZ,      --datetime      YYYY-MM-DDThh:mm:ssZ UTC. Get All Data On or After Date. 
                                                Ex. 1970-01-01T00:00:00Z acts as flag to obtain all data.
      -c, --credentials        update qualys api user credentials: qualys username, password or api_fqdn_server
      -t, --test               test qualys credentials
      -i, --initialize_user    For automation, create a /opt/qetl/users/[userhome] directory 
                               without being prompted.
      -l, --logs               detailed logs sent to stdout for testing qualys credentials
      -v, --version            Help and QualysETL version information.
      -r, --report             brief report of the users directory structure.
      -p, --prompt-credentials prompt user for credentials, also accepts stdin with credentials piped to program.
      -m, --memory-credentials get credentials from environment: 
                               Example: q_username="your userid", q_password=your password, q_api_fqdn_server=api fqdn, q_gateway_fqdn_server=gateway api fqdn
      -s, --stdin-credentials  send credentials in json to stdin. 
                               Example:
                               {"q_username": "your userid", "q_password": "your password", "q_api_fqdn_server": "api fqdn", "q_gateway_fqdn_server": "gateway api fqdn"}
      etld_config_settings.yaml notes:
         1. To Enable CSV Distribution, add the following keys to etld_config_settings.yaml and toggle on/off them via True or False
              kb_distribution_csv_flag: True                    # populates qetl_home/data/knowledgebase_distribution_dir
              host_list_distribution_csv_flag: True             # populates qetl_home/data/host_list_distribution_dir
              host_list_detection_distribution_csv_flag: True   # populates qetl_home/data/host_list_detection_distribution_dir
              asset_inventory_distribution_csv_flag: True       # populates qetl_home/data/asset_inventory_distribution_dir
              was_distribution_csv_flag: True                   # populates qetl_home/data/was_distribution_dir
              These files are prepared for database load, tested with mysql.  No headers are present.  
              Contact your Qualys TAM and schedule a call with David Gregory if you need assistance with this option.
         2. To enable TruRisk, ask your TAM to add TruRisk to your subscription, then update the following keys in your etld_config_settings.yaml
            host_list_payload_option: {'show_ars': '1', 'show_ars_factors': '1'}
            host_list_detection_payload_option: {'show_qds': '1', 'show_qds_factors': '1'}
```
### 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.
```bash
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.
         Example:
            1) /opt/qetl/users/newuser
        usage: qetl_manage_user [-h] [-u QETL_USER_HOME_DIR] [-e EXECUTE_ETL_MODULE] [-d DATETIME] [-c] [-t] [-l] [-p] [-s] [-m] [-r]
        Command to Extract, Transform and Load Qualys Data into various forms ( CSV, JSON, SQLITE3 DATABASE )
        optional arguments:
          -h, --help            show this help message and exit
          -u QETL_USER_HOME_DIR, --qetl_user_home_dir QETL_USER_HOME_DIR
                                Please enter -u option
          -e EXECUTE_ETL_MODULE, --execute_etl_module EXECUTE_ETL_MODULE
                                Execute etl_knowledgebase, etl_host_list, etl_host_list_detection, etl_asset_inventory, etl_test_system
          -d DATETIME, --datetime DATETIME
                                YYYY-MM-DDThh:mm:ssZ UTC. Get All Data On or After Date. Ex. 1970-01-01T00:00:00Z acts as flag to obtain all data.
          -c, --credentials     update qualys api user credentials stored on disk: qualys username, password or api_fqdn_server
          -t, --test            test qualys credentials
          -l, --logs            detailed logs sent to stdout for test qualys credentials
          -p, --prompt_credentials
                                prompt user for credentials
          -s, --stdin_credentials
                                read stdin credentials json {"q_username":"your userid", "q_password":"your password", "q_api_fqdn_server":"api fqdn", "q_gateway_fqdn_server":"gateway api fqdn"}
          -m, --memory_credentials
                                Get credentials from environment variables in memory: q_username, q_password, q_api_fqdn_server, and optionally add q_gateway_fqdn_server. Ex. export q_username=myuser
          -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
```bash
(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: 20210528012603    | dgregory        | setup_logging_stdout                | LOGGING SUCCESSFULLY SETUP FOR STREAMING
     2	2021-05-28 01:26:03,836 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | setup_logging_stdout                | PROGRAM: ['/home/dgregory/opt/qetl/qetl_venv/bin/qetl_manage_user', '-u', '/opt/qetl/users/qualys_user', '-e', 'etl_knowledgebase: 20210528012603']
     3	2021-05-28 01:26:03,897 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | get_sqlite_version                  | SQLite version found is: 3.31.1.
     5	2021-05-28 01:26:03,898 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | setup_kb_vars                       | kb_export_dir is direct from yaml
    26	2021-05-28 01:26:03,902 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | setup_host_list_vars                | host_list_payload_option yaml - notags
    30	2021-05-28 01:26:03,906 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603']
    32	2021-05-28 01:26:03,907 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | knowledgebase_extract                          | start
    38	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | get_cred                            |      username:         quays93
    40	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | get_cred                            |      api_fqdn_server:  qualysapi.qg2.apps.qualys.com
    41	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | get_cred                            |  ** Warning: Ensure Credential File permissions are correct for your company.
    42	2021-05-28 01:26:03,909 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | knowledgebase_extract                          | cookie      - False
    47	2021-05-28 01:26:05,717 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | knowledgebase_extract                          | end
    50	2021-05-28 01:26:05,718 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_extract_wrapper                  | end knowledgebase_extract xml from qualys
    51	2021-05-28 01:26:05,719 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_shelve_wrapper           | start kb_shelve xml to shelve
    52	2021-05-28 01:26:05,719 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_shelve                           | start
    55	2021-05-28 01:26:05,744 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    56	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    59	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_shelve                           | end
    61	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_shelve_wrapper           | end   kb_shelve xml to shelve
    62	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_json_wrapper                  | start kb_load_json transform Shelve to JSON
    63	2021-05-28 01:26:05,815 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_json                             | start
    66	2021-05-28 01:26:05,840 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_load_json                             | count qid loaded to json: 137
    67	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    69	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_json                             | end
    71	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_json_wrapper                  | end   kb_load_json transform Shelve to JSON
    72	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_to_csv_wrapper                   | start kb_load_csv - shelve to csv
    73	2021-05-28 01:26:05,841 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_create_csv_from_shelve           | start
    77	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_create_csv_from_shelve           | count rows written to csv: 137
    78	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    80	2021-05-28 01:26:05,864 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_create_csv_from_shelve           | end
    82	2021-05-28 01:26:05,867 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | log_dbm_info                        | dbm etl_workflow_validation_type - dbm.gnu - /opt/qetl/users/qualys_user/qetl_home/data/kb_shelve
    85	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_to_csv_wrapper                   | end   kb_load_csv - shelve to csv
    87	2021-05-28 01:26:05,868 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_cve_qid_csv_report               | Start
    91	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_cve_qid_csv_report               | Count of CVE rows written: 334
    92	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_cve_qid_csv_report               | End
    93	2021-05-28 01:26:05,869 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_sqlite                           | start
    98	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | bulk_insert_csv_file                | Count rows added to table: 137
    99	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | 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: 20210528012603    | dgregory        | kb_load_sqlite                           | end
   102	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_distribution_wrapper             | start kb_distribution
   104	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_dist                             | start
   105	2021-05-28 01:26:05,884 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603    | dgregory        | kb_dist                             | end
   107	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_distribution_wrapper             | end   kb_distribution
   108	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase: 20210528012603    | dgregory        | kb_end_wrapper                      | runtime for kb_etl_workflow in seconds: 1.9780801669985522
   109	2021-05-28 01:26:05,885 | INFO     | etl_knowledgebase: 20210528012603    | 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: 20210528012603']
```
### Review ETL KnowledgeBase Data
```bash
(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 kb_sqlite.db knowledgebase_extract_dir
     1	kb_sqlite.db
     2  kb_utc_run_datetime_2022-01-13T07:29:49Z_utc_last_modified_after_2021-12-14T00:00:00Z_batch_000001.json.gz  
     3  kb_utc_run_datetime_2022-01-13T07:29:49Z_utc_last_modified_after_2021-12-14T00:00:00Z_batch_000001.xml.gz
```
## License
[Apache License](http://www.apache.org/licenses/LICENSE-2.0)
    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.
## ChangeLog
Beginning with 0.6.98 a change log will be maintained here.
```

%prep
%autosetup -n qualysetl-0.8.14

%build
%py3_build

%install
%py3_install
install -d -m755 %{buildroot}/%{_pkgdocdir}
if [ -d doc ]; then cp -arf doc %{buildroot}/%{_pkgdocdir}; fi
if [ -d docs ]; then cp -arf docs %{buildroot}/%{_pkgdocdir}; fi
if [ -d example ]; then cp -arf example %{buildroot}/%{_pkgdocdir}; fi
if [ -d examples ]; then cp -arf examples %{buildroot}/%{_pkgdocdir}; fi
pushd %{buildroot}
if [ -d usr/lib ]; then
	find usr/lib -type f -printf "/%h/%f\n" >> filelist.lst
fi
if [ -d usr/lib64 ]; then
	find usr/lib64 -type f -printf "/%h/%f\n" >> filelist.lst
fi
if [ -d usr/bin ]; then
	find usr/bin -type f -printf "/%h/%f\n" >> filelist.lst
fi
if [ -d usr/sbin ]; then
	find usr/sbin -type f -printf "/%h/%f\n" >> filelist.lst
fi
touch doclist.lst
if [ -d usr/share/man ]; then
	find usr/share/man -type f -printf "/%h/%f.gz\n" >> doclist.lst
fi
popd
mv %{buildroot}/filelist.lst .
mv %{buildroot}/doclist.lst .

%files -n python3-qualysetl -f filelist.lst
%dir %{python3_sitelib}/*

%files help -f doclist.lst
%{_docdir}/*

%changelog
* Fri May 05 2023 Python_Bot <Python_Bot@openeuler.org> - 0.8.14-1
- Package Spec generated