Qualys API Best Practices Series - ETL Blueprint Example Code within Python Virtual Environment
Project description
Qualys API Best Practices Series
Explore the Qualys 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 provided in this guide.
Schema - QualysETL Vulnerability Data Example.
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:
-
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.
- Qualys REST API:
-
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).
- Customer Operations:
-
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 Warehouse Options:
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.
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
-
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.
-
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.
-
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.
-
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 22.04 system, ensuring a smooth and swift start.
-
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:
- KnowledgeBase - QID Definitions
- Host List - Host Information
- Host List Detection - Host Vulnerability Information
- CyberSecurity Asset Inventory - Detailed Asset Inventory
- Web Application Scanning - Web Applications and Web Application Vulnerability Findings.
- 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 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.
# 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
Note: See the end of this document for history of release notes
- 0.9.2 - Added new section above "Qualys API Versioning included in QualysETL". Added additional documentation on what to change for API Versioning.
# 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 , - 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 . - 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
- Quick Start
- Qualys API Best Practices Series
- ETL Examples
- Application Manager and Data
- Securing Your Application in the Data Center
- Example Run Logs
- License
- ChangeLog
- Release Notes Log
Usage Information - qetl_manage_user
1) Help Screen.
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
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.
3) ETL Asset Inventory (GAV/CSAM API)
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.
4) ETL Web Application Data (WAS API)
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.
5) ETL Policy Compliance through PCRS
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
Quick Start
- Ubuntu 22.04 LTS latest is the primary OS to run QualysETL.
- Red Hat 9.x latest is an alternative OS that has been tested.
- Amazon Linux 2023 latest is an alternative OS that has been tested.
Contact your TAM and David Gregory for details.
Prerequisites Python Module on Ubuntu 22.04
1) Ubuntu 22.04 LTS
2) Python 3.8.5 or Latest Stable Release
3) On base 22.04 you'll need two additional packages.
sudo apt-get install python3-venv
sudo apt install python3-pip
4) Disk Space on Host.
- 100,000 hosts, expect ~400 Gigabytes for full copy of VM Data (Confirmed, Potential, Info Gathered)
- KnowledgeBase - expect ~1 Gigabyte.
- Host List - expect ~10 Gigabyte for 100K Hosts.
- Host List Detection - expect ~300-400 Gigabytes for 100K Hosts.
Installation
First Time Setup Activity on Ubuntu 22.04
- Login as "non-root" user that will run qualysetl.
- sudo root authorization required.
- Create your /opt/qetl application directory
- update apt package cache
- Install python3-venv
- Install python3-pip
- Install sqlite3
- Install sqlite3 sql browser
First Time Setup Instructions on Ubuntu 22.04
#!/usr/bin/env bash
# First Time Setup - Pre-create directory /opt/qetl
# Login as user that will execute qetl_manage_user
sudo mkdir /opt/qetl
sudo chown $USER:$USER /opt/qetl # Note: If special group, update $USER:[your group] here before executing.
sudo apt update
sudo apt install -y python3-venv python3-pip sqlite3 sqlitebrowser
Alternative First Time Setup Activity on Red Hat 9.x
- Login as "non-root" user that will run qualysetl.
- sudo root authorization required.
- Create your /opt/qetl application directory
- Install python3.9, python3-pip and sqlite
- Update alternatives to point python3 at python3.9
Alternative First Time Setup Instructions on Red Hat 8.x or Red Hat 9.x
- NOTE: On Red Hat 8 or 9, check to see if your distribution already has python3 version 3.9 or greater. "Ex. python3 --version". If so, then you can skip reinstalling python and setting alternatives.
#!/usr/bin/env bash
# First Time Setup - Pre-create directory /opt/qetl
# Login as user that will execute qetl_manage_user
sudo mkdir /opt/qetl
sudo chown $USER:$USER /opt/qetl
sudo yum -y install python39
sudo alternatives --set python3 /usr/bin/python3.9
sudo yum -y install python3-pip
sudo yum -y install sqlite
Install or Upgrade QualysETL activity on Ubuntu or Red Hat
- Login as "non-root" user that will run qualysetl.
- deactivate to exit any current python virtual environment you may be in.
- Install/Upgrade qualysetl into your
/home/$USER/.localpython directory - Create qualysetl python virtual environment in /opt/qetl/qetl_venv, installing all required modules in venv
- Execute qualysetl to see help screen
Install or Upgrade QualysETL Instructions on Ubuntu or Red Hat
#!/usr/bin/env bash
# Login as user that will execute qetl_manage_user
# Install Application in Python Virtual Environment /opt/qetl/qetl_venv
# Exit if in a Python virtual environment
[ -n "$VIRTUAL_ENV" ] && { echo "Please deactivate the virtual environment and rerun this script."; exit 1; }
python3 -m pip install --upgrade qualysetl
~/.local/bin/qetl_setup_python_venv /opt/qetl
echo "Follow instructions output from qetl_setup_python_venv"
Create your first qualysetl user
To setup your first user, you'll need your qualys api username, password and your api fqdn.
Example transcript of setting up a new user
qualysetl@ubuntu:~$ source /opt/qetl/qetl_venv/bin/activate
(qetl_venv) qualysetl@ubuntu:~$ qetl_manage_user -u /opt/qetl/users/quays_dt4
qetl_user_home_dir does not exist: /opt/qetl/users/quays_dt4/qetl_home
Create new qetl_user_home_dir? /opt/qetl/users/quays_dt4/qetl_home ( yes or no ): yes
qetl_user_home_dir created: /opt/qetl/users/quays_dt4/qetl_home
Current username: initialuser in config: /opt/qetl/users/quays_dt4/qetl_home/cred/.etld_cred.yaml
Update Qualys username? ( yes or no ): yes
Enter new Qualys username: quays_dt4
Current api_fqdn_server: qualysapi.qualys.com
Update api_fqdn_server? ( yes or no ):
Enter new api_fqdn_server: qualysapi.qualys.com
Update password for username: quays_dt4
Update password? ( yes or no ): yes
Enter your Qualys password:
You have updated your credentials.
Qualys Username: quays_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 quays_dt4 at api_fqdn_server: qualysapi.qualys.com
Testing Qualys Login for quays_dt4 Succeeded at qualysapi.qualys.com
with HTTPS Return Code: 200.
Thank you, exiting.
(qetl_venv) qualysetl@ubuntu:~$
Execute your first ETL.
Your initial configuration limits the total hosts downloaded to 1000 hosts vm_processed_after utc.now - 1 day. The initial configuration will only consume up to 2 connections. You can test this to ensure you are able to download data before moving on to more data.
- Command - qetl_manage_user -u /opt/qetl/users/quays_dt4 -e etl_host_list_detection
- Ouputs:
- Full Knowledgebase on first run.
- Host List vm_processed_after utc.now - 1 day limited to 1000 hosts for testing.
- Host List Detection driven by scope of Host List.
Transcript of command execution.
qetl_manage_user -u /opt/qetl/users/quays_dt4 -e etl_host_list_detection
Starting etl_host_list_detection. For progress see: /opt/qetl/users/quays_dt4/qetl_home/log/host_list_detection.log
Ending etl_host_list_detection. For results see: /opt/qetl/users/quays_dt4/qetl_home/log/host_list_detection.log
sqlitebrowser /opt/qetl/users/quays_dt4/qetl_home/data/host_list_detection_sqlite.db
SQLite Browser displaying Knowledgebase, Host List, and Host List Detection. Note that the knowledgebase in this database only includes qids found in host list detection. To see the full knowledgebase, open kb_sqlite.db. Q_Host_List_Detection is a view of Q_Host_List ( PREFIX HL_ ), Q_Host_List_Detection_Hosts ( PREFIX HLDH_ ), Q_Host_List_Detectino_QIDS ( PREFIX HLDQ_ ).
Uninstall
Uninstall qualysetl activity on Ubuntu 22.04.
- deactivate to exit any current python virtual environment you may be in.
- optionally remove application/data:
- python virtual environment: /opt/qetl/qetl_venv
- qualysetl data directory: /opt/qetl/users
- python3-venv
- python3-pip
- sqlite3
- sqlitebroswer
#!/usr/bin/env bash
deactivate # If you are in a python virtual environment
python3 -m pip uninstall qualysetl
# Optionally remove python virtual env, pip, sqlite3, sqlitebrowser and users application data.
# cd /opt/qetl/
# rm -ir qetl_venv # Optionally remove qetl_venv
# rm -ir users # Optionally remove users directory with data
# sudo apt remove -y python3-venv python3-pip sqlite3 sqlitebrowser
Uninstall qualysetl activity on Red Hat 8.x
- deactivate to exit any current python virtual environment you may be in.
- optionally remove application/data:
- python virtual environment: /opt/qetl/qetl_venv
- qualysetl data directory: /opt/qetl/users
#!/usr/bin/env bash
deactivate # If you are in a python virtual environment
python3 -m pip uninstall qualysetl
# Optionally remove python virtual env and user data
# cd /opt/qetl/
# rm -ir qetl_venv # Optionally remove qetl_venv
# rm -ir users # Optionally remove users directory with data
- Jump to ETL Examples to transform Qualys data into CSV, JSON and SQLite Databases.
Qualys API Best Practices Series
The example code from the Qualys API Best Practices Series is being hosted here to help customers with an example blueprint to automate transformation of data into their corporate data systems, further enhancing the visibility of outlier systems that are vulnerable.
This example code has been enhanced with some exception processing, logging, and a single point of execution creating an operational context within which to test/develop the code so customers can build automation into their remediation program.
Workflow Diagram
The workflow depicts the flow of etl for host list detection. The key output is the sqlite database that is ready for distribution
- qetl_manage_user -u [userdir] -e etl_host_list_detection -d [datetime] - Resulting sqlite database ready for distribution.
Component Diagram
The component diagram depicts major system interoperability components that deliver data into the enterprise.
| Component | Color | Purpose |
|---|---|---|
| Execution Environment | Blue | Host and Cloud where this application operates |
| Application | Grey | Application context to identify Local Docker, Python Application, Host and/or Filesystems |
| Input | Orange | Qualys data consumed by application |
| Execution | Green | Execution ETL of Qualys data through various methods. (The Python Execution Environment on Docker or Traditional Host) |
| Data | Yellow | Host Data Folders that separate Application, and Subscription Data Users along with distribution pipelines representing the distribution of data to external sources, Cloud, Client, Other |
| Future | Black | TBD Future State Components such as GraphQL Server. |
Blueprint
Customer have many options for Qualys API integration today. Some customers realize they need to develop their own internal code to transform complex data, create custom metrics, create custom reports or ensure data is more accessible within their organizations for metrics and custom reporting.
As a result, Qualys decided on creating the API Best Practices Series to jumpstart clients with a blueprint of example code to help them automate delivery of complex data into their enterprise.
The overarching goal is to simplify our customers security stack and help them significantly reduce cost and complexity.
Key Goals and Solutions of this series are:
| Goal | Solution |
|---|---|
| Automate Vulnerability Data accessibility, transformation of complex data for analysis | JSON, CSV, SQLite Database Formats of Qualys data readily accessible to Analytical BI Tools for on-demand analysis or for downstream loading into Enterprise Data Storage. |
| A single query interface to Qualys data | TBD Future GraphQL Server interface to data. |
| Automate Capturing Vulnerability Data into corporate processes | Blueprint of example code customers can customize to enhance their internal automation "API-First" strategy. |
| Automate Distribution of Vulnerability Data to Cloud Providers | Optional Distribution methods into cloud systems such as Amazon S3 Bucket |
| Automate Application Enhancements and Delivery | Docker application instance for reliable CI/CD delivery of enhancements, as well as traditional host execution on Linux Platforms. |
| Provide Execution Flexibility, Work Load Management, Password Security | Blueprint for enterprise jobstream execution (Ex. Autosys), password vaults (Ex. Hashicorp), or simple command line execution from a Virtual Machine instance of Ubuntu running on a laptop. |
| Provide Continous Vulnerability Data Pipeline | Blueprint for data transformation pipeline from Qualys to Enterprise Data Stores in various formats ( JSON, CSV, SQLite Database ) |
Roadmap
Capability | Target | Description
---------- | ------ | -----------
KnowledgeBase | June 2021 | Automate download and transform of KnowledgeBase into CSV, JSON and SQLite Database
Host List | June 2021 | Automate download and transform of Host List into CSV, JSON and SQLite Database
Host List Detection | June 2021 | Automate download and transform of Host List Detection into CSV, JSON and SQLite Database
Python Virtual 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.
Database Injection | Aug 2023 | Methods to inject schema/data from QualysETL into your downstream databases. Ex. Azure Cosmos DB (PostgreSQL), Amazon RedShift, PostgreSQL Open Source, MySql Open Source, SnowFlake, Microsoft SQL Server. Contact your Qualys TAM to schedule a call with David Gregory if you wish to use this feature.
Visualization Use Case | Aug 2023 | Use QualysETL to build your downstream databases for use with PowerBI, Tableau, Etc. Contact your Qualys TAM to schedule a call with David Gregory if you wish to use this feature.
QWEB 10.23 Updates | Aug 2023 | Delivered additional fields for Host List and Host List Detection. For details see: See [QWEB 10.23 release notification for details](https://www.qualys.com/docs/release-notes/qualys-cloud-platform-10.23-api-release-notes.pdf)
Web Application Scanning(WAS) | Aug 2023 | Updated timing in WAS for long running jobs.
Docker Image | Aug 2023 | Contact your TAM to schedule a call with David Gregory. Encapsulate Python Application into distributable docker image for ease os operation and upgrade.
Policy Compliance | Oct 2023 | PCRS Delivered (multi-threaded). Automate download and transform of Policies, Hosts and Posture Information for your hosts.
WAS Blog | Oct 2023 | Blog for WAS Module.
Policy Compliance Blog | Oct 2023 | Blog for Policy Compliance Module.
All Modules | May 2024 | Multiple new field updates across Host List, Host List Detection, CSAM and WAS. See change log for details.
API Versioning | Nov 2024 | Added API Versioning to support for QWEB Release 10.30 along with new fields supported by new API Versions. See 0.9.1 release notes for details.
Container Security | Feb 2025 | Container Security Image and Container Vulnerability Data.
FIM | Mar 2025 | File Integrity Monitoring
Other Modules | 2025 | TBD
Technologies
Project tested with:
- Ubuntu version: 22.04
- Redhat version: 8.x/9.x latest
- SQLite3 version: 3.31.1
- Python version: 3.8.5
- Qualys API: latest
Examples of Usage
- 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 with your TAM for Questions.
- host_list_detection_concurrency_limit: 2
(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
#
# 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.
# Turn HTTPS TLS Verify On(True) or Off(False). Useful when behind proxy with self served certificate for testing.
requests_module_tls_verify_status: True
# Provide time out for pcrs api calls
pcrs_http_conn_timeout: 3600
# Provide methods to clean non-utf8 data. Set to True if you utf8 parsing error in log files.
xmltodict_parse_using_codec_to_replace_utf8_error: False
# API Version Updates included in >= 0.9.1.
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'
# Include etl_kb CSV in output.
kb_distribution_csv_flag: True
# Include etl_host_list_detection CSV in output.
host_list_distribution_csv_flag: True
# API Concurrency for /api/[verion]/fo/asset/host/vm/detection
# See [Qualys Limits Guide] https://www.qualys.com/docs/qualys-api-limits.pdf with your TAM for Questions.
host_list_detection_concurrency_limit: 2
# Number of hosts per concurrent connection. Leave at 500 unless otherwise advised by Qualys to update.
host_list_detection_multi_proc_batch_size: 500
# Include etl_host_list_detection CSV in output.
host_list_detection_distribution_csv_flag: True
# Include etl_asset_inventory CSV in output.
asset_inventory_distribution_csv_flag: True
# Include etl_was CSV in output.
was_distribution_csv_flag: True
# Exclude TruRisk, used by consultant subscriptions.
host_list_payload_option_exclude_trurisk: False
ETL Examples
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.
qetl_manage_user -u /opt/qetl/users/quser -e etl_knowledgebase -d 1970-01-01T00:00:00Z
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.
qetl_manage_user -u /opt/qetl/users/quser -e etl_host_list -d [YYYY-MM-DDThh:mm:ssZ]
See Application Manager and Data for location of your qetl_home directory.
ETL Host List Detection
Host List Detection ETL - Includes KnowledgeBase and Host List 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.
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.
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.
qetl_manage_user -u /opt/qetl/users/quser -e etl_was -d [YYYY-MM-DDThh:mm:ssZ]
ETL Policy Compliance PCRS
Policy Compliance (PCRS API) ETL - Includes Policy, Host, and Posture Information for your host assets.
qetl_manage_user -u /opt/qetl/users/quser -e etl_pcrs -d [YYYY-MM-DDThh:mm:ssZ]
Application Manager and Data
qetl_manage_user application
- qetl_manage_user is your entry point to manage ETL of Qualys data.
Host List Detection SQLite Database
- qetl_manage_user -u [userdir] -e etl_host_list_detection -d [datetime] - Resulting sqlite database ready for distribution.
Environment
- Python virtual environment
- Managed by qetl_manage_user
- Example options for qetl Home Directories:
- Prod: /opt/qetl/users/[user_name]/qetl_home
- Test: /usr/local/test/opt/qetl/users/[user_name]/qetl_home
- Dev: $HOME/opt/qetl/users/[user_name]/qetl_home
Application Directories
| Path | Description |
|---|---|
| opt/qetl/users/ | Directory of All Users |
| opt/qetl/users/[user]/qetl_home | Parent directory path for a user |
| [user]/qetl_home | User Home Directory |
| qetl_home/bin | User bin directory for customer to host scripts they create. |
| qetl_home/cred | Credentials Directory |
| qetl_home/cred/.etld_lib_credentials.yaml | Credentials file in yaml format. |
| qetl_home/cred/.qualys_cookie | Cookie file used for Qualys session management. |
| qetl_home/config | Application Options Configuration Directory |
| qetl_home/config/etld_lib_config_settings.yaml | Application Options |
| qetl_home/log | Logs - Directory 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/log/pcrs.log | LOG - PCRS API 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/pcrs_sqlite.db | Database - PCRS 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/pcrs_extract_dir | Extract - PCRS 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 |
| qetl_home/data/pcrs_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 useful for transfer of data between systems |
| CSV | Comma Separated Values useful for transfer of data between systems Formatted to help import data into various BI or Database Tools: Excel, Apache Open Office, Libre Office, Tableau, Microsoft PowerBI, SQL Database Loader |
| XML | Extensible Markup Language useful for transfer of data between systems |
| SQLite Database | SQLite Database: SQLite Database populated with Qualys data, Useful as a self-contained SQL Database of Qualys data for Analysis, Useful as an intermediary transformation into your overall Enterprise ETL Process, SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine |
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 for details of each log file.
cd qetl_home/log
head -3 kb.log
(qetl_venv) qualysetl@ubuntu:/opt/qetl/qetl_venv/bin$ cat /opt/qetl/users/qualys_user/qetl_home/log/kb.log | nl
1 2021-05-28 01:26:03,836 | INFO | etl_knowledgebase: 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:
- -p, --prompt-credentials prompt user for credentials, also accepts stdin with credentials piped to program.
- -m, --memory-credentials get credentials from environment: q_username, q_password, q_api_fqdn_server
- -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.
Example Run Logs
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
(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 Run Log
- Make sure you are not in your Python Virtual Environment when installing this software.
Notice the command prompt does not include (qetl_env).
(qetl_env) qualysetl@ubuntu:~$ deactivate
qualysetl@ubuntu:~$ python3 -m pip install qualysetl
Collecting qualysetl
Downloading qualysetl-0.6.30-py3-none-any.whl (79 kB)
|████████████████████████████████| 79 kB 1.8 MB/s
Installing collected packages: qualysetl
Successfully installed qualysetl-0.6.30
qualysetl@ubuntu:~$
qetl_setup_python_env Run Log
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.
(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_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"}
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.
qetl_manage_user Add User
To add a new user, execute qetl_manage_user -u [opt/users/your_new_user]. See example run log below.
qualysetl@ubuntu:~$ source /opt/qetl/qetl_venv/bin/activate
(qetl_venv) qualysetl@ubuntu:~$ qetl_manage_user
Please enter -u [ your /opt/qetl/users/ user home directory path ]
Note: /opt/qetl/users/newuser is the root directory for your qetl userhome directory,
enter a new path including the opt/qetl/users/newuser
in the path you have authorization to write to.
the prefix to your user directory opt/qetl/users is required.
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_was, etl_pcrs, 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
(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
(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
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.
Version | Date of Change | Description of Changes
------- | -------------- | ----------------------
0.6.98 | 2021-08-06 10:00 ET | minor update to order of python virtual env package install. Install Script: qetl_setup_python_venv
0.6.99 | 2021-08-06 11:30 ET | minor update, added module chardet.
0.6.100 | 2021-08-10 12:00 ET | minor documentation update.
0.6.101 | 2021-08-11 12:00 ET | minor update to asset_inventory gateway selection.
0.6.102 | 2021-08-13 12:00 ET | minor update to documentation.
0.6.103 | 2021-08-26 18:00 ET | minor update to allow host list detection to continue to run for up to 1 day.
0.6.104 | 2021-08-27 18:00 ET | update to address encoding error in complex data.
0.6.105 | 2021-09-09 12:00 ET | updated roadmap, and updated retry after receiving 409 (concurrency) or 202 (duplicate operation), sleep 2 min and retry.
0.6.106 | 2021-09-29 12:00 ET | Minor update to allow sqlite 3.26.
0.6.107 | 2021-09-29 20:00 ET | Minor update to adding ability to show tags in Host List. If host_list_show_tags: '1' is added to etld_config_settings.yaml, then the host list will include qualys tags.
0.6.108 | 2021-10-01 20:00 ET | Updated documentation to include Red Hat 8.4 instructions.
0.6.109 | 2021-10-02 12:00 ET | Updated documentation to include Asset Inventory (GAV/CSAM V2) API.
0.6.112 | 2021-11-07 12:00 ET | Updated etl_asset_inventory to include new fields: criticality, businessInformation, assignedLocation, businessAppListData. Updated retry and program max run time sanity checks. Updated Asset Inventory Logging to include count of assets prior to executing download. Updated Host List to include cloud meta data.
0.6.113 | 2021-11-16 12:00 ET | Updated file change sanity check to 20 min of inactivity.
0.6.117 | 2021-11-18 06:00 ET | Updated http wait time from 30 sec to 5 min. Added counters to asset inventory csv logging. Reverse Sort to add newest assets to shelve in asset_inventory_shelve without overwriting dups.
0.6.118 | 2021-11-18 06:00 ET | Updated performance reading shelve database in asset inventory process. Tested 1.5 Million hosts successfully.
0.6.119 | 2021-11-24 06:00 ET | Updated asset inventory features to include presenting JSON in csv cells instead of indexed list, as well as feature to not truncate data. To enable these feature, edit your etld_config_settings.yaml to include 'asset_inventory_present_csv_cell_as_json: True' and edit your etld_config_settings.yaml to include: asset_inventory_csv_truncate_cell_limit: False
0.6.123 | 2021-12-01 19:00 ET | Part 1) Updated asset inventory features to include tables Q_Asset_Inventory_Software_Assetid (Asset ID to Software) and Q_Asset_Inventory_Software_Unique ( unique list of software and lifecycle info found in asset inventory ). These tables are useful to create views of unique software, unique software -> server.
0.6.123 | 2021-12-01 19:00 ET | Part 2) Updated qetl_manage_user credential handling to support -p prompt for cred, -s accept json creds from stdin, -m accept creds exported to environment.
0.6.124 | 2021-12-02 09:00 ET | Minor update to improve exception handling in extract
0.6.126 | 2021-12-04 19:00 ET | Minor update to help and version options for qetl_manage_user
0.6.130 | 2021-12-09 12:00 ET | Part 1) Added feature present JSON in csv cells to etl_knowledgebase, etl_host_list, etl_host_list_detection, etl_asset_inventory. To enable these feature, edit your etld_config_settings.yaml to include 'kb_present_csv_cell_as_json: True', 'host_list_present_csv_cell_as_json: True' 'host_list_detection_present_csv_cell_as_json: True', 'asset_inventory_present_csv_cell_as_json: True'
0.6.130 | 2021-12-09 12:00 ET | Part 2) Added feature "no truncation" if truncate cell limit is 0 in etl_knowledgebase, etl_host_list, etl_host_list_detection, etl_asset_inventory. To enable this feature, edit your etld_config_settings.yaml to update: 'asset_inventory_csv_truncate_cell_limit: 0' 'kb_csv_truncate_cell_limit: 0' 'host_list_csv_truncate_cell_limit: 0' 'host_list_detection_csv_truncate_cell_limit: 0'.
0.6.130 | 2021-12-09 12:00 ET | Part 3) Added feature to allow customers in development to set Python3 requests verify=False. This setting is not recommended as it can result in a man-in-the-middle (MitM) attack. To enable Python3 requests verify=False, edit your etld_config_settings.yaml and add the setting 'requests_module_tls_verify_status: False'. Default is True. When set to False logging will include warning messages about insecurity of the setting. We recommend repairing certificate chain instead of setting this option to False. Defaults to True, requiring requests to verify the TLS certificate at the remote end. If verify is set to False, 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. Only set this to False for testing.
0.6.130 | 2021-12-09 12:00 ET | Part 4) Minor updates to improve progress counters in logging, minor update to asset inventory logging to include batch number.
0.6.131 | 2021-12-09 13:00 ET | Minor updates to formatting of ReadMe.
0.7.6 | 2022-01-12 16:00 ET | Begin 0.7.x series to include major update in performance and updates to db schemas. See below for changes. Please test before replacing 0.6.x series.
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 1) All extract data is loaded into their respective extract directories. knowledgebase_extract_dir, host_list_extract_dir, host_list_detection_extract_dir, asset_inventory_extract_dir. All files are gzip compressed.
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 2) CSV Files are no longer auto generated. use sqlite3 -csv -header sqlite_file.db "select * from TABLE_NAME" > OUTPUTFILE.csv to generate your csv files post process.
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 3) All xml files are converted to json files in their respective extract directories.
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 4) host_list_detection_sqlite.db schema has been updated. Please review tables to create the views of data you require. Q_Host_List_Detection is now a view of Q_Host_List, Q_Host_List_Detection_HOSTS, and Q_Host_List_Detection_QIDS. Each field in view Q_Host_List_Detection is prefixed with the source of their data ( HL_ = Q_Host_List, HLDH_ = Q_Host_List_Detection_Hosts, HLDQ_ = Q_Host_List_Detection_QIDS. host_list_detection_sqlite.db can be used to update a central database of all historical data post process.
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 5) host_list_sqlite.db schema has been updated. Please review tables to create the views of data you require.
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 6) kb_load_sqlite.db has been renamed kb_sqlite.db
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 7) kb_sqlite.db schema has been updated. Please review tables to create the views of data you require.
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 8) asset_inventory_sqlite.db schema has been updated. Please review tables to create the views of data you require.
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 9) -e etl_test_system will execute a sampling of all etl programs with the resulting log in log/test_system.log. ERRORS in this log indicate an unhealthy system.
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 10) There is no csv cell truncation as all csv cells with nested data are now json objects instead of flat lists.
0.7.6 | 2022-01-12 16:00 ET | changes to 0.7.x series: 11) A new CVE view of knowledgebase is available Q_KnowledgeBase_CVE_LIST.
0.7.7 | 2022-01-13 10:00 ET | Minor updates to test_system to get 75 hosts.
0.7.8 | 2022-01-13 15:00 ET | Minor updates to documenation prior to pypi.org launch.
0.7.9 | 2022-01-14 15:00 ET | Minor updates to documenation.
0.7.10 | 2022-01-15 02:00 ET | Update to asset inventory workflow to optimize data load.
0.7.11 | 2022-01-15 14:00 ET | Q_Knowledgebase_CVE_LIST view bug fix.
0.7.13 | 2022-01-20 19:00 ET | Update to allow for test or prod install of specific qualysetl version. Also, improvements in exception processing across modules and preliminary work on WAS.
0.7.14 | 2022-01-21 09:00 ET | Updated etl_asset_inventory to ensure gzip compression is default. Update documentation from Red Hat 8.4 to Red Hat 8.5 which is the current 8.x series of Red Hat.
0.7.15 | 2022-01-21 09:00 ET | Updated help documentation for qetl_setup_python_venv.
0.7.16 | 2022-02-09 15:00 ET | Updated etl_asset_inventory for GAV only customer processing.
0.7.17 | 2022-03-25 12:00 ET | Updated etl_asset_inventory enhancements to retry exception processing for malformed json and http error codes.
0.7.18 | 2022-03-25 12:00 ET | Updated Roadmap
0.7.19 | 2022-03-26 12:00 ET | Updated retry limits for etl_asset_inventory.
0.7.20 | 2022-03-27 15:00 ET | Updated etl_asset_inventory auth token refresh.
0.7.40 | 2022-08-02 18:00 ET | Updated etl_asset_inventory auth token refresh for edge case during maintenance window (http 503). Also, updated Road Map.
0.7.40 | 2022-08-02 18:00 ET | Updated http_conn_timeout default for all modules to address long running queries.
0.7.40 | 2022-08-02 18:00 ET | Updated Host List to include ASSET_RISK_SCORE, ASSET_CRITICALITY_SCORE, ARS_FACTORS. Edit etld_config_settings.yaml to include: host_list_payload_option: {'show_ars': '1', 'show_ars_factors': '1'} to enable capturing data. Your subscription must have ARS enabled. Contact your TAM and dgregory@qualys.com if this option does not work for you.
0.7.40 | 2022-08-02 18:00 ET | Updated Host List Detection to include QDS, QDS_FACTORS Edit etld_config_settings.yaml to include: host_list_detection_payload_option: {'show_qds': '1', 'show_qds_factors': '1'} to enable capturing data. Your subscription must have ARS enabled. Contact your TAM and dgregory@qualys.com if this option does not work for you.
0.7.41 | 2022-08-30 18:00 ET | Updated Host List Detection to enable host_list_detection_multi_proc_batch_size for values less than 2000 hosts.
0.7.42 | 2022-08-31 06:00 ET | Updated ulimit for open files to accomdate multiprocessing pipes in host list detection for large jobs.
0.7.44 | 2022-08-31 11:00 ET | Updated to ensure root user cannot install or execute qualysetl
0.7.45 | 2022-08-31 21:00 ET | Documention Updates - Asset Inventory Schema Image along with removing old comments from etld_config_settings.yaml template.
0.7.46 | 2022-09-01 16:00 ET | Add ram, disk, swap, cpu info to logging at beginning of job.
0.7.47 | 2022-09-02 09:00 ET | Add SYS stat for ram, disk, swap, cpu to logging throughout job run.
0.7.48 | 2022-09-18 09:00 ET | GAV/CSAM Fields added to SQL Database - domainRole,riskScore,passiveSensor,domain,subdomain,whois,isp,asn.
0.7.48 | 2022-09-18 09:00 ET | GAV/CSAM Documentation of Schema updated with additional fields added to SQL Database: domainRole,riskScore,passiveSensor,domain,subdomain,whois,isp,asn
0.7.48 | 2022-09-18 09:00 ET | Host List Detection Documentation of Schema updated with additional fields added to SQL Database - Q_Host_List: ASSET_RISK_SCORE, ASSET_CRITICALITY_SCORE, ARS_FACTORS
0.7.48 | 2022-09-18 09:00 ET | Host List Detection Documentation of Schema updated with additional fields added to SQL Database - Q_Host_List_Detection_QIDS: QDS, QDS_FACTORS
0.7.49 | 2022-09-19 03:00 ET | Documentation Update minor.
0.7.50 | 2022-09-19 04:00 ET | Documentation Update minor.
0.7.51 | 2022-10-05 15:00 ET | Added update to counters in logs, added retest if gateway 401 encountered.
0.7.56 | 2022-11-04 05:00 ET | Added -e etl_was to qetl_manage_user options to extract WAS Applications, Findings and Catalog.
0.7.56 | 2022-11-04 05:00 ET | Updated STATUS_TABLE for all modules. STATUS_COUNT renamed LAST_BATCH_PROCESSED, STATUS_DETAILS json updated to include details of which etl workflow updated the table along with workflow log timestamp to correlate the logs with the database update.
0.7.56 | 2022-11-04 05:00 ET | Updated base64 routine to correct error when processing complex passwords.
0.8.00 | 2022-12-05 05:00 ET | Major update, be sure to test before going to production.
0.8.00 | 2022-12-05 05:00 ET | Updates: 1) qetl_manage_user -e etl_was has been added to provide you with Web Application Scanning data including WebApps, Findings and Catalog.
0.8.00 | 2022-12-05 05:00 ET | Updates: 2) qetl_manage_user -e validate_etl_[etl name] will scan etl_[etl_name] log for errors and report success or fail.
0.8.00 | 2022-12-05 05:00 ET | Updates: - Example: qetl_manage_user -u /opt/qetl/users/youruser -e validate_etl_host_list_detection
0.8.00 | 2022-12-05 05:00 ET | Updates: - Example: qetl_manage_user -u /opt/qetl/users/youruser -e validate_etl_asset_inventory
0.8.00 | 2022-12-05 05:00 ET | Updates: - Example: qetl_manage_user -u /opt/qetl/users/youruser -e validate_etl_was
0.8.00 | 2022-12-05 05:00 ET | Updates: 3) qetl_manage_user -i -u /opt/qetl/users/[your new qetl user] will automatically initialize user directory without prompting. This is useful when automating run of QualysETL on new systems/docker images as no prompts are provided.
0.8.00 | 2022-12-05 05:00 ET | Updates: - Example: qetl_manage_user -i -u /opt/qetl/users/testuser will automatically create the -u directory structure without prompting.
0.8.00 | 2022-12-05 05:00 ET | Updates: 4) distribution - when enabled, all tables from ETL are prepared for database load.
0.8.00 | 2022-12-05 05:00 ET | Updates: Edit etld_config_settings.yaml adding the following keys:
0.8.00 | 2022-12-05 05:00 ET | Updates: - kb_distribution_csv_flag: True
0.8.00 | 2022-12-05 05:00 ET | Updates: - host_list_distribution_csv_flag: True
0.8.00 | 2022-12-05 05:00 ET | Updates: - host_list_detection_distribution_csv_flag: True
0.8.00 | 2022-12-05 05:00 ET | Updates: - asset_inventory_distribution_csv_flag: True
0.8.00 | 2022-12-05 05:00 ET | Updates: - was_distribution_csv_flag: True
0.8.00 | 2022-12-05 05:00 ET | Updates: Tested with the following MySQL options:
0.8.00 | 2022-12-05 05:00 ET | Updates: - Bash Script Example:
0.8.00 | 2022-12-05 05:00 ET | Updates: - export TABLE_NAME=QETL.Q_KnowledgeBase
0.8.00 | 2022-12-05 05:00 ET | Updates: - zcat [Q_KnowledgeBase.*.csv.gz] | mysql -v -e "LOAD DATA LOCAL INFILE '/dev/stdin' INTO TABLE ${TABLE_NAME} CHARACTER SET UTF8 FIELDS TERMINATED BY ',' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n';COMMIT;"
0.8.00 | 2022-12-05 05:00 ET | Updates: The default max_size for each field in distribution is 1000000 characters. To adjust this to meet your database field limits, edit etld_config_settings.yaml and add the following key/value pairs for each etl you want to customize max_field size for in distribution files.
0.8.00 | 2022-12-05 05:00 ET | Updates: - kb_distribution_csv_max_field_size: 2000000
0.8.00 | 2022-12-05 05:00 ET | Updates: - host_list_distribution_csv_max_field_size: 2000000
0.8.00 | 2022-12-05 05:00 ET | Updates: - host_list_detection_distribution_csv_max_field_size: 2000000
0.8.00 | 2022-12-05 05:00 ET | Updates: - asset_inventory_distribution_csv_max_field_size: 2000000
0.8.00 | 2022-12-05 05:00 ET | Updates: - was_distribution_csv_max_field_size: 2000000
0.8.00 | 2022-12-05 05:00 ET | Updates: For long running jobs etl_host_list_detection and etl_asset_inventory, these both generate distribution files through multiprocessing, so files are prepared for downstream ingestion as they are read from Qualys.
0.8.00 | 2022-12-05 05:00 ET | Updates: - Use this feature to immediately begin streaming Qualys data to your downstream system by inserting distribution files into your downstream system as each batch is created.
0.8.00 | 2022-12-05 05:00 ET | Updates: - Each distribution file is the product of integrity testing and load to SQLite prior exporting to distribution batch file for downstream processing..
0.8.00 | 2022-12-05 05:00 ET | Updates: 5) BATCH_DATE, BATCH_NUMBER added to Q_Asset_Inventory for tracability back to original batch json data used for loading table
0.8.00 | 2022-12-05 05:00 ET | Updates: 6) BATCH_NUMBER should always be stored as a text field.
0.8.00 | 2022-12-05 05:00 ET | Updates: 7) Removed Q_Host_List_Detection view. Table Views can be injected into database post process to meet customer requirements.
0.8.01 | 2022-12-06 05:00 ET | Minor Documentation Updates.
0.8.02 | 2022-12-06 05:00 ET | Minor Documentation Updates.
0.8.05 | 2022-12-06 05:00 ET | Updated to allow for csv quoting and dialect customization. The following are defaults that can be adjusted in etld_config_settings.yaml.
0.8.05 | 2022-12-09 05:00 ET | csv_distribution_python_csv_quoting = 'csv.QUOTE_NONE'
0.8.05 | 2022-12-09 05:00 ET | csv_distribution_python_csv_dialect_delimiter = '\t'
0.8.05 | 2022-12-09 05:00 ET | csv_distribution_python_csv_dialect_doublequote = False
0.8.05 | 2022-12-09 05:00 ET | csv_distribution_python_csv_dialect_escapechar = '\'
0.8.05 | 2022-12-09 05:00 ET | csv_distribution_python_csv_dialect_lineterminator = '\n'
0.8.05 | 2022-12-09 05:00 ET | csv_distribution_python_csv_dialect_quotechar = None
0.8.05 | 2022-12-09 05:00 ET | csv_distribution_python_csv_dialect_skipinitialspace = False
0.8.05 | 2022-12-09 05:00 ET | csv_distribution_python_csv_dialect_strict = False
0.8.05 | 2022-12-09 05:00 ET | The csv_distribution_python options above are tested with mysql load options in bash shell:
0.8.05 | 2022-12-09 05:00 ET | zcat [table file].csv.gz | mysql $PORT_OPT -v -e "LOAD DATA LOCAL INFILE '/dev/stdin' INTO TABLE ${TABLE_NAME} CHARACTER SET UTF8 FIELDS TERMINATED BY '\\t' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n';"
0.8.05 | 2022-12-09 05:00 ET | SEE Log for options that are selected for your csv_distribution run to validate the options meet your needs.
0.8.10 | 2022-12-16 09:00 ET | Internal enhancements to authentication, replacing etld_lib_credentials with etld_lib_authentication_objects.
0.8.10 | 2022-12-16 09:00 ET | Added etld_config_settings.yaml option was_catalog_start_greater_than_last_id=[ID NUM], resulting in pulling only catalog entries greater_than_last_id entered.
0.8.10 | 2022-12-16 09:00 ET | Added transform to asset inventory table, from sensor json, new table fields: "sensor_lastPcScanDateAgent", "sensor_lastPcScanDateScanner", "sensor_lastVmScanDateAgent", "sensor_lastVmScanDateScanner" added to SQLite Schema.
0.8.11 | 2022-12-20 14:00 ET | Updated first time setup of user to allow for updating user/password from template.
0.8.14 | 2023-01-23 09:00 ET | Updated WAS to iterate / include over 1000 findings in a web application.
0.8.20 | 2023-08-08 23:00 ET | Added experimental support for Host List Detection ASSET_CVE field. Contact your Technical Account Manager and David Gregory to enable ASSET_CVE. See [QWEB 10.23 release notification for details](https://www.qualys.com/docs/release-notes/qualys-cloud-platform-10.23-api-release-notes.pdf)
0.8.20 | 2023-08-08 23:00 ET | Added Database Injection - Methods to inject schema/data from QualysETL into your downstream databases. Ex. Azure Cosmos DB (PostgreSQL), Amazon RedShift, PostgreSQL Open Source, MySql Open Source, SnowFlake, Microsoft SQL Server. Contact your Qualys TAM to schedule a call with David Gregory if you wish to use this feature.
0.8.20 | 2023-08-08 23:00 ET | Visualization Use Case - Use QualysETL to build your downstream databases for use with PowerBI, Tableau, Etc. Contact your Qualys TAM to schedule a call with David Gregory if you wish to use this feature.
0.8.20 | 2023-08-08 23:00 ET | QWEB 10.23 Updates - Delivered additional fields for Host List and Host List Detection. For details see: See [QWEB 10.23 release notification for details](https://www.qualys.com/docs/release-notes/qualys-cloud-platform-10.23-api-release-notes.pdf)
0.8.20 | 2023-08-08 23:00 ET | Web Application Scanning(WAS) - Updated timing in WAS for long running jobs.
0.8.20 | 2023-08-08 23:00 ET | Docker Image Testing - Contact your TAM to schedule a call with David Gregory. Encapsulate Python Application into distributable docker image for ease os operation and upgrade.
0.8.21 | 2023-08-09 11:00 ET | Minor updates to images depicting host list detection schema and web application scanning schema.
0.8.30 | 2023-10-06 11:00 ET | Added Policy Compliance PCRS -e etl_pcrs to QualysETL.
0.8.30 | 2023-10-06 11:00 ET | Added fields to CSAM -e etl_asset_inventory: easmTags, hostingCategory1, customAttributes, organizationName
0.8.40 | 2023-10-07 18:00 ET | PCRS - Added Performance Improvements Added to reduce memory usage and improve multiprocessing.
0.8.50 | 2023-10-09 18:00 ET | PCRS - Optional Normalized Schema added for PCRS PostureInfo Table, ~50% reduction in space required for PostureInfo.
0.8.52 | 2023-10-22 18:00 ET | Updated Platform Identification, Added type to Q_WAS_FINDING table in -e etl_was, Default to Normalization for PCRS, and minor doco updates.
0.8.76 | 2023-11-30 10:00 ET | Minor update to ensure systems have swap space before executing qualysetl. Updated csv distribution format.
0.8.80 | 2024-01-26 15:00 ET | Minor updates to documentation, addressed edgecase to fix non-utf8 data.
0.8.85 | 2024-01-29 10:00 ET | Added option to exclude trurisk for edge case where VMDR is not enabled in customer subscription. Ex. Consulting Edition
0.8.91 | 2024-03-05 10:00 ET | Minor Update - Added ability to present payload options to etl_asset_inventory through etld_config_settings.yaml.
0.8.91 | 2024-03-05 10:00 ET | VM IOPS Note: Transforming large amounts of data requires IOPS greater than 3000. If you start encountering disk/io errors, ex. you see *sqlite.db-journal file remaining after QualysETL ends, you could be hitting IOPS limits. Either increase IOPS at your service provider or throttle your IOPS within your VM to slow down your disk I/O and stay within boundary of IOPS set by your service provider.
0.8.95 | 2024-03-11 18:00 ET | Added edge case exception logic where python request returns null object when retrieving bearer token.
0.8.100| 2024-03-13 18:00 ET | Added retry logic for IOPS exceeded disk io error.
0.8.115| 2024-03-15 18:00 ET | Added logic to independently rebuild Q_Knowledgebase_In_Host_List_Detection table. python3 -m host_list_detection_05_transform_load_xml_to_sqlite
0.8.115| 2024-03-15 18:00 ET | Added etld_lib_config_settings.yaml - sqlite_pragma_journal: 'WAL' # Valid values are: 'DELETE', 'TRUNCATE', 'PERSIST', 'MEMORY', 'WAL', 'OFF'
0.8.115| 2024-03-15 18:00 ET | Added etld_lib_config_settings.yaml - sqlite_pragma_synchronous: 'NORMAL' # Valid values are:'OFF', 'NORMAL', 'FULL'
0.8.115| 2024-03-15 18:00 ET | Added etld_lib_config_settings.yaml - sqlite_pragma_temp_store: 'MEMORY' # Valid values are:'DEFAULT', 'FILE', 'MEMORY'
0.8.115| 2024-03-15 18:00 ET | Added etld_lib_config_settings.yaml - sqlite_pragma_cache_size: '-4000' # Default of -4000 is appx. 15 MB used by cache
0.8.126| 2024-03-24 14:00 ET | Minor update to enhance logging.
0.8.130| 2024-03-38 19:00 ET | Minor update to fix problem relocating opt/qetl directory upon first installation.
0.8.150| 2024-05-11 19:00 ET | Minor update to report edge case where user had not completed first time Qualys registration prior to executing API calls.
0.8.150| 2024-05-11 19:00 ET | 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| 2024-05-11 19:00 ET | 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| 2024-05-11 19:00 ET | Additional Knowledgebase optional fields added: DETECTION_INFO, LAST_CUSTOMIZATION, DIAGNOSIS_COMMENT, CONSEQUENCE_COMMENT, SOLUTION_COMMENT, COMPLIANCE_LIST, AUTOMATIC_PCI_FAIL, TECHNOLOGY
0.8.150| 2024-05-11 19:00 ET | 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| 2024-05-11 19:00 ET | 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| 2024-05-11 19:00 ET | 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| 2024-05-11 19:00 ET | 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| 2024-05-11 19:00 ET | 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.151| 2024-05-22 15:00 ET | Documentation Update - Added diagram of QualysETL System building Data Warehouse - QualysETL: Unified Data Integration and Distribution for Comprehensive Security Insights
0.9.1 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | Add API Versioning: Update etld_config_settings.yaml to utilize new Qualys API Endpoints. The following endpoint updates are supported:
0.9.1 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | Table Update Notes for recent releases 10.30.
0.9.1 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | Special Table Update Notes: Contact your TAM to request enabling your subscription to populate these fields.
0.9.1 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | 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 | 2024-11-04 09:00 ET | Table Update: q_host_list_detection_qids - Add SOURCE
0.9.1 | 2024-11-04 09:00 ET | Table Update: q_knowledgebase - Add PATCH_PUBLISHED_DATE
0.9.1 | 2024-11-04 09:00 ET | Table Update: q_knowledgebase_in_host_list_detection - Add PATCH_PUBLISHED_DATE
0.9.1 | 2024-11-04 09:00 ET | Table Update: q_knowledgebase_in_q_was_finding - Add PATCH_PUBLISHED_DATE
0.9.1 | 2024-11-04 09:00 ET | Table Update: q_host_list - Add PC_AUTH_SUCCESS_DATE, OS_HOSTNAME
0.9.1 | 2024-11-04 09:00 ET | Table Update: q_host_list_detection_hosts - Add OS_HOSTNAME
0.9.1 | 2024-11-04 09:00 ET | Table Update: q_host_list_detection_hosts - Add NETWORK_NAME
0.9.1 | 2024-11-04 09:00 ET | Update: q_host_list_detection_qids where only 1 qid record exists, add to db even if assetid = 0.
0.9.2 | 2024-11-11 09:00 ET | Added new section above "Qualys API Versioning included in QualysETL". Added additional documentation on what to change for API Versioning.
Release Notes Log
-
0.8.115 - Added etld_lib_config_settings.yaml - sqlite_pragma_cache_size: '-4000' # Default of -4000 is appx. 15.62 MB used by cache
-
0.8.115 - Added etld_lib_config_settings.yaml - sqlite_pragma_temp_store: 'MEMORY' # Valid values are:'DEFAULT', 'FILE', 'MEMORY'
-
0.8.115 - Added etld_lib_config_settings.yaml - sqlite_pragma_synchronous: 'NORMAL' # Valid values are:'OFF', 'NORMAL', 'FULL'
-
0.8.115 - Added etld_lib_config_settings.yaml - sqlite_pragma_journal: 'WAL' # Valid values are: 'DELETE', 'TRUNCATE', 'PERSIST', 'MEMORY', 'WAL', 'OFF'
-
0.8.115 - Added logic to independently rebuild Q_Knowledgebase_In_Host_List_Detection # python3 -m host_list_detection_05_transform_load_xml_to_sqlite
-
0.8.100 - Added retry logic for AWS IOPS exceeded disk io error, account over allocated error.
-
0.8.95 - Added edge case exception logic where python request returns null object when retrieving bearer token.
-
0.8.91
- Minor Update - Added ability to present payload options to etl_asset_inventory through etld_config_settings.yaml.
- VM IOPS Note: Transforming large amounts of data requires IOPS greater than 3000.
- If AWS Compute Optimizer Finding = Over-provisioned, then you may be overconsuming IOPS resulting in disk i/o errors. See the details page of your instance-id for AWS Compute Optimizer Finding.
- Amazon has a procedure to determine if you need more IOPS and their support team can provide you with Microburst IOPS for a given day.
- Amazon Procedure: https://repost.aws/knowledge-center/ebs-identify-micro-bursting
- If you start encountering disk/io errors, ex. you see *sqlite.db-journal file remaining after QualysETL ends, you could be hitting IOPS limits. Either increase IOPS at your service provider or throttle your IOPS within your VM to slow down your disk I/O and stay within boundary of IOPS set by your service provider.
-
0.8.85
- Added etld_config_settings.yaml option to exclude trurisk for edge case where VMDR is not enabled in customer subscription. Ex. Consulting Edition.
- host_list_payload_option_exclude_trurisk: True
- host_list_detection_payload_option_exclude_trurisk: True
- Added etld_config_settings.yaml option to exclude trurisk for edge case where VMDR is not enabled in customer subscription. Ex. Consulting Edition.
-
0.8.80
- Minor updates to documentation to highlight usage of QualysETL.
- Added option to etld_config_settings.yaml to manage edge case of non-utf8 data embedded in device information.
- When set to True, additional processing is added to replace non-utf8 data.
- xmltodict_parse_using_codec_to_replace_utf8_error: True
- Default is xmltodict_parse_using_codec_to_replace_utf8_error: False
- Minor updates to first time install and upgrade instructions to add an error check to each, simplify code, and improve portability by calling out
'$USER' instead of '$USERNAME'.
-
0.8.76 Updates to distribution_dir and edge case no swap space
- Edge case: Abort if swap size is zero and ask user to add swap space (recommend 4GB - 8GB swap).
- Improved edge case exception processing for out of memory on small memory systems.
- Default csv distribution update:
- default output format improved for database loader processing:
- New: {'quoting': csv.QUOTE_ALL, 'delimiter': ',', 'doublequote': True, 'escapechar': None, 'lineterminator': '\r\n', 'quotechar': '"', 'skipinitialspace': False, 'strict': False}
- Example:
psql -c "\COPY ${SCHEMA_NAME}.${TABLE_NAME} FROM STDIN WITH (FORMAT csv, DELIMITER ',', QUOTE '\"', ENCODING 'UTF8')"
- Example:
- Old:
{'quoting': csv.QUOTE_NONE, 'delimiter': '\t', 'doublequote': False, 'escapechar': '\\', 'lineterminator': '\n', 'quotechar': None, 'skipinitialspace': False, 'strict': False}
- New: {'quoting': csv.QUOTE_ALL, 'delimiter': ',', 'doublequote': True, 'escapechar': None, 'lineterminator': '\r\n', 'quotechar': '"', 'skipinitialspace': False, 'strict': False}
- Note that csv distribution output should only be used if downstream application is developed to handle new fields over time.
- To Maintain a data contract, use the explicit ordered field names in a sqlite3 select statement to maintain data contract with downstream systems.
- default output format improved for database loader processing:
- Created Release Notes Log section at tail end of this document to hold historical release notes.
-
0.8.52 includes the following updates. See Changelog for additional details.
- Updated PCRS - Default to Normalized Schema Option. Use etld_config_settings.yaml if you do not want to normalize the schema.
- Updated Platform Identification to recognize additional PODs
- Minor documentation updates.
- Updated -e etl_was to include type in q_was_finding table.
-
0.8.50 includes the following updates. See Changelog for additional details.
- 0.8.50:
- Optional Normalized Schema for PCRS PostureInfo Table, ~50% reduction in space required for PostureInfo.
- To normalize PCRS PostureInfo add the following option to your etld_config_settings.yaml file.
- pcrs_postureinfo_schema_normalization_flag: True
- The schema will change to an optimized schema reducing space required by ~50%.
- A new table will be created where policyid, controlid, technology id are key to obtaining details of control.
- Q_PCRS_PostureInfo table has the following fields removed and placed in new table Q_PCRS_PostureInfo_Controls for normalization.
- policyTitle,controlStatement,rationale,remediation,controlReference,technology,criticality
- See PCRS Schemas below in documenation for more details.
- Performance Improvements to reduce memory and speed up multiprocessing to download data faster.
- Added Policy Compliance PCRS to QualysETL including the Policy List, Host Ids associated with each Policy and Posture Info.
- The 0.8.x >= 0.8.30 series supports Policy Compliance (PCRS), Vulnerability Management (VMDR), GAV/CSAM V2 and Web Application Scanning (WAS) Data.
- Added easmTags, hostingCategory1, customAttributes, organizationName to etl_asset_inventory (CSAM)
- Optional Normalized Schema for PCRS PostureInfo Table, ~50% reduction in space required for PostureInfo.
- 0.8.50:
0.8.21 includes the following updates. See Changelog for additional details.
- Added QualysETL Automation for injecting schema/data into downstream databases for metrics, analysis and visualization in PowerBI, Tableau, etc. Databases Tested include Azure CosmosDB (PostgreSQL), PostgreSQL Open Source, Amazon Redshift, Snowflake, Mysql, Microsoft SQL Server.
- Added Docker Beta to run QualysETL in a container
- Added QWEB 10.23 Updates - See QWEB 10.23 release notification for details
- Tested on Ubuntu 22.04 and Red Hat 9.x as they are the latest supported platforms.
- The 0.8.x series supports VM, CSAM and WAS Data.
- Next on the roadmap is Policy Compliance (PCRS) for Nov, 2023.
- Please see the accompanying videos for additional guidance
- Part 3 - Host List Detection. Qualys API Best Practices Series
- Part 4 - CyberSecurity Asset Management. Qualys API Best Practices Series
- The latest schema snapshots are in this document for reference. To obtain the latest schema from QualysETL, please use the SQLite Database output from QualysETL.
- See Python Package Index for Qualys ETL for latest version of qualysetl.
- See Roadmap for additional details on what's coming next.
- See Quick Start to get started now.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
qualysetl-0.9.2.tar.gz
(318.3 kB
view details)
Built Distribution
qualysetl-0.9.2-py3-none-any.whl
(287.6 kB
view details)
File details
Details for the file qualysetl-0.9.2.tar.gz.
File metadata
- Download URL: qualysetl-0.9.2.tar.gz
- Upload date:
- Size: 318.3 kB
- Tags: Source
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/4.0.2 CPython/3.10.12
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | e6897c058730c5ac4e75bcb175f59e65976ca921c53b93bc3b14f03bb78fe752 |
|
| MD5 | 5a08793400c337e18925bf73aea1f6f6 |
|
| BLAKE2b-256 | f486b01b3de7515a8450c723a7a969f32ff1a54cf35636a1bf8f9a8fd1453886 |
File details
Details for the file qualysetl-0.9.2-py3-none-any.whl.
File metadata
- Download URL: qualysetl-0.9.2-py3-none-any.whl
- Upload date:
- Size: 287.6 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via: twine/4.0.2 CPython/3.10.12
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 | 008aaa45da9dd3e8d1dfe27a663df222cedcc910c608bcd8dd2f37cec8456ef6 |
|
| MD5 | d9428d74b70e3fdaba23dfe3cd80a2c1 |
|
| BLAKE2b-256 | 611e2057f2d43117d6097c468d633a1d28d9cc934d9d78d26354bfb1f89a9e89 |
