Forum Discussion

marktait19's avatar
marktait19
Copper Contributor
Feb 08, 2023

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
  • BaruchAbitbol's avatar
    BaruchAbitbol
    Copper Contributor

    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

    • marktait19's avatar
      marktait19
      Copper Contributor
      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
      • BaruchAbitbol's avatar
        BaruchAbitbol
        Copper Contributor
        you can in line 2 the following filter on order to limit it to 7 days
        | where Timestamp > ago (7d)
  • keith-mad's avatar
    keith-mad
    Copper Contributor
    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.
    • Jaideep445's avatar
      Jaideep445
      Copper Contributor
      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"

Resources