- 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
- 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,SubscriptionIdWhen 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 $WorkspaceNameSo far, our PowerShell ps1 script file looks as follows:
$WorkspaceName = 'ShudLogAnalytics'
$WorkspaceResourceGroupName = 'LogAnalytics'
$Workspace = Get-AzOperationalInsightsWorkspace -ResourceGroupName $WorkspaceResourceGroupName -Name $WorkspaceNameUsing 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.resultsSo 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.resultsGo 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.
Once authenticated, you will see the Subscription Context that is selected.
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.ps1The 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-xxxxxxxxxxxxExporting our results to CSV
The last line of our PowerShell Script file is written as follows:
$QueryResults.resultsLet’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" -NoTypeInformationOur 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" -NoTypeInformationGo ahead and execute the PowerShell Script file again.
This time we have no output in the PowerShell Window.
If we go to C:\Blog, we should see a file called Results.csv. Which we do.
After opening the file, we can see that all of our columns are filled out.
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.
What if I wanted to set my computer as a variable? Let say I have this integration that will check eventID code per specific servers and i set from my powershell script the following:
Param($computer)
.
.
.
If (!$computer){
$computer = “FQDN”
}
(So it will provide or select random computer name)
And I want to have the same random selection or param in my Log analytics query let say
QueryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query ‘event | where Computer contains “$computer”
I am planning to use with my job scheduler so i need to find ways to randomly pick computer name in my query based on what is inputed in param as the $computer value
I tried for many hours to figure out how to get a variable to instantiate within the query with no success. You’ll have to get all the results and filter it afterwards.
Great post Elan, Danilyn, you can try this
$Query = “event | where Computer contains `”$computer`””
$QueryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query $Query