Import external data in KQL

There are multiple ways on how you can import your asset management information: either from a CSV-File , from a SQL Table or with a Custom Table (my prefered way).

From CSV-File

let asset = externaldata (
Host:string, domain:string ) [@"https://raw.githubusercontent.com/flo7000/serverownervulnerabilitynotification/main/example.csv"
] with (ignoreFirstRecord=true);
asset

From SQL Table

# Convert-TableToKQLString is from my custom PowerShell Module
$sqlServerName = "yourDB.database.windows.net"
$sqlDatabaseName = "db01"
$sqlSelectQuery = "SELECT hostLookup, contact, oe FROM [dbo].[asset]"
$sqlAccessToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

$table = Invoke-Sqlcmd -ServerInstance $sqlServerName -Database $sqlDatabaseName -Query $sqlSelectQuery -AccessToken $sqlAccessToken
if ($table.Rows.Count -eq 0) { Write-Output "No data found in SQL query. Exiting script."; exit }
$assetString = Convert-TableToKQLString -table $table -fields @('hostLookup', 'contact', 'oe')

$kqlQuery = @"
let asset = datatable(HostSrc: string, contact: string, oe: string)
[
    $assetString
];
DeviceTvmSoftwareVulnerabilities
| extend HostSrc = replace_string(DeviceName, ".flo7000.ch", "")
| join kind=inner (asset) on HostSrc

...

From Custom Table

$kqlQuery = @"
ServerAsset_CL
| where TimeGenerated > ago(1d)
"@

Export onprem SQL Data to Custom Table

You can do this with assetcustomtablesyncer.ps1.

Export Azure Arc Servers to Custom Table

You can do this with arcsync.ps1.

---