Workbooks are quite amazing. Its quite often I look at someone else’s work in a Workbook and blown away, not knowing that you could do whatever it is that I saw. One such amazing thing Workbooks can do is Datasets as Parameters. Meaning we can get data from Log Analytics, Azure Resource Graph, Application Insights, even APIs. Once we have this data, we can join it with subsequent KQL queries elsewhere in the Workbook. This is quite powerful and really useful trick, for a variety of reasons.
- Querying an external API
- Log Analytics, App Insights, etc datasets in other queries in your workbook
- Using parameters as “let” statements with Azure Resource Graph since ARG doesn’t support let statements
Another reason to do this, is sometimes JSON Path can be slow, especially with larger datasets.
Example 1
In this example I query the Azure Policy API to get exemptions data. Unfortunately for me, I did all this work only to have exemptions actually added to Azure Resource Graph. But, that doesn’t mean this isn’t a useful example. At least my work wasn’t for nothing.
These are the settings I used to query the API. Setting the language to JSON, Multiline and pre-format the JSON data. I then use that parameter in another parameter to format it.
In this parameter I set the query type to Log Analytics and set the parameter as a Workspace parameter. As long as you have access to run queries in this workspace, this will work fine. To reference your previous parameter you need to use todynamic(). In my examples It would not work until I put double quotes around it and used “escapejson” after the parameter name.
I then reference the exemptionsPack parameter through the workbook like this.
Right now you might be saying well the exemptions pack query section looks a lot like the query above. You would be right, except the second parameter extracts the data and removes the value property, making it significantly easier to reference the data in the actual query. Additionally because ARG doesn’t support let statements, what I did was call the policyresources table and just do distinct tenantId cause it was easy and the least amount of data.
Now we can do summaries, joins and anything else on the data to provide insights. Like I said this one is no longer necessary as the exemptions have been added to the policy resources table. I’ve already updated my workbook to use those tables instead. So be on the look out for a new policy insights workbook from me.
Example 2
This is from another in-flight workbook. With this example, I take 3 queries, 1 from Azure Resource Graph, 1 from Log Analytics, 1 from App Insights. Bring them all into a Log Analytics workspace and combine them. I start with an inventory of Log Analytics and App Insights in Azure Resource Graph.
and because I couldn’t get the whole query in, here it is.
resources
| where type =~ 'microsoft.insights/components'
or type =~ 'microsoft.operationalinsights/workspaces'
or type =~ 'microsoft.operationalinsights/clusters'
| join kind=leftouter (
resources
| where type =~ 'microsoft.insights/components'
| extend IngestionMode = iff(properties.IngestionMode == "ApplicationInsights", "Classic", tostring(properties.IngestionMode)),
ChildAppInsights= id
| where IngestionMode == "LogAnalytics"
| extend id = tostring(properties.WorkspaceResourceId)
| summarize LinkedAppInsights = count(), AppInsights = makeset(ChildAppInsights) by id)
on id
| extend IngestionMode = iff(properties.IngestionMode == "ApplicationInsights", "Classic", tostring(properties.IngestionMode))
| extend ParentWorkspace = iff(IngestionMode == "LogAnalytics", tostring(properties.WorkspaceResourceId),''),
Retention = tostring(iff(type =~ 'microsoft.insights/components', properties.RetentionInDays, properties.retentionInDays)),
Quota = iff(properties.workspaceCapping.dailyQuotaGb == -1, "No Quota", properties.workspaceCapping.dailyQuotaGb),
WorkspaceRBAC = tostring(properties.features.enableLogAccessUsingOnlyResourcePermissions)
| project id = tolower(id), name, subscriptionId, IngestionMode, ParentWorkspace, LinkedAppInsights, Retention, Quota, WorkspaceRBAC
| extend bag = pack("id",id,"name", name,"subscriptionId", subscriptionId, "IngestionMode",IngestionMode, "ParentWorkspace",ParentWorkspace,"LinkedAppInsights", LinkedAppInsights, "Retention", Retention, "Quota", Quota, "WorkspaceRBAC", WorkspaceRBAC)
| summarize tostring(make_list(bag))
The Log Analytics and App Insights parameters look like this. (App Insights not pictured, cause its literally the same… but for App Insights)
Then joining them all together with a Log Analytics based query in the Workbook
A series of Let statements and then some left outer joins. The only problem with this approach is if one of the query fails or doesn’t have any data the entire query here will fail. Currently trying to figure out a way to work around that.
Example 3
The DCR Toolkit Workbook is an incredibly complex workbook with many neat tricks to reverse engineer. In this example, they use a parameter to grab NSA event IDs from an external site. They then bring that into another parameter and parse and pivot the data while referencing other parameters and then use this parameter to help build the DCR. This is by far the most complicated example.
The parameter is then referenced in the nsaIds underneath the Windows DCR group in the Workbook.
The parameter is set to use the Workspace parameter, so as long as you have access to run queries in the workspace(s) that are set, this will bring the nsaRecommendedEvents parameter into this query with the print statement. One note here, you need to use todynamic() when referencing these parameters. Additionally, you may have noticed from my queries above I use escapejson and they used parsejson. I have no idea why but when I tried parsejson with my examples, I could never get it to work. I suspect it has something to do with the format of the data we’re each pulling in.
// First Parse the NSA JSON print events=todynamic({nsaRecommendedEvents:parsejson}) | extend events = events['Recommended Events to Collect'] // First we pivot the data | mv-apply events on ( extend Category = tostring(bag_keys(events)[0]) | extend IdentityCheck = iff('{Identity}' == 'Ingest', Category in ("Account Usage", "Pass the Hash Detection", "Remote Desktop Logon Detection"), false), NetworkCheck = iff('{Network}' == 'Ingest', Category in ("Network Policy", "Windows Firewall"), false), SystemCheck = iff('{System}' == 'Ingest', Category in ("Application Whitelisting", "Application Crashes", "System or Service Failures", "Clearing Event Logs", "Software and Service Installation", "Kernel Driver Signing", "Windows Defender Activities", "DNS/Directory Services", "Certificate Service", "Boot Events", "System Integrity") , false), UserCheck = iff('{User}' == 'Ingest', Category in ("PowerShell Activities", "Mobile Device Activities") , false) | extend description = events[Category].description | extend events = events[Category].events | where IdentityCheck == true or IdentityCheck == true or NetworkCheck == true or UserCheck == true ) | mv-apply events on ( extend eventName = tostring(bag_keys(events)[0]) | extend eventId = tostring(bag_keys(events[eventName])[0]) | extend properties = events[eventName][eventId] | project-away events ) | evaluate bag_unpack(properties) | project eventId = split(replace_regex(eventId,@'\s',@''),','), eventName | mvexpand eventId | summarize by toint(eventId), eventName | sort by eventId asc | project packed = pack_all()
This parameter is then used in yet another parameter to join together a whole bunch of parameters. Merge-caption
Conclusion
Azure Workbooks are an extremely powerful data visualization tool for your Azure estate. Using Parameters as datasets enables many scenarios for your Workbooks. From querying APIs to creating data sets from Log Analytics there are many possibilities.