Over the past several months, I’ve been delving more and more into Azure Log Analytics and I must say that I absolutely love it. It’s incredibly fast and seeing the results come in right away is an instant gratification. And while this article is not going to be geared around KQL queries and how to use Log Analytics, it is going to focus on how to query Log Analytics via Powershell and the setup that’s involved with making it happen.
Allowing us to use Powershell to pull this information gives us the ability to automate and streamline events in a single pane of glass… and spoiler alert, this uses the Invoke-AzOperationalInsightsQuery cmdlet to query the workspace.
Table Of Contents
What is Log Analytics and what language does it use?
Log Analytics is Azure’s own Security Event and Incident Management (SEIM) tool and it gives administrators the ability to view log details within their tenant. There are several categories to query from such as AuditLogs, SignInLogs and RiskyUsers to name a few, and having those details on hand gives me the upper edge whenever I’m trying to figure out a problem.
Furthermore, Log Analytics uses Kusto Query Languange (KQL) in the backend to drive this functionality and it’s relatively easy to get started once you get the hang of formulating queries. What I like the most about it, is that you can set it up using tabular expressions which makes the overall query much easier to read.
However, one important thing to note is that everything is case-sensitive so just make sure you keep that in mind if you’re not seeing the results you’re expecting to see.
Setup Prerequisites
In order to get started, there are several requirements and prerequisites that need to be met to have a successful outcome. Let’s take a minute to list the requirements that are needed.
- Azure AD P1/P2 license to send to Log Analytics
- An active subscription
- The user querying the data will also need read permissions to the subscription
- An active Log Analytics workspace
- PowerShell Az Module (specifically Az.OperationalInsights)
- Global Administrator or Security Administrator Azure AD roles
I’m running Azure AD P2 license in my lab and my test account, Buzz Lightyear, is granted the Security Administrator role using PIM. This account also has read access to the subscription.
Create a Log Analytics Workspace
In addition to creating an Azure AD subscription, you’ll need to create a Log Analytics workspace to be able to specify that workspace when sending the logs.
To get there, I usually search for Log Analytics workspaces in top search bar but if you want to save yourself an extra click, here is the direct link. If you haven’t created a workspace yet, be sure to click “Create” to create one.
If you already have one created like I do, click on it and copy the Workspace ID. We’ll need this later.
Send logs to workspace via diagnostic settings
As mentioned, one of the requirements is to have a workspace created so we can send the data there. Since we already have a workspace created, let’s take the next step to ensure the logs we want to send to the workspace are enabled.
Back in the Azure Portal:
- Navigate to Azure Active Directory -> Diagnostic settings
- Create or Edit the setting
- Select the categories you would like to enable
- Ensure “Send to Log Analytics workspace” is checked
- Specify the subscription and Log Analytics workspace dropdown details accordingly
- Click Save
Use KQL to compile a query
At this point, you have now successfully configured your Log Analytics to capture events from the categories that you specified. Next is to actually use the product to retrieve data that you’re interested in.
The possibilities of exactly what you want to query are pretty much unlimited as far as I’m concerned. However, some of the most common queries I use on a regular basis are related to sign-in details, risk events and certain audit log details.
I’m going to demo a simple query to see how many times the user Buzz Lightyear has signed in over the past 7 days, but I would highly recommend you familiarize yourself with the KQL Quick Reference Microsoft guide for further learning.
//Since we are only interested in sign-in logs, we'll specify SignInLogs. //The TimeGenerated will specify how long you want to search back. SigninLogs | where TimeGenerated > ago(7d) | where Identity contains "Buzz Lightyear" or UserPrincipalName contains "[email protected]" | distinct UserPrincipalName, UserDisplayName, ClientAppUsed, AppDisplayName, AppId | limit 5
How to query log analytics via Powershell
Whenever you want to query Log Analytics via Powershell I would always recommend testing the query in the Azure Portal first to make sure you’re not spinning your wheels if something doesn’t work the way it’s intended.
Second, since we’re going to be passing in a relatively long string, we need to make sure that our quotes are properly handled. By that I mean if we’re using joins that require the ‘$’ character or properties that contain quotes like the sample above, we need to make sure those characters are either escaped or properly set in the overall query (using single and double quotes accordingly).
PS C:\> $Query = 'SigninLogs | where TimeGenerated > ago(7d) | where Identity contains "Buzz Lightyear" or UserPrincipalName contains "[email protected]" | distinct UserPrincipalName, UserDisplayName, ClientAppUsed, AppDisplayName, AppId | limit 5' PS C:\> $WorkspaceId = 'b0xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' PS C:\> $ResultList = Invoke-AzOperationalInsightsQuery -WorkspaceId $WorkspaceID -Query $Query -ErrorAction Stop | ` select -ExpandProperty Results PS C:\> $ResultList | ft UserPrincipalName UserDisplayName ClientAppUsed AppDisplayName AppId ----------------- --------------- ------------- -------------- ----- [email protected] Buzz Lightyear Browser OfficeHome 4765445b-32c6-49b0-83e6-1d937652… [email protected] Buzz Lightyear Browser Office365 Shell WCSS-Client 89bee1f7-5e6e-4d8a-9f3d-ecd60125… [email protected] Buzz Lightyear Browser Azure Portal c44b4083-3bb0-49c1-b47d-974e53cb… PS C:\>
Conclusion
That’s it, we now know how to query Log Analytics via Powershell. The best part is, you can use this technique to automate reports or simply use it in conjunction with other automation tools since it’s available to you through a command line interface.
This is something I use in the real world and it has helped me out tremendously, but I’m curious to know how this can apply to you and your environment.
