Add table azure_lighthouse_definition

marcbrlcln commented 1 month ago

References Associated Terraform data resource: https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/lighthouse_definition We would like to be able to check if an Azure Lighthouse has been deployed.

ParthaI commented 1 month ago

Hi @marcbrlcln,

I have pushed changes to the issue762 branch, adding support for a new table named azure_lighthouse_definition. Unfortunately, I couldn't test it due to resource unavailability in our environment.

It would be great if you could give it a shot in the PR branch(issue762) and share your feedback with us.

To try it out:

Clone the repository: git clone https://github.com/turbot/steampipe-plugin-azure.git
Navigate to the directory: cd steampipe-plugin-azure
Checkout the branch: git checkout issue762
Run the command: make
Execute the query

Thank you!

Shr3ps commented 1 month ago

Hello @ParthaI,

we have tested your branch with @marcbrlcln , the new table is available but returns empty result:

%  steampipe query                                                                                                                 12:17:53
Welcome to Steampipe v0.23.1
For more information, type .help
> select * from azure_lighthouse_definition
+----+----------------------------+------+-------+------+-------------+------------------------------+----------------------+--------------------+----------------+------+-------+------+----------------+--------------------+--------+------+
| id | registration_definition_id | type | scope | name | description | registration_definition_name | managed_by_tenant_id | provisioning_state | authorizations | plan | title | akas | resource_group | sp_connection_name | sp_ctx | _ctx |
+----+----------------------------+------+-------+------+-------------+------------------------------+----------------------+--------------------+----------------+------+-------+------+----------------+--------------------+--------+------+
+----+----------------------------+------+-------+------+-------------+------------------------------+----------------------+--------------------+----------------+------+-------+------+----------------+--------------------+--------+------+
>

Shr3ps commented 1 month ago

Here is a Terraform resource state for information:

resource "azurerm_lighthouse_assignment" "lighthouse_assign" {
    id                       = "/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationAssignments/212bf435-4782-8a2f-192c-fbe4c87a66b4"
    lighthouse_definition_id = "/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/54ebcb54-7b94-1b61-3583-96f35d34e61f"
    name                     = "212bf435-4782-8a2f-192c-fbe4c87a66b4"
    scope                    = "/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e"
}

ParthaI commented 1 month ago

Ahh, sorry about that. I've pushed another commit to the same branch, updating the scope value for the API call.

Could you please pull the latest changes from the issue762 branch to your local repository, build the plugin again, and then run the query?

Apologies for the inconvenience caused by my earlier code.

Shr3ps commented 1 month ago

Hum, still empty result after doing:

git pull # (it fetched your last commit adding the "s" to subscription)
make
steampipe query "select * from azure_lighthouse_definition"

ParthaI commented 1 month ago

Hmm, Seems to be something going wrong, I will retake a look at it and let you know. Thanks!

ParthaI commented 1 month ago

Hello @Shr3ps,

I was having an second pair of eyes on the code changes pushed in the issue762 branch and found no issues. We are passing the scope value (subscriptions/<Subscription ID>) by default as mentioned in the document. It should return the rows.

Could you please:

Confirm that the relevant Azure CLI command is returning the expected results?
Check if you are receiving any errors or empty rows when running the query with the scope parameter? (If you receive a 404 error, I believe the scrope value is incorrect)
Try using the Azure API Explorer for this purpose.

Please note: Before running the query, make sure to terminate all Steampipe running processes, rebuild the plugin in your local environment, and then run the query. I have temporarily disabled the Ignore error config for the table in the same branch(Please pull the latest changes).

Thank you for being so cooperative!

Shr3ps commented 1 month ago

Hello @ParthaI , I tried to pull your latest changes, but same on steampipe, empty results, and no errors seems returned.

On the other hand, the REST API call is working fine using azure cli:

 %  az rest -u 'https://management.azure.com/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions?api-version=2022-10-01&$filter=' | jq '.[][].id'
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/1bea883d-818e-3f25-472f-7f3501c52c2a"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/3bc2a212-f55f-0469-feec-09122de9d9df"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/54ebcb54-7b94-1b61-3583-96f35d34e61f"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/5ad1701f-aff2-b9db-6466-c4d25b3720f7"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/5cbe293f-270f-f389-3b8b-6bfadcc74d4f"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/6b200bbe-ea6c-7ec7-e4e7-2753b0aa55c9"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/81c2054f-2435-c1a7-1c31-cc517bcccc9e"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/8cf1c4d2-466f-230e-9af9-faeec1e46b53"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/98d91214-d761-2426-f80c-ab8c13cbf5ac"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/b044541b-1fb2-c28d-c549-02762bdedc6a"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/b4368fe7-6c97-947e-4a3d-bc2395350052"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/c7b52807-7943-032d-bc70-de58e292395c"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/cd1d2f05-b107-24cd-4b57-3a49f6456e62"
"/subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/ec673a61-d1b4-e78b-5494-2dd7e29d7d7c"

I made a jq query in my sample code, if you need the full returned json payload, let me know (I can paste one definition, but with some anonymous data)

Shr3ps commented 1 month ago

Hello @ParthaI ,

Not sure about what I will say but, seems that SDK used is this one: https://github.com/Azure/azure-sdk-for-go/blob/v68.0.0/profiles/latest/managedservices/mgmt/managedservices/models.go which seems to use an old API version 2019-06-01

my az rest tests are using API version 2022-10-01

Shr3ps commented 3 weeks ago

hello @ParthaI , any news?

ParthaI commented 3 weeks ago

Sorry for the delayed response, @Shr3ps. I'm not sure if the version matters in our case. I reviewed my code but didn't find anything wrong there.

However, I have developed a standalone code using a different package of the Go SDK that might help us investigate further.

package main

import (
    "context"
    "fmt"
    "log"

    "github.com/Azure/azure-sdk-for-go/sdk/azidentity"
    "github.com/Azure/azure-sdk-for-go/sdk/resourcemanager/managedservices/armmanagedservices"
)

func main() {
    // Create a DefaultAzureCredential which provides a default implementation for authentication
    cred, err := azidentity.NewDefaultAzureCredential(nil)
    if err != nil {
        log.Fatalf("failed to create default azure credential: %v", err)
    }

    // Create a new ManagedServicesClient
    client, err := armmanagedservices.NewRegistrationDefinitionsClient(cred, nil)
    if err != nil {
        log.Fatalf("failed to create managed services client: %v", err)
    }

    // Define the scope
    scope := "subscriptions/<YOUR SUBSCRIPTION ID>" // replace with your subscription ID

    // List the registration definitions
    pager := client.NewListPager(scope, nil)

    // Iterate over the response
    for pager.More() {
        page, err := pager.NextPage(context.TODO())
        if err != nil {
            log.Fatalf("failed to get registration definitions: %v", err)
        }
        for _, item := range page.Value {
            fmt.Println(*item)
        }
    }
}

Could you please execute the above Go code and let me know if it works?

I appreciate your help and apologize for any inconvenience.

Shr3ps commented 3 weeks ago

Hello @ParthaI , thanks for your answer, your sample code is working fine:

───────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ File: go.mod
───────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ module github.com/turbot/_test_lighthouse
   2   │
   3   │ go 1.21.5
   4   │
   5   │ require (
   6   │     github.com/Azure/azure-sdk-for-go/sdk/azcore v1.11.1 // indirect
   7   │     github.com/Azure/azure-sdk-for-go/sdk/azidentity v1.6.0 // indirect
   8   │     github.com/Azure/azure-sdk-for-go/sdk/internal v1.8.0 // indirect
   9   │     github.com/Azure/azure-sdk-for-go/sdk/resourcemanager/managedservices/armmanagedservices v0.7.0 // indirect
  10   │     github.com/AzureAD/microsoft-authentication-library-for-go v1.2.2 // indirect
  11   │     github.com/golang-jwt/jwt/v5 v5.2.1 // indirect
  12   │     github.com/google/uuid v1.6.0 // indirect
  13   │     github.com/kylelemons/godebug v1.1.0 // indirect
  14   │     github.com/pkg/browser v0.0.0-20240102092130-5ac0b6a4141c // indirect
  15   │     golang.org/x/crypto v0.24.0 // indirect
  16   │     golang.org/x/net v0.26.0 // indirect
  17   │     golang.org/x/sys v0.21.0 // indirect
  18   │     golang.org/x/text v0.16.0 // indirect
  19   │ )

 %  ./test                                                                                                                                                        14:48:26
{<nil> 0xc000314000 0xc0001a23a0 0xc0001a2190 0xc0001b0090 0xc0001a23b0}
{<nil> 0xc000314070 0xc0001a2420 0xc0001a2440 0xc0001b0360 0xc0001a2430}
{<nil> 0xc0003140e0 0xc0001a26a0 0xc0001a26c0 0xc0001b04b0 0xc0001a26b0}
{<nil> 0xc000314150 0xc0001a2d70 0xc0001a2d90 0xc0001b0930 0xc0001a2d80}
{<nil> 0xc0003141c0 0xc0001a30a0 0xc0001a2e90 0xc0001b09f0 0xc0001a30b0}
{<nil> 0xc000314230 0xc0001a32f0 0xc0001a3310 0xc0001b0c30 0xc0001a3300}
{<nil> 0xc0003142a0 0xc0001a3410 0xc0001a3430 0xc0001b0cf0 0xc0001a3420}
{<nil> 0xc000314310 0xc0001a38e0 0xc0001a3900 0xc0001b0f90 0xc0001a38f0}
{<nil> 0xc000314380 0xc0001a3b60 0xc0001a3b80 0xc0001b10e0 0xc0001a3b70}
{<nil> 0xc0003143f0 0xc0001a3ea0 0xc0001a3c90 0xc0001b11a0 0xc0001a3c80}
{<nil> 0xc000314460 0xc0003b2060 0xc0003b2080 0xc0001b1380 0xc0003b2070}
{<nil> 0xc0003144d0 0xc0003b2470 0xc0003b2490 0xc0001b1440 0xc0003b2480}
{<nil> 0xc000314540 0xc0003b24f0 0xc0003b2510 0xc0001b1650 0xc0003b2500}
{<nil> 0xc0003145b0 0xc0003b28d0 0xc0003b28f0 0xc0001b18c0 0xc0003b28e0}

ParthaI commented 3 weeks ago

Awesome, I have pushed the changes into the branch issue762 by updating the Go package to use github.com/Azure/azure-sdk-for-go/sdk/resourcemanager/managedservices/armmanagedservices instead. Please pull the latest changes to your local and build the plugin locally again. And please let me know if you encounter any further issues.

Thank you so much for your help!

Shr3ps commented 3 weeks ago

It's now working fine :)

Do you plan to also include lighthouse assignments?

ParthaI commented 3 weeks ago

I'm glad to hear that it's now working fine! Currently, we don't have plans to include lighthouse assignments. However, if you have any specific requirements or features you'd like to see, please feel free to raise an issue. We're always open to considering new ideas and feedback. Thank you!

ParthaI commented 2 weeks ago

Hello @Shr3ps, I have raised a draft PR by adding two tables azure_lighthouse_definition and azure_lighthouse_assignment. I have pushed the changes to the same branch(issue762).

Could you please give it a try by pulling the latest changes to your local and build the plugin locally?

Please let me know if you are running into any issues.

Thank You!

Shr3ps commented 2 weeks ago

Thanks @ParthaI I confirm, your latest push are working fine. I now have also assignments. Just one remark is that assignment can two type of scope : at RG level or at Subscription level.

With the latest commit, I always have: scope | <null>

ParthaI commented 2 weeks ago

Thank you very much for your confirmation, @Shr3ps. I have pushed another commit to the branch. I hope the column value will now always be populated correctly. The default value is subscriptions/<Subscription ID> since we are making the API call at the subscription scope by default. If we pass the scope value as a query parameter (e.g., select * from azure_lighthouse_definition where scope = '...'), it will be set according to the provided value in the query parameter.

Shr3ps commented 2 weeks ago

Oh I think I miss-understand your purpose of the column "scope", its seems to be the "API call scope", which in my opinion is not really needed. What we need in terms of column is "on what the current LightHouse definition is assign to", like I said in my previous comment, it can be only one off the two types of assignment:

assigned to a Resource Group (in that case the column resource_group seems to have the good value, but maybe could be renamed assigned_resource_group)
assigned to a Subscription (in that case the current column resource_group have a wrong value with your code, I have 'microsoft.managedservices'),

I would like to have something which tells me to what type of scope my definition is assigned to.

For the record, with your latest push, the current scope column has always the value "/susbcriptions/xxxx" no matter on which scope it's assigned, (testing with : steampipe query "select * from azure_lighthouse_assignment")

ParthaI commented 2 weeks ago

Ahh my bad, when you say:

assigned to a Resource Group (in that case the column resource_group seems to have the good value, but maybe could be renamed assigned_resource_group)

assigned to a Subscription (in that case the current column resource_group have a wrong value with your code, I have 'microsoft.managedservices'),

For both the azure_lighthouse_assignment and azure_lighthouse_definition tables, are you experiencing the same issue?

The resource_group column is a common column across all tables, and changing its name for this specific table would deviate from our standard development practices.
However, we could consider an alternative approach. We could aggregate the rows such that the resource_group column will have a value if it is assigned to a Resource Group; otherwise, the value would be null.

Could you please let us know your thoughts on this approach?

Thank you for your understanding and cooperation.

ParthaI commented 2 weeks ago

FYI, We can have Definition/Assignments in different scopes:

Subscription: /subscriptions/{subscription-id}
Resource Groups: /subscriptions/{subscription-id}/resourceGroups/{resource-group-name}
Management Groups: /providers/Microsoft.Management/managementGroups/{management-group-id}
Individual Resources: /subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/{resource-provider}/{resource-type}/{resource-name}

Shr3ps commented 2 weeks ago

Ahh my bad, when you say:

assigned to a Resource Group (in that case the column resource_group seems to have the good value, but maybe could be renamed assigned_resource_group)

assigned to a Subscription (in that case the current column resource_group have a wrong value with your code, I have 'microsoft.managedservices'),

For both the azure_lighthouse_assignment and azure_lighthouse_definition tables, are you experiencing the same issue?
* The `resource_group` column is a common column across all tables, and changing its name for this specific table would deviate from our standard development practices.

Ho I didn't checked before, but now you said it: for the table azure_lighthouse_definition table, I always have: resource_group | microsoft.managedservices which is wrong. Lighthouse definition can only be done at Subscription level, so for this table resource_group should be always null, or column removed.

* However, we could consider an alternative approach. We could aggregate the rows such that the `resource_group` column will have a value if it is assigned to a Resource Group; otherwise, the value would be `null`.

Could you please let us know your thoughts on this approach?

For the azure_lighthouse_assignment , yes I think we can have this approch if it's well documented.

Note: the assignment ID differs depending on which scope it's assigned:

-[ RECORD 9  ]---------------------------------------------------------------------------
name                       | 212bf435-4782-8a2f-192c-fbe4c87a66b4
id                         | /subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationAssignments/212bf435-4782-8a2f-192c-fbe4c87a66b4
registration_assignment_id | 212bf435-4782-8a2f-192c-fbe4c87a66b4
type                       | Microsoft.ManagedServices/registrationAssignments
scope                      | subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e
registration_definition_id | /subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/54ebcb54-7b94-1b61-3583-96f35d34e61f
provisioning_state         | Succeeded
title                      | 212bf435-4782-8a2f-192c-fbe4c87a66b4
akas                       | ["azure:///subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationAssignments/212bf435-4782-8a2f-192c-fbe4c87a66b4","azure:///subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/microsoft.managedservices/registrationassignments/212bf435-4782-8a2f-192c-fbe4c87a66b4"]
resource_group             | microsoft.managedservices
sp_connection_name         | azure
sp_ctx                     | {"connection_name":"azure","steampipe":{"sdk_version":"5.10.1"}}
_ctx                       | {"connection_name":"azure","steampipe":{"sdk_version":"5.10.1"}}
-[ RECORD 10 ]---------------------------------------------------------------------------
name                       | 15d276a9-de93-2a7a-a965-09cfc88aef34
id                         | /subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/resourceGroups/rg-tfstates-claranet-prod-data/providers/Microsoft.ManagedServices/registrationAssignments/15d276a9-de93-2a7a-a965-09cfc88aef34
registration_assignment_id | 15d276a9-de93-2a7a-a965-09cfc88aef34
type                       | Microsoft.ManagedServices/registrationAssignments
scope                      | subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e
registration_definition_id | /subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/providers/Microsoft.ManagedServices/registrationDefinitions/b4368fe7-6c97-947e-4a3d-bc2395350052
provisioning_state         | Succeeded
title                      | 15d276a9-de93-2a7a-a965-09cfc88aef34
akas                       | ["azure:///subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/resourceGroups/rg-tfstates-claranet-prod-data/providers/Microsoft.ManagedServices/registrationAssignments/15d276a9-de93-2a7a-a965-09cfc88aef34","azure:///subscriptions/f9c2b144-xxxx-yyyy-zzzz-ed58f82d0c1e/resourcegroups/rg-tfstates-claranet-prod-data/providers/microsoft.managedservices/registrationassignments/15d276a9-de93-2a7a-a965-09cfc88aef34"]
resource_group             | rg-tfstates-claranet-prod-data
sp_connection_name         | azure
sp_ctx                     | {"connection_name":"azure","steampipe":{"sdk_version":"5.10.1"}}
_ctx                       | {"connection_name":"azure","steampipe":{"sdk_version":"5.10.1"}}

ParthaI commented 2 weeks ago

Thank you, @Shr3ps, for the detailed information.

I have updated the PR with the following changes:

The value in the Scope column will remain null unless a value is provided in the query parameter using the WHERE clause.
The resource_group column will contain a value if the Definition/Assignment points to a resource group.
Otherwise, the resource_group column will remain null.

Please try it again and let me know if you encounter any further issues.

Thanks again for all the help and cooperation!

Shr3ps commented 2 weeks ago

Thanks @ParthaI everything is fine now.

Maybe a last improvement suggestion: can we have a column in the assignment table with the scope ID? (so we can have directly on "what" the definition is assigned to)

FYI, We can have Definition/Assignments in different scopes:

* Subscription: `/subscriptions/{subscription-id}`

* Resource Groups: `/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}`

* Management Groups: `/providers/Microsoft.Management/managementGroups/{management-group-id}`

* Individual Resources: `/subscriptions/{subscription-id}/resourceGroups/{resource-group-name}/providers/{resource-provider}/{resource-type}/{resource-name}`

ParthaI commented 2 weeks ago

Hello @Shr3ps, I appreciated your suggestions.

We generally add columns based on the API response, and the current response does not contain a scope property.

From the API perspective, the scope ID is required to make the API call.
In Steampipe, we already have the subscription ID, so by default, we are passing the scope as /subscriptions/{subscription-id}.
Additionally, we provide the functionality to pass the scope ID as an optional parameter.

However, with the current table setup, I believe we can achieve this by leveraging the id column of the table.

Example query:

select
  case
    when id like '/subscriptions/%/resourceGroups/%/providers/%/%/%' then
      substring(id from '/subscriptions/[^/]+/resourceGroups/[^/]+/providers/[^/]+/[^/]+/[^/]+')
    when id like '/subscriptions/%/resourceGroups/%' then
      substring(id from '/subscriptions/[^/]+/resourceGroups/[^/]+')
    when id like '/subscriptions/%' then
      substring(id from '/subscriptions/[^/]+')
    when id like '/providers/Microsoft.Management/managementGroups/%' then
      substring(id from '/providers/Microsoft.Management/managementGroups/[^/]+')
    else
      null
  end as scope_id,
  registration_definition_id,
  id
from
  azure_lighthouse_assignment;

Please make the adjustment to the above query based on the requirements and let me know if that helps.

Thanks!

Shr3ps commented 2 weeks ago

ok @ParthaI LGTM :)

Your latest comment should be a documented example IMO

turbot / steampipe-plugin-azure

Add table azure_lighthouse_definition #762