Forum Discussion
marktait19
Feb 08, 2023Copper Contributor
Security Recommendation - is it available in any table in KQL query editor
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
- 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
- BaruchAbitbolCopper Contributor
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- marktait19Copper ContributorThank 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- BaruchAbitbolCopper Contributoryou can in line 2 the following filter on order to limit it to 7 days
| where Timestamp > ago (7d)
- keith-madCopper ContributorI 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.
- Jaideep445Copper ContributorI 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"
- Susan_HerbstCopper Contributorqr code