KQL Query for finding Resource Name, Resource Type,Subscription Name,Tags,Resource Group

Question

I would like to retrieve using Resource Graph explorer all the resources along with their name, resource types, location, resource groups, subscription name.
I want to retrieve only those resources that do not have a tag "Malcode" or the value of the tag "Malcode " is null or empty. Can anyone please help me write this query. I know i have to use a join as i need to combine two tables resources and resourcecontainers.
But i am not able to figure out the correct way to do this and it is not retrieving me the expected results. Appreciate any help

Answer 1

@olufemia-MSFT -- What i would like to retrieve are those resources where the tag key 'malcode' is missing the value or null.
Also, i would like to get the below columns in my CSV :

1) Resource Type 2) Resource Name 3) Location 4) Resource Group 5) Subscription Name.

So if you run the query which you wrote above and i tweak your query according to my requirements:

resources
| where (tags.malcode) =~ ' '
| project-away identity, plan, managedBy,properties,zones,extendedLocation,kind,id,sku,tenantId

If you run the above query, the problem i was facing was the above query wasn't showing me the field Subscription Name, it was only showing subscription ID.

So i had to use a left outer join and join using subscription Id and then i got back the 5 columns which i wanted to see in my CSV.

Hope you got my question now.