KQL embedded in Power Query to query Defender Log through Advanced Hunting

[VaibhavBhosle]

let
AdvancedHuntingQuery = "
DeviceTvmSoftwareVulnerabilitiesKB
| where VulnerabilitySeverityLevel == "High"
",
HuntingUrl = "https://api.securitycenter.microsoft.cxxxxxxxxxxxxxxxx",
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]),
Results = Response[Results],
Rows = Table.FromRecords(Results, Schema[Name])
in
Rows

[VaibhavBhosle]

Thanks @Expiscornovus  much appreciated.

[Expiscornovus · Accepted solution]

Hi @VaibhavBhosle,

To be clear, your question is how to use KQL queries in your Power Query, correct?

If that is the case I would suggest to have a look at a couple of Microsoft examples.

I found the below is example in the Azure Data Explorer connector documentation. It demonstrates how you could use a function for your KQL queries and use those in your Power Query M.

The example is from a different type of data source, but in concept you should be able to use the same for your setup.

https://learn.microsoft.com/en-us/power-query/connectors/azure-data-explorer#provide-parameters-to-a...