Export and Import Saved Queries and Functions from one Sentinel Workspace to Another

[rpargman]

I have been getting so much value out of Azure Sentinel, custom log types, and custom functions to parse logs and make them easy to query in KQL (I have Sysmon, Suricata and Zeek among others). I've spent a lot of time creating and fine-tuning saved queries and functions in one workspace, and now I'd like to easily export all of those saved queries and functions into another workspace.

So much of Sentinel is built on APIs, it seems like there should be a programatic way to export these into a json structure (or something) and then import those into another workspace, but I can't find it in the documentation. I know that I can take these one at a time, copy and paste from one workspace into another. That would be OK with one or two custom functions, but I have over 30. I'd like to automate this if possible. Does anyone know a way to get that done? I'm comfortable with writing custom code if needed.

[#2 · Accepted solution]

@rpargman You need to use the Log Analytics REST API to get access to those.  Take a look at: https://docs.microsoft.com/en-us/rest/api/loganalytics/savedsearches  to get started

[#3]

Thank you! That Log Analytics API is amazing. I didn't realize that it could get the queries, too.

[#4]

@rpargman , @Gary Bushey :  the powershell cmdlets might be an easier start than the API: create, remove, get

[#5]

Oh thank you! I'll check those out, too. I appreciate the tip.

[#6]

@rpargmanI had to do this the other day

# Get-AzContext -ListAvailable
# Set the source workspace
Set-AzContext -Subscription "<Source Subscription>"
$ResourceGroup = "<Source RG>"
$WorkspaceName = "<Source WorkSpace"

# Only export saved queries from these categories
$Categories = ("sec", "usage", "proxy", "win", "o365")

$ExportedSearches = (Get-AzOperationalInsightsSavedSearch -ResourceGroupName $ResourceGroup -WorkspaceName $WorkspaceName).Value.Properties | Where-Object { $Categories -contains $_.Category }

# Set the destination workspace
Set-AzContext -Subscription "<Dest Subscription>"
$ResourceGroup = "<Dest RG>"
$WorkspaceName = "<Dest WorkSpace"

# Import Saved Searches
foreach ($search in $ExportedSearches) {
    $id = $search.Category + "|" + $search.DisplayName
    New-AzOperationalInsightsSavedSearch -Force -ResourceGroupName $ResourceGroup -WorkspaceName $WorkspaceName -SavedSearchId $id -DisplayName $search.DisplayName -Category $search.Category -Query $search.Query -Version $search.Version
}

[#7]

This is very useful, but I'd appreciate some help doing this slightly differently please:
1. dump searches to a file
2. import the file back to sentinel (say after making some changes)
eg:
Get-AzOp.... > outfile
New-AzOp... < outfile

I can't  figure out the correct format for outfile and I don't know the import command using a file.

[#8]

@SocInABox just use JSON to serialise it:

export-searches.ps1 (./export-searches.ps1 myRG myWorkspace > searches.json)

$ResourceGroup =  $args[0]
$WorkspaceName =  $args[1]

(Get-AzOperationalInsightsSavedSearch -ResourceGroupName $ResourceGroup -WorkspaceName $WorkspaceName).Value.Properties | ConvertTo-Json

You can easily add, remove, update queries in the JSON file then:

import-searches.ps1 (./import-searches.ps1 myRG myWorkspace searches.json)

$ResourceGroup =  $args[0]
$WorkspaceName =  $args[1]
$InputFile = $args[2]

foreach ($search in  Get-Content $InputFile | ConvertFrom-Json) {
    $id = $search.Category + "|" + $search.DisplayName
    Write-Output "Importing $($search.DisplayName) ($($search.Category))"
    New-AzOperationalInsightsSavedSearch -Force -ResourceGroupName $ResourceGroup -WorkspaceName $WorkspaceName -SavedSearchId $id -DisplayName $search.DisplayName -Category $search.Category -Query $search.Query -Version $search.Version
}

[#9]

Thanks @pemontto , I love you man :D.

Seriously much appreciated I needed that convert-to/from json example.

[#10]

Hey thanks for the great input in this thread. I wanted to check in before I go about trying to do this with functions. My question is basically the same. I was hoping to find a way to do this with the az-cli and core functions. Not so much as exporting, which would be a bonus. But editing and saving. I went through and did this in the GUI yesterday and it was a time consuming to say the least. Would you by chance have any guidance on that?

[#11]

@arkscout Not quite sure what your requirements are, but if you want to update/overwrite you could probably use Set-AzOperationalInsightsSavedSearch. However I think using New-AzOperationalInsightsSavedSearch with -Force overwrites/updates based on the id.

[#12]

@pemontto , thanks again for your excellent queries.
Maybe someone can use these variations I made for my purpose:

Title:  Scripts for House Cleaning your Saved Searches in Sentinel

./export-search.ps1<resource group> <workspace> > test.json

#export ALL saved searches

$ResourceGroup =  $args[0]
$WorkspaceName =  $args[1]

(Get-AzOperationalInsightsSavedSearch -ResourceGroupName $ResourceGroup -WorkspaceName $WorkspaceName).Value.Properties | ConvertTo-Json

./export-search-bycategories.ps1<resource group> <workspace> > test.json

#export only the saved search categories specified in the $Categories variable below.

$ResourceGroup =  $args[0]

$WorkspaceName =  $args[1]

# Only export saved queries from these categories - comma separated

$Categories = ("test")

(Get-AzOperationalInsightsSavedSearch -ResourceGroupName $ResourceGroup -WorkspaceName $WorkspaceName).Value.Properties | Where-Object { $Categories -contains $_.Category }

|ConvertTo-Json

./import-searches.ps1 <resource group> <workspace> test.json

# use this to import after making your changes from the above export json

$ResourceGroup =  $args[0]

$WorkspaceName =  $args[1]

$InputFile = $args[2]

foreach ($search in  Get-Content $InputFile | ConvertFrom-Json) {

    $id = $search.Category + "|" + $search.DisplayName

    Write-Output "Importing $($search.DisplayName) ($($search.Category))"

    New-AzOperationalInsightsSavedSearch -Force -ResourceGroupName $ResourceGroup -WorkspaceName $WorkspaceName -SavedSearchId $id -DisplayName $search.DisplayName -Category $search.Category -Query $search.Query -Version $search.Version

}

./remove-searches.ps1 <resource group> <workspace>  test.json

# use this to REMOVE saved searches

# note: if you remove the last saved search from a category it will automatically remove the category folder

$ResourceGroup =  $args[0]

$WorkspaceName =  $args[1]

$InputFile = $args[2]

foreach ($search in  Get-Content $InputFile | ConvertFrom-Json) {

    $id = $search.Category + "|" + $search.DisplayName

    Write-Output "Removing $($search.DisplayName) ($($search.Category))"

    Remove-AzOperationalInsightsSavedSearch -ResourceGroupName $ResourceGroup -WorkspaceName $WorkspaceName -SavedSearchId $id -debug

}

./remove-query.ps1 <resource group> <workspace> "<folder category>|<query name>"

# use this to remove a single query

# pro tip - you can NOT remove a category/folder but removing the last query will automatically remove the folder

$ResourceGroup =  $args[0]

$WorkspaceName =  $args[1]

$query =  $args[2]

 Write-Output "Removing query: $query"

Remove-AzOperationalInsightsSavedSearch -ResourceGroupName $ResourceGroup -WorkspaceName $WorkspaceName -SavedSearchId $query -debug