Elan Shudnow's Blog

MVP Logo

Retrieving Activity Log Data from Azure Log Analytics – Part 3

by Leave a Comment

Part 1

  • What is Log Analytics?
  • What is the Activity Log?
  • Two methods for ingesting Activity Log Data into Log Analytics
    • Option #1 – Old/Current Method Being Deprecated where you go into your Log Analytics Workspace and hook the Activity Log directly into the workspace
    • Option #2 – New Method leveraging Activity Log Diagnostic Settings

Part 2

  • Leverage Kusto Query Language (KQL) to build a custom shared dashboard for Virtual Machines that have been created

Part 3

  • Leverage the KQL Query we build within PowerShell to pull data into a variable which will then be exported to CSV

Execute our KQL Query via PowerShell

It is possible to leverage a KQL Query and gather the results via PowerShell. This can be leveraged when wanting to dump data to CSV, TXT, HTML, etc… The command used to do this is Invoke-AzOperationalInsightsQuery. It is also possible to leverage the Rest API. In this article, we will discuss using Invoke-AzOperationalInsightsQuery.

The KQL Query we built in Page 2 is as follows:

workspace("ShudLogAnalytics").AzureActivity
| where TimeGenerated > ago(120d)
| where ResourceProviderValue =~ "MICROSOFT.COMPUTE" and OperationNameValue =~ "MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE" and ActivitySubstatusValue =~ "Created"
| extend ResourceName = iff(isnull(Resource), Properties_d.resource, Resource)
| project TimeGenerated, OperationNameValue, ActivityStatusValue,ActivitySubstatusValue,ResourceName,ResourceGroup,Caller,SubscriptionId

When calling the Query via PowerShell, it will one sentence of text. The following will be the string we use with single quotes around the Query. It was simply moving the | where line onto the previous line and having a space in between and then having quotes around the entire query.

'workspace("ShudLogAnalytics").AzureActivity | where TimeGenerated > ago(120d) | where ResourceProviderValue =~ "MICROSOFT.COMPUTE" and OperationNameValue =~ "MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE" and ActivitySubstatusValue =~ "Created" | extend ResourceName = iff(isnull(Resource), Properties_d.resource, Resource) | project TimeGenerated, OperationNameValue, ActivityStatusValue,ActivitySubstatusValue,ResourceName,ResourceGroup,Caller,SubscriptionId'

There are some additional commands we want to add to a .ps1 PowerShell Script file. These are as follows:

  • Workspace Name
  • Workspace Resource Group Name
  • Pull the Workspace Object into a Variable
  • Execute the Query against the Workspace

Let’s look at each of these.

Specifying the Log Analytics Workspace name and Resource Group name

In the PowerShell Script, we will specify the Log Analytics Workspace name and Resource Group name.

$WorkspaceName = 'ShudLogAnalytics'
$WorkspaceResourceGroupName = 'LogAnalytics'

Pulling the Log Analytics Workspace Object into a Variable

We need to actual pull in the Workspace into a variable using Get-AzOperationalInsightsWorkspace. This Workspace variable will be leveraged when running Invoke-AzOperationalInsightsQuery.

$Workspace = Get-AzOperationalInsightsWorkspace -ResourceGroupName $WorkspaceResourceGroupName -Name $WorkspaceName

So far, our PowerShell ps1 script file looks as follows:

$WorkspaceName = 'ShudLogAnalytics'
$WorkspaceResourceGroupName = 'LogAnalytics'
$Workspace = Get-AzOperationalInsightsWorkspace -ResourceGroupName $WorkspaceResourceGroupName -Name $WorkspaceName

Using our KQL Query to pull our Log Analytics Data

Finally, we want to initiate our query against our workspace and pulling our results into a variable. We do this by running the following command:

$QueryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query 'workspace("ShudLogAnalytics").AzureActivity | where TimeGenerated > ago(120d) | where ResourceProviderValue =~ "MICROSOFT.COMPUTE" and OperationNameValue =~ "MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE" and ActivitySubstatusValue =~ "Created" | extend ResourceName = iff(isnull(Resource), Properties_d.resource, Resource) | project TimeGenerated, OperationNameValue, ActivityStatusValue,ActivitySubstatusValue,ResourceName,ResourceGroup,Caller,SubscriptionId'

Notice how for the -workspace switch, we specify $Workspace? That is because we had pulled our Log Analytics Workspace into the $Workspace variable and when we are submitting our query, we are telling Invoke-AzOperationalInsightsQuery to use that workspace.

So far, our PowerShell ps1 script file looks as follows:

$WorkspaceName = 'ShudLogAnalytics'
$WorkspaceResourceGroupName = 'LogAnalytics'
$Workspace = Get-AzOperationalInsightsWorkspace -ResourceGroupName $WorkspaceResourceGroupName -Name $WorkspaceName
$QueryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query 'workspace("ShudLogAnalytics").AzureActivity | where TimeGenerated > ago(120d) | where ResourceProviderValue =~ "MICROSOFT.COMPUTE" and OperationNameValue =~ "MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE" and ActivitySubstatusValue =~ "Created" | extend ResourceName = iff(isnull(Resource), Properties_d.resource, Resource) | project TimeGenerated, OperationNameValue, ActivityStatusValue,ActivitySubstatusValue,ResourceName,ResourceGroup,Caller,SubscriptionId'

In order to see the results, we need to add the following:

$QueryResults.results

So far, our PowerShell ps1 script file looks as follows:

WorkspaceName = 'ShudLogAnalytics'
$WorkspaceResourceGroupName = 'LogAnalytics'
$Workspace = Get-AzOperationalInsightsWorkspace -ResourceGroupName $WorkspaceResourceGroupName -Name $WorkspaceName
$QueryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query 'workspace("ShudLogAnalytics").AzureActivity | where TimeGenerated > ago(120d) | where ResourceProviderValue =~ "MICROSOFT.COMPUTE" and OperationNameValue =~ "MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE" and ActivitySubstatusValue =~ "Created" | extend ResourceName = iff(isnull(Resource), Properties_d.resource, Resource) | project TimeGenerated, OperationNameValue, ActivityStatusValue,ActivitySubstatusValue,ResourceName,ResourceGroup,Caller,SubscriptionId'
$QueryResults.results

Go ahead and modify your script to include your Log Analytics Workspace name and Resource Group name. Then save the file as a .ps1 file.I saved mine as C:\Blog\Blog.ps1.

Executing our script

Login to Azure PowerShell by using Connect-AzAccount and signing in via the Interactive Login prompt. This will require the Az Module rather than the old AzureRM Module. To install the Az Module, see documentation here.

Login to Azure using PowerShell AZ Module

Once authenticated, you will see the Subscription Context that is selected.

Verify the Azure Subscription Context Az PowerShell Module is set to

If you have multiple subscriptions, do a Get-AzSubscription, take note of the Subscription ID, and do a Select-AzSubscription -SubscriptionID <SubscriptionID>. Keep in mind, because we are pulling Log Analytics Data, you must be in the Subscription where the Log Analytics Workspace is located.

In PowerShell, let’s navigate to the directory our PowerShell Script is stored in. Because I stored my script in C:\Blog, I’ll do a cd C:\blog.

Execute the script by typing in the following:

.\Blog.ps1

The results we get back are as follows. Notice how VMs that were created prior to 3/5/2019 have a ResourceName and the VMs I created for this blog (LogAnalytics01 and LogAnalytics02) also have a ResourceName. That is because of the KQL extend I did in Part 2 of this article series. It is successfully working in PowerShell as well.

PS C:\Blog> .\Blog.ps1                                                                                                                                                                                                                                                                                                                                

TimeGenerated          : 2020-02-04T20:41:41.238Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : ElanTag01
ResourceGroup          : ElanTag01
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2020-02-04T20:49:30.039Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : ElanTag02
ResourceGroup          : ElanTag02
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2020-02-04T20:57:48.976Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : ElanTag03
ResourceGroup          : ElanTag03
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2020-02-04T21:17:15.582Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : ElanTag04
ResourceGroup          : ElanTag04
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2020-02-04T21:24:01.535Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : ElanTag05
ResourceGroup          : ElanTag05
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2020-02-03T16:32:43.159Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : InstancesTestVM
ResourceGroup          : InstancesTestVM
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2020-03-05T14:52:40.832Z
OperationNameValue     : MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE
ActivityStatusValue    : Accept
ActivitySubstatusValue : Created
ResourceName           : loganalytics01
ResourceGroup          : LOGANALYTICSBLOG
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2020-03-05T14:53:49.011Z
OperationNameValue     : MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE
ActivityStatusValue    : Accept
ActivitySubstatusValue : Created
ResourceName           : loganalytics02
ResourceGroup          : LOGANALYTICSBLOG
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2020-01-22T17:43:33.112Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : vmsqlblog01
ResourceGroup          : vmsqlblog01
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2020-01-22T18:04:26.3Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : vmssms01
ResourceGroup          : vmssms01
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2019-12-12T16:59:09.581Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : vmblog02
ResourceGroup          : vmblog
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2019-12-13T02:37:25.251Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : sqlvm01
ResourceGroup          : sqlvm01
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2019-12-10T22:49:37.201Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : iac
ResourceGroup          : iac
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2019-12-11T03:52:46.258Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : ansible
ResourceGroup          : ansible
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2019-12-11T04:25:01.36Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : iac
ResourceGroup          : iac
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

TimeGenerated          : 2019-12-11T04:54:51.35Z
OperationNameValue     : Microsoft.Compute/virtualMachines/write
ActivityStatusValue    : Accepted
ActivitySubstatusValue : Created
ResourceName           : vmblog01
ResourceGroup          : vmblog
Caller                 : [email protected]
SubscriptionId         : 959bxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

Exporting our results to CSV

The last line of our PowerShell Script file is written as follows:

$QueryResults.results

Let’s leverage the pipeline feature of PowerShell and export the data to CSV. Modify that line of code to become:

$QueryResults.results | Export-CSV -Path "C:\Blog\Results.csv" -NoTypeInformation

Our code will now look as follows:

WorkspaceName = 'ShudLogAnalytics'
$WorkspaceResourceGroupName = 'LogAnalytics'
$Workspace = Get-AzOperationalInsightsWorkspace -ResourceGroupName $WorkspaceResourceGroupName -Name $WorkspaceName
$QueryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query 'workspace("ShudLogAnalytics").AzureActivity | where TimeGenerated > ago(120d) | where ResourceProviderValue =~ "MICROSOFT.COMPUTE" and OperationNameValue =~ "MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE" and ActivitySubstatusValue =~ "Created" | extend ResourceName = iff(isnull(Resource), Properties_d.resource, Resource) | project TimeGenerated, OperationNameValue, ActivityStatusValue,ActivitySubstatusValue,ResourceName,ResourceGroup,Caller,SubscriptionId'
$QueryResults.results | Export-CSV -Path "C:\Blog\Results.csv" -NoTypeInformation

Go ahead and execute the PowerShell Script file again.

This time we have no output in the PowerShell Window.

Run the PowerShell Script

If we go to C:\Blog, we should see a file called Results.csv. Which we do.

Verify the Results.CSV file has been exported

After opening the file, we can see that all of our columns are filled out.

Verify the Results.CSV data

Thanks for reading all the way to the end. Hopefully you found this valuable. And if you have any questions, as always, feel free to comment.

Reader Interactions

Leave a Reply

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