Sometimes when you need data for your reporting you have to ingest and store it, here are two recent use cases that do that, they get Teams and Azure Sentinel Incident data and keep the data within a workspace, to query:
However using a Workbook we can also get API data, I'm using the Azure Sentinel's API to demonstrate this, but you can use others, the Workbook will retrieve the data but not store it. If you need to store the data use a method like the ones above.
Here the API is documentation, there is a Preview folder as well as a Stable branch, I’m mainly showing the preview features:
Note: You can see I have used the parameters we created for the Subscription, Workspace and Resource Group. We didn’t tick the option to allow multiple sections, so this API is aligned to one Workspace at a time. You can see the last part list the API section, in this case "/alertRules" You can read the other API sections from the API docs I listed in the sources section.
Add the api-version parameter in the Parameters tab
Value: 2019-01-01-preview (or the one asked for, if the query has an error it will list versions to try)
Note: Supported api-versions are listed in the Azure Sentinel API docs, see the sources section above.
Press “Run Query” and you should get JSON data returned
Now move to the “Result Setting” tab, this is where we will use JSONpath – see sources section earlier in this doc for extra details.
JSON path table to: $.value
You can then pick out columns to display, I have used these:
Now when you run the Query you just get the columns you requested, the [column id] is a friendly name (no spaces), and the [Column JSON path] is the location to read from in the JSON file.
You can now repeat but add a new query for each API you wish to see. This shows Alerts in one query and Alert templates in the next. I have highlighted a few fields, we'll use these later.
My example workbook, has Alert, Alert templates, Incidents, Connectors and Aggregated Cases.
This is just an example so you can edit and tweak the outputs to your own requirements.
The Connectors api, shows Microsoft sources (not CEF, or 3rd parties), also as some of the APIs are in preview you need to validate the data returned, and swap to the latest api or stable branch when they are released.
This allows us to take the ALERTS results and Alert Templates and “merge” them together
I have used this example to show how the Alerts and Alert templates queries look together. Essentially its like KQL JOIN, mapping the two displayNames
I have also moved some data so it appears next to each other in the final report output. In particular I wanted "lastUdateUtc" and "createdDateUtc" next to each other, showing when the Alert template was first created and when it was last updated - data from two separate APIs.
The final report looks like this:
So we now have a report using APIs without having to use any other tools, like Logic Apps or a Azure Function.
To Bindiya for the original inspiration, our Azure Sentinel engineers Mor and Ely for answering my questions (a lot of questions, sorry Mor!) and to Liron for actually showing me how to get it all working - thank you.