Executing Azure KQL Query using PowerShell?

Question

Executing Azure KQL Query using PowerShell?

EnterpriseArchitect 6,041

How can I use PowerShell to execute the Azure KQL query?

AzureActivity  
| where TimeGenerated >= ago(90d)  
| project  _ResourceId

Any help would be greatly appreciated.

Shweta Mathur 30,296 Reputation points Microsoft Employee Moderator

2023-12-11T05:27:00.51+00:00

Hi @EnterpriseArchitect ,

Just checking in to see if the below answer helped. If this answers your query, please don’t forget to click "Accept Answer" which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.
JamesTran-MSFT 36,906 Reputation points Microsoft Employee Moderator

2023-12-13T19:34:11.4066667+00:00

@EnterpriseArchitect

I wanted to check in and see if you had any other questions or if you were able to resolve this issue?

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.

If the information helped address your question, please Accept the answer. This will help us and also improve searchability for others in the community who might be researching similar information.
george zaki 5 Reputation points

2024-09-02T02:09:04.6666667+00:00
george zaki 5 Reputation points

2024-09-02T02:09:53.7166667+00:00

AzureActivity

| where TimeGenerated >= ago(90d)

| project _ResourceId
EnterpriseArchitect 6,041 Reputation points

2024-09-02T02:18:53.4566667+00:00

@george zaki ,
The OpenAPI posting is empty ?

Accepted answer

2 additional answers

Your answer

Shweta Mathur 30,296 Reputation points Microsoft Employee Moderator

2023-12-11T05:27:00.51+00:00

Hi @EnterpriseArchitect ,

Just checking in to see if the below answer helped. If this answers your query, please don’t forget to click "Accept Answer" which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.
JamesTran-MSFT 36,906 Reputation points Microsoft Employee Moderator

2023-12-13T19:34:11.4066667+00:00

@EnterpriseArchitect

I wanted to check in and see if you had any other questions or if you were able to resolve this issue?

If you have any other questions, please let me know. Thank you for your time and patience throughout this issue.

If the information helped address your question, please Accept the answer. This will help us and also improve searchability for others in the community who might be researching similar information.
george zaki 5 Reputation points

2024-09-02T02:09:04.6666667+00:00
george zaki 5 Reputation points

2024-09-02T02:09:53.7166667+00:00

AzureActivity

| where TimeGenerated >= ago(90d)

| project _ResourceId
EnterpriseArchitect 6,041 Reputation points

2024-09-02T02:18:53.4566667+00:00

@george zaki ,
The OpenAPI posting is empty ?

Answer 1

Luis Arias 8,621 Volunteer Moderator

Hello EnterpriseArchitect,

You can use the bellow code to query your log analytics, first you need to login and setup the AzContext on powershell:

Connect-AzAccount
Set-AzContext -Subscription "<Your-Subscription>"
$la_name = "<Your-LogAnalytics-name>"
$la_rg = "<Loganalytics-RG>"
$la_id = (Get-AzOperationalInsightsWorkspace -Name $la_name -ResourceGroupName $la_rg).CustomerID
## Take in consideration you have permission as reader over workspace
$query = "AzureMetrics 
| where TimeGenerated >= ago(1d)"

$outQuery.Results | Out-GridView

Let me know if this is helpful.

Luis

EnterpriseArchitect 6,041 Reputation points

2023-12-07T14:27:08.8+00:00

@Luis Arias , Thank you for the reply.
So in my case, do I need to collect all logs first to single Log Analytics?
Luis Arias 8,621 Reputation points Volunteer Moderator

2023-12-07T15:02:03.5733333+00:00

Not necessarily that will depend on your use case, both options have their own advantages and disadvantages.

Here more architecture information:

https://learn.microsoft.com/en-us/azure/azure-monitor/logs/workspace-design

You can get some insights from this thread:

https://techcommunity.microsoft.com/t5/azure-observability/single-vs-multiple-instances-of-log-analytics/m-p/116960

Yellulla, Jagadish 10

@Luis Arias I have a KQL query which has joins (I used two tables)). Can this query be executed in Powershell.
For me, KQL query with single table worked. When I used two tables, saying "Bad Request"

Invoke-AzOperationalInsightsQuery : Operation returned an invalid status code 'BadRequest'
At line:1 char:17
+ ... ryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace - ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [Invoke-AzOperationalInsightsQuery], ErrorResponseException
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.OperationalInsights.Query.InvokeOperationalInsightsQuery

EnterpriseArchitect 6,041 Reputation points

2024-09-01T11:07:28.5566667+00:00

Thanks @Luis Arias

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 3

george zaki 5

Connect-AzAccount

Set-AzContext -Subscription "<Your-Subscription>"

$la_name = "<Your-LogAnalytics-name>"

$la_rg = "<Loganalytics-RG>"

$la_id = (Get-AzOperationalInsightsWorkspace -Name $la_name -ResourceGroupName $la_rg).CustomerID

Take in consideration you have permission as reader over workspace

$query = "AzureMetrics

| where TimeGenerated >= ago(1d)"

$outQuery.Results | Out-GridView

Share via

Executing Azure KQL Query using PowerShell?

2 additional answers

Take in consideration you have permission as reader over workspace

Your answer