How can I get a unique count of resources from AWS Config Advanced query

Question

I've written the following query attempting to list all IAM users in my organization.

SELECT
  arn,
  COUNT(*)
WHERE
  resourceType = 'AWS::IAM::User'
GROUP BY
  arn

When I run this query in the Advanced query editor against my account, I get one result for the account (As expected)

Output

arn                                     COUNT(*)
arn:aws:iam::99999999:user/foo            1

When I select my Organization Aggregator as the query target (described here), I get multiple results for each ARN - which is impossible.

Output:

arn                                     COUNT(*)
arn:aws:iam::99999999:user/foo            15
arn:aws:iam::99999998:user/foo            15
arn:aws:iam::99999997:user/bar            15

My best guess thus far is that I'm querying "Configuration Items", not resources so I get multiple results. Config has evaluated 15 rules, and the arn field is the resource it evaluated against.

I'm trying to do simple inventory queries, and AWS config doesn't appear to have any sort of unique operator I can use in my SQL syntax... Is there a better query I should be running, or perhaps misconfiguration of my AWS config environment? Why is Config returning the same object so many times?

score 0 · Answer 1 · answered May 03 '21 at 01:52

My best guess thus far is that I'm querying "Configuration Items", not resources so I get multiple results.

No, your query is correct. AWS::Config::ResourceCompliance is the resourceType you would use to query compliance results.

Is there a better query I should be running, or perhaps misconfiguration of my AWS config environment? Why is Config returning the same object so many times?

Are you recording in 15 regions by chance? If so, it seems like you are recording global resources (such as IAM users) in each region instead of one, primary region.

You can check from the AWS Config Service console page: select Settings, then Edit, you should see a checkbox for Include global resources (e.g., AWS IAM resources).

score 0 · Answer 2 · answered May 29 '21 at 07:57

IAM User, like most AWS IAM resources, is a global resource, so if you've enabled Include global resources/includeGlobalResourceTypes setting for every Config recorded in your 15 regions, there will be 15 configuration items of the same IAM User (one for every region) recorded and available to query in your Organization aggregator.

AWS Config advanced queries do not support DISTINCT at this time, and awsRegion for IAM User resource is set to global, so it doesn't look like there's a way to filter out the duplicates.

I'm afraid your only option is to disable Include global resources/includeGlobalResourceTypes setting on your Config recorders in all but one region (e.g. us-east-1), so only one configuration item is created aggregated for each global resource.

How can I get a unique count of resources from AWS Config Advanced query

2 Answers2