Using the Sentinel API to view data in a Workbook

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:  

• Protecting Teams
  https://techcommunity.microsoft.com/t5/azure-sentinel/protecting-your-teams-with-azure-sentinel/ba-p/1265761
• Sentinel Incidents
  https://www.managedsentinel.com/2020/05/10/sentinel-incidents-kpi/

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.

Sources:

1. Here the API is documentation, there is a Preview folder as well as a Stable branch, I’m mainly showing the preview features:

https://github.com/Azure/azure-rest-api-specs/tree/master/specification/securityinsights/resource-manager/Microsoft.SecurityInsights/preview/2019-01-01-preview/examples  

Note: The examples help you see the output you’ll get, and the API you need to call.

2. https://docs.microsoft.com/en-us/azure/azure-monitor/platform/workbooks-jsonpath  We will use some methods, so this link to JSON path may help you later.
3. You’ll need the Azure Monitor Workbooks documentation from Github: https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/Documentation/DataSources/DataSources.md 
   We will be using the Azure Resource Manager provider to retrieve the Azure Sentinel API data (or other sources)
   
   There is a nice example here: https://github.com/microsoft/Application-Insights-Workbooks/blob/master/Documentation/Samples/AlertDataARM.md
   
   I will expand upon this example now,  you have two choices:

Choice 1.

Take my example Workbook from Github and import it? Download and Instructions to import

Choice 2:

Build one from scratch (the best way to learn!)

Start here and follow the steps to create this workbook: https://github.com/microsoft/Application-Insights-Workbooks/blob/master/Documentation/Samples/AlertDataARM.md#setup-parameters

We will follow the using steps 1, 2 and onwards however for step #2, we also want to create a Workspace and resourceGroup parameter to make controlling the API easier.

Click Add parameter to create a new parameter. Use the following settings:

1. Parameter name: Subscription

2. Parameter type: Subscription picker

3. Required: Checked

4. Get data from: Default Subscriptions

5. Use the Save button in the toolbar to save this parameter.

6. Create a Parameter name: Workspace

7. Parameter type: Resource picker

8. Get data from: Query and use this query syntax

   
   
   

   resources
   | where type =~ 'microsoft.operationalinsights/workspaces'
   | project id, label = name

   
   Next
   
   

9. Create a Parameter name: resourceGroup

10. Parameter type: text

11. Hide Parameter: check this box – as we don’t need to see this in the finished workbook

12. Get data from: Query and use this query syntax.  This retrieves the Resource Group from the selected Workspace.

    
    

    resources
    | where type =~ 'microsoft.operationalinsights/workspaces
    | where id == "{Workspace}
    | project resourceGroup​

    
    
    
13. This should now look like this
    
    
    
    

Now let's GET some data from the Azure Sentinel API.

Click Add query to create a query control.

1.           Data source: Azure Resource Manager (Preview)

2.           Http Method: GET

This is a new step not in the guide: use a Path of: 

/subscriptions/{Subscription:id}/resourceGroups/{resourceGroup}/providers/Microsoft.OperationalInsights/workspaces/{Workspace:name}/providers/Microsoft.SecurityInsights/alertRules

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

1. Parameter: api-version

2. 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. 

Note:

The Connectors api, shows Microsoft sources (not CEF, or 3^rd 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. 


---------------------------------------------------------------------------------------

Taking this to the next level

Lets us now add a Merge query 

Click Add query to create a query control.

1.           Data source: MERGE

2.           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:

Summary:

So we now have a report using APIs without having to use any other tools, like Logic Apps or a Azure Function. 

Special thanks

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.