Microsoft

With the new Azure Security Center's built-in vulnerability assessment solution (powered by Qualys), 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 Azure Security Center 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.

 

24 Comments
Occasional Visitor

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.

Senior Member

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...

 

Senior Member

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

Microsoft

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

Occasional Visitor

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,

 

 

Occasional 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. 

Occasional Visitor

@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.

Senior Member

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. 

Senior Member

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...

Senior Member

@Yuri Diogenes Thanks will do !

Occasional Visitor

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

Senior Member

@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.

Senior Member

@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

New 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.

New 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
Occasional Visitor

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

 

Occasional Visitor

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?

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

Thank You.