### Get Basic Azure Lighthouse Definition Info

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_lighthouse_definition

Retrieve the name, ID, tenant IDs, and names associated with Azure Lighthouse definitions. Useful for auditing and understanding the basic configuration of cross-tenant management setups.

```sql
select
  name,
  id,
  managed_by_tenant_id,
  managed_by_tenant_name,
  managed_tenant_name,
  type
from
  azure_lighthouse_definition;
```

```sql
select
  name,
  id,
  managed_by_tenant_id,
  managed_by_tenant_name,
  managed_tenant_name,
  type
from
  azure_lighthouse_definition;
```

--------------------------------

### Get Disk Size and Performance Info

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_compute_disk

Gain insights into the performance and size of each disk for optimizing resource allocation and identifying bottlenecks.

```sql
select
  name,
  disk_size_gb as disk_size,
  disk_iops_read_only as disk_iops_read_only,
  disk_iops_read_write as provision_iops,
  disk_iops_mbps_read_write as bandwidth,
  disk_iops_mbps_read_only as disk_mbps_read_write
from
  azure_compute_disk;
```

```sql
select
  name,
  disk_size_gb as disk_size,
  disk_iops_read_only as disk_iops_read_only,
  disk_iops_read_write as provision_iops,
  disk_iops_mbps_read_write as bandwidth,
  disk_iops_mbps_read_only as disk_mbps_read_write
from
  azure_compute_disk;
```

--------------------------------

### Install Steampipe Azure Exporter CLI

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_app_service_plan

Install the Steampipe Exporter CLI for Azure using a shell script. This allows you to extract data from Azure resources without a full Steampipe database setup.

```bash
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- azure
```

--------------------------------

### List Spot VMs and Eviction Policy

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_compute_virtual_machine

Find all virtual machines of the 'Spot' priority and display their name, VM ID, and eviction policy. Useful for cost management.

```sql
select
  name,
  vm_id,
  eviction_policy
from
  azure_compute_virtual_machine
where
  priority = 'Spot';
```

```sql
select
  name,
  vm_id,
  eviction_policy
from
  azure_compute_virtual_machine
where
  priority = 'Spot';
```

--------------------------------

### Get Basic Build Definition Info

Source: https://hub.steampipe.io/plugins/turbot/azuredevops/tables/azuredevops_build_definition

Retrieve the ID, name, quality, project ID, repository ID, and creation date for all build definitions. This helps in understanding the fundamental properties of your build configurations.

```sql
select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition;
```

```sql
select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition;
```

--------------------------------

### Get Role Assignments by Specific Directory Scope

Source: https://hub.steampipe.io/plugins/turbot/azuread/queries/azure_active_directory/example_role_assignments_with_specific_directory_scope

Find role assignments that are scoped to a specific directory scope. This example uses the root directory scope ('/').

```sql
select
  id,
  principal_id,
  role_definition_id,
  directory_scope_id,
  app_scope_id,
  condition
from
  azuread_directory_role_assignment
where
  directory_scope_id = '/';
```

```sql
select
  id,
  principal_id,
  role_definition_id,
  directory_scope_id,
  app_scope_id,
  condition
from
  azuread_directory_role_assignment
where
  directory_scope_id = '/';
```

--------------------------------

### List User Activities (PostgreSQL FDW / SQLite Extension)

Source: https://hub.steampipe.io/plugins/turbot/azuread/queries/azure_active_directory/example_list_all_activities_initiated_by_a_specific_user

This example demonstrates how to list user-initiated activities using JSON extraction functions, compatible with PostgreSQL Foreign Data Wrappers or SQLite Extensions.

```sql
select
  activity_display_name,
  activity_date_time,
  category,
  operation_type,
  json_extract(
    json_extract(initiated_by, '$.user'),
    '$.userPrincipalName'
  ) as initiated_user,
  result
from
  azuread_directory_audit_report
where
  filter = 'initiatedBy/user/userPrincipalName eq ''test@org.onmicrosoft.com''';

```

--------------------------------

### List VMs with JIT Access Enabled (PostgreSQL/SQLite)

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/security_center/example_list_virtual_machines_with_jit_access_enabled

This query lists virtual machines with JIT access enabled, displaying their name, ID, and configured ports. It's useful for security audits and managing access.

```sql
select  vm.name,  vm.id,  jsonb_pretty(vms -> 'ports') as portsfrom  azure_security_center_jit_network_access_policy,
  jsonb_array_elements(virtual_machines) as vms,
  azure_compute_virtual_machine as vmwhere  lower(vms ->> 'id') = lower(vm.id);
```

--------------------------------

### Get Azure Load Balancer Rules

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_lb_rule

Explore the configuration and status of Azure load balancer rules. This aids in understanding the type and provisioning state of each rule, which can help in managing and troubleshooting your Azure load balancer setup.

```sql
select
  id,
  name,
  type,
  provisioning_state,
  etag
from
  azure_lb_rule;
```

```sql
select
  id,
  name,
  type,
  provisioning_state,
  etag
from
  azure_lb_rule;
```

--------------------------------

### List virtual machines with JIT access enabled (using jsonb_pretty)

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_security_center_jit_network_access_policy

Identifies virtual machines with Just-In-Time (JIT) access enabled. This query uses jsonb_pretty to format the ports information. Useful for managing security risks by limiting open ports.

```sql
select
  vm.name,
  vm.id,
  jsonb_pretty(vms -> 'ports') as ports
from
  azure_security_center_jit_network_access_policy,
  jsonb_array_elements(virtual_machines) as vms,
  azure_compute_virtual_machine as vm
where
  lower(vms ->> 'id') = lower(vm.id);
```

--------------------------------

### Get Basic Azure Log Profile Information

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_log_profile

Retrieve the name, ID, storage account ID, and service bus rule ID for all Azure log profiles. This helps in understanding the basic configuration and associations of your logging setup.

```sql
select
  name,
  id,
  storage_account_id,
  service_bus_rule_id
from
  azure_log_profile;
```

--------------------------------

### Get Azure Maintenance Window Details (Postgres)

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/maintenance/example_maintenance_window_specifics

Retrieve specific maintenance window details like start time, expiration, duration, time zone, and recurrence using the Postgres FDD syntax. This is useful for planning and impact assessment.

```sql
select
  name,
  window ->> 'StartDateTime' as start_date_time,
  window ->> 'ExpirationDateTime' as expiration_date_time,
  window ->> 'Duration' as duration,
  window ->> 'TimeZone' as time_zone,
  window ->> 'RecurEvery' as recur_every
from
  azure_maintenance_configuration;
```

--------------------------------

### Get Basic VM Scale Set VM Info

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_compute_virtual_machine_scale_set_vm

Analyze VM scale set settings to understand distribution and organization. Useful for managing resources and monitoring regional deployment.

```sql
select
  name,
  scale_set_name,
  instance_id,
  id,
  vm_id,
  region,
  resource_group
from
  azure_compute_virtual_machine_scale_set_vm;
```

```sql
select
  name,
  scale_set_name,
  instance_id,
  id,
  vm_id,
  region,
  resource_group
from
  azure_compute_virtual_machine_scale_set_vm;
```

--------------------------------

### List Private Endpoint Connection Details (SQLite)

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/event_hubs/example_list_private_endpoint_connection_details

Get details for private endpoint connections in Azure Event Hubs Namespaces using a SQLite-compatible query. This example demonstrates how to parse JSON data from the `private_endpoint_connections` field within the `azure_eventhub_namespace` table.

```sql
select
  name,
  n.id,
  json_extract(connections.value, '$.id') as connection_id,
  json_extract(connections.value, '$.name') as connection_name,
  json_extract(connections.value, '$.privateEndpointPropertyID') as property_private_endpoint_id,
  json_extract(connections.value, '$.provisioningState') as property_provisioning_state,
  connections.value as property_private_link_service_connection_state,
  json_extract(connections.value, '$.type') as connection_type
from
  azure_eventhub_namespace as n,
  json_each(private_endpoint_connections) as connections;
```

--------------------------------

### Show Project Capabilities with Pretty JSON

Source: https://hub.steampipe.io/plugins/turbot/azuredevops/queries/azure_dev_ops/example_show_project_capabilities

Retrieve project ID, name, and formatted JSON for version control and process template capabilities. Use this to get a human-readable view of project settings.

```sql
select
  id,
  name,
  jsonb_pretty(capabilities -> 'versioncontrol') as version_control,
  jsonb_pretty(capabilities -> 'processTemplate') as process_template
from
  azuredevops_project;
```

--------------------------------

### List all authentication method configurations

Source: https://hub.steampipe.io/plugins/turbot/azuread/queries/azure_active_directory/example_list_all_authentication_method_configurations

Lists all available authentication methods and their key properties to understand configured authentication options in your tenant. Uses the azuread_authentication_method_policy table.

```sql
SELECT
  *
FROM
  azuread_authentication_method_policy;
```

--------------------------------

### Get IP Configuration Details for Azure Bastion Hosts

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_bastion_host

Analyze IP configuration details for each Azure Bastion host, including name, ID, IP address, allocation method, and SKU. This provides insights into the network setup of Azure resources.

```sql
select
  h.name as bastion_host_name,
  i.name as ip_configuration_name,
  ip_configuration_id,
  ip_address,
  public_ip_allocation_method,
  sku_name as ip_configuration_sku
from
  azure_bastion_host h,
  jsonb_array_elements(ip_configurations) ip,
  azure_public_ip i
where
  i.id = ip -> 'properties' -> 'publicIPAddress' ->> 'id';
```

```sql
select
  h.name as bastion_host_name,
  i.name as ip_configuration_name,
  ip_configuration_id,
  ip_address,
  public_ip_allocation_method,
  sku_name as ip_configuration_sku
from
  azure_bastion_host h,
  json_each(ip_configurations) ip,
  azure_public_ip i
where
  i.id = json_extract(ip.value, '$.properties.publicIPAddress.id');
```

--------------------------------

### Get Cosmos DB Restore Regions (JSON Extract)

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/cosmos_db/example_get_the_regions_that_the_database_accounts_can_be_restored_from

This query uses JSON extraction functions to retrieve the same information as the previous example: Cosmos DB account name, primary region, and details of restorable locations. This is an alternative method for accessing nested JSON data.

```sql
select
  name,
  region,
  json_extract(restorable_locations, '$.LocationName') as restorable_location,
  json_extract(restorable_locations, '$.CreationTime') as regional_database_account_creation_time,
  json_extract(
    restorable_locations,
    '$.RegionalDatabaseAccountInstanceID'
  ) as restorable_location_database_instance_id
from
  azure_cosmosdb_restorable_database_account;
```

--------------------------------

### List App Service Plans Using Spot Instances (Boolean)

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/app_service/example_list_of_app_service_plan_that_owns_spot_instances

This query retrieves App Service Plans where the 'is_spot' property is true. Use this to identify plans actively using spot instances.

```sql
select
  name,
  is_spot,
  kind,
  region,
  resource_group
from
  azure_app_service_plan
where
  is_spot;
```

--------------------------------

### Install Steampipe Azure Export CLI

Source: https://hub.steampipe.io/plugins/turbot/azure

Installs the Steampipe Export CLI for Azure using a shell script. This is the recommended method for installation.

```bash
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- azure

```

--------------------------------

### List VMs with JIT Access Enabled (PostgreSQL/SQLite - Alternative)

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/security_center/example_list_virtual_machines_with_jit_access_enabled

An alternative SQL query to list virtual machines with JIT access enabled. This version extracts port information differently, suitable for specific PostgreSQL/SQLite configurations.

```sql
select  vm.name,  vm.id,  vms.value as portsfrom  azure_security_center_jit_network_access_policy,
  json_each(virtual_machines) as vms,
  azure_compute_virtual_machine as vmwhere  lower(json_extract(vms.value, '$.id')) = lower(vm.id);
```

--------------------------------

### Install Steampipe Azure AD SQLite Extension

Source: https://hub.steampipe.io/plugins/turbot/azuread

Installs the Steampipe Azure AD plugin as a SQLite Extension. This script downloads and installs the appropriate package for your OS and architecture.

```bash
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/sqlite.sh)" -- azuread
```

--------------------------------

### List all Azure App Configurations

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_app_configuration

Retrieve basic information such as ID, name, type, provisioning state, and creation date for all Azure App Configurations.

```sql
select
  id,
  name,
  type,
  provisioning_state,
  creation_date
from
  azure_app_configuration;
```

--------------------------------

### Install Azure DevOps Exporter CLI

Source: https://hub.steampipe.io/plugins/turbot/azuredevops/tables/azuredevops_git_repository

Installs the Steampipe Exporter CLI for Azure DevOps. This script downloads and installs the necessary binary to extract data using the Azure DevOps plugin.

```bash
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- azuredevops
```

--------------------------------

### Install Steampipe Azure AD Postgres FDW

Source: https://hub.steampipe.io/plugins/turbot/azuread

Installs the Steampipe Azure AD plugin as a native Postgres Foreign Data Wrapper (FDW). This script downloads and installs the necessary files for your system.

```bash
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/postgres.sh)" -- azuread
```

--------------------------------

### Retrieve Basic VM Size Information

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_compute_virtual_machine_size

Fetches basic details including name, region, and core count for all available VM sizes. This is a foundational query for understanding the VM landscape.

```sql
select  name,  region,  number_of_cores,  memory_in_mbfrom  azure_compute_virtual_machine_size;
```

--------------------------------

### Install Azure AD Plugin

Source: https://hub.steampipe.io/plugins/turbot/azuread/mods

Installs the azuread Steampipe plugin. This is the first step to using the plugin.

```bash
steampipe plugin install azuread
```

--------------------------------

### List Service Endpoints Not Ready (SQL - using 0 for false)

Source: https://hub.steampipe.io/plugins/turbot/azuredevops/queries/azure_dev_ops/example_list_service_endpoints_which_are_not_ready

This alternative SQL query achieves the same result by explicitly checking if the `is_ready` property is equal to 0, which represents a false state for service endpoints.

```sql
select
  id,
  name,
  is_ready,
  is_shared,
  owner,
  type
from
  azuredevops_serviceendpoint
where
  is_ready = 0;
```

--------------------------------

### Install Azure Steampipe Plugin

Source: https://hub.steampipe.io/plugins/turbot/azure/mods

Use this command to install the Steampipe plugin for Azure. This is a prerequisite for using Azure-related mods.

```bash
steampipe plugin install azure
```

--------------------------------

### List virtual machines with JIT access enabled (using json_each)

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_security_center_jit_network_access_policy

Identifies virtual machines with Just-In-Time (JIT) access enabled. This query uses json_each and json_extract for processing virtual machine data. Useful for managing security risks by limiting open ports.

```sql
select
  vm.name,
  vm.id,
  vms.value as ports
from
  azure_security_center_jit_network_access_policy,
  json_each(virtual_machines) as vms,
  azure_compute_virtual_machine as vm
where
  lower(json_extract(vms.value, '$.id')) = lower(vm.id);
```

--------------------------------

### Audit Log Analytics Extension Installation on Linux Azure Arc Machines

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/compute/arc_compute_machine_linux_log_analytics_agent_installed

This SQL query checks for the Log Analytics extension (OMSAgentForLinux) on Linux Azure Arc machines. It returns 'ok' if installed and succeeded, 'alarm' if not installed, and 'skip' for non-Linux machines.

```sql
with compute_machine as(
  select 
    id,
    name,
    subscription_id,
    resource_group 
  from 
    azure_hybrid_compute_machine,
    jsonb_array_elements(extensions) as e
  where 
    e ->> 'name' = 'OMSAgentForLinux'
    and e ->> 'provisioningState' = 'Succeeded')
select 
  a.id as resource,
  case 
    when a.os_name <> 'linux' then 'skip'
    when m.id is not null then 'ok'
    else 'alarm'
  end as status,
  case 
    when a.os_name <> 'linux' then a.name || ' is of ' || a.os_name || ' operating system.'
    when m.id is not null then a.name || ' log analytics extension installed.'
    else a.name || ' log analytics extension not installed.'
  end as reason,
  a.resource_group as resource_group,
  sub.display_name as subscription
from 
  azure_hybrid_compute_machine as a
  left join compute_machine as m on m.id = a.id,
  azure_subscription as sub
where 
  sub.subscription_id = a.subscription_id;
```

--------------------------------

### List Plan Details using Steampipe CLI

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/lighthouse/example_list_plan_details_for_each_lighthouse_definition

Use this query with the Steampipe CLI to fetch plan details for Lighthouse definitions. It extracts plan information directly from the `azure_lighthouse_definition` table.

```sql
select
  name,
  plan ->> 'name' as plan_name,
  plan ->> 'product' as plan_product,
  plan ->> 'publisher' as plan_publisher,
  plan ->> 'version' as plan_version
from
  azure_lighthouse_definition;
```

--------------------------------

### List App Service Plans Using Spot Instances (Integer)

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/app_service/example_list_of_app_service_plan_that_owns_spot_instances

This query retrieves App Service Plans where the 'is_spot' property is equal to 1. This is an alternative way to filter for plans using spot instances.

```sql
select
  name,
  is_spot,
  kind,
  region,
  resource_group
from
  azure_app_service_plan
where
  is_spot = 1;
```

--------------------------------

### Install Latest Azure DevOps Plugin

Source: https://hub.steampipe.io/plugins/turbot/azuredevops/versions

Installs the latest stable version of the Turbot Azure DevOps plugin. Use this for general use.

```bash
steampipe plugin install azuredevops
```

--------------------------------

### List User Creation Activities (PostgreSQL)

Source: https://hub.steampipe.io/plugins/turbot/azuread/tables/azuread_directory_audit_report

Explore recent user creation activities within the past week. This query helps identify who initiated the creation and the username of the new user. Requires the Azure AD provider to be configured.

```sql
select
  activity_date_time,
  category,
  operation_type,
  initiated_by -> 'user' ->> 'userPrincipalName' as initiated_user,
  t ->> 'userPrincipalName' as new_user_name
from
  azuread_directory_audit_report,
  jsonb_array_elements(target_resources) as t
where
  activity_display_name = 'Add user'
  and activity_date_time >= (current_date - interval '7 days')
order by activity_date_time;

```

--------------------------------

### Check Dependency Agent Installation on Windows VMs

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/compute/compute_vm_network_traffic_data_collection_windows_agent_installed

This SQL query checks if the Microsoft Dependency agent (ExtensionType 'DependencyAgentWindows') is successfully installed and provisioned on Azure Windows virtual machines. It identifies VMs where the agent is installed ('ok' status) and those where it is not ('alarm' status) or are non-Windows ('skip' status).

```sql
with agent_installed_vm as (
  select
    distinct a.vm_id
  from
    azure_compute_virtual_machine as a,
    jsonb_array_elements(extensions) as b
  where
    b ->> 'ExtensionType' = 'DependencyAgentWindows'
    and b ->> 'Publisher' = 'Microsoft.Azure.Monitoring.DependencyAgent'
    and b ->> 'ProvisioningState' = 'Succeeded'
)
select
  a.vm_id as resource,
  case
    when a.os_type <> 'Windows' then 'skip'
    when b.vm_id is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when a.os_type <> 'Windows' then a.title || ' is of ' || a.os_type || ' operating system.'
    when b.vm_id is not null then a.title || ' have data collection agent installed.'
    else a.title || ' data collection agent not installed.'
  end as reason,
  a.resource_group as resource_group,
  sub.display_name as subscription
from
  azure_compute_virtual_machine as a
  left join agent_installed_vm as b on a.vm_id = b.vm_id,
  azure_subscription as sub
where
  sub.subscription_id = a.subscription_id;
```

--------------------------------

### Install Azure SQLite Extension

Source: https://hub.steampipe.io/plugins/turbot/azure

Install the Azure SQLite Extension using the provided script. The script will prompt for plugin name, version, and destination.

```shell
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/sqlite.sh)" -- azure
```

--------------------------------

### List Repositories by Project Name (JSON Path)

Source: https://hub.steampipe.io/plugins/turbot/azuredevops/queries/azure_dev_ops/example_list_repositories_of_a_particular_project

This query lists repositories for a specific project by filtering on the project name using JSON path extraction. Useful for understanding project structure and repository details.

```sql
select
  id,
  name,
  default_branch,
  is_fork,
  project_id,
  size
from
  azuredevops_git_repository
where
  project ->> 'name' = 'private_project';
```

--------------------------------

### Install Azure Postgres FDW

Source: https://hub.steampipe.io/plugins/turbot/azure

Install the Azure Postgres Foreign Data Wrapper using the provided script. The script will prompt for plugin details.

```shell
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/postgres.sh)" -- azure
```

--------------------------------

### Select All Available Memory Metrics

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_compute_virtual_machine_metric_available_memory_hourly

Retrieves all available hourly memory metrics for Azure virtual machines.

```sql
select
  *
from
  azure_compute_virtual_machine_metric_available_memory_hourly
```

--------------------------------

### List Servers by SKU

Source: https://hub.steampipe.io/plugins/turbot/azure/queries?table=azure_postgresql_flexible_server

Identify Azure PostgreSQL Flexible Servers based on their SKU (e.g., compute and storage tier). Useful for cost management and performance analysis.

```sql
select
  name,
  resource_group,
  location,
  sku
from
  azure_postgresql_flexible_server
where
  sku = 'Standard_D2s_v3';
```

--------------------------------

### Install Specific Azure DevOps Plugin Versions

Source: https://hub.steampipe.io/plugins/turbot/azuredevops/versions

Installs specific versions of the Turbot Azure DevOps plugin. Use these commands to pin to a particular release.

```bash
steampipe plugin install azuredevops@1
```

```bash
steampipe plugin install azuredevops@1.2
```

```bash
steampipe plugin install azuredevops@1.2.0
```

```bash
steampipe plugin install azuredevops@1.1
```

```bash
steampipe plugin install azuredevops@1.1.1
```

```bash
steampipe plugin install azuredevops@1.1.0
```

```bash
steampipe plugin install azuredevops@1.0
```

```bash
steampipe plugin install azuredevops@1.0.0
```

```bash
steampipe plugin install azuredevops@0
```

```bash
steampipe plugin install azuredevops@0.2
```

```bash
steampipe plugin install azuredevops@0.2.0
```

```bash
steampipe plugin install azuredevops@0.1
```

```bash
steampipe plugin install azuredevops@0.1.1
```

```bash
steampipe plugin install azuredevops@0.1.0
```

```bash
steampipe plugin install azuredevops@0.0
```

```bash
steampipe plugin install azuredevops@0.0.1
```

--------------------------------

### Check Log Analytics Agent Installation on VM Scale Sets

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/compute/compute_vm_scale_set_log_analytics_agent_installed

This SQL query identifies Azure Virtual Machine Scale Sets and checks if the Log Analytics agent extension (MicrosoftMonitoringAgent for Windows or OmsAgentForLinux for Linux) is successfully installed and configured with a workspace ID. It reports 'ok' if installed and 'alarm' otherwise.

```sql
with agent_installed_vm_scale_set as (
  select
    distinct a.id as vm_id
  from
    azure_compute_virtual_machine_scale_set as a,
    jsonb_array_elements(extensions) as b
  where
    b ->> 'Publisher' = 'Microsoft.EnterpriseCloud.Monitoring'
    and b ->> 'ExtensionType' = any(
      ARRAY [ 'MicrosoftMonitoringAgent',
      'OmsAgentForLinux' ]
    )
    and b ->> 'ProvisioningState' = 'Succeeded'
    and b -> 'Settings' ->> 'workspaceId' is not null
)
select
  a.id as resource,
  case
    when b.vm_id is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when b.vm_id is not null then a.title || ' have log analytics agent installed.'
    else a.title || ' log analytics agent not installed.'
  end as reason,
  a.resource_group as resource_group,
  sub.display_name as subscription
from
  azure_compute_virtual_machine_scale_set as a
  left join agent_installed_vm_scale_set as b on a.id = b.vm_id,
  azure_subscription as sub
where
  sub.subscription_id = a.subscription_id;

```

--------------------------------

### List VM Sizes by Core Count

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_compute_virtual_machine_size

Retrieves VM sizes that meet or exceed a minimum core count (e.g., 8 cores). Helps in selecting VMs with sufficient processing power.

```sql
select  name,  number_of_cores,  regionfrom  azure_compute_virtual_machine_sizewhere  number_of_cores >= 8;
```

--------------------------------

### Get Basic Role Assignment Info

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_role_assignment

Retrieve the name, ID, principal ID, and principal type for all role assignments. Use this to get a general overview of access permissions.

```sql
select
  name,
  id,
  principal_id,
  principal_type
from
  azure_role_assignment;
```

```sql
select
  name,
  id,
  principal_id,
  principal_type
from
  azure_role_assignment;
```

--------------------------------

### List Build Definitions by Project Name (Direct Access)

Source: https://hub.steampipe.io/plugins/turbot/azuredevops/queries/azure_dev_ops/example_list_build_definitions_of_a_particular_project

Retrieve build definition details by filtering on the project name using direct JSON field access. Useful for auditing project configurations.

```sql
select
  id,
  name,
  quality,
  project_id,
  repository_id,
  created_date
from
  azuredevops_build_definition
where
  project ->> 'name' = 'private_project';
```

--------------------------------

### Check VM Guest Configuration Extension with System-Assigned Managed Identity

Source: https://hub.steampipe.io/plugins/turbot/azure/queries/compute/compute_vm_guest_configuration_with_system_assigned_managed_identity

This SQL query identifies Azure virtual machines where the Guest Configuration extension is installed. It then checks if these VMs have a system-assigned managed identity configured. The output indicates 'ok' for compliant VMs, 'alarm' for non-compliant ones (extension installed without system-assigned identity), and 'skip' if the extension is not installed.

```sql
with gc_installed_vm as (
  select
    distinct a.vm_id,
    title
  from
    azure_compute_virtual_machine as a,
    jsonb_array_elements(extensions) as b
  where
    b ->> 'Publisher' = 'Microsoft.GuestConfiguration'
)
select
  a.vm_id as resource,
  case
    when b.vm_id is null then 'skip'
    when b.vm_id is not null
    and string_to_array(identity ->> 'type', ', ') @> array [ 'SystemAssigned' ] then 'ok'
    else 'alarm'
  end as status,
  case
    when b.vm_id is null then a.title || ' guest configuration extension not installed.'
    when b.vm_id is not null
    and string_to_array(identity ->> 'type', ', ') @> array [ 'SystemAssigned' ] then a.title || ' guest configuration extension installed with system-assigned managed identity.'
    else a.title || ' guest configuration extension not installed with system-assigned managed identity.'
  end as reason,
  a.resource_group as resource_group,
  sub.display_name as subscription
from
  azure_compute_virtual_machine as a
  left join gc_installed_vm as b on a.vm_id = b.vm_id
  left join azure_subscription as sub on sub.subscription_id = a.subscription_id;
```

--------------------------------

### Find SKUs with Specific Capabilities (e.g., Premium SSD)

Source: https://hub.steampipe.io/plugins/turbot/azure/queries?table=azure_compute_resource_sku

Identify compute resource SKUs that support specific capabilities, such as Premium SSD storage. This is crucial for deploying workloads that require high-performance storage.

```sql
select
  *
from
  azure_compute_resource_sku
where
  'Premium_LRS' = ANY(storage_sku_types);
```

--------------------------------

### Get Basic API Management Backend Information

Source: https://hub.steampipe.io/plugins/turbot/azure/tables/azure_api_management_backend

Retrieve general information about backend configurations, including name, ID, protocol, service name, URL, and type.

```sql
select
  name,
  id,
  protocol,
  service_name,
  url,
  type
from
  azure_api_management_backend;
```

```sql
select
  name,
  id,
  protocol,
  service_name,
  url,
  type
from
  azure_api_management_backend;
```