Blog 5: Automate Vulnerability Status Report Generation (Qualys)
Introduction:
Problem Statement:
- Daily we used to make Vulnerability status report & have to submit to the management.
- Accurate reporting: There’s some issues in Qualys tool with subject to accurate reporting & agent synchronization, due to which we’re getting some false positive report.
o Many times we observed fixed vulnerability reopens, or patch has been successfully installed but vulnerability hasn’t fixed due to multiple factors, like this we get many inaccurate reports.
o So to make record for patched vulnerability & to share daily vulnerability status report (open & fixed) to management, we’ve to perform this activity.
- Other tools: We’re not on relying on one tool, due to inaccuracy & dependency issues. We also have SCCM tool to fix non-patchable vulnerabilities & other vulnerabilities, which Qualys unable to patch due to multiple factors. So to get the patch vulnerability report from vulnerability management tool.
- Vulnerability Report includes:
o Vulnerability Before Deployment
o Vulnerability Mitigated
o Vulnerability Open as of today
o Asset with Vulnerability Before deployment
o Asset with Vulnerability Fixed
- This task is manually executed. Procedure are as follows:
o Start Qualys
o Go-to Patch Management Module
o Go-to Job tab
o Select any job > click drop-down options > select View progress
o Click Download job button
o View job report status > Click Download report
o Zip file will be downloaded including csv report
o Extract zip file > csv report extracted.
o Open csv file, following columns will be there:
rowId | ASSET ID | ASSET STATUS | ASSET NAME | ASSET LAST CHECKED-IN TIME |
OPERATING SYSTEM | PATCH STATUS | PATCH ID | PATCH TITLE | CATEGORY |
SEVERITY | BULLETIN | KB | QID | CVE |
REASON | REASON CODE | OS STATUS | EXIT CODE | HTTP STATUS |
o Delete all columns except Patch Title & QID.
Patch Title | QID |
o Create new sheet, (naming VM Report) with following columns, fill necessary details for reporting:
Job Name | Patch Title | Tags name | QID | Vulnerability Before Deployment |
Vulnerability Mitigated | Vulnerability Open as of today | Asset with vulnerability Before deployment | Asset with vulnerability Fixed |
Picture 1.0
o Perform vlookup in VM report with csv report using Patch title to fetch QID & press enter
§ =vlookup(Patch-title column of VM report, Patch-title column of CSV file, 2)
§ We can do vlookup for single patch or multiple patch.
o QID will be collected in VM report file.
o Now, use QQL Query for last 5 columns of VM report.
§ Vulnerability Before Deployment:
· SYNTAX: vulnerabilities.vulnerability.qid:[<QID(s) comma's separated>] and vulnerabilities.firstFound:[ <YYYY-MM-DD (TAKE ANY INITIAL DATE BEFORE DEPLOYMENT)> ... <YYYY-MM-DD (DEPLOYMENT DATE)> ]
· Eg: vulnerabilities.vulnerability.qid:[91870, 91875] and vulnerabilities.firstFound:[2017-01-03 ... 2023-01-26]
§ Vulnerability Mitigated:
· SYNTAX: vulnerabilities.vulnerability.qid:[<QID(s) comma's separated>] and vulnerabilities.lastFixed:[<YYYY-MM-DD (DEPLOYMENT START DATE)> ... <YYYY-MM-DD (DEPLOYMENT END DATE)>]
· Eg: vulnerabilities.vulnerability.qid:[91870, 91875] and vulnerabilities.lastFixed:[2023-01-26 ... 2023-02-02]
§ Vulnerability Open as of Today:
· SYNTAX: vulnerabilities.vulnerability.qid:[<QID(s) comma's separated>]
· Eg: vulnerabilities.vulnerability.qid:[91870, 91875]
§ Asset with Vulnerability Before Deployment:
· SYNTAX: vulnerabilities.vulnerability.qid:[<QID(s) comma's separated>] and vulnerabilities.firstFound:[ <YYYY-MM-DD (TAKE ANY INITIAL DATE BEFORE DEPLOYMENT)> ... <YYYY-MM-DD (DEPLOYMENT DATE)> ]
· Eg: vulnerabilities.vulnerability.qid:[91870, 91875] and vulnerabilities.firstFound:[2017-01-03 ... 2023-01-26]
§ Asset with Vulnerability Fixed:
· SYNTAX: vulnerabilities.vulnerability.qid:[<QID(s) comma's separated>] and vulnerabilities.lastFixed:[<YYYY-MM-DD (DEPLOYMENT START DATE)> ... <YYYY-MM-DD (DEPLOYMENT END DATE)>]
· Eg: vulnerabilities.vulnerability.qid:[91870, 91875] and vulnerabilities.lastFixed:[2023-01-26 ... 2023-02-02]
o Format should look like this:
Picture 1.1
o Execute all queries on portal one-by-one & save the output.
o Final report is ready & task has been completed successfully.
o Output results (sharing in column format, ideally it should be in row format for multiple patches & queries, as shared screenshot below):
Deployed Date | |
End Date | |
Job Name | |
Patch Title | |
Tags name | |
QQL (Tag Name) | |
QID | |
QQL (Vulnerability Before Deployment) | |
Vulnerability Before Deployment | |
QQL (Vulnerability Mitigated) | |
Vulnerability Mitigated | |
QQL (Vulnerability Open as of today) | |
Vulnerability Open as of today | |
QQL (Asset with vulnerability Before deployment) | |
Asset with vulnerability Before deployment | |
QQL (Asset with vulnerability Fixed) | |
Asset with vulnerability Fixed | |
Picture 1.2Solution:
- The objective is the automate this entire above process/activity to save time & efforts.
- Technical objective is, to get the numerical output from portal as we’ve query stored in excel sheet, we just need to connect both to get the expected results without performing any manual work like earlier.
- For that, we need to automate the process by building application using any programming language (like python, java).
- Application lifecycle will be as follows:
Picture 1.3
- EXPLANATION OF PROCESS:
o Once excel sheet is ready with queries & blank output (as shown in pic 1.1).
o Start automation application, it’ll fetch queries from excel sheet (we can do manually copy-paste also), & bot will send request to Qualys via Qualys API.
o Qualys will process query & respond the output.
o Once application/bot will capture the output, it’ll transfer the same to excel sheet.
o Same process will continue for all queries one-by-one.
- We can use Waterfall SDLC lifecycle model, which is easy & convenient for this project.
- This is how entire automation flow for automation application can be built, will execute & process will improve which will result fruitfull.
Technology Requirements:
1. Microsoft Excel
2. Programming/Scripting language (Python, java, JavaScript, etc)
3. Qualys API
NOTE : Sharing idea as per my research & to improve process.
Comments
Post a Comment