Azure Sentinel Incidents in PowerBI

Introduction

When I was writing my blog post about downloading the Azure Sentinel Incidents, I got to thinking about what else could you do with them? As I had been working with PowerBI I thought, why not give that a shot? I would rather have these show up in an Azure Sentinel Workbook but, so far, there is no way to get these to obtain external data.

PowerBI

If you have not heard of PowerBI, stop what you are doing and go to https://powerbi.microsoft.com/en-us/. It is an amazing reporting tool that allows users to, somewhat, easily create their own reports. I mainly use the free desktop version but there is also a paid online version that offers more features.

In any event, if you do not have it, download and install the free desktop version. I would recommend the 64bit version. I will say right now that I am far from an expert in it and even I can make some nice reports.

Getting Started

First things, first. You need to know where to get the data. You can read my Your first Azure Sentinel REST API call to learn how to work with the unofficial Azure Sentinel REST APIs.

As stated in the post, the URL to get the cases will be

https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.OperationalInsights/workspaces/{workspaceName}/providers/Microsoft.SecurityInsights/cases?api-version=2019-01-01-preview

making all the needed replacements for {subscriptionId}, {resourceGroupName}, and {workspaceName}. 

If you follow the instructions in the blog post and run all the commands in PowerShell you will get a listing of, at most, the first 200 incidents. If you happen to have more than 200 incidents, you will notice an additional entry at the very bottom called nextLink.

This is the same URL as above but followed with a very long string of apparently random numbers, letters, dashes, and underscores. This is known as a skiptoken and it tells Azure Sentinel where to start looking for the incidents so that we will skip the first 200, since they have already been returned, and starting at the 201st incident, return the next 200.

This will become more relevant when we start looking into PowerBI.

PowerBI – Getting Data

Start PowerBI desktop from your computer. When introduction Window opens, click on the Get Data link

This will open the Get Data window. Click on Other and then select Blank Query from the bottom of the list and click Connect

This in turn will open the Power Query Editor where you can view the results of your query and make changes. For now, click on the Advanced Editor button to open the Advanced Editor window

As you may have guess, the Advanced Editor window is where you can enter and modify your query completely. You can think of it as when working with a web page editor, like WordPerfect, you can either drag and drop sections on to the page or you can switch to the HTML view and enter the HTML directly. This is tjhe equivalent of entering the HTML for PowerBI.

We are going to enter some code, written in a language known as Power Query, that will tell PowerBI to go out to the URL listed above to get our Incidents and, if it exists, use the nextLink entry to go and get the next batch of Incidents. Repeat this process until nextLink no longer appears in our listings.

Rob Reilly wrote Loading paged and related data from MS Graph API into Power BI using a recursive Power Query function about how this code works and I used it as the basis of my code and it is based on recursion. Remember, to understand recursion you must first understand recursion (one of my favorite geek jokes)

The code is listed below (note that you will need to translate the & in line 6 into an & for the code to work correctly. Dang syntax highlighter).

let
    GetCases = (Path) =>
        let 
            Source = Json.Document(Web.Contents(Path)),
            NextList = @Source[value],
            result = try @NextList & @GetCases(Source[nextLink]) otherwise @NextList
        in
            result,
    AllCases = GetCases("https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.OperationalInsights/workspaces/{workspaceName}/providers/Microsoft.SecurityInsights/cases?api-version=2019-01-01-preview")
in
    AllCases

I’ll explain the code below:

Line 1: This lets PowerBI know that we are starting some code.

Line 2: This is setting up a new function, called GetCases, that takes one parameter, called Path.

Line 3: This just lets PowerBI know that we are starting the code for the function.

Line 4: This is making a REST call, using the URL passed into the Path parameter, and converting the output into JSON.

Line 5: This is taking the value section of the call, which is the listing of Incidents in this case. Remember when we got the listing of the incidents when running the commands in PowerShell, there was a value entry and a nextLink entry

Line 6: This is the recursive call. It is using PowerBI’s version of try/catch to take what information we obtained in Line 5 and add to it whatever information we get by calling GetCases again with the value stored in the nextLink section of the data returned from the call in line 4. If this throws an error then there is nothing in the nextLink section so just return the data in NextList which we obtained from line 4 (this is the code after the otherwise keyword)

Line 7 & 8: This tells PowerBI that we are done with the function

Line 9: This makes the initial call to GetCases with the starting URL. Make sure to add your own subscription ID, Resource Group Name, and Workspace Name

Line 10 and 11: This tells PowerBI that we are done with the outside code block.

Copy all this code and replace the existing code in the Advanced Editor window and click Done. Don’t forget to make the change to line 6 and the URL substitutions! It should look similiar to what is below

If prompted with the PowerBI login page shown below, select Organizational account and login using the same credentials you use to login to Azure.

If everything went well, you should see a window like the one below. If not double check to make sure there are no typos.

So now you have data but it is not that usable. Luckily PowerBI makes it easy to expand this information into something more usable.

Right click on the List entry (black text with orange background) and NOT any of the lines under it. Select the To Table entry. If your menu does not look like the one below, make sure you right clicked on the word List itself and not anything beneath it.

This will open a new window called To Table. Leave all the entries as is and click OK.

Now the Power Query Editor window will look like

This still doesn’t look too usable. It is just one column that is made up of Records. PowerBI can help with that as well!

On the right side of the Column1 entry is an icon with arrows pointing both ways.

This will allow you expand that column into its underlying parts. Clicking on it will show you a listing of the field that make up the record as shown below

Select the fields that you want to have expanded and click OK. Don’t worry about selecting all the columns, you can delete them later if you decide you don’t need them. This will expand the column as shown below.

It may be hard to tell from the image above, but the Column1.Properties column is also comprised of Records and also the expansion icon on the right side. Click it to expand that column.

You can continue to expand the other columns that have a Record data type as needed.

There are also some, like Column1.properties.labels that have a List data type. You can expand these as well but what they will do is duplicate the row for each entry in the List. So if a column is comprised of the values A, B, and C, there will be one row that has A as the value that column, one with B, and one with C.

So now we have all the Incidents in a format we can use. However the names of the fields are somewhat confusing. This is easily remedied. You can right click on the header of each column and select Rename. This will allow to rename the column to something more usable. You can also delete any columns that you realize you do need by clicking on Remove.

Now that you have your data, you can create reports in PowerBI. I am not going to go into the details how to create these reports but the image below shows a very simple report that shows all the open Incidents by severity, the number of open incidents assigned to each person, and the number of open cases organized by how long they have been opened. I will tell the last chart requires that you create a column that is not included in the REST API and you may need to change the data type of some of the fields.

Not sure how well it comes across in the image below but all 3 charts are linked. I clicked on the my name in the Open Incidents by Assigned To and both the other charts were filtered to show only the information pertaining to me. So the bottom chart shows just those cases that are mine. If you look just to the left of the 80 on the X-axis you can see the there are tiny smudges that are dark blue. Those represent cases assigned to me.

Conclusion

PowerBI can be used with the Azure Sentinel REST APIs to get Incident, and other information, that can then be used to generate reports. By using recursion in the web calls, you can assure that you get all the information you need to properly create the reports.

This is just the top of the iceberg of what you can do with these reports. You can easily create reports that show how long incidents were active before they were closed, who has closed the most incidents based on status, and what was the overall severity makeup of your closed incidents to just name a few.

Have fun experimenting!