qualysetl 0.9.20
Qualys API Best Practices Series - ETL Blueprint Example Code within Python Virtual Environment
License: Apache
Requires Python: >=3.8.5
Keywords: qualys, etl, qualys.com, david gregory, qualysetl, qualysapi

# [Qualys API Best Practices Series](https://blog.qualys.com/tag/api-best-practices-series) 
Explore the  [Qualys API Best Practices Series](https://blog.qualys.com/tag/api-best-practices-series), for insightful guidance on maximizing the effectiveness of the Qualys API and QualysETL. 
This series offers valuable tips, expert advice, and practical strategies to help you optimize your use of the Qualys platform for enhanced cybersecurity and compliance management. 
Ideal for both new and experienced users, it offers key insights and showcases the practical use of QualysETL, enriching your understanding and skills in API interactions and data management.

# Discover QualysETL: Empowering Cybersecurity with Qualys Data

QualysETL is a valuable tool in harnessing the power of Qualys data, enabling organizations to efficiently Measure, Communicate, and Eliminate Cyber Risk. This synergy fortifies businesses against the evolving landscape of digital threats.

- **Innovative Data Management**: QualysETL is an open-source Python application crafted to streamline the Extract, Transform, Load (ETL) process. It adeptly handles the distribution of Qualys data into various databases, offering convenient processing of CSV, JSON, and XML data formats.

- **Centralized Data Management**: QualysETL allows for the consolidation of all Qualys-generated data into a unified 'gold source' database. This central repository of data is instrumental in enhancing the overall security and compliance capabilities of an organization.

- **Streamlined Data Processing**: QualysETL takes the helm in executing API calls, thus systematically preparing Qualys data for organizational use. This level of automation and precision in data processing significantly reduces manual effort and resource allocation.

- **Strengthened Security Posture**: The combination of QualysETL and Qualys data equips organizations with the tools necessary to significantly improve their security and compliance frameworks. This collaborative effort is essential in mitigating the risks associated with cyberattacks and ensuring adherence to pertinent legal and regulatory requirements.

- **Getting Started Made Simple**: Integrating QualysETL into your organization's cybersecurity strategy is straightforward. For an easy setup, refer to the [installation instructions](#installation) provided in this guide.


## Schema - QualysETL Vulnerability Data Example.
  - [![](https://github.com/user-attachments/assets/ef74979b-4e18-4dff-84e7-b6844657b1f3)](https://github.com/user-attachments/assets/ef74979b-4e18-4dff-84e7-b6844657b1f3)

# QualysETL: Transforming Cybersecurity with Success Stories

QualysETL has enhanced how customers manage their cybersecurity data. With its robust and automated pipeline, organizations efficiently funnel data into a variety of databases, enhancing security insights and decision-making. Key databases tested include:

- Azure CosmosDB PostgreSQL
- Azure SQL Server
- Snowflake
- PostgreSQL
- Microsoft SQL Server
- MySQL
- SQLite
- Amazon RedShift

The success stories of QualysETL are a testament to its versatility and the value it delivers:

- **Enhanced Remediation and Reporting**: Integrating Qualys data with corporate resources, customers have seen significant improvements in Remediation Reporting, Metrics, and Visualization, leading to more effective cybersecurity strategies.

- **Advanced Data Analysis**: Analysts leverage the rich data sets from Qualys for in-depth forensic examinations, gaining deeper insights into cybersecurity threats and vulnerabilities.

- **Simplified Data Visualization**: With QualysETL, simplifying and focusing data for tools like Tableau, PowerBI, or custom web applications has never been easier, enabling more intuitive and accessible cybersecurity analytics.

- **Measurable Cyber Risk Reduction**: Customers effectively Measure, Communicate, and Eliminate Cyber Risk, substantially De-Risking their business operations.

QualysETL is not just limited to current functionalities. It already supports key Qualys Modules like:

- Vulnerability Management
- Policy Compliance
- CyberSecurity Asset Management
- Web Application Scanning Data

And with an eye on the future, the 2024 roadmap includes exciting additions such as Qualys Container Security and Qualys FIM, further expanding its capabilities and reinforcing its position as a valuable tool in cybersecurity data management.

##  QualysETL: Unified Data Integration and Distribution for Comprehensive Security Insights

This diagram represents the QualysETL system designed to integrate and distribute data from multiple Qualys subscriptions for comprehensive security insights. The system operates within a virtual machine or Docker host container running the latest Ubuntu or Redhat distribution. It automates the data extraction and loading process using a CRON scheduler and bash scripts.

#### Key Components:

1. **Qualys Unified Platform:**
   - **Qualys REST API:** 
     - Supports one subscription or multi-subscription scenarios.
     - Used by customers with one or more subscriptions and MSP/MSSP handling multiple clients.

2. **QualysETL System:**
   - **Customer Operations:** 
     - Automation through bash scripts and a CRON scheduler.
   - **QualysETL Modules Supported:**
     - Ephemeral data snapshots with multi-subscription support.
     - Supported Modules: Vulnerability Data (VM), Policy Compliance Data (PC), Cyber Security Asset Management Data (CSAM), Web Application Scanning Data (WAS).
     - Future Modules: Container Security (CS), File Integrity Management (FIM).

3. **Persistent Data Storage:**
   - **Data Warehouse Options:**
     - Various database options tested for compatibility and performance.
     - Includes Snowflake, PostgreSQL, Azure SQL Server, Azure Cosmos DB, Amazon RDS, MySQL, Limited Amazon RedShift, and in the future GCP Cloud SQL PostgreSQL, and GCP Big Query.

#### Data Flow:
- The automation component runs CRON jobs to initiate the data extraction process.
- Data is retrieved from one or more  Qualys subscriptions via the Qualys REST API.
- The QualysETL system extracts the data, transforms the data, prepares the data and loads it into the specified data warehouses or databases, creating schemas and loading the data for further visualization, analysis and reporting.

This architecture ensures seamless integration and distribution of Qualys data, providing a unified view for enhanced security insights across multiple subscriptions and clients.

[![](https://github.com/dg-cafe/test/assets/82658653/35a99071-ea3b-472a-b3ca-ecb262306447)](https://github.com/dg-cafe/test/assets/82658653/35a99071-ea3b-472a-b3ca-ecb262306447)


# QualysETL Central Data Warehouse
Once customers have used QualysETL to gather data from various sources such as Qualys Vulnerability Data, Policy Compliance Data, CyberSecurity Asset Management Data, and Web Application Scanning data into a database, there are several critical activities they typically perform to enhance their security posture and compliance. These activities include:

   - **Data Analysis and Prioritization:** Analyzing the collected data to identify and prioritize security vulnerabilities and compliance issues. This involves sorting through the vulnerabilities to determine which are most critical based on factors like severity, exploitability, and potential impact.
   - **Risk Assessment:** Performing a comprehensive risk assessment using the data to understand the potential impact of identified vulnerabilities and non-compliance issues on the business.
   - **Remediation Planning:** Developing remediation plans to address identified vulnerabilities and compliance gaps. This may involve patch management, configuration changes, or other corrective actions.
   - **Compliance Monitoring:** Using policy compliance data to monitor adherence to internal policies and external regulatory requirements. This helps in ensuring ongoing compliance and identifying areas where policies may need to be adjusted.
   - **Asset Management:** Leveraging CyberSecurity Asset Management data to maintain an up-to-date inventory of all hardware and software assets. This is crucial for effective vulnerability management and ensuring that all assets are accounted for in security planning.
   - **Trend Analysis and Reporting:** Analyzing trends over time to identify recurring issues or vulnerabilities and generating reports for various stakeholders, including management, IT teams, and external regulators.
   - **Incident Response Planning:** Utilizing the data to inform and improve incident response plans. By understanding where vulnerabilities exist and how they can be exploited, organizations can develop more effective response strategies.
   - **Security Policy Development and Adjustment:** Refining and developing security policies based on the insights gained from the data. This might involve updating access controls, data protection strategies, or other security protocols.
   - **Training and Awareness Programs:** Using insights from the data to inform security training and awareness programs. Understanding common vulnerabilities and compliance issues can help tailor training to address specific organizational needs.
   - **Integration with Other Security Tools:** Integrating the database with other security tools and platforms for a more cohesive and automated security approach. This can include SIEM (Security Information and Event Management) systems, threat intelligence platforms, and automated remediation tools.
   - **Regular Audits and Assessments:** Conducting regular audits and assessments to ensure that the security measures are effective and to identify areas for improvement.

By effectively utilizing Qualys data, organizations can significantly enhance their security and compliance posture, reducing the likelihood of successful cyber attacks and ensuring adherence to relevant laws and regulations.

## Key Advantages of Using QualysETL

1. **Effortless Data Handling**: QualysETL simplifies the process of Extracting, Transforming, Loading, and Distributing Qualys data with a single command, offering a no-code solution that streamlines data management.

2. **Versatile Data Preparation**: The tool adeptly prepares data in various formats including XML, JSON, SQLite, and CSV. It ensures readiness for seamless integration into popular databases like MySQL, PostgreSQL, Snowflake, Amazon RedShift, and more.

3. **Real-Time Data Streaming**: Features a streaming data option for the immediate ingestion of vulnerability reports or asset inventory updates, enabling timely updates to your downstream databases.

4. **User-Friendly and Quick Setup**: QualysETL is designed for easy installation and use. Get it up and running in just 5 minutes on an Ubuntu 24.04 system, ensuring a smooth and swift start.

5. **Open Source Flexibility**: Distributed under the Apache 2 license, QualysETL offers the benefits of open-source software, including transparency, community support, and the freedom to modify the tool to suit your specific needs.

## Qualys data Included in QualysETL:
1. **KnowledgeBase** - QID Definitions
2. **Host List** - Host Information
3. **Host List Detection** - Host Vulnerability Information
4. **CyberSecurity Asset Inventory** - Detailed Asset Inventory
5. **Web Application Scanning** - Web Applications and Web Application Vulnerability Findings.
6. **PCRS Policy Compliance** - Policy, Host, and Host Posture Information.

## Qualys API Versioning included in QualysETL:
We’re thrilled to announce a significant enhancement to our API infrastructure: the introduction of API versioning, which is vital for maintaining seamless communication and data exchange between applications during software development. The introduction of API versioning is a strategic change to empower our customers with greater control, better stability, and smoother integration processes.

* See API Versioning Announcement: https://notifications.qualys.com/api/2024/05/17/introducing-api-versioning-a-strategic-upgrade-for-enhanced-stability-and-control-for-api-integrations

### API Versioning and QualysETL
* QualysETL has been enhanced to allow for API Versioning.  As new versions of API endpoints are released, if QualysETL requires an update, it will be recorded here.


### Enable new V5 Host List and V4 Host List Detection API Version Endpoints in QualysETL.
* Adding these entries to your etld_config_settings.yaml will enable the new api endpoint versions in QualysETL Version >= 0.9.16
```text
# API Update 0.9.16 Update 2025-10-04.
host_list_api_endpoint: '/api/5.0/fo/asset/host/'
host_list_detection_api_endpoint: '/api/4.0/fo/asset/host/vm/detection/'
```

### Enable new API Version Endpoints in QualysETL.

* By adding these entries to the etld_config_settings.yaml, you'll enable new versions of these endpoints.  These API endpoints require enabling by March 2025, please test before March 2025.
```text
# 0.9.1 - etld_config_settings.yaml additions to enable API Endpoint in QualysETL.
kb_api_endpoint: '/api/3.0/fo/knowledge_base/vuln/'
host_list_api_endpoint: '/api/3.0/fo/asset/host/'
host_list_detection_api_endpoint: '/api/3.0/fo/asset/host/vm/detection/'
pcrs_postureinfo_api_endpoint: '/pcrs/2.0/posture/postureInfo/userdefinedfield'
```

* Release Details of each API Endpoint Update.
  * kb_api_endpoint: '/api/3.0/fo/knowledge_base/vuln/' - https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
  * host_list_api_endpoint: '/api/3.0/fo/asset/host/' - https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
  * host_list_detection_api_endpoint: '/api/3.0/fo/asset/host/vm/detection/' - https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
  * pcrs_postureinfo_api_endpoint: '/pcrs/2.0/posture/postureInfo/userdefinedfield' - https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
* Schema Updates ( New Fields ).
  * q_host_list_detection_qids - Add SOURCE
  * q_knowledgebase - Add PATCH_PUBLISHED_DATE
  * q_knowledgebase_in_host_list_detection - Add PATCH_PUBLISHED_DATE
  * q_knowledgebase_in_q_was_finding - Add PATCH_PUBLISHED_DATE
  * q_host_list - Add PC_AUTH_SUCCESS_DATE, OS_HOSTNAME
  * q_host_list_detection_hosts - Add OS_HOSTNAME
  * q_host_list_detection_hosts - Add NETWORK_NAME
* Contact your TAM to request enabling your subscription to populate these fields.
  * PC_AUTH_SUCCESS_DATE - tables: q_host_list. https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
  * OS_HOSTNAME - table: q_host_list_detection_hosts. https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-10.28-api-release-notes.pdf
  * OS_HOSTNAME - table: q_host_list. https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-10.28-api-release-notes.pdf
  * NETWORK_NAME - table: q_host_list_detection_hosts. https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-10.27-api-release-notes.pdf


## [Release Notes](#releasenotes)
Note: See the end of this document for history of [release notes](#releasenotes)
- 0.9.20 - Allow configuration of requests module Accept-Encoding in HTTP Header for all API Calls.
- 0.9.20 - etld_config_settings.yaml - kb_http_accept_encoding, host_list_http_accept_encoding
- 0.9.20 - etld_config_settings.yaml - host_list_detection_http_accept_encoding, asset_inventory_http_accept_encoding
- 0.9.20 - etld_config_settings.yaml - was_http_accept_encoding, pcrs_http_accept_encoding
- 0.9.20 - default for kb_http_accept_encoding is 'identity' to address some edge case slowness. set to '' to go back go request module default.
- 0.9.20 - http_accept_encoding types supported are 'gzip, deflate' or 'identity' or '' (for default gzip, deflate)
- 0.9.16 - Update to GAV/CSAM V2 API - endpoint: /rest/2.0/search/am/asset
- 0.9.16 - Update to GAV/CSAM V2 API - release notes:  https://docs.qualys.com/en/csam/release-notes/cybersecurity_asset_management/release_3_6_1_api.htm
- 0.9.16 - Update to GAV/CSAM V2 API - table: Q_ASSET_INVENTORY table FIELDS: inventoryListData
- 0.9.16 - Update to Host List API V5 - endpoint: /api/5.0/fo/asset/host
- 0.9.16 - Update to Host List API V5 - table: Q_HOST_LIST table FIELDS: VM_AUTH_INSUFFICIENT_PRIVILEGE
- 0.9.16 - Update to Host List API V5 - release notes: https://docs.qualys.com/en/vm/release-notes/qweb/release_10_34_api.htm
- 0.9.16 - Update to Host List API V4 - endpoint: /api/4.0/fo/asset/host
- 0.9.16 - Update to Host List API V4 - table: Q_HOST_LIST table FIELDS: AGENT_ACTIVATION_TITLE, AGENT_ACTIVATION_KEY 
- 0.9.16 - Update to Host List API V4 - release notes: https://docs.qualys.com/en/vm/release-notes/qweb/release_10_32_api.htm
- 0.9.16 - Please update your etld_config_settings.yaml for V5 Host List and V4 Host List detection to ensure enablement for >= 0.9.16.
-         # API Update 0.9.16.
-         host_list_api_endpoint: '/api/5.0/fo/asset/host/'
-         host_list_detection_api_endpoint: '/api/4.0/fo/asset/host/vm/detection/'
- 0.9.10 - For etld_config_settings.yaml - add ability to remove all arf_filter_keys from api call. host_list_detection_payload_option: {'arf_filter_keys': None} 
- 0.9.9 - Fixed initial setup error where template for .etld_cred.yaml file was missing from setup.
- 0.9.8 - Fixed edge case for multiprocessing queue added retry loop to improve long running batch transform.   stop_multiprocessing_queue_to_transform_and_load_xml_files_into_sqlite
- 0.9.4 - Added ability to remove unicode null from all data.  To enable, update etld_config_settings.yaml and set to True or False for "remove_unicode_null_from_row_flag: True", default False.
- 0.9.3 - Updated Install or Upgrade QualysETL Instructions on Ubuntu or Red Hat to create temporary python virtual env to build out QualysETL.
- 0.9.2 - Added new section above "Qualys API Versioning included in QualysETL".  Added additional documentation on what to change for API Versioning. 
```text
# etld_config_settings.yaml additions to enable API Endpoint in QualysETL.
kb_api_endpoint: '/api/3.0/fo/knowledge_base/vuln/'
host_list_api_endpoint: '/api/3.0/fo/asset/host/'
host_list_detection_api_endpoint: '/api/3.0/fo/asset/host/vm/detection/'
pcrs_postureinfo_api_endpoint: '/pcrs/2.0/posture/postureInfo/userdefinedfield'
```
- 0.9.1 - Added support for QWEB 10.30 API Versioning: https://notifications.qualys.com/api/2024/05/17/introducing-api-versioning-a-strategic-upgrade-for-enhanced-stability-and-control-for-api-integrations
- 0.9.1 - Add API Versioning: Update etld_config_settings.yaml to utilize new Qualys API Endpoints.   The following endpoint updates are supported:
- 0.9.1 - Add API Versioning: etld_config_settings.yaml - kb_api_endpoint: '/api/3.0/fo/knowledge_base/vuln/' - https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
- 0.9.1 - Add API Versioning: etld_config_settings.yaml - host_list_api_endpoint: '/api/3.0/fo/asset/host/' - https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
- 0.9.1 - Add API Versioning: etld_config_settings.yaml - host_list_detection_api_endpoint: '/api/3.0/fo/asset/host/vm/detection/' - https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
- 0.9.1 - Add API Versioning: etld_config_settings.yaml - pcrs_postureinfo_api_endpoint: '/pcrs/2.0/posture/postureInfo/userdefinedfield' - https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
- 0.9.1 - Table Update Notes for recent releases 10.30.
- 0.9.1 - Table Update Notes: /api/3.0/fo/knowledge_base/vuln/ - ADD FIELD PATCH_PUBLISHED_DATE - tables: q_knowledgebase, q_knowledgebase_in_host_list_detection, q_knowledgebase_in_q_was_finding. https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
- 0.9.1 - Table Update Notes: /api/[2.0|3.0]/fo/asset/host/vm/detection - Add SOURCE Field - tables: q_host_list_detection_qids, Source of Data, ex. QUALYS.  This is a future use field for different sources of vulnerability data that can be added to Qualys.
- 0.9.1 - Special Table Update Notes: Contact your TAM to request enabling your subscription to populate these fields.
- 0.9.1 - Special Table Update Notes: /api/3.0/fo/asset/host/ - (Contact your TAM to enable populating field) ADD FIELD PC_AUTH_SUCCESS_DATE - tables: q_host_list. https://docs.qualys.com/en/vm/release-notes/qweb/release_10_30_api.htm
- 0.9.1 - Special Table Update Notes: /api/[2.0|3.0]/fo/asset/host/vm/detection - (Contact your TAM to enable populating field) ADD FIELD OS_HOSTNAME - table: q_host_list_detection_hosts. https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-10.28-api-release-notes.pdf
- 0.9.1 - Special Table Update Notes: /api/[2.0|3.0]/fo/asset/host - (Contact your TAM to enable populating field) ADD FIELD OS_HOSTNAME - table: q_host_list. https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-10.28-api-release-notes.pdf
- 0.9.1 - Special Table Update Notes: /api/[2.0|3.0]/fo/asset/host/vm/detection - (Contact your TAM to enable populating field) ADD FIELD NETWORK_NAME - table: q_host_list_detection_hosts. https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-10.27-api-release-notes.pdf
- 0.9.1 - Table Update: q_host_list_detection_qids - Add SOURCE
- 0.9.1 - Table Update: q_knowledgebase - Add PATCH_PUBLISHED_DATE
- 0.9.1 - Table Update: q_knowledgebase_in_host_list_detection - Add PATCH_PUBLISHED_DATE
- 0.9.1 - Table Update: q_knowledgebase_in_q_was_finding - Add PATCH_PUBLISHED_DATE
- 0.9.1 - Table Update: q_host_list - Add PC_AUTH_SUCCESS_DATE, OS_HOSTNAME
- 0.9.1 - Table Update: q_host_list_detection_hosts - Add OS_HOSTNAME
- 0.9.1 - Table Update: q_host_list_detection_hosts - Add NETWORK_NAME
- 0.9.1 - Update: q_host_list_detection_qids where only 1 qid record exists, add to db even if assetid = 0.
- 0.8.151 - Documentation Update - Added diagram of QualysETL System building Data Warehouse -   QualysETL: Unified Data Integration and Distribution for Comprehensive Security Insights
- 0.8.150 - Snowflake Users - Please update your snowflake-loader/.import_schema_data_type_changes_snowflake.txt file to include new fields: q_asset_inventory.softwarecomponent.VARIANT q_was_webapp.urlexcludelist.VARIANT q_was_webapp.urlincludelist.VARIANT q_was_webapp.postdataexcludelist.VARIANT
- 0.8.150 - Minor update to report edge case where user had not completed first time Qualys registration prior to executing API calls.
- 0.8.150 - Minor update to logging for pcrs.log to display final totals at end of logging, while interim counters are label "Updated" instead of "Total"
- 0.8.150 - Knowledgebase Update to include new field <CODE_MODIFIED_DATETIME> - https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-10.25-api-release-notes.pdf
- 0.8.150 - Additional Knowledgebase optional fields added: DETECTION_INFO, LAST_CUSTOMIZATION, DIAGNOSIS_COMMENT, CONSEQUENCE_COMMENT, SOLUTION_COMMENT, COMPLIANCE_LIST, AUTOMATIC_PCI_FAIL, TECHNOLOGY
- 0.8.150 - GAV/CSAM Asset Inventory update to include new field "softwareComponent" - https://cdn2.qualys.com/docs/release-notes/qualys-gav-csam-2.16.1-api-release-notes.pdf
- 0.8.150 - Host List Detection update to include new field <LINUX_HOSTNAME> - add option to etld_config_settings.yaml host_list_detection_payload_option - Contact TAM to enable this option and request meeting with David Gregory for Q/A - https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-10.26-api-release-notes.pdf
- 0.8.150 - WAS Update to include new field <detectionScore>, <riskScore> - https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-3.17.1-api-release-notes.pdf
- 0.8.150 - Host List Detection update to include new field <NETWORK_NAME> - https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-10.27-api-release-notes.pdf
- 0.8.150 - WAS Update to include two fields that will replace urlWhitelist with urlAllowlist, urlBlacklist with urlExcludelist, postDataBlacklist with postDataExcludelist.  Also added new field <fixedDate>. - https://cdn2.qualys.com/docs/release-notes/qualys-cloud-platform-3.18-api-release-notes.pdf
- 0.8.130 - Minor update to fix problem relocating opt/qetl directory upon first installation.
- 0.8.126 - Minor update to enhance logging.


## Table of contents
* [Examples of Usage](#examples-of-usage)
* [Quick Start](#quick-start)
   * [Install](#installation)
   * [Uninstall](#uninstall)
* [Qualys API Best Practices Series](#qualys-api-best-practices-series)
   * [Workflow Diagram](#workflow-diagram)
   * [Component Diagram](#component-diagram)
   * [Blueprint](#blueprint)
   * [Roadmap](#roadmap)
   * [Technologies](#technologies) 
* [ETL Examples](#etl-examples)
    * [ETL KnowledgeBase](#etl-knowledgebase)
    * [ETL Host List](#etl-host-list)
    * [ETL Host List Detection](#etl-host-list-detection)
    * [ETL Asset Inventory](#etl-asset-inventory)
    * [ETL Web_Application_Scanning](#etl-web-application-scanning-data)
    * [ETL Policy Compliance PCRS](#etl-policy-compliance-pcrs)
* [Application Manager and Data](#application-manager-and-data)
    * [Host List Detection SQLite Database](#host-list-detection-sqlite-database)
    * [Data Formats](#data-formats)
    * [Logging](#logging)
    * [Application Monitoring](#application-monitoring)
* [Securing Your Application in the Data Center](#securing-your-application-in-the-data-center)
    * [Password Vault](#password-vault)
* [Example Run Logs](#example-run-logs)
    * [Uninstall and Install qetl](#uninstall-and-install-qetl)
    * [qetl_setup_python_env](#qetl_setup_python_env)  
    * [qetl_manage_user](#qetl_manage_user)
    * [qetl_manage_user Add User](#qetl_manage_user-add-user)
    * [qetl_manage_user ETL KnowledgeBase](#qetl_manage_user-etl-knowledgebase)
    * [Review ETL KnowledgeBase Data](#review-etl-knowledgebase-data)
* [License](#license)
* [ChangeLog](#changelog)
* [Release Notes Log](#release-notes-log)

# Usage Information - qetl_manage_user
<a name="examples"></a>

## 1) Help Screen.
```text
    
    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,
    Example:
             qetl_manage_user -u /opt/qetl/users/[your_user_name]
 
    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_pcrs
                                       -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_pcrs
                                       -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"}
      
    Example: ETL Host List Detection
    
    qetl_manage_user -u [path] -e etl_host_list_detection -d 1970-01-01T00:00:00Z
    
     - qetl_manage_user will download all knowledgebase, host list and host list detection vulnerability data,
       transforming/loading it into sqlite and optionally the corresponding distribution directory.
     
     Inputs: 
       - KnowledgeBase API, Host List API, Host List Detection API.
       - ETL KnowledgeBase
         - /api/2.0/fo/knowledge_base/vuln/?action=list
       - ETL Host List
         - /api/2.0/fo/asset/host/?action=list
       - ETL Host List Detection - Stream of batches immediately ready for downstream database ingestion.
         - /api/2.0/fo/asset/host/vm/detection/?action=list
     Outputs:
       - XML, JSON, SQLITE, AND Distribution_Directory of CSV BATCH FILES PREPARED FOR DATABASE INGESTION.
         - host_list_detection_extract_dir - contains native xml and json transform of data from qualys, compressed in uniquely named batches.
         - host_list_detection_distribution_dir - contains transformed/prepared data ready for use in database loaders such as mysql.
         - host_list_detection_sqlite.db - sqlite database will contain multiple tables:
           - Q_Host_List                            - Host List Asset Data from Host List API.
           - Q_Host_List_Detection_Hosts            - Host List Asset Data from Host List Detection API. 
           - Q_Host_List_Detection_QIDS             - Host List Vulnerability Data from Host List Detection API. 
           - Q_KnowledgeBase_In_Host_List_Detection - KnowledgeBase QIDs found in Q_Host_List_Detection_QIDS. 
         
   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) ETL Host List Detection

```text
    qetl_manage_user -u [path] -e etl_host_list_detection -d 1970-01-01T00:00:00Z
    
     - qetl_manage_user will download all knowledgebase, host list and host list detection vulnerability data,
       transforming/loading it into sqlite and optionally the corresponding distribution directory.
     
     Inputs: 
       - KnowledgeBase API, Host List API, Host List Detection API.
       - ETL KnowledgeBase
         - /api/2.0/fo/knowledge_base/vuln/?action=list
       - ETL Host List
         - /api/2.0/fo/asset/host/?action=list
       - ETL Host List Detection - Stream of batches immediately ready for downstream database ingestion.
         - /api/2.0/fo/asset/host/vm/detection/?action=list
     Outputs:
       - XML, JSON, SQLITE, AND Distribution_Directory of CSV BATCH FILES PREPARED FOR DATABASE INGESTION.
         - host_list_detection_extract_dir - contains native xml and json transform of data from qualys, compressed in uniquely named batches.
         - host_list_detection_distribution_dir - contains transformed/prepared data ready for use in database loaders such as mysql.
         - host_list_detection_sqlite.db - sqlite database will contain multiple tables:
           - Q_Host_List                            - Host List Asset Data from Host List API.
           - Q_Host_List_Detection_Hosts            - Host List Asset Data from Host List Detection API. 
           - Q_Host_List_Detection_QIDS             - Host List Vulnerability Data from Host List Detection API. 
           - Q_KnowledgeBase_In_Host_List_Detection - KnowledgeBase QIDs found in Q_Host_List_Detection_QIDS. 

```
### Schema etl_host_list_detection - Extract from host_list_detection_sqlite.db file.

[![](https://github.com/user-attachments/assets/ef74979b-4e18-4dff-84e7-b6844657b1f3)](https://github.com/user-attachments/assets/ef74979b-4e18-4dff-84e7-b6844657b1f3)

## 3) ETL Asset Inventory (GAV/CSAM API)

```text
    qetl_manage_user -u [path] -e etl_asset_inventory -d 1970-01-01T00:00:00Z

     - qetl_manage_user will download all asset inventory data, transforming/loading them into sqlite.
         
     Inputs: 
       - Global Asset View/CyberSecurity Asset Management API V2.
       - ETL Asset Inventory - Stream of batches immediately ready for downstream database ingestion.
         - /rest/2.0/search/am/asset?assetLastUpdated=[date]
     Outputs:
       - JSON, SQLITE, AND Distribution_Directory of CSV BATCH FILES PREPARED FOR DATABASE INGESTION.
         - asset_inventory_extract_dir - contains json of data from qualys, compressed in uniquely named batches.
         - asset_inventory_distribution_dir - contains transformed/prepared data ready for use in database loaders such as mysql.
         - asset_inventory_sqlite.db - sqlite database will contain multiple tables:  
           *  Q_Asset_Inventory                  - Asset Inventory of Asset Last Updated -d 'DATE' to now 
           *  Q_Asset_Inventory_Software_Unique  - Unique List of Software
           *  Q_Asset_Inventory_Software_AssetId - Unique List of AssetId to Software 
    
```
### Schema etl_asset_inventory - Extract from asset_inventory_sqlite.db file.

[![](https://github.com/user-attachments/assets/7c48759f-71e5-4bcb-a9a1-7ccd2c139448)](https://github.com/user-attachments/assets/7c48759f-71e5-4bcb-a9a1-7ccd2c139448)

## 4) ETL Web Application Data (WAS API)

```text
    Example: 
    
    qetl_manage_user -u [path] -e etl_was -d 1970-01-01T00:00:00Z
    
     Inputs:
       - Web Application Scanning API
         - /qps/rest/3.0/search/was/catalog
         - /qps/rest/3.0/search/was/webapp
         - /qps/rest/3.0/get/was/webapp/<id>
         - /qps/rest/3.0/search/was/finding
     Outputs:
       - was_extract_dir - contains json of data from qualys, compressed in uniquely named batches.
       - was_distribution_dir - contains transformed/prepared data ready for use in database loaders such as mysql.
       - was_sqlite.db - sqlite database will contain multiple tables:  
         *  Q_WAS_WebApp   - Web Applications and Web Application Details
         *  Q_WAS_Finding  - Web Application Findings (Vulnerabilities)
         *  Q_WAS_Catalog  - WAS Module Catalog 
```
### Schema etl_was - Extract from was_sqlite.db file.

[![](https://github.com/user-attachments/assets/46bf6df9-60fd-4b6e-a6ca-602964fd74be)](https://github.com/user-attachments/assets/46bf6df9-60fd-4b6e-a6ca-602964fd74be)

## 5) ETL Policy Compliance through PCRS

```text
    Example: 
    
    qetl_manage_user -u [path] -e etl_pcrs -d 1970-01-01T00:00:00Z ( Last Evaluated Date for Policy ).
   
     Inputs:
       - PCRS API
         - /pcrs/1.0/posture/policy/list 
         - /pcrs/1.0/posture/hostids
         - /pcrs/1.0/posture/postureInfo
     Outputs:
       - pcrs_extract_dir       - contains json of data from qualys, compressed in uniquely named batches.
       - pcrs_distribution_dir  - contains transformed/prepared data ready for use in database loaders such as mysql.
       - pcrs_sqlite.db         - sqlite database will contain multiple tables:  
         *  Q_PCRS_POLICY_LIST  - List of policies list with lastEvaluationDate=1970-01-01T00:00:00Z
         *  Q_PCRS_HOSTIDS      - List of hostids associated with each Active policy.
         *  Q_PCRS_POSTUREINFO  - Posture Information for each Host.
      
     Configuration:
       - Default:
         * Running qetl_manage_user -u [userpath] -e etl_pcrs -d [evaluation date] will result in 
           all data on or after evaluation date being pulled for all assets scanned 1 hour before evaluation date.  
         
       - /pcrs/1.0/posture/policy/list
         * qetl_manage_user -d [evaluation date]
         * etld_config_settings.yaml example:
            - pcrs_policy_list_payload_option: {'lastEvaluationDate': '2023-09-04T00:00:00Z'}
            ** See VM/PC API guide for details of parameters: https://www.qualys.com/docs/qualys-api-vmpc-user-guide.pdf
                   
       - /pcrs/1.0/posture/hostids
         * qetl_manage_user -d [evaluation date] will result in lastEvaluationDate minus 1 hour = lastScanDate  
           unless overridden via etld_config_settings.yaml
         * etld_config_settings.yaml example:
            - pcrs_hostids_payload_option: {'lastScanDate': '2023-09-04T00:00:00Z'}
            ** See VM/PC API guide for details of parameters: https://www.qualys.com/docs/qualys-api-vmpc-user-guide.pdf
            
       - /pcrs/1.0/posture/postureInfo
         * qetl_manage_user -d [evaluation date] will result in lastEvaluationDate minus 1 hour = lastScanDate  
           unless overridden via etld_config_settings.yaml
         * etld_config_settings.yaml examples:
            - pcrs_postureinfo_payload_option: {'lastScanDateFrom': '2023-09-10T00:00:00Z', 'lastScanDateTo': '2023-09-17T00:00:00Z', 'evidenceRequired': '0', 'statusChangedSince': '2023-09-02T00:00:00Z'}
            - pcrs_postureinfo_payload_option: {'evidenceRequired': '1'}
            - pcrs_postureinfo_payload_option: {'statusChangedSince': '2023-09-01T00:00:00Z'}
            ** See VM/PC API guide for details of parameters: https://www.qualys.com/docs/qualys-api-vmpc-user-guide.pdf
            ** Optional normalized schema for PCRS, reducing space used by PostureInfo by ~50%.
            - pcrs_postureinfo_schema_normalization_flag: True 


       - etld_config_settings.yaml include/exclude policy examples:
         * Include only the following policy id's 
           pcrs_policy_id_include_list: ['123456', '334835'] 
         * Exclude the following policy ids.
           pcrs_policy_id_exclude_list: ['880900'] 
            

             
```
### Normalized Schema etl_pcrs - Extract from pcrs_sqlite.db file. 
- With etld_config_settins.yaml, pcrs_postureinfo_schema_normalization_flag: True the following optimized schema will be produced by -e etl_pcrs

[![](https://github.com/user-attachments/assets/a6d15277-4fc6-4a80-8508-ceab347edf