Forum Discussion
Security Recommendation - is it available in any table in KQL query editor
- 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
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
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
- you can in line 2 the following filter on order to limit it to 7 days
| where Timestamp > ago (7d)- you can add*
*In order- 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