automatic import of python-qualysetlopeneuler20.03

author: CoprDistGit <infra@openeuler.org> 2023-05-05 12:59:28 +0000
committer: CoprDistGit <infra@openeuler.org> 2023-05-05 12:59:28 +0000
commit: 38e2c462cf42f940ba73ee1435022eca56052acf (patch)
tree: c61116b1b370c389d4f5b2958915888e4d594ff8
parent: 68b4d773e4a0e1a37e381756c0587562eb2aced7 (diff)
3 files changed, 1964 insertions, 0 deletions
diff --git a/.gitignore b/.gitignore
index e69de29..9e7c6e2 100644
--- a/.gitignore
+++ b/.gitignore
@@ -0,0 +1 @@
+/qualysetl-0.8.14.tar.gz
diff --git a/python-qualysetl.spec b/python-qualysetl.spec
new file mode 100644
index 0000000..e04b937
--- /dev/null
+++ b/python-qualysetl.spec
@@ -0,0 +1,1962 @@
+%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
diff --git a/sources b/sources
new file mode 100644
index 0000000..7402e28
--- /dev/null
+++ b/sources
@@ -0,0 +1 @@
+d8d2299e3b2465fb79b368a1dc3d8b66  qualysetl-0.8.14.tar.gz
author	CoprDistGit <infra@openeuler.org>	2023-05-05 12:59:28 +0000
committer	CoprDistGit <infra@openeuler.org>	2023-05-05 12:59:28 +0000
commit	38e2c462cf42f940ba73ee1435022eca56052acf (patch)
tree	c61116b1b370c389d4f5b2958915888e4d594ff8
parent	68b4d773e4a0e1a37e381756c0587562eb2aced7 (diff)