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: CV...
microsoft defender for endpoint
Microsoft Defender for Office 365
threat hunting
  • BaruchAbitbol's avatar
    BaruchAbitbol
    Mar 01, 2023
    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
slouies's avatar
slouies
Copper Contributor
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 🙂
Jaideep445's avatar
Jaideep445
Copper Contributor
Nov 01, 2023

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

 

 

Resources