Security Recommendation - is it available in any table in KQL query editor

[marktait19]

Hi.

When in Security Recommendations, I can enter a CVE reference, and there is a column in the display for "Security Recommendation" (please see attached screenshot).

So for example, for: CVE-2020-1938

The Security Recommendations advises: Update Apache Tomcat

Is there any table available within the KQL editor, that will display that recommendation for a given CVE?

I've checked in DeviceTvmSoftwareVulnerabilitiesKB and DeviceTvmSoftwareVulnerabilities - however the Security Recommendations field is not available in either of those.

Any help would be much appreciated,

Thanks, Mark

[#2]

qr code

[#3]

I don't think you can. I'm currently waiting on the Recommendations section to be enabled via API calls so I could use a LogicApp to post daily updates on the Recommendations.

[#4]

@marktait19 

Hey,

There is no easy way to use KQL to retrieve the table of the "Security recommendations" through "Advanced Hunting".
You need to "Join" two tables based on the "ConfigurationID"

Just let me know if have any further questions:

DeviceTvmSecureConfigurationAssessment
| project DeviceName, ConfigurationId
| join (DeviceTvmSecureConfigurationAssessmentKB
| project ConfigurationId, ConfigurationName, ConfigurationDescription, RiskDescription, ConfigurationCategory, ConfigurationImpact, ConfigurationSubcategory, RemediationOptions
)
on ConfigurationId
| project ConfigurationId, DeviceName, ConfigurationName, ConfigurationDescription, RiskDescription, ConfigurationCategory, ConfigurationImpact, ConfigurationSubcategory, RemediationOptions
| distinct DeviceName, ConfigurationId, ConfigurationName, ConfigurationDescription, RiskDescription, ConfigurationCategory, ConfigurationImpact, ConfigurationSubcategory, RemediationOptions
| sort by DeviceName asc

[#5]

Thank you for your suggestion.

When I run this, I'm only getting 1 device returned (with 118 results - I'm looking over the last 30 days), but I can't see anything in the query which would limit the results.

I'll keep working with the query you've provided though -it must be a restriction on my end thats limiting it.

Cheers, Mark

[#6]

you can in line 2 the following filter on order to limit it to 7 days
| where Timestamp > ago (7d)

[#7]

you can add*
*In order

[#8]

Hi - sorry, I wasn't clear - it's just not returning the number of results I'd expect. It should be listing 100's of devices, but I'm only seein 1 device listed in all 118 results.

Thanks again, Mark

[#9 · Accepted solution]

fixed it.

try this:

DeviceTvmSecureConfigurationAssessment
| project DeviceName, ConfigurationId
| join kind=inner ( DeviceTvmSecureConfigurationAssessmentKB
| project ConfigurationName, ConfigurationDescription, RiskDescription, ConfigurationCategory, ConfigurationImpact, ConfigurationSubcategory, RemediationOptions, ConfigurationId
)
on ConfigurationId
| project ConfigurationId, DeviceName, ConfigurationName, ConfigurationDescription, RiskDescription, ConfigurationCategory, ConfigurationImpact, ConfigurationSubcategory, RemediationOptions
| distinct DeviceName, ConfigurationId, ConfigurationName, ConfigurationDescription, RiskDescription, ConfigurationCategory, ConfigurationImpact, ConfigurationSubcategory, RemediationOptions
| sort by DeviceName asc

[#10]

Thank you so much - all the best!

[#11]

@BaruchAbitbol 

Works well. Is there a way we can exclude the devices with onboarding status "can be onboarded" ? Number of devices in the network do read some extra devices and they all get added in the recommendations list. So, trying to exclude those devices. I did bit of modification to the query but not sure if this is correct or not.

DeviceTvmSecureConfigurationAssessment

| project DeviceName, ConfigurationId

| join kind=inner ( DeviceTvmSecureConfigurationAssessmentKB

| project ConfigurationName, ConfigurationDescription, RiskDescription, ConfigurationCategory, ConfigurationImpact, ConfigurationSubcategory, RemediationOptions, ConfigurationId

)

on ConfigurationId

| join kind=inner (DeviceInfo

| where OnboardingStatus !contains "can be onboarded" and ExposureLevel contains "high")

on DeviceName

| project ConfigurationId, DeviceName, ConfigurationName, ConfigurationDescription, RiskDescription, ConfigurationCategory, ConfigurationImpact, ConfigurationSubcategory, RemediationOptions

| distinct DeviceName, ConfigurationId, ConfigurationName, ConfigurationDescription, RiskDescription, ConfigurationCategory, ConfigurationImpact, ConfigurationSubcategory, RemediationOptions

| sort by DeviceName asc

[#12]

Hi,
Theoretically, it should work.
I'm just concerned that we will see some duplicate entries because also in "DeviceInfo" a device can appear more than once.

[#13]

When I tested the query, it did show one device more than once, but for a separate recommendation, which I don't mind as I would like to see all recommendations. Unless someone has a better idea.

[#14]

Hello,
I am very interested in this query.
Hower, it seems that some entries in "DeviceTvmSecureConfigurationAssessment" have "ConfigurationId" values that are not found in the "DeviceTvmSecureConfigurationAssessmentKB" table.
Any idea ?

[#15]

Yeah, I just checked and there about 27 less configuration ID's in DeviceTvmSecureConfigurationAssessmentKB. Not sure if there is any other common field that can be used. May be let's try to understand what you are trying to accomplish and will see if there is any other alternative.

[#16]

I have been able to connect to API via Power BI OData connection.
Following query can be used as is in OData query.

let
Source = OData.Feed("https://api.securitycenter.microsoft.com/api/recommendations", null, [Implementation="2.0"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Vulnerabilities", "Vulnerabilities.1"}, {"Software", "Software.1"}, {"recommendationName", "Recommendation Name"}, {"productName", "Product Name"}, {"vendor", "Vendor"}, {"recommendedVersion", "Recommended Version"}, {"recommendationCategory", "Category"}, {"subCategory", "Sub Category"}, {"severityScore", "Severity Score"}, {"publicExploit", "Public Exploit"}, {"remediationType", "Remediation Type"}, {"exposedMachinesCount", "Exposed Machines"}})
in
#"Renamed Columns"

[#17]

Thanks a lot for your answer.
To answer your question :
what I'm trying to do is to extract security recommandations for specifics devices (on which we have put a manual tag).
We have the email addresses of the owners of those devices from an external source.
So we would be able to send an email to those users on regular basis, with the list of security recommandations they should implement.

Our scope is a little bit specific : our users are students, academic staff and administrative staff of a University 🙂

[#18]

Hope this helps. To me it does but everyone is different. 

What I have done for my environment is created two Tabs in Power BI and connected them to the Defender API directly, using API.

First one is for "App and Security Recommendations". Following OData query can be used for this. I used the following query in Blank editor. This different from OData.

let
Source = OData.Feed("https://api.securitycenter.microsoft.com/api/recommendations", null, [Implementation="2.0"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Vulnerabilities", "Vulnerabilities.1"}, {"Software", "Software.1"}, {"recommendationName", "Recommendation Name"}, {"productName", "Product Name"}, {"vendor", "Vendor"}, {"recommendedVersion", "Recommended Version"}, {"recommendationCategory", "Category"}, {"subCategory", "Sub Category"}, {"severityScore", "Severity Score"}, {"publicExploit", "Public Exploit"}, {"remediationType", "Remediation Type"}, {"exposedMachinesCount", "Exposed Machines"}})
in
#"Renamed Columns"

It has several columns, but not all of them are useful for everyone. So, I use very few of them in the actual Report. I use columns: Category, Sub Category, Recommendation Name, Product Name, Public Exploit, Exposed Machines (Number), Recommended Version and Remediation Type.

Then I have second Tab where I have again connected to the API for finding what applications are installed on devices. But now I am using more of KQL query to get the data. From there I can find if the application is on the latest version or not. If not, that qualifies for email to be sent to the device owner or an action for IT guys to update the application. That gives me Three columns: DeviceName, SoftWareName and SoftwareVersion. Devices with old Software Version qualify for emails to be sent out.

let
AdvancedHuntingQuery = "DeviceTvmSoftwareInventory | where DeviceName !="""" | distinct SoftwareName, DeviceName, SoftwareVersion",

HuntingUrl = "https://api.securitycenter.microsoft.com/api/advancedhunting",

Response = Json.Document(Web.Contents(HuntingUrl, [Query=[key=AdvancedHuntingQuery]])),

TypeMap = #table(
{ "Type", "PowerBiType" },
{
{ "Double", Double.Type },
{ "Int64", Int64.Type },
{ "Int32", Int32.Type },
{ "Int16", Int16.Type },
{ "UInt64", Number.Type },
{ "UInt32", Number.Type },
{ "UInt16", Number.Type },
{ "Byte", Byte.Type },
{ "Single", Single.Type },
{ "Decimal", Decimal.Type },
{ "TimeSpan", Duration.Type },
{ "DateTime", DateTimeZone.Type },
{ "String", Text.Type },
{ "Boolean", Logical.Type },
{ "SByte", Logical.Type },
{ "Guid", Text.Type }
}),

Schema = Table.FromRecords(Response[Schema]),
TypedSchema = Table.Join(Table.SelectColumns(Schema, {"Name", "Type"}), {"Type"}, TypeMap , {"Type"}),
Results = Response[Results],
Rows = Table.FromRecords(Results, Schema[Name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(TypedSchema, (c) => {c{0}, c{2}}))

in Table

[#19]

I did reply earlier to this one but it looks like my reply got lost somewhere. At least I can't see it. So, posting again. Apologies if you see this as a duplicate.

I hope this helps. It helps me but everyone is different. I have created two Tabs/Queries in Power BI. One is for "App and Security Recommendations" and the other one is for Finding Apps on a Device.

First query is OData query and it reads as:
let
Source = OData.Feed("https://api.securitycenter.microsoft.com/api/recommendations", null, [Implementation="2.0"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Vulnerabilities", "Vulnerabilities.1"}, {"Software", "Software.1"}, {"recommendationName", "Recommendation Name"}, {"productName", "Product Name"}, {"vendor", "Vendor"}, {"recommendedVersion", "Recommended Version"}, {"recommendationCategory", "Category"}, {"subCategory", "Sub Category"}, {"severityScore", "Severity Score"}, {"publicExploit", "Public Exploit"}, {"remediationType", "Remediation Type"}, {"exposedMachinesCount", "Exposed Machines"}})
in
#"Renamed Columns"

Columns I select are in the screenshot here.

Second query for finding apps on devices is in blank editor in Power BI. It is more of a KQL query. It reads as:

let
AdvancedHuntingQuery = "DeviceTvmSoftwareInventory | where DeviceName !="""" | distinct SoftwareName, DeviceName, SoftwareVersion",

HuntingUrl = "https://api.securitycenter.microsoft.com/api/advancedhunting",

Response = Json.Document(Web.Contents(HuntingUrl, [Query=[key=AdvancedHuntingQuery]])),

TypeMap = #table(
{ "Type", "PowerBiType" },
{
{ "Double", Double.Type },
{ "Int64", Int64.Type },
{ "Int32", Int32.Type },
{ "Int16", Int16.Type },
{ "UInt64", Number.Type },
{ "UInt32", Number.Type },
{ "UInt16", Number.Type },
{ "Byte", Byte.Type },
{ "Single", Single.Type },
{ "Decimal", Decimal.Type },
{ "TimeSpan", Duration.Type },
{ "DateTime", DateTimeZone.Type },
{ "String", Text.Type },
{ "Boolean", Logical.Type },
{ "SByte", Logical.Type },
{ "Guid", Text.Type }
}),

Schema = Table.FromRecords(Response[Schema]),
TypedSchema = Table.Join(Table.SelectColumns(Schema, {"Name", "Type"}), {"Type"}, TypeMap , {"Type"}),
Results = Response[Results],
Rows = Table.FromRecords(Results, Schema[Name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(TypedSchema, (c) => {c{0}, c{2}}))

in Table

Columns used from this query are Device Name, Software Name and Software Version.

So, devices with old Software version qualify for emails to be sent out or can be action items for IT guys to update those devices.

I really hope this helps.

[#20]

This is definitely helpful !
Thank you so much !