Add Saved Queries to Log Analytics

loganalytics

Hey Folks, in today’s article want to show you how to add saved queries to log analytics in all subscriptions for the same tenant.

Log Analytics is a service which allows you to store all logs from all your Azure services in one place. It allows in easy way to create query useful for us and gather all information which are needed for us. In one of the article my PS bro Paweł show you how to get sign-in logs from Azure using PowerShell – really recommend to check it if you never use this service before.

My script will make life easier for all Azure admins, and allows to create specific queries in all subscription. All Azure users which have an access to Log Analytics will be able to check all queries stored without asking admin to do that 🙂

Script is quite easy. In first way you should define query which will be populated to Azure. $Queries array allow to multiple creation of saved queries to Log Analytics, so you can add all of them at one time. Next it gather all subscriptions from tenant in which you are logged in and store them in variable. Later it iterate on each subscription, gather all log analytics and adding query across all of them and that’s all.

Script:

In below example I show you how to add query with Powershell which will gather all security events logs from Azure Security Center. Of course you can adjust it for your needs with specific category.

$Queries = @"
[
    {
        "query": "SecurityEvent | where TimeGenerated > ago(30d)",
        "displayName": "Security Center Events",
        "category": "SecurityCenterEvents",
        "Version":  1
    }
]
"@ | ConvertFrom-Json

$SubscriptionsArray = Get-AzureRmSubscription

foreach ($Subscription in $SubscriptionsArray) {
    Select-AzureRmSubscription -SubscriptionId $Subscription.Id
    Write-Output "Working on subscription $($subscription.Name)"
    $LogAnalytics = Get-AzureRmOperationalInsightsWorkspace
    foreach ($LogAnalytic in $LogAnalytics) {

        foreach ($query in $Queries) {
            Try{
                $id = $query.category + "|" + $query.displayName
                New-AzureRmOperationalInsightsSavedSearch -ResourceGroupName $LogAnalytic.ResourceGroupName -WorkspaceName $LogAnalytic.Name -SavedSearchId $id -DisplayName $query.DisplayName -Category $query.Category -Query $query.Query -Version $query.Version
                Write-Output "Succefully added search query $($query.DisplayName) to Log Analytic Workspace $($LogAnalytic.Name)."
            }
            Catch{
                $exc = $_.Exception.Message
                Write-Output "Unexpected error occured during adding query $($query.DisplayName) for Log analytic Workspace $($LogAnalytic.Name). Error: $exc"
            }
        }

    }
}

Hope it will be usefull for some of you 😉

Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.