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:
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
5 Replies to “How to generate a monthly Defender ATP Threat and Vulnerability Report”
I tried this exact query and I got the error:
‘project’ operator: Failed to resolve table or column expression named ‘DeviceTvmSoftwareInventoryVulnerabilities’
Hi there, I’ve updated the blog post , there was an issue when copying the code from WordPress.
It should now display correctly, otherwise the code can also be taken from here
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
Hi Nigel, thanks for the feedback, MS did make some schema changes in advanced hunting, so the query needs to be updated, Computername is now Device name.
I have updated the query here
So just update the 2 hunting queries in your flow and then this should work again.
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:
| project DeviceName, SoftwareName, CveId, SoftwareVersion, VulnerabilitySeverityLevel
| join (DeviceTvmSoftwareVulnerabilitiesKB
| project AffectedSoftware, VulnerabilityDescription , CveId , CvssScore , IsExploitAvailable
| project CveId , SoftwareName , SoftwareVersion , CvssScore , VulnerabilitySeverityLevel, IsExploitAvailable , DeviceName
| distinct CveId, DeviceName, SoftwareName , SoftwareVersion, CvssScore , VulnerabilitySeverityLevel, IsExploitAvailable
| sort by CvssScore