Get tenant displayName using Kusto queries

Question

I can't seem to find a way to retrieve the displayName for a tenantid using a table available in Azure Monitor.

Using Azure Resource Graph Kusto queries I query resources across tenants (Lighthouse). The resources contains a property for tenantId, but I want a way to lookup that tenantId to a name.

E.g. in the column tenantId I want that mapped up to the displayName.

Resources
| where type == "microsoft.network/virtualnetworks"
| project tenantId, id, name

Is there a way or do I end up getting that information from another place?

Answer 1

I don't believe that there is any Tenant table in ARG like resourcecontainers has subscription names.

It looks like there are CLI extras that will do the extra lookups/mappings for you, not sure why that isn't a feature of the queries themselves?

in workbooks, you could possibly call the ARM api to look up tenants:

https://learn.microsoft.com/en-us/rest/api/resources/tenants/list?view=rest-resources-2022-12-01&tabs=HTTP

and then use a workbooks Merge datasource to join in the names?

Answer 2

I'll add the solution I went for based on John Gardner's answer.

Step 1: Create a hidden text parameter

Step 2: Parse text content

Create a new Query Item and create a variable to hold the tenant information.

let tenantLookup = (print packed = (parse_json('{hiddenTenantLookup:value}'))
| mv-expand packed.value
| evaluate bag_unpack(packed_value)
| project defaultDomain, displayName, domains, id, tenantId
);
//tenantLookup

Step 2: Join tenantLookup with another table

| join kind = inner tenantLookup on $left.tenantId == $right.tenantId

EDIT: (Improving based on comment feedback from John Gardner)

I'm creating a query step and use the Make the step conditional visible with a parameter, which is just empty, to hide the step.

I'm unable to use odata notation $select with azure resource manager, so I get the all the fields. But I can limit the projected columns using the jsonpath.

// Doesn't work with $select
/tenants?$select=tenantId,displayName&api-version=2022-12-01

// Works without $select
/tenants?api-version=2022-12-01

Then I do a Left outer join on the two tables: