CLI Quicksight filter

Question & Answer
, ,
1

Hi,

The line below returns

aws quicksight list-data-sources --aws-account-id=000000000  --region eu-west-1

{
“DataSources”: [
{
“Arn”: “arn:aws:quicksight:eu-west-1:00000000:datasource/0000000c-0000-4f80-b992-c00000000”,
“DataSourceId”: “0000000c-0000-4f80-b992-c00000000”,
“Name”: “DWH”,
“Type”: “REDSHIFT”,
“Status”: “UPDATE_SUCCESSFUL”,
“CreatedTime”: “2018-07-25T15:58:57.582000+02:00”,
“LastUpdatedTime”: “2018-07-25T15:58:57.665000+02:00”,
“DataSourceParameters”: {
“RedshiftParameters”: {
“Host”: “vvv-dwh-ddddd.xxxxxx.eu-west-1.redshift.amazonaws.com”,
“Port”: aaaa,
“Database”: “ccc”,
“ClusterId”: “vvv-dwh-ddddd”
}
},
“SslProperties”: {
“DisableSsl”: false
}
} …

I would like to query by Host name. I can get to the host key but can’t make figure out how to appply value filter.

aws quicksight list-data-sources --aws-account-id=000000000  --region eu-west-1 --query "DataSources[].DataSourceParameters[].values(@)[].Host[]"

Cheers,
A.

2

Artsal,

If you want to filter the results by a specific value, you will need to add a comparison in the query expression. If you want to display all the result fields filtered by value ‘xxx’, it should look like ‘DataSources[?DataSourceParameters.values(@).Host==`xxx`]’. For more examples and explanation, you can go here for documentation on filtering AWS CLI output.

Hope this helps.

Krishna

1 Like
3

Hi @Krishna

I am afraid that your solution doesn’t work.

The following query returns an error.

aws quicksight list-data-sources --aws-account-id=000000000000 --region eu-west-1 --query "DataSources[?DataSourceParameters.values(@).Host=='abc.xxxxxx.eu-west-1.redshift.amazonaws.com']"

Error:

In function values(), invalid type for value: None, expected one of: [‘object’], received: “null”

Best,
Art

4

Art,

Apologies, I didn’t look at the JSON structure properly. Please try using --query ‘DataSources[?DataSourceParameters.RedshiftParameters.Host==`xxx`]’.

Thanks,
Krishna

1 Like
5

Thanks Krishna!

It works fine now :slight_smile: … if I may … and how would one filter by Port? When I try to filter by Port I get ‘’
The port number is without quotes: “Port”: 5439
Many thanks,
Art

6

Sorry just realised … the reason why I tried to use

DataSources.DataSourceParameters.values(@)

is that there are also other parameters than ‘RedshiftParameters’.

I want filter by host regardless of this value.

Thanks,
Art

7

Art,

Please take a look at the documentation related to client side filtering. The output from the AWS CLI command is JSON and you will be able to use standard JMESPath syntax to create the expressions to meet your filtering requirements in the query flag.

I have not tested it, but if you want to filter on two values (Host & Port) where both values need to be true, the expression may be something like ‘DataSources[?DataSourceParameters.RedshiftParameters.Host==`xxx` && DataSourceParameters.RedshiftParameters.Port==`xxx`]’.

8

Hi Krishna,

Many thanks for your replay however it seems that my question was confusing. I want to find out how I can run the query but without specifying the path ‘name’ as the name can vary. For example your last statement:

‘DataSources[?DataSourceParameters.RedshiftParameters.Host==xxx && DataSourceParameters.RedshiftParameters.Port==xxx]’

I would like to replace ‘RedshiftParameters’ with an index or something else as the name can change to ‘SnowflakeParameters’. So currently to get all values I would have to run two queries:

‘DataSources[?DataSourceParameters.RedshiftParameters.Host==xxx && DataSourceParameters.RedshiftParameters.Port==xxx]’

and

‘DataSources[?DataSourceParameters.SnowflakeParameters.Host==xxx && DataSourceParameters.SnowflakeParameters.Port==xxx]’

I would like to run just one.

Many thanks,
Art

9

Hi @Krishna,
Any thoughts on my question?
Cheers,
Art