Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
Exporting Vulnerability Assessment Results in Microsoft Defender for Cloud
Published Mar 05 2020 11:25 AM 38.2K Views
Microsoft

With the new Microsoft Defender for Cloud built-in vulnerability assessment solution, you can manage the deployment of the agent and the visualization of the results from a single dashboard. You can learn more about this integration and how it works by reading this article, and watch a quick demo available here.

The vulnerability assessment results that appear in the Microsoft Defender for Cloud dashboard, will look like this:

 

Fig1.JPG

 

While this visualization is very helpful and dynamic, one question that comes up very often is: how can I export this assessment to a CSV file? The answer is: you can do that using Azure Resource Graph (ARG)! Follow the steps below to perform this task:

 

1. In the Azure Portal, go to Resource Graph Explorer as shown below:

 

Fig1_1.JPG

 

2. Type the query below:

Note: this query below was changed on 8/28/2020 to reflect the changes made in the recommendation name. Thanks @DavidTex for calling this out in the comment section.

 

securityresources
 | where type == "microsoft.security/assessments"
 | where * contains "vulnerabilities in your virtual machines"
 | summarize by assessmentKey=name //the ID of the assessment
 | join kind=inner (
    securityresources
     | where type == "microsoft.security/assessments/subassessments"
     | extend assessmentKey = extract(".*assessments/(.+?)/.*",1,  id)
 ) on assessmentKey
project assessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId
extend description = properties.description,
         displayName = properties.displayName,
         resourceId = properties.resourceDetails.id,
         resourceSource = properties.resourceDetails.source,
         category = properties.category,
         severity = properties.status.severity,
         code = properties.status.code,
         timeGenerated = properties.timeGenerated,
         remediation = properties.remediation,
         impact = properties.impact,
         vulnId = properties.id,
         additionalData = properties.additionalData

3. Click Run Query button and you will see the result, similar to figure below:

 

Fig2.JPG

 

4. Click Download as CSV button.

 

Now that you downloaded the CSV, you can open it and consume the data generated by the assessment.

 

40 Comments
Copper Contributor

I am trying to run this script in Azure Resource Graph but not getting any results. Is this to be modified anywhere before using?

Microsoft

Hello @KamalDhingra , no there is nothing to modify. Maybe when you copy and paste there are some extra spaces? I tested in many environments and it works as is.

Copper Contributor

When I run the query, I only see the first 1000 results out of >3500.
Do you have suggestions for the most effective way to partition the query so I can download all of the results?

Microsoft

Hi @Paul Johnson , please read the section "working with large dataset" in the article below and see if it helps you:

https://www.codeisahighway.com/how-azure-resource-graph-is-gonna-change-the-way-you-search-and-scrip...

 

Copper Contributor

Thanks much Yuri! 
I enjoyed your presentation yesterday... :)

Microsoft

Thanks @Paul Johnson - appreciate the feedback and I'm glad you liked. 

Copper Contributor

Does anyone have an updated query which includes the CVE numbers and the CVSS score?  If so, would you mind sharing?  

Microsoft

@cdeeter you can add these to the extended section of the query to see the cvss:

 

   cvssList = properties.additionalData.cvss,

   cveArray = properties.additionalData.cve,

 

 

Iron Contributor

@Yuri Diogenesmy Kusto skills are not enough to create query wit Join statements. Do you think it is possible to expand the query with machine IP (internal and external) I can see an example query to pull all machines with external IP addresses here - https://docs.microsoft.com/en-us/azure/governance/resource-graph/samples/advanced?tabs=azure-cli#joi...

Microsoft

@Sergg for the example I gave, you just need to copy the statements from my previous reply, and paste in the extend section of the original query (can be under additionalData = properties.additionalData). Regarding the link you send, it should work too. 

Copper Contributor

@Yuri Diogenes can you write a query that can pull the private IP, hostname, CVE number, CVSS score, and date identified that I can paste into ARG?  My Kusto skills are very elementary and I'm struggling to write this.

Copper Contributor

Hi @Yuri Diogenes.
1st of all, thank you for your contribution, it was highly appreciated.
Regarding the script, in my case, i think Microsoft updated recently the display name, so searching on the strings will return nothing.
Here is the script updated for less watchful people :smile:

securityresources
 | where type == "microsoft.security/assessments"
 | where * contains "Vulnerabilities in your virtual machines"
 | summarize by assessmentKey=name //the ID of the assessment
 | join kind=inner (
    securityresources
     | where type == "microsoft.security/assessments/subassessments"
     | extend assessmentKey = extract(".*assessments/(.+?)/.*",1,  id)
 ) on assessmentKey
project assessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId
extend description = properties.description,
         displayName = properties.displayName,
         resourceId = properties.resourceDetails.id,
         resourceSource = properties.resourceDetails.source,
         category = properties.category,
         severity = properties.status.severity,
         code = properties.status.code,
         timeGenerated = properties.timeGenerated,
         remediation = properties.remediation,
         impact = properties.impact,
         vulnId = properties.id,
         additionalData = properties.additionalData
Microsoft

Thanks @DavidTex - that's absolutely correct. The recommendation was updated recently and your change reflects the latest name for the recommendation. Thanks for contributing, I will make sure to add a note and give you credits to call this out. 

Copper Contributor

Can resource graph be integrated directly into PowerBI? 

 

I you can do this directly from Azure Monitor. But would be awesome to do this directly from resource graph?

Microsoft

@fergo747 this feature is not available, make sure to add your feedback here. According to the status of this request, this integration is planned, but not available yet  https://feedback.azure.com/forums/915958-azure-governance/suggestions/36072472-visualize-resource-gr...

Copper Contributor

@Yuri Diogenes Thanks will do !

Copper Contributor

I have enabled Qualys on a VM and the extension is showing as good. I've waited a few days but note the following:

 

-The blade mentioned here does not show anything for the VM (or anything at all because I've only enabled 1 VM so far): https://docs.microsoft.com/en-us/azure/security-center/remediate-vulnerability-findings-vm#view-find...

 

-When trying to use the code above, it turns out that the Workspace does not have a table called 'securityresources'. I've verified that the VM is connected to the Workspace.

 

Thanks

Chris

Copper Contributor

@ChrisSommers  you need to run this at the Azure Resource Graph query level and not at the Azure Log Analytics Level.
There is no schema for this at the Azure Log Analytics, I understand that they both use KQL and might be a bit confusing ‌‌:smile:

 

If you go to Azure Resource Graph Explorer you will see on your left side the schema, and you will find the securityresources table.

Copper Contributor

@ChrisSommers  you need to run this at the Azure Resource Graph query level and not at the Azure Log Analytics Level.
There is no schema for this at the Azure Log Analytics, I understand that they both use KQL and might be a bit confusing ‌‌DavidTex_0-1601304816056.gif

 

If you go to Azure Resource Graph Explorer you will see on your left side the schema, and you will find the securityresources table.

More info at https://docs.microsoft.com/en-us/azure/governance/resource-graph/first-query-portal

Brass Contributor

Hi there folks,

hope everyone is keeping safe n' well!

 

I'm new to Kusto, so jumping in deep end and trying to merge the Virtual Machine hostname in as an additional column.

 

I think I need to call on microsoft.compute/virtualmachines, but not having much luck.

 

Any ideas?

 

Thanks ya'll.

Brass Contributor

All sorted...

 

securityresources
 | where type == "microsoft.security/assessments"
 | where * contains "Vulnerabilities"
 | summarize by assessmentKey=name //the ID of the assessment
 | join kind=inner (
    securityresources
     | where type == "microsoft.security/assessments/subassessments"
     | extend assessmentKey = extract(".*assessments/(.+?)/.*",1,  id)
 ) on assessmentKey
where split(id, "/")[7] == "virtualMachines"
project assessmentKey, vmname = split(id, "/")[8], subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId
extend description = properties.description,
         displayName = properties.displayName,
         resourceId = properties.resourceDetails.id,
         resourceSource = properties.resourceDetails.source,
         category = properties.category,
         severity = properties.status.severity,
         code = properties.status.code,
         timeGenerated = properties.timeGenerated,
         remediation = properties.remediation,
         impact = properties.impact,
         vulnId = properties.id,
         additionalData = properties.additionalData
Copper Contributor

Hi All

 

This is the first time I've come across Azure Resource Graph so not familiar with it at all yet. Just wondered if someone could advise on what script would enable me to export as a CSV all vulnerabilities reported in this section of Security Center:

 

Remediate Security Configurations > Vulnerabilities in security configuration on your machines should be remediated

 

Ideally, by the list being ordered first by CCeId and all VMs pertaining to that being listed underneath that CCeId.

 

security center.png

 

Copper Contributor

This query is fantastic, but when I try to add another json parse line for "Published = properties.publishedTime", it comes back NULL.  Anyone know why?

Copper Contributor

Hi, Does anyone have an updated query which includes the Published Time?  If so, can you please share the same.

Thank You.

Copper Contributor

So how can we properly alert on these events / vulnerabilities? 

 

Use case: we want to be notified when a high severity or a vulnerability is patchable and with a high score.

Copper Contributor

Hi All,

 

I am trying to use "Vulnerabilities in security configuration on your machines should be remediated" with kusto query give in this page not getting result.Could someone help?

 

In addition is it possible to generate report no of vulnerabilities count for each vm?

Copper Contributor

@scarabeetle and @@Ravindiran_Prabakaran This works for me: 

PublishedTime = properties.additionalData.publishedTime
Copper Contributor

Hi all,

I am trying to create a csv report for Vulnerability Assessment Results in Security Center using Qualys.


I noticed that there is a "TIMEGENERATED" property which I mapped to a corresponding column called "lastDetected".

Is there a mapping available that I can use to populate a "firstDetected" dated on a VM with the vulnerability?

The goal is to capture the aging of a vulnerability for risk prioritization.

 

Thanks,

Microsoft

Hello @dgaribaldi 

 

We don’t keep old values unfortunately. The data we present is always up to date per machine- And the TimeGenerated field present the scan time.

Microsoft

HI all,

This report is excellent and really I love it. Is it possible to schedule and notify external via email?

Copper Contributor

@hnakada  just use a Logic App to schedule by time trigger and connect it for example with O365 to send email (or if you have any SMTP)

Microsoft

I tried  to use logic app but the JSON parser could not work as data has multiple array and size limit. Then I used azure function to pull the KQL and add into SQL server. 

I am running automation via using the logic app and calling  my Function APP but the same  is failing with exception Have you tried automating this via any means?Yuri Diogenes

Iron Contributor

Does anyone know if (produced but not supported by Microsoft) free AzTS toolset https://github.com/azsk/AzTS-docs supports Vulnerability data extraction? It is not clear from the PowerBI reports document - https://github.com/azsk/AzTS-docs/tree/main/02-Monitoring%20security%20using%20AzTS

Copper Contributor

Good evening all

 

Just playing with this at the moment in preparation for my AZ-500 exam, but thinking of real world scenario for work, after remediating initial findings is there a way to create an alert for when a new vulnerability is found?

 

or

 

A weekly report that is emailed?

Copper Contributor

Good day,

Thanks for the great post. Got a quick question/request

how can we find where the vulnerability was detected? there are times where although a fix has been applied, Qualys still report the asset as vulnerable. In these scenarios you have to see where the vulnerability was detected "registry, user profile, file path to a vulnerable installer,...." this information is missing both in the portal and in the KQL query here.

Thanks a lot

Copper Contributor

Good evening all

 

i have been thinking about this a lot as the scan and results are only current and no history.

i was thinking 2 solutions the first is a runbook running PowerShell to query the azure resource explorer and save this to a CSV once a week to a storage account (more or less frequent depending on your compliance levels)

the other way is very similar but the output is streamed to log analytics this way you can create alerts, dashboards and some history  

Brass Contributor

Based on our observation, the integrated Qualys Scanner is also detecting vulnerabilities of nonRunning Kernels on Linux. With the standalone license of Qualys such vulnerabilities can be easily filtered:

Exclude or display vulnerabilities for non-running Linux kernels (qualys.com)

 

Does anyone know if this detail (a specific vulnerability was found on the running or nonRunning kernel) is also exposed via resource graph? Or any other API of Defender for Cloud?

Microsoft

@rkelly141   I have implemented the solution to pull the results of this query via function app into a SQL server and then pulled the same into Power BI. In that way I can assign vulnerabilities to image onwers by further adding the correlation in my SQL Server by creatin views. 

Copper Contributor

Thanks @Yuri Diogenes for sharing this information. Updated script with a minor change 

securityresources
 | where type == "microsoft.security/assessments"
 | where * contains "Machines should have vulnerability findings resolved"
 | summarize by assessmentKey=name //the ID of the assessment
 | join kind=inner (
    securityresources
     | where type == "microsoft.security/assessments/subassessments"
     | extend assessmentKey = extract(".*assessments/(.+?)/.*",1,  id)
 ) on assessmentKey
| project assessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId
| extend description = properties.description,
         displayName = properties.displayName,
         resourceId = properties.resourceDetails.id,
         resourceSource = properties.resourceDetails.source,
         category = properties.category,
         severity = properties.status.severity,
         code = properties.status.code,
         timeGenerated = properties.timeGenerated,
         remediation = properties.remediation,
         impact = properties.impact,
         vulnId = properties.id,
         additionalData = properties.additionalData

 

Copper Contributor

How do we find the URL to qualys portal where we can also view all the assets scanned?

I am not able to find that in the documentation where it explains how to install qualys agent via azure portal.

Co-Authors
Version history
Last update:
‎Oct 24 2021 04:09 AM
Updated by: