_This _API Best Practices Series is designed for Qualys customer programmers or stakeholders with a general knowledge of programming who want to implement best practices for improving the development, design, and performance of their programs that use the Qualys API. For non-customers, the Qualys API demonstrates our commitment to interoperability with the enterprise IT security stack.

Qualys Policy Compliance (PC) automates the assessment of thousands of technical security controls. It also centralizes the documentation of evidence where misconfigurations and lapses have been discovered or fixed. Policy Compliance provides proof of compliance across industry standards, regulations, benchmarks, frameworks, and mandates that help harden and secure host systems against existing threats.

In this blog post, let’s discuss how to obtain Policy Compliance Data via Posture Streaming (PCRS) API. Discover Policy List, Host IDs, and Posture Information data in compressed JavaScript Object Notation (JSON) form and into the latest timestamped, point-in-time SQLite database.

To help programmers realize this goal, we are providing a blueprint of example code called QualysETL that is open-sourced under the Apache 2 License for your organization.

Before we proceed further, here’s a list of benefits from Policy Compliance.

Policy Compliance Provides Organizations With

• Advanced compliance with CIS & configuration management
• Over 850 policies & 19K+ controls mitigate audit failures.
• Out-of-the-box policies reduce efforts from days to minutes.
• Custom reporting & dashboard ensure you are audit-ready.
• Auto-discovery & OCA (Out of Band Configuration Assessment) coverage eliminates blind spots.
• 98% less time and cost
• ROI (return on investment) - 43% Potential cost savings & risk reduction
• (Application Programming Interface) such as the Posture Streaming API (PCRS) for Automation.

The Qualys application programming interface (API) allows programmers to derive maximum benefit from Policy Compliance data. Your enterprise will benefit from the step-by-step instructions provided in this post, along with live examples to help you effectively extract, transform, load, and distribute Qualys PCRS data to your database in the cloud.

About the Qualys API

The Qualys API is a key component in the API-first model. Since the founding of Qualys in 1999, a rich set of Qualys APIs has been available and continues to improve. As a result, programmers at Qualys customer organizations have been able to automate processing Qualys in new ways, increasing their return on investment (ROI) and improving overall mean-time-to-remediate (MTTR).

In the diagram below, QualysETL is depicted as a workflow from which you can use the resulting compressed JSON or SQLite database for analysis on your desktop as part of a continuous live data feed to update your corporate data store in the cloud or your local data center.

Even with all these advances, some customers continue to experience suboptimal performance in automation.

So, let’s take a look at what are the inherent automation challenges to Extract, Transform, and Load (ETL) Qualys data?

And what do we mean by ETL?

1. Extractrefers to extracting PCRS data using the Qualys PCRS API. In this step, you execute API calls to extract Qualys Policy Compliance data from our platform, including Policy, Hosts, and Posture Information.
2. Transformrefers to reading the resulting extracted data from Qualys and transforming it into other forms or formats your organization decides will be useful (e.g., SQL, DDL, or CSV). The Transform step is your opportunity to prepare and enhance the PCRS data, for example, injecting security intelligence specific to your environment that will help drive remediation. Examples of this include data classification or application owner to prioritize risk.
3. Loadrefers to loading the data into its final form on disk for independent analysis (e.g., SQLite) and/or distributing Qualys data to its destination in the cloud for use in metrics or visualization tools such as PowerBI or Tableau.

Automation Challenges****

With any API, there are inherent automation challenges. Some of these are:

• What are the most effective programming methods to extract PCRS from the API reliably and efficiently?
• How to obtain some or all the PCRS policies, Hosts, and Posture Information.
• How to integrate Qualys data into an SQL database for use in automation

In the Example JSON Output image below, we have noted some key fields, including:

1. The ID and title are your unique Qualys Policy ID and Title.
2. The status is either active for evaluation or inactive where no evaluation occurs.
3. The lastEvaluatedDate indicates when the Policy was last executed.

You should transform JSON data for transfer or prepare the data for ingestion into a database for future correlations with other corporate data sources. The QualysETL blueprint of example code can help you here.

For more information about our JSON Fields in Qualys PCRS, please refer to the Compliance Section of the VM/PC API Guide.

About QualysETL

QualysETL is a blueprint that can be used by your organization as a starting point to develop your ETL automation. In the image below, you can see the QualysETL workflow, which includes the following processes:

1. **Extract:**API Calls used to obtain Qualys data
2. **Transform:**Methods used to enhance data, prepare/cleanse data, and catch errors in data
3. **Load:**Methods to produce various forms of data useful to your organizations, such as SQLite
4. **Distribution:**Methods you develop to distribute Qualys data to your organization.

In the diagram, we show the base schema for the following tables:

1. q_pcrs_policy_list – Policy List
2. q_pcrs_hostids – Host ID List
3. q_pcrs_postureinfo – Posture Information
4. q_pcrs_postureinfo_controls – Normalized Table of Posture Information Controls
5. q_pcrs_status – Status of QualysETL.

These tables are automatically created through QualysETL of PCRS and will grow over time as Qualys adds new capabilities to PCRS. See the Compliance Section - Posture Streaming API (PCRS) for a complete list of fields available in PCRS API.

With PCRS data prepared for use, you want to distribute it for usage by your corporation. For example, you can distribute a timestamped version of the SQLite Database into an Amazon Web Services Relational Database Service or an AWS (Amazon Web Services) S3 Bucket. Further, you can make the SQLite database available locally for analysts so they process and report on vulnerabilities in your organization using their desktop tool of choice. Data usage flexibility is achieved at this point.

Contact your Qualys Technical Account Manager and arrange a meeting with David Gregory, Principal Solutions Architect of API, if you are interested in automation to inject data directly into your downstream database from QualysETL.

PCRS Activity Diagram****

The PCRS Activity Diagram below depicts QualysETL pagination to obtain Qualys PCRS data along with the simultaneous loading of PCRS data into an SQL Database. The activities include:

1. Policy List and Host IDs are extracted and batched. Transform and Load Queue is also started as a separate process.
2. Posture Information is extracted through Multiprocessing and sent to the Transform and Load Queue for Database Ingestion.
3. While QualysETL runs, you can immediately begin distributing your data to downstream systems for Metrics, Visualization, and Analysis to drive remediation.

API Call Recommendations:****

• The key to success is to follow the sequence of API calls, including:
  • Get Policy List
  • Resolve Host IDs for each Policy.
  • Get Posture Information
• Distribute snapshots of your ETL data for desktop analysis or as a continuous update pipeline in your organization’s data store.

Postman Examples****

To authorize Postman API Calls, a Bearer Token will be used. Take a look at the following infographic using your credentials to obtain the Bearer Token. It should be saved for reuse and will expire in 4 hours.

Use your bearer token in the following examples:

1. Get the Policy List
2. Get the Host IDs for a Policy.
3. Get Posture Information for a batch of Host IDs.

In the first example, we use Postman to extract policies that have been evaluated with a “lastEvaluationDate” of epoch using the “/pcrs/1.0/posture/policy/list” endpoint.

The "https://{{base_url}}/"_ _is your subscription Gateway Server URL from Platform Identification.

If you are new to Postman, see the Qualys API Fundamentals Training to learn more about Postman and how it can be developed with Qualys API.

In the second example, the “/pcrs/1.0/posture/hostids” endpoint extracts a specific policy with all hosts scanned since epoch. You will use the resulting list of hosts in your next API call to get the posture information for each policy.

In the third example, the “/pcrs/1.0/posture/postureInfo” endpoint is used to extract specific policies posture information for the group of Host IDs passed to the API Call in the payload body of the request.

You can develop your integration with the PCRS API or leverage the QualysETL Blueprint of open-source Python code to download all your PCRS Data with a single command!

QualysETL Installation****

To install QualysETL, we recommend you provision a secure, patched, up-to-date virtual machine instance of Ubuntu 22.04 that has connectivity to the internet. When that step is completed, you can log into your Ubuntu instance and follow along with the accompanying video to install the application and run your first ETL.

The instructions are located on pypi.org at: <https://pypi.org/project/qualysetl/&gt;

Next, you can run your own SQL queries to analyze the data and tune the application to meet your needs.

QualysETL Installation Summary

Some Key Takeaways

1. With one command, you can ETL your Policy Compliance data into an SQLite Database, ready for analysis or distribution
2. Data is flexible as it can be sent downstream to other SQL Databases for applications such as PowerBI or Tableau to visualize your data.
3. QualysETL is a blueprint of example code you can extend or use as you need because it is open source and distributed under the Apache 2 license.

At this point, you should be able to query your PCRS SQLite database. Below is an example SQL query to find all the policies with a keyword in the title “DISA” and retrieve all corresponding records.

Put QualysETL Into Practice

Leverage QualysETL as a blueprint of example code to produce a current PCRS SQLite Database, ready for analysis or distribution.

For questions, existing Qualys customers can schedule time through their Technical Account Manager to meet with our solutions architects for help. Non-customers can request access to the Qualys API or QualysETL as a free trial of Qualys Policy Compliance to learn more about their full capabilities.

Learn More****

• API User Guide: Compliance Section - Posture Streaming (PCRS) of VM/PC API User Guide
• Blog: Managing Security Configuration Risk with Policy Compliance
• Blog: Elevate Your Security Posture
• Training Courses: “Qualys API Fundamentals", “Policy Compliance Foundation”
• Getting Started: Policy Compliance Guide

About This Series****

The Qualys Security Blog’s API Best Practices series helps programmers at Qualys customer organizations create a unified view of Qualys data across our cloud services, including Qualys VMDR (Vulnerability Management, Detection, and Response), CSAM (Cyber Security Asset Management), WAS (Web Application Scanning) and PCRS (Policy Compliance – Posture Streaming API).

The API Best Practices Series will continue to expand over the coming months to cover other key aspects of the Qualys API, with each presentation building on the previous one and, in the aggregate, providing an overall best practice view of the Qualys API.