How to generate a monthly Defender ATP Threat and Vulnerability Report

Posted onAuthorAlex Verboon5 Comments

Update 11 January 2020 – Microsoft has updated the Advanced Hunting Schema, so ComputerName is now DeviceName in the queries.

Just recently Microsoft announced that the Defender ATP advanced hunting schema was extended with the following tables:

  • DeviceTvmSoftwareInventoryVulnerabilities
  • DeviceTvmSoftwareVulnerabilitiesKB
  • DeviceTvmSecureConfigurationAssessment
  • DeviceTvmSecureConfigurationAssessmentKB

This allows us to run advanced hunting queries to find and extract Defender ATP TVM data.

Now the people in your organization who are responsible for threat and vulnerability management might not necessarily have the knowledge of using the advanced hunting query language or are provided access to the Defender ATP console. So why not just send them a monthly report? Following is how to create a monthly Defender ATP TVM report using advanced hunting and Microsoft Flow.

Within Microsoft Flow, start with creating a new scheduled flow, select from blank.

Within the Recurrence step, select Advanced options and adjust the time zone and time as per your needs.

Within the Advanced Hunting action of the Defender ATP connector we use the following advanced hunting query.

Next, we convert the data into CSV

And then we save the CSV file to a SharePoint or OneDrive location. The below example saves the file to a folder in my personal OneDrive.

Next, we fetch the content, so we can use it as an attachment.

Now we run another advanced hunting query, so we get some numbers that we can add into our e-mail message.

The results of the summary are now converted into a HTML table, that we embed into the e-mail message body.

And finally, we compose the e-mail.

When all steps and actions are configured, we test the flow and if all goes well we get a summary as shown below.

And an e-mail in our inbox.

That’s it for today, hope this provided you with some inspiration on how to share Defender ATP Threat and Vulnerability information.

Have a great day

Alex

Related posts:

  1. Defender ATP Advanced hunting with TI from URLhaus
  2. How to customize Windows Defender ATP Alert Email Notifications
  3. Managing Time Zone and Date formats in Microsoft Defender Security Center
  4. Managing Role Based Access (RBAC) for Microsoft Defender Advanced Threat Protection
  5. Advance your Microsoft Defender ATP hunting skills using the Atomic execution framework

5 Replies to “How to generate a monthly Defender ATP Threat and Vulnerability Report”

  1. I tried this exact query and I got the error:
    ‘project’ operator: Failed to resolve table or column expression named ‘DeviceTvmSoftwareInventoryVulnerabilities’

    Any ideas?

  3. Ran well a few times, but this month I get an error on the advanced hunting – ‘project’ operator: Failed to resolve scalar expression named ‘ComputerName’. Fix semantic errors in your query

  5. Hi Alex,

    first, thanks for your work and blog! I modified your query a litte for me, because I need for every CVE the affected machines. If I run the query I get only one affected machine for one CVE. I added the ‘DeviceName’ into the output file. Do you have an idea how to get for all CVEs all affected Machines? I search about two hours in github and search engines but I found nothing, that works for me.
    Here my query. Many thanks for help:

    DeviceTvmSoftwareInventoryVulnerabilities
    | project DeviceName, SoftwareName, CveId, SoftwareVersion, VulnerabilitySeverityLevel
    | join (DeviceTvmSoftwareVulnerabilitiesKB
    | project AffectedSoftware, VulnerabilityDescription , CveId , CvssScore , IsExploitAvailable
    )
    on CveId
    | project CveId , SoftwareName , SoftwareVersion , CvssScore , VulnerabilitySeverityLevel, IsExploitAvailable , DeviceName
    | distinct CveId, DeviceName, SoftwareName , SoftwareVersion, CvssScore , VulnerabilitySeverityLevel, IsExploitAvailable
    | sort by CvssScore

    Cheers
    Marcel

Leave a Reply