GitHub Repository: Azure/Azure-Sentinel-Notebooks
Path: blob/master/A Getting Started Guide For PowerShell AML Notebooks.ipynb
³²⁴⁹ views

Kernel: Python 3.8 - AzureML

A Getting Started Guide for Microsoft Sentinel notebooks with PowerShell

Notebook Version: 2.0

Data Sources Required:

Log Analytics - SecurityEvent (Optional)

**.Net Interactive installation is required! **:

To use this notebook, you will first need to install .Net Interactive. Please follow the instructions in the section of "Installing the required PowerShell modules". Current Ubuntu version is 18.04, and .NET SDK 5.0 is required. Details can be found in this article -> Microsoft Sentinel Notebooks + Powershell.

** About this notebook **:

This notebook takes you through the basics needed to get started with PowerShell notebooks that leverage Microsoft Sentinel data and APIs.

This notebook assumes that you are running this in an Azure Machine Learning notebooks environment created via the Microsoft Sentinel UI as this notebook has not yet been tested in other environments. Check the official documentation on creating a Microsoft Sentinel AML workspace/environment to learn more.

For a notebook that provides more definitive guidance to the notebook experience, launch the A Getting Started Guide for Microsoft Sentinel ML Notebooks notebook from the Microsoft Sentinel notebook UI. This notebook provides a step-by-step overview of the notebook experience as well as some tips and tricks on how to get the most out of your Jupyter notebook experience.

For more information as to why Juypter for security investigations, check out this excellent article Why Use Jupyter for Security Investigations

Lastly, don't forget to install .Net Interactive to use this notebook!

Installing .NET SDK and Interactive

Please execute the following cells to install .NET SDK and Interactive. You need install them once on a compute instance, and only once. If you have already install them, you may safely ignore these following 6 cells in this block.

In [ ]:

# Kernel: Python 3.8 - AzureML
# If you are not sure, you may check what kernels you already have on the compute instance:
!jupyter kernelspec list

In [ ]:

# Kernel: Python 3.8 - AzureML
# For Ubuntu 18.04
!wget https://packages.microsoft.com/config/ubuntu/18.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb
!sudo dpkg -i packages-microsoft-prod.deb

In [ ]:

# Kernel: Python 3.8 - AzureML
# Install .NET SDK 5.0
!sudo apt-get update; \
sudo apt-get install -y apt-transport-https && \
sudo apt-get update && \
sudo apt-get install -y dotnet-sdk-5.0

In [ ]:

# Kernel: Python 3.8 - AzureML
# Install the dotnet interactive global tool
!dotnet tool install --global Microsoft.dotnet-interactive

In [ ]:

# Kernel: Python 3.8 - AzureML
# Createï¿½symlinkï¿½between the dotnet interactive and thelocal bin directory 
!sudo ln -s /home/azureuser/.dotnet/tools/dotnet-interactive /usr/local/bin/dotnet-interactive

In [ ]:

# Kernel: Python 3.8 - AzureML
# Finally install the .NET kernel: C#, F#, and PowerShell
!dotnet interactive jupyter install

Once successfully complete the above steps, please:

** 1. Reload the page,
** ** 2. Select .NET (PowerShell) kernel
** ** 3. Then restart kernel **

Using Azure Notebooks

For this notebook we are going to be using PowerShell, so you will need to select the ".NET (PowerShell)" kernel in the dropdown on the top right corner of the notebook UI.

Once you have selected the right kernel, you are ready to move onto the next code cell.

Installing the required PowerShell modules

Code cells behave in the same way your code would in other environments, so you need to remember about common coding practices such as variable initialization and module/library imports. For this notebook you only need to make sure to install the required PowerShell modules since those are not installed by default.

In [ ]:

##Installs modules necessary to run notebook
Install-Module Az.Compute,Az.Resources,Az.OperationalInsights,powershell-yaml -Force

Working with PowerShell within a Jupyter notebook - The Basics

In this section we added a few tips and tricks to using PowerShell in a notebook!

While there are differences between running PowerShell in a notebook environment vs a local machine, most features are support.
If you plan on porting your existing scripts, there are some modifications might need to be made to account for these differences.
Due to an additional UI+kernel intecepting your PowerShell commands, be sure to not overload the output as this can cause a chokepoint.
Also, since the output goes to a white (or black if darkmode is set) UI, some output colors might need to be modified to be visible.

Work with the display

In [ ]:

##Get more details on your PowerShell environment
$PSVersionTable

In [ ]:

##Output Markdown
Out-Display "**THIS IS BOLD** _ITALICS_" -MimeType text/markdown

##Set foreground color
$host.UI.RawUI.ForegroundColor = [System.ConsoleColor]::Blue

##Default output colors can be changed 
$Host.PrivateData.WarningBackgroundColor = "White"
$Host.PrivateData.WarningForegroundColor = "Black"

##Change output color inline
Write-Host "Lets get started ..." -ForegroundColor Blue -BackgroundColor White

##You can write-host with the -nonewlinke flag
Write-Host "Hello " -NoNewline -ForegroundColor Red
Write-Host "World!" -ForegroundColor Blue

Output to HTML or markdown.

In [ ]:

##Output HTML
#!html
<b>Hello in HTML!</b>

In [ ]:

#!markdown

Write a **list** ...
* first item
* second item

...or a _table_...

|CallerIP    |PrincipalString |
|---------|--------|
|10.1.1.1    |[email protected]      |
|10.1.1.2    |[email protected]   |

Output from C# and visa versa. Yes, switching between DotNet languages is possible too

In [ ]:

#!csharp
var x="Hello using C#!";
Console.WriteLine(x);

#!pwsh
$x = "Hello using PowerShell!"
Write-Host $x

Download content

In [ ]:

##Download IOCs from the internet and use them in your investigation/hunts
$ips = (Invoke-WebRequest 'https://raw.githubusercontent.com/parthdmaniar/coronavirus-covid-19-SARS-CoV-2-IoCs/master/IPs').content
$ips

Prompt for information

In [ ]:

##You can ask for user input
Write-Host "Don't forget that execution of cells will block on prompts until you submit!"
$name = Read-Host -Prompt "What is the server name you would like to investigate? "
$name

Use progress bars or run commands in parallel

In [ ]:

##You can use a progress bar
For ($i=0; $i -le 100; $i++) {
    Write-Progress -Id 1 -Activity "Parent work progress" -Status "Current Count: $i" -PercentComplete $i -CurrentOperation "Counting ..."
 
    For ($j=0; $j -le 10; $j++) {
        Start-Sleep -Milliseconds 5
        Write-Progress -Parent 1 -Id 2 -Activity "Child work progress" -Status "Current Count: $j" -PercentComplete ($j*10) -CurrentOperation "Working ..."
    }
    
    if ($i -eq 50) {
        Write-Host "Doing the work around here..." -Foreground DarkBlue
        "Output goes here..."
    }
}

In [ ]:

##If you have long running task, that prints output to the screen
##use the -Parallel flag to run them in parallel, vastly improving performance.
##Example below runs one loop sequentially while the second example runs them in parallel

Write-Host "Number of seconds running commands sequentially: " -nonewline
(Measure-Command { 
    1..5 | ForEach-Object -Process {write-output "This is number $_"; sleep 2}
}).Seconds

Write-Host "Number of seconds running commands in parallel: " -nonewline
(Measure-Command { 
    1..5 | ForEach-Object -Parallel {write-output "This is number $_"; sleep 2}
}).Seconds

Microsoft Sentinel Configuration

Once we have set up our Jupyter environment with the libraries that we'll use in the notebook, we need to make sure we have some configuration in place. Some of the notebook components need addtional configuration to connect to external services (e.g. API keys to retrieve Threat Intelligence data). This includes configuration for connection to our Microsoft Sentinel workspace. For this notebook, we simply import the configuration from the config.json file that is created in your notebook explorer folder when you launch the notebook from the Microsoft Sentinel UI.

In [ ]:

##Get your configuration file settings
$nbcontentpath = "config.json"
if(!(test-path $nbcontentpath)){
    write-host "INFO: Your configuration path ($nbcontentpath) could not be located."
    write-host "INFO: Attempting to build the file path explicitly.  If this continues to be a problem, run 'dir' within the cell to find the current working directory and update the `$nbcontentpath variable accordingly."    
    $username = read-host "Enter the user name used for the notebook file explorer (the name of the top level folder):"
    $nbcontentpath = "users\$username\config.json"
}

##Path fix in case you picked up the cookie cutter configuration file (if you cloned repo from GitHub in terminal)
if(test-path $nbcontentpath){
    $content = gc $nbcontentpath | ?{$_ -match "cookiecutter"}
    if($content.Length -gt 0) {
        $nbcontentpath = "..\" + $nbcontentpath
    }    
}

try {
    $nbconfigcontent = Get-Content $nbcontentpath -ErrorAction Stop    
}
catch {
    write-host "ERROR: Your configuration path ($nbcontentpath) could not be located. Please fix before continuing further."    
}

##Set variables you will use throughout the notebook
$tenantId =  ($nbconfigcontent | ConvertFrom-Json).tenant_id
$subscriptionId = ($nbconfigcontent | ConvertFrom-Json).subscription_id
$resourceGroup = ($nbconfigcontent | ConvertFrom-Json).resource_group
$workspaceName = ($nbconfigcontent | ConvertFrom-Json).workspace_name
$workspaceId = ($nbconfigcontent | ConvertFrom-Json).workspace_id

Write-Host "SubscriptionId: " $subscriptionId 
Write-Host "TenantId: " $tenantId
Write-Host "WorkspaceId: " $workspaceId
Write-Host "workspaceName: " $workspaceName

Connect to your Microsoft Sentinel workspace

Once you have configured your notebook, now you can connect to your workspace.

Note:

We changed the default foreground colors in case you are using the "Light" notebook UI theme, since the yellow output will be hard to see. Feel free to modify.

In [ ]:

#Change the default colors used for PowerShell warnings as they make the Connect-AzAccount output difficult to see 
$Host.PrivateData.WarningBackgroundColor = "White"
$Host.PrivateData.WarningForegroundColor = "Black"

##Connect to selected subscription
Connect-AzAccount -UseDeviceAuthentication
Select-AzSubscription -SubscriptionId $subscriptionId -TenantId $tenantId

In [ ]:

##Configure the Log Analytics workspace
$workspace = $null
$workspaces = Get-AzOperationalInsightsWorkspace -ResourceGroupName $resourceGroup
if($workspaces.Length -gt 1) {
    Write-Host "INFO: Multiple workspaces detected." 
    foreach($wksp in $workspaces){
        if($wksp.Name -eq $workspaceName)    {
          $workspace = $wksp
        }        
    }    
}
else {
     $workspace = $workspaces 
}
Write-Host "INFO: Ensure that the workspace -- {"$workspace.Name"} is the intended target workspace before continuing to the next cell."   
$workspace

Access your hunting queries

Utilize the savedsearch API to download and run your hunting queries

In [ ]:

##Query your workspace using the savedsearches API
$savedSearchQueries = (Get-AzOperationalInsightsSavedSearch -ResourceGroupName $resourceGroup -WorkspaceName $workspaceName).value
$huntingQueries = $savedSearchQueries | %{$_.properties } | ? {$_.Category -match "Hunting Queries"}
Write-Host "Displaying the first 5 hunting queries..."
0..4 | foreach {Write-Host "Hunting Query Name: " -nonewline;$huntingQueries[$_].DisplayName}

Access your Microsoft Sentinel incidents

Utilize the Microsoft Sentinel API to download metadata regarding your incidents

Note: It could take a few seconds to download all of your incidents!

In [ ]:

##Build resource id
$worksapceId = "subscriptions/${subscriptionId}/resourceGroups/${resourceGroup}/providers/Microsoft.OperationalInsights/workspaces/${workspaceName}"
$incidentsResource = $worksapceId + "/" + "providers/Microsoft.SecurityInsights/incidents"

##Get incidents
$incidents = Get-AzResource -ResourceId $incidentsResource -ApiVersion "2019-01-01-preview"

##Only display a few incidents as the notebook has to translate the PowerShell output into the Jupyter UI
0..4 | foreach {Write-Host "Incident Number {$_}: " -nonewline;$incidents[$_].Properties}

In [ ]:

##Retrieve all of your incident counts and day they were created
$incidentsforgraph = $incidents | % {$_.properties} | select Title, Description, Severity, Status, Owner, createdTimeUtc, relatedAnalyticRuleIds, incidentUrl | ? {(get-date $_.createdTimeUtc) -gt (get-date).AddDays(-31d)} 

##Add formated property for the date they were created to make it easier to create graphs
foreach($incident in $incidentsforgraph)
{
  $incident | Add-Member -MemberType NoteProperty -Name NewDate -Value (get-date $incident.createdTimeUtc -format "yyyy-MM-dd") -Force
}

##Retrieve all of your incident counts and day they were created for the last 30 days
$openIncidents   =   $incidentsforgraph | ? {$_.Status -ne "Closed"} 
$closedIncidents =   $incidentsforgraph | ? {$_.Status -eq "Closed"} 
Write-Host "Total incidents (last 30 days)        : " $incidentsforgraph.count
Write-Host "Total Open Incidents (last 30 days)   : " $openIncidents.count
Write-Host "Total Closed Incidents (last 30 days) : " $closedIncidents.count

Chart your incidents using XPlot

Charts can be rendered using Xplot.Plotly. Here is a simple example on how to combine your incident data with XPlot.

In [ ]:

##At least one of each incident type (open or closed) must exists to run this cell
if(($closedIncidents -eq $null) -or ($openIncidents -eq $null)){
    Write-Host "You need at least one instance of each incident type (open or closed) to render the chart"
}
else {
    ##Create open incident plots
    $openSeries = [Graph.Scatter]::new()
    $openSeries.name = "Open Incidents"
    $openSeries.x = @(($openIncidents | group-object -Property NewDate).Name | % {$_.ToString()})
    $openSeries.y = (($openIncidents | group-object -Property NewDate | Select Count | %{$_.Count})) 

    ##Create closed incident plots
    $closeSeries = [Graph.Scatter]::new()
    $closeSeries.name = "Closed Incidents"
    $closeSeries.x =  @(($closedIncidents | group-object -Property NewDate).Name | % {$_.ToString()})
    $closeSeries.y =  (($closedIncidents | group-object -Property NewDate | Select Count | %{$_.Count})) 

    ##Display chart
    $chart = @($openSeries, $closeSeries) | New-PlotlyChart -Title "Open vs Closed Incidents"
    Out-Display $chart
}

Query your Microsoft Sentinel Data

Data within your Microsoft Sentinel workspace can be manipulated. My favorite part about working with notebooks is that I can extract values from one query or API call and use them as inputs to another query and/or API.

Note:

The query below requires the Heartbeat table. This was chosen as an example since it will reside in all Microsoft Sentinel workspaces.
For a more 'real world' example, pick another table or add your own query.

In [ ]:

##Add a timeframe variable
$timeframe = $null
do {
  $timeframe = Read-Host "How many days back would you like to query the data? (you must enter an integer for number of days):"
  $timeframe = $timeframe -as [int]
  if ($timeframe -eq $null) { write-host "You must enter a numeric value" }
}
until ($timeframe -ne $null)
write-host "You entered: $timeframe days as the input timespan."


##Query Heartbeat table
$query = "Heartbeat | where TimeGenerated >= ago($timeframe" + "d" + ") | take 10"
Write-Host "Query to run: " $query
##Run query and add results to object. Now you can use object to display data or graph
$queryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -query $query
#0..1 | foreach {Write-Host "Result Number {$_}: ";$queryResults.results[$_] }
$queryResults.Results | select TimeGenerated, ComputerIP, Computer, `
    OSType, RemoteIPLongitude, RemoteIPLatitude, RemoteIPCountry, `
    Resource, ResourceType, ComputerEnvironment | Format-Table

Match Microsoft Sentinel data with IOCs

You can also join data from external sources...

In [ ]:

##Using the example from an earlier cell, collect a list of IOCs and join them with IPs from a query
##Download IOCs from the internet and use them in your investigation/hunts
$ips = ((Invoke-WebRequest 'https://raw.githubusercontent.com/parthdmaniar/coronavirus-covid-19-SARS-CoV-2-IoCs/master/IPs').content).ToString() -Split "`n" 


##Query the Log Analytics table
$query = @"
    Heartbeat 
    | where TimeGenerated >= ago(1d) 
    | summarize RecordCount=count() by ComputerIPs=ComputerIP    
"@

##Run query and add results to object. Now you can use object to display data or graph
$queryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -query $query
$computerips=($queryResults.Results | Group-Object ComputerIPs).Group.ComputerIPs

##Compare IOC IPs to IPs from your logs
Write-Host "Example of comparing IPs to IOCs..."
foreach($computerip in ($computerips | select -first 10 )) {
    for($i=0;$i -lt $ips.Length;$i++) {
        write-host "IOC-IP:" $ips[$i] "does not match IP:" $ips[$i] "from logs!"
        if($i -gt 3) {break}
    }
}

Enriching data

Now that we have seen how to query for data, we can see how you can enrich data with additional data sources.

For this we are going to use an external threat intelligence provider to give us some more details about a URL.
The example cell below assuming you have a VirusTotal (VT) key in your yaml configuration file
If you don't already have a VirusTotal API key, signup here.
If not, you can either hardcode the VT key in the cell or run the A Getting Started Guide for Microsoft Sentinel ML Notebooks notebook for instructions on how to create the configuration file with your VirusTotal key included.

In [ ]:

##Get your configuration file settings
$configFileSuccess=$false
$yamlcontentpath = "msticpyconfig.yaml"
$yaml = $null
if(!(test-path $yamlcontentpath)) {
    write-host "INFO: Your configuration path ($yamlcontentpath) could not be located."
    write-host "INFO: Attempting to build the file path explicitly.  If this continues to be a problem, run 'dir' within the cell to find the current working directory and update the `$nbcontentpath variable accordingly."    
    $username = read-host "Enter the user name used for the notebook file explorer:"
    $yamlcontentpath = "users\$username\msticpyconfig.yaml"
}

##Path fix in case you picked up the cookie cutter configuration file (if you cloned repo from GitHub in terminal)
if(test-path $yamlcontentpath){
    $content = gc $yamlcontentpath #| ? {$_ -match "your-workspace-id"}
    if($content.Length -gt 0) {
        $yamlcontentpath = "..\" + $yamlcontentpath
    }    
}

##Set Yaml content
try {
    $configFileSuccess=$true
    $yamlcontent = Get-Content $yamlcontentpath -ErrorAction Stop -Raw
    $yaml = ConvertFrom-Yaml  $yamlcontent
}
catch {
    $configFileSuccess=$false
    write-host "ERROR: Your configuration path ($yamlcontentpath) could not be located. Please fix or hardcode the key before continuing further."    
}


##Harcode your key here if you haven't configured the yaml configuration file
##$APIKey = "<>"

##Set your API key and you are good to go
$APIKey = $yaml.TIProviders.VirusTotal.Args.AuthKey
if($APIKey -eq $null){
    $configFileSuccess = $false
}
else {
    $configFileSuccess = $true
}

if($configFileSuccess) {
    write-host "INFO: Your VT key was correctly configured. "  
    $yaml.TIProviders.VirusTotal
}
else {
    write-host "ERROR: Your VT key was not correctly configured. Please fix before continuing further."  
}

In [ ]:

##Input VT URL and Key
##Ideally, it would be better to retrieve the key from msticpyconfig.yaml
##$APIKey = '<VT_KEY_HERE>'

$Resource = Read-Host "Enter the URL you would like to submit (example: support.btcsupports.com):"

##Test URL
##$Resource = 'http://support.btcsupports.com/'

##Setup VT URI
$URI = 'https://www.virustotal.com/vtapi/v2/url/report'
$QueryResources =  $Resource -join ','
$OldEAP = $ErrorActionPreference
$ErrorActionPreference = 'SilentlyContinue'
$Body = @{'resource'= $QueryResources; 'apikey'= $APIKey; 'scan'=$scanurl}


# Start building parameters for REST Method invokation.
$Params =  @{}
$Params.add('Body', $Body)
$Params.add('Method', 'Get')
$Params.add('Uri',$URI)
$Params.Add('ErrorVariable', 'RESTError')
$ReportResult = Invoke-RestMethod @Params

$ErrorActionPreference = $OldEAP

if ($RESTError)
{
    if ($RESTError.Message.Contains('403'))
    {
throw 'API key is not valid.'
    }
    elseif ($RESTError.Message -like '*204*')
    {
throw 'API key rate has been reached.'
    }
    else
    {
throw $RESTError
    }
}

foreach ($URLReport in $ReportResult)
{
    $URLReport.pstypenames.insert(0,'VirusTotal.URL.Report')
    Write-host "Resource:" $URLReport.resource
    Write-host "Last Scan:" $URLReport.scan_date
    Write-host "VT Link:" $URLReport.permalink
    Write-host "Positive Scans:" $URLReport.positives
    Write-host "Total Scans:" $URLReport.total     
    $URLReport
}

Get your watchlist aliases and data

Retrieve your watchlist aliases and data by running the code below

Note: You must be part of the private preview program to use this feature. Sign-up at www.aka.ms/SecurityPrP to get started!

In [ ]:

##Retrieve watchlist results
$queryResults = $null
$watchlistalias = read-host "Enter your watchlist alias to get the results:"
$query = "_GetWatchlist('$watchlistalias')"
$queryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -query $query
$queryResults.Results | Select Watchlistitem | select -first 100