_The Qualys Security Blogs _[API Best Practices Series]()_ is designed for Qualys customer programmers or stakeholders with a general knowledge of programming who want to implement best practices to improve 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 CSAM]() helps cybersecurity teams to find and manage cyber risks in their known and unknown IT assets. It continuously discovers and maintains a rich asset inventory of systems including desktops, servers, and other devices.
Some key capabilities of Qualys CSAM are:
* Software inventory with lifecycle Information to drive proactive remediation
* Categorization and normalization of hardware and software information for researching software availability; e.g. Reveals blind spots where security tools may be missing from systems
* Identification of unauthorized software or out-of-date software so cybersecurity teams can prioritize those risks and reduce technology debt
* Import of business information into Qualys CSAM to add context to host systems for risk scoring and prioritization of remediation
* Qualys Cloud Agent information including: what modules are activated, agent last check-in date, agent last inventory scan date, last vulnerability scan date, and last policy compliance scan date to get the latest security information from IT systems
The Qualys application programming interface (API) allows programmers to derive maximum benefit from CSAM data. If you are a programmer, your enterprise may benefit from the step-by-step instructions provided in this post.
In Part 4 of [this series](), the goal is to obtain CSAM data in both compressed JavaScript Object Notation (JSON) form as well as 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 to develop with.
The [accompanying video presents QualysETL]() in more detail, along with live examples to help you effectively extract, transform, load, and distribute Qualys CSAM data as well as combine CSAM data with [vulnerability data]() for a unified view of your security data.
## About the Qualys API
The Qualys API is a key component in our API-first model. Since the founding of Qualys in 1999, a rich set of Qualys APIs have been available and continue to improve. As a result, programmers at Qualys customers 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 in our API, some enterprise customers continue to experience suboptimal performance in various areas such as automation. What are the inherent automation challenges to Extract, Transform and Load (ETL) Qualys data? And what do we mean by ETL?
1. First, **Extract **refers to extracting CSAM data using the Qualys CSAM API. In this step you execute API calls to extract Qualys CyberSecurity Asset Management data from our Platform.
2. Second, **Transform **refers to reading the resulting extracted data from Qualys and transforming it into other forms or formats that your organization decides will be useful (e.g. SQL, DDL, or CSV). The Transform step is your opportunity to prepare and enhance the CSAM 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. Finally, **Load **refers 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 best practice programming methods to extract CSAM from the Qualys API reliably and efficiently
* How to obtain some or all the CSAM JSON output, which provides rich asset inventory information
* How to integrate Qualys data into an SQL database for use in automation
In the Example JSON Output image below, we have highlighted some key fields including:
1. The lastSeenAssetId which is the ID that will be used for pagination over many assets
2. The hasMore flag which is set to 1 when there are more assets to paginate through
3. The assetId which is the unique ID assigned to this host
4. The lastModifiedDate which indicates when the asset was last updated by Qualys CSAM
You will want to 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 with that objective.
For more information about our JSON Fields in Qualys CSAM, please refer to the [GAV/CSAM V2 API Appendix]().
## 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 processes to:
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 initial Q_Asset_Inventory table created through QualysETL of CSAM. This table contains your Qualys CSAM data and will grow over time as Qualys adds new capabilities to CSAM. See the [GAV/CSAM V2 API Guide]() for a complete list of fields available in CSAM.
With CSAM data prepared for use, you may want to distribute it for usage by your corporation. For example, you may want to distribute a timestamped version of the SQLite Database into an Amazon Web Services Relational Database Service, or an AWS S3 Bucket. Further, you could make the SQLite database available locally for analysts so they can process and report on vulnerabilities in your organization using their desktop tool of choice. Data usage flexibility is achieved at this point.
## CSAM Activity Diagram****
The CSAM Activity Diagram below depicts QualysETL pagination to obtain Qualys CSAM data along with the simultaneous loading of CSAM data into an SQL Database. The activities include:
1. CSAM Extract is scoped at up to 300 assets per API call with last updated date/time driving extract
2. QualysETL will extract CSAM data and through multiprocessing it will simultaneously transform and load CSAM data
3. While QualysETL is running, you can immediately begin distributing your data to downstream systems for metrics, visualization, and analysis to drive remediation
### API Call Recommendations:
* Use a page size of 300 assets, incrementally extract to the last updated date/time
* Use the hasMore Flag set to 1 and lastSeenAssetId to paginate through your API calls
* Distribute snapshots of your ETL data for desktop analysis or as a pipeline of continuous updates in your organizations data store
* Reset your token every four hours to ensure you continue to successfully authenticate to the CSAM API
## Postman Examples****
In the following three examples, we will get a bearer token, get the total number of host assets in your Qualys instance, and obtain the first 300 hosts.
In the first example below, we use Postman to _Get Bearer Token_ from Qualys using the key parameters. Once retrieved, the Bearer Token is used to authenticate and authorize API calls to GAV/CSAM V2 API and is valid for four hours.
In the second example, we use the Bearer Token from the first example to obtain the total number of host assets in your Qualys instance using the CSAM /rest/2.0/count/am/asset endpoint.
In the third example, we extract the first 300 assets. Notice that the hasMore flag is set to 1 and the lastSeenAssetId is present. You will use these fields to get your next batch of 300 assets. You can develop your own integration with the GAV/CSAM V2 API or leverage the QualysETL Blueprint of open-source python code to download all your CSAM 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 20.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]().
Next, you can run your own SQL queries to analyze the data and tune the application to meet your needs.
**Select Statement Example 1: **Find a specific Cloud Agent version
**Select Statement Example 2:** Unified View of CSAM and [vulnerability data]() to find Log4j vulnerabilities, along with the last agent check-in date and modules activated to determine if patching is enabled.
**QualysETL installation key takeaways:******
1. With one command, you can ETL Qualys CSAM into an SQLite Database, ready for analysis or distribution
2. QualysETL is a blueprint of example code you can extend or use as you need because it is open source distributed under the Apache 2 license
## Put QualysETL into Practice****
Leverage QualysETL as a blueprint of example code to produce a current CSAM 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 part of their [free trial of Qualys CSAM]() to learn more about their full capabilities.
## Learn More****
* Read the [CSAM API User Guide]()
* [Qualys GAV/CSAM Key Features]()
* Article: [_Understanding Entity IDs_]()
* Training Course: [Qualys API Fundamentals]()
## About This Series****
The Qualys Security Blogs [API Best Practices series]() helps programmers at Qualys customer organizations create a unified view of Qualys data across our cloud services including Qualys VMDR (Parts 1-3) and Qualys CSAM.
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 aggregate providing an overall best practice view of the Qualys API.Read More