strcat() to create URLs
From June 9 2020
You can use all kinds of cool functions in the Kusto Query Language. One of the most familiar for any programming languages is concat or in KQL syntax strcat()
One of its many uses can be combined with | extend to generate a URL that can be passed into Azure Workboooks, or sent as a link in a email body.
As an example maybe you want to generate a table to present in a weekly briefing of newly created Azure VMs and provide a URL link for the analyst to go to directly to investigate further.
You can write a KQL using strcat() like:
AzureActivity | where TimeGenerated >= ago(7d) | where ActivityStatusValue == "Succeeded" | where OperationName == "Create or Update Virtual Machine" | distinct Caller,CallerIpAddress,SubscriptionId,ResourceGroup,Resource //,TimeGenerated | extend VMUrl = strcat("https://portal.azure.com/#@tenantname.onmicrosoft.com/resource/subscriptions/", SubscriptionId, "/resourceGroups/", ResourceGroup, "/providers/Microsoft.Compute/virtualMachines/", Resource, "/overview") // "https://portal.azure.com/#@tenantname.onmicrosoft.com/resource/subscriptions/{SubscriptionId}/resourceGroups/{ResourceGroup}/providers/Microsoft.Compute/virtualMachines/{Resource}/overview" | project Resource,ResourceGroup,SubscriptionId,Caller,CallerIpAddress,VMUrl
or depending on Schema and Results:
AzureActivity
| where TimeGenerated >= ago(7d)
| where ActivityStatusValue == "Success"
| where OperationNameValue contains "Microsoft.Compute/virtualMachines/write"
| distinct Caller,CallerIpAddress,_ResourceId //,TimeGenerated
| extend VMUrl = strcat("https://portal.azure.com/#@tenantname.onmicrosoft.com/resource", _ResourceId, "/overview")
You will see that towards the end we use | extend to create an additional column in the table. After the | extend we declare a column name VMUrl = and begin to populate data. To populate data we then use strcat() and use a combination of strings "something" and also the ColumnNames above like SubscriptionId to dynamically generate values. All the while using comma , to sperate each string and value to concatenate together in a final URL.
The end result is:
Using these results we can pass them into a Azure Workbook tile or in a weekly briefing email delivered by a Logic App as a HTML Table.
