Add AWS:Application type support to aws_ssm_inventory_entry

[atfurman]

Is your feature request related to a problem? Please describe.

I am attempting to generate a list of all installed software reported by SSM across an AWS organization.

Upon attempting to query AWS:Application data from SSM Inventory I encountered the following error:

> select * from aws_ssm_inventory_entry where type_name = 'AWS:Application'

Error: operation error SSM: GetInventory, https response error StatusCode: 400, RequestID: fb054b86-2b0f-49ef-83b2-be7a730ae056, InvalidResultAttributeException: Result type name AWS:Application is not supported. Supported type names are: AWS:InstanceInformation, AWS:PatchSummary. Default result type name is AWS:InstanceInformation.
        operation error SSM: GetInventory, https response error StatusCode: 400, RequestID: 5ef2f6f6-9be2-4b6f-b505-ba0b7827f761, InvalidResultAttributeException: Result type name AWS:Application is not supported. Supported type names are: AWS:InstanceInformation, AWS:PatchSummary. Default result type name is AWS:InstanceInformation.
        operation error SSM: GetInventory, https response error StatusCode: 400, RequestID: c5ec771b-9e2e-4527-b0ac-faacfbdb7ce6, InvalidResultAttributeException: Result type name AWS:Application is not supported. Supported type names are: AWS:InstanceInformation, AWS:PatchSummary. Default result type name is AWS:InstanceInformation.
        operation error SSM: GetInventory, https response error StatusCode: 400, RequestID: 37967035-b155-4a13-b735-cafb964befc9, InvalidResultAttributeException: Result type name AWS:Application is not supported. Supported type names are: AWS:InstanceInformation, AWS:PatchSummary. Default result type name is AWS:InstanceInformation.
        operation error SSM: GetInventory, https response error StatusCode: 400, RequestID: 0bd22bc4-e35b-4973-bdca-9bb489e37abd, InvalidResultAttributeException: Result type name AWS:Application is not supported. Supported type names are: AWS:InstanceInformation, AWS:PatchSummary. Default result type name is AWS:InstanceInformation.
        operation error SSM: GetInventory, https response error StatusCode: 400, RequestID: d567f928-4a06-4576-835e-3c56dd29149a, InvalidResultAttributeException: Result type name AWS:Application is not supported. Supported type names are: AWS:InstanceInformation, AWS:PatchSummary. Default result type name is AWS:InstanceInformation. (SQLSTATE HV000)

+-------------+-----------+--------------+----------------+---------+-------+-----------+--------+------------+------+
| instance_id | type_name | capture_time | schema_version | entries | title | partition | region | account_id | _ctx |
+-------------+-----------+--------------+----------------+---------+-------+-----------+--------+------------+------+
+----

Describe the solution you'd like
Support for AWS:Application type, in one form or another.

Describe alternatives you've considered
We have an existing solution in the form of a custom python application using boto3 but would strongly prefer to migrate this functionality to steampipe if it were possible to do so.

Is support on for this on the roadmap?

Additional context
Add any other context or screenshots about the feature request here.

[ParthaI]

Hello, @atfurman , I'm sorry to hear that you're encountering an issue, and I appreciate you bringing it to our attention. We will investigate and address it if there is a feasibility to add that support.

Thank You!

[ParthaI]

Hi @atfurman, I've made some discoveries regarding the issue you're encountering. Please have a look.

• Based on the AWS API doc, the error(InvalidResultAttributeException) is expected here.
• In terms of design, aws_ssm_inventory functions as the parent table to aws_ssm_inventory_entry. Both tables feature an optional qualifier, type_name, sharing the same column name.
• Following the design principles, when the ParentHydrate function is employed in a table, the parent table (aws_ssm_inventory) is queried first, followed by the child table (aws_ssm_inventory_entry).
• Within the parent table aws_ssm_inventory, the type_name specified in the WHERE clause is forwarded to the [ResultAttribute](https://github.com/turbot/steampipe-plugin-aws/blob/main/aws/table_aws_ssm_inventory.go#L232) as an input parameter. However, the provided value (AWS:Application) is not compatible with the GetInventory API call, leading to an error. Refer to the documentation for a list of supported type names for the ResultAttribute.

Related issue concerning the type_name in aws_ssm_inventory_entry: GitHub Issue #1880.

• When querying the aws_ssm_inventory_entry table with a specific type_name in the WHERE clause (e.g., SELECT instance_id, type_name FROM aws_ssm_inventory_entry WHERE type_name = 'AWS:AWS:Application'), the query retrieves all inventory data by first accessing the parent table aws_ssm_inventory, as configured by the parent hydrate in aws_ssm_inventory_entry.
• The type_name value specified in the query parameter is passed down to the parent table aws_ssm_inventory.
• The aws_ssm_inventory table's list API call constructs the input parameter based on the query parameters provided in the WHERE clause.
• Although aws_ssm_inventory uses the type_name from the optional qualifiers as input for the GetInventory API call, this results in an error. The reason is that AWS:Application is not a supported input parameter (ResultAttribute) for the GetInventory API call. Instead, it is a valid value for the ListInventoryEntries API, which is a list call used by the aws_ssm_inventory_entry table.

@cbruno10, I'm seeking advice regarding table design. Would it be more effective to change the column name type_name to entry_type_name in the aws_ssm_inventory_entry table and improve the optional qual implementation for better filtering? Do you have any other ideas that could help solve the ambiguity issue?

This way, we can construct the input parameter based on the specific API used for each table (aws_ssm_inventory_entry and aws_ssm_inventory), thus reducing any potential confusion.

Based on the AWS documentation for the aws_ssm_inventory table, we can utilize the Filter parameter along with various permutations and combinations of the type_name value as follows:

 aws ssm get-inventory --aggregators 'Groups=[{Name=ApplicationAndFile,Filters=[{Key=TypeName,Values=[AWS:Application],Type=Equal}]}]'

  aws ssm get-inventory --aggregators 'Groups=[{Name=ApplicationAndFile,Filters=[{Key=TypeName,Values=[AWS:InstanceInformation],Type=Exists}]}]'

  aws ssm get-inventory --filters "Key=AWS:InstanceInformation.PlatformType,Type=Equal,Values=Linux"

  aws ssm get-inventory --filters "Key=AWS:InstanceInformation.PlatformType"

  aws ssm get-inventory --aggregators '[{"Expression": "AWS:InstanceInformation.PlatformName"}]'

Thanks!

[atfurman]

Thanks for the quick and detailed response @ParthaI! If I am understanding correctly, this is currently a design limitation for steampipe and would require changes to the table aws_ssm_inventory_entry to resolve.

I'll continue to follow this issue closely, as my org will need to make a decision within the next few weeks to either wait for steampipe to support this or to redirect resources to updating our existing internally designed solution to meet our reporting needs. Any updates you are able to provide in terms of intent/feasibility to address and any timeline estimates would be helpful in informing our decision and greatly appreciated.

[cbruno10]

@ParthaI Here's the proposal we had discussed:

• Add new columns to aws_ssm_inventory to cover all Filter input param keys
  • Check if the value from the API is the same as what a user would input in Filter, this will determine if we can get the data from the API and if we can/should populate from FromQual
  • Other notes on how we support Filter:
    • Goal is to make filtering as easy as possible in queries, so not adding a JSON filter column for now
    • Only support Type=Equal with a single value, even though the API can accept multiple values
• Test if we can use the instance_id column value qual from aws_ssm_inventory_entry in aws_ssm_inventory without adding it as a new column/new key column in aws_ssm_inventory, e.g.,

if equalQuals["instance_id"] != nil {
  // Use here
}

• Update aws_ssm_inventory so type_name column from aws_ssm_inventory_entry is not used in the Filter’s ResultAttributes
  • This is a breaking change, but a necessary one I believe as users cannot pass any value into the type_name column for the aws_ssm_inventory_entry table today anyway
• We will not add Filter support in aws_ssm_inventory_entry table for now but we can later on if users need it
  • When we add it, we should also use key columns from aws_ssm_inventory_entry table in aws_ssm_inventory table since we can’t avoid calling the parent hydrate currently

[ParthaI]

@cbruno10, I have raised a Draft PR regarding the above discussion for this issue. Please take a look.

• Add new columns to aws_ssm_inventory to cover all Filter input param keys

• I have added nearly 30 optional quals columns to cover all Filter input param keys
• We can cover all Filter input param keys by adding the two optional columns(filter_key and filter_value) only, but we can not filter the results with multiple filter values in a single query.

---

• Check if the value from the API is the same as what a user would input in Filter, this will determine if we can get the data from the API and if we can/should populate from FromQual

• It is a bit tricky to achieve the goal of determining the column value from the API response.
• As per the API behavior, the API always returns the response of the type name that is supported for the API input param ResultAttribute where the provided filter value matches the type name attribute KEY and VALUE internally and returns the result as per ResultAttribute input param provided in.
  • E.g: If we run the query select * from aws_nagraj.aws_ssm_inventory where application_attribute_key = 'ApplicationType' and application_attribute_value = 'Development/Libraries'. it filters the inventory data of the type name AWS:InstanceInformation(Default value of ResultAttribute) internally from the API end, where the type name attribute property(ApplicationType) matches with the given value(Development/Libraries) for the inventories and returns that inventory of type name AWS:InstanceInformation.
  • Note: The API behavior is the same in AWS CLI. The relevant CLI command for achieving the above steampipe query is aws ssm get-inventory --filters "Key=AWS:Application.ApplicationType,Values=Development/Libraries,Type=Equal"

---

• Other notes on how we support Filter:

• Goal is to make filtering as easy as possible in queries, so not adding a JSON filter column for now
• Only support Type=Equal with a single value, even though the API can accept multiple values

• We have added separate columns for different type names for which filtration can be happen by the API
• Currently, the optional quals support Type=Equal with a single value. If the user wants to get the results for multiple values by providing the value in the IN clause, then our Steampipe SDK has the capability to handle it and return the result as expected.

---

• Test if we can use the instance_id column value qual from aws_ssm_inventory_entry in aws_ssm_inventory without adding it as a new column/new key column in aws_ssm_inventory, e.g.,

if equalQuals["instance_id"] != nil {
 // Use here
}

• Now the table aws_ssm_inventory can use the instance_id column value qual from aws_ssm_inventory_entry in aws_ssm_inventory without adding it as a new column/new key column in aws_ssm_inventory
• E.g; If we run the query select * from aws_ssm_inventory_entry where instance_id = 'i-kel37462ss3', the instance_id value will be use in the parent table aws_ssm_inventory for filtering out the inventories for that particular instance.

---

• Update aws_ssm_inventory so type_name column from aws_ssm_inventory_entry is not used in the Filter’s ResultAttributes

• This is a breaking change, but a necessary one I believe as users cannot pass any value into the type_name column for the aws_ssm_inventory_entry table today anyway

• Updated the table aws_ssm_inventory code not to use the type_name column from aws_ssm_inventory_entry table today anyway.

---

• We will not add Filter support in aws_ssm_inventory_entry table for now but we can later on if users need it

• When we add it, we should also use key columns from aws_ssm_inventory_entry table in aws_ssm_inventory table since we can’t avoid calling the parent hydrate currently

• We have not used any filter in the table aws_ssm_inventory_entry, only one change has been made that filter by type_name. If the type_name has been specified in the query parameter, then use that value; otherwise, fall back to the type name value of the parent table(aws_ssm_inventory).

Additional information:

I have tested the changes with possible query parameter combinations as follows.

OK:

1. select * from aws_nagraj.aws_ssm_inventory where application_attribute_key = 'ApplicationType' and application_attribute_value = 'Development/Libraries'
2. select * from aws_nagraj.aws_ssm_inventory where application_attribute_key = 'Architecture' and application_attribute_value = 'x86_64'
3. select * from aws_nagraj.aws_ssm_inventory where application_attribute_key = 'Name' and application_attribute_value = 'PyYAML'
4. select * from aws_nagraj.aws_ssm_inventory where application_attribute_key = 'ApplicationType' and application_attribute_value in ('Development/Libraries', 'System Environment/Libraries')
5. select * from aws_nagraj.aws_ssm_inventory where application_attribute_key in ('ApplicationType', 'Architecture') and application_attribute_value = 'Development/Libraries'
6. select * from aws_nagraj.aws_ssm_inventory where tag_attribute_key = 'Name'
7. select * from aws_nagraj.aws_ssm_inventory where resource_group_attribute_key = 'Name' and resource_group_attribute_value = 'test32'

NOT OK:

1. select * from aws_nagraj.aws_ssm_inventory where filter_key = 'Custom:InspectorThirdPartyPackage.Message' and filter_value = 'SUCCESS'
2. select * from aws_nagraj.aws_ssm_inventory where application_attribute_key in ('ApplicationType', 'Architecture') and application_attribute_value in ('Development/Libraries', 'x86_64')

@cbruno10, Please review the PR and provide feedback on the design and column naming convention. Please let me know if you have any suggestions for improvements or other changes.

@atfurman, Could you please test it in the PR branch issue-1972 and share your feedback regarding whether the code changes meet your requirements? Your input is greatly appreciated.

Steps to test the code changes:

• git clone https://github.com/turbot/steampipe-plugin-aws.git
• cd steampipe-plugin-aws
• git checkout issue-1972
• make
• Run steampipe query

Thank you!

[atfurman]

@ParthaI, I have tested issue-1972 and it appears to do exactly what we originally hoped the plugin would do. Many thanks for addressing this so quickly! 💯

[atfurman]

Happy new year @ParthaI @cbruno10. I would like to follow up on this to see if it is possible to get any estimate for this feature to be added to an official release. We are preparing a release of our internal tooling for later this month and have a couple of features which are dependent upon #1980 - will shortly need to make a decision to include in the upcoming release or delay for another cycle.