Filters

Last modified on September 20, 2023

Filters allow you to narrow request results when programmatically interacting with StrongDM via the CLI or the API. This article describes how to use filters with the sdm admin CLI commands, including proper syntax, usage examples, and available filter parameters and values.

For information on how to use filters in API requests with any of StrongDM’s SDKs, please consult the documentation for the specific tool you wish to use.

Syntax and Filtering Considerations

Filters are specified in sdm admin CLI commands with the --filter flag followed by the field and the value on which you want to filter.

Example:

sdm admin users list --filter '<FIELD>:<VALUE>'

Let’s say, for example, that your organization has 50 users whose first name is Sam, and you want to list only those people. To do that, run the command with the filter set as follows:

sdm admin users list --filter 'firstname:sam'

Possible filter fields and values are described in section Filter Parameters by Entity and section Potential Resource Type Values.

Wildcards

The --filter flag accepts wildcard (*) values for certain fields, such as name and email. For example, you can use a filter and wildcard to list only users whose email address ends with @strongdm.com:

sdm admin users list --filter 'email:*@strongdm.com'

Special characters

Special characters must be properly escaped using quotation marks. Additionally, multi-word names in filters must be encapsulated in quotation marks. For example, name:"Foo Bar" is correct, and name:Foo Bar is not.

Case

Filters are case-insensitive. Uppercase and lowercase values return the same results.

Usage Examples

This section provides examples of various ways to use single filters and multiple filters with the sdm admin management commands.

List Servers by name

The following example command shows how you can apply a filter to list all servers with a name that includes the word “admin.” Note the use of wildcards around “admin.”

$ sdm admin servers list --filter 'name:*admin*'
Server ID               Name                           Type
rs-03ad1e1b240c85c1     azure-gateway - CA (admin)     sshCert
rs-7bb96dd41d9ac70b     azure-gateway-admin            ssh

Show only sshCert Servers

In the following example, the type filter is used to list SSH Certificate-type servers:

$ sdm admin servers list --filter 'type:sshCert'
Server ID               Name                           Type
rs-1b08901ed124e296     azure-gateway                  sshCert
rs-2b73c2267a7e1379     azure-gateway - CA (root)      sshCert
rs-03ad1e1b240c85c1     azure-gateway - CA (admin)     sshCert

Use multiple filters

The following example uses two filters, one for type and one for name, to list SSH Certificate-type servers that have admin in their name:

$ sdm admin servers list --filter 'type:sshCert,name:*admin*'
Server ID               Name                           Type
rs-03ad1e1b240c85c1     azure-gateway - CA (admin)     sshCert

Use multiple flags for multiple filters

You can also provide filters as separate flags to achieve the same results, as in the following example:

$ sdm admin servers list --filter 'type:sshCert' --filter 'name:*admin*'
Server ID               Name                           Type
rs-03ad1e1b240c85c1     azure-gateway - CA (admin)     sshCert

Filter based on ID

When id is used as a filter, results return any matching results. Because every ID is unique, it would be impossible to match more than one simultaneously if multiple id filters are provided.

In the example shown, you can see that listing servers and filtering by id results in a list of all servers that have the specified IDs.

$ sdm admin servers list --filter 'id:rs-1b08901ed124e296' --filter 'id:rs-2b73c2267a7e1379' --filter 'id:rs-03ad1e1b240c85c1'
Server ID               Name                           Type
rs-1b08901ed124e296     azure-gateway - CA             sshCert
rs-2b73c2267a7e1379     azure-gateway - CA (Copy)      sshCert
rs-03ad1e1b240c85c1     azure-gateway - CA (admin)     sshCert

Bulk Operations Examples

You can use filters to assist with various bulk actions, such as showing all websites for a given hostname, deleting a group of resources, and so forth. This section includes some examples of such bulk operations.

Update multiple Resources

You may use filters to do batch updates on multiple resources.

In the following example, an Update command is used with the --filter and --tags flags to add the env=public tag to all HTTP (No Auth) type-websites:

$ sdm admin websites update --filter 'type:httpnoauth' --tags 'env=public'
changed 4 out of 4 matching datasources

To check that the env=public tag has been applied to the correct websites, you can filter for all websites with the type httpnoauth, as in the following example:

$ sdm admin websites list --filter 'type:httpnoauth'
Website ID              Name                    Type           Tags
rs-3b34c199bef73d19     google                  httpNoAuth     env=public
rs-000000000004682d     ksql control center     httpNoAuth     env=public
rs-4d1c88780405f0ad     potato                  httpNoAuth     env=public
rs-000000000004d17f     support kibana          httpNoAuth     env=public

Delete multiple Resources

You can use the --filter flag to delete a group of the same resources that have something in common. The filter specifies what they have in common, such as an assigned tag or the resource type.

In the example shown, the --filter flag is used to delete all the websites that are tagged with env=public.

$ sdm admin websites delete --filter 'tags:env=public' --apply
deleted 4 datasources

JSON Filters

For larger or more complex search queries, you can use a JSON file to define your list of filters. Commands that point to JSON files use the --filter-json flag instead of --filter.

Example:

sdm admin datasources list --filter-json <PATH_TO_YOUR_JSON_FILE>

Let’s say that you want to list a specific datasource and all PostgreSQL datasources that have been assigned the region=EU tag. Your command includes the --filter-json flag and the path to the JSON filter file:

sdm admin datasources list --filter-json /Users/alice.glick/Documents/example.json

The JSON filter file includes several filter parameters and their values, as in the following example:

[
    {
        "ids": [
            "rs-0835300a78ea36a0"
        ]
    },
    {
        "type": "postgres",
        "tags": {
            "region": "EU"
        }
    }
]

Note that the JSON-based filter is the union of filters, whose attributes are additive. In this example, results of the filter file are the union of one datasource (id = rs-0835300a78ea36a0) and all datasources whose type is postgres and contain the region=EU tag.

Filter Parameters by Entity

Fields available to filter on vary by entity type. This section describes all possible filter parameters for the following entity types:

  • Accounts (users and services)
  • Gateways and relays
  • Resources (clouds, clusters, datasources, servers, websites)
  • Roles

Supported data types for filter values

Data typeDescription
BooleanTrue or false values, including true, false, t, f, 1, and 0
EmailText values that are properly formatted email addresses
IPSupports IPv4 address with or without port
KVPKey-value pair in the format title=value
StringAny non-null value
URLData that follows the pattern of a URL

Users and services

FieldDescriptionValue typeUsage example
activeUsers who have (true) or have not (false) actively used StrongDM in the last 90 daysBooleansdm admin users list --filter 'active:false'
emailUser’s email addressEmailsdm admin users list --filter 'email:alice.glick@strongdm.com'
firstnameUser’s first nameString`sdm admin users
fullnameUser’s full name (first and last) or the service account’s nameStringsdm admin services list --filter 'fullname:*Service
idUser IDStringsdm admin users list --filter 'id:a-005c9fd06213dba8'
lastnameUser’s last nameStringsdm admin users list --filter 'lastname:glick'
lockedUsers who are locked out or not from StrongDMBooleansdm admin users list --filter 'locked:true'
managedUsers who are managed and provisioned by StrongDM (false) or managed and provisioned by a third-party identity provider (true) such as Azure AD or OktaBooleansdm admin users list --filter 'managed:false'
permissionlevelUser’s permission level (admin, admin-token, auditor, database-admin, multi-team-leader, relay, service, suspended, scim-token, or user)Stringsdm admin users list --filter 'permissionlevel:database-admin'
suspendedUser’s statusBooleansdm admin users list --filter 'suspended:true'
tagsTag(s) assigned to the user; supports wildcards (*); tag values containing commas must be inside quotesKVPsdm admin users list --filter 'tags:region="useast,uswest"'
typeType of account (user or service)Stringsdm admin users list --filter 'type:user'

Gateways and relays

FieldDescriptionValue typeUsage example
bindaddrBind address; note that this parameter is only for gatewaysIPsdm admin relays list gateways --filter 'bindaddr:0.0.0.0:5000'
idID of the gateway or relayStringsdm admin relays list --filter 'id:n-123abc4d567e89fg'
listenaddrIP or host address that the gateway listens on; this parameter is only for gateways, as relays do not listen for client connectionsIP, URLsdm admin gateways --filter 'listenaddr:ec2-1-23-456-78.compute-1.amazonaws.com:5000'
nameName of the gateway or relayStringsdm admin relays list --filter 'name:docs'
onlineStatus of the gateway or relayBooleansdm admin relays list --filter 'online:false'
tagsResource tag(s) assigned to the gateway or relayKVPsdm admin relays list --filter 'tag:env=dev'
typeNode type (gateway or relay)Stringsdm admin relays list --filter 'type:relay'

Resources: clouds, clusters, datasources, servers, websites

FieldDescriptionValue typeUsage example
bindinterfaceIP address to which the resource is bound, in the 127.0.0.1 to 127.255.255.254 IP address range; default is 127.0.0.1IPsdm admin datasources list --filter 'bindinterface:127.0.0.1'
healthyHealth status of the resourceBooleansdm admin datasources list --filter 'healthy:false'
hostnameHostname of the resource; for websites, the URL of the websiteURLsdm admin datasources list --filter 'hostname:example-host.com'
httpsubdomainOrganization’s web domain valueStringsdm admin datasources list --filter 'httpsubdomain:education-team'
idID of the resourceStringsdm admin datasources list --filter 'id:rs-058a6582617b2c95'
nameName of the resourceStringsdm admin datasources list --filter 'name:ExampleResourceName'
portPort numberNumbersdm admin datasources list --filter 'port:27017'
portoverridePort override to which the resource is boundNumbersdm admin datasources list --filter 'portoverride:1234'
remoteIdentityEnabledMethod of authentication for the resource, either Remote Identities (true) or leased credentials (false)Booleansdm admin servers list --filter 'remoteidentityenabled:true'
secretStoreIdSecret store identifier for the resource; use sdm admin secretstores list to get itStringsdm admin clouds list --filter 'secretStoreId:se-1a2b3cd45678e9f1'
tagsResource tag(s) assigned to the resourceKVPsdm admin datasources list --filter 'tag:env=dev'
typeSpecific type of resource (for example, sshCert, redis, and so forth)Stringsdm admin datasources list --filter 'type:redis'
usernameUsername to be used for authentication to the resourceStringsdm admin datasources list --filter 'username:admin'

Roles

FieldDescriptionValue typeUsage example
idRole IDStringsdm admin roles list --filter 'id:r-449dd90f60f610d7'
managedRoles (groups) that are managed and provisioned by StrongDM (false) or managed and provisioned by a third-party identity provider (true) such as Azure AD or OktaBooleansdm admin roles list --filter 'managed:false'
nameName of the roleStringsdm admin roles list --filter 'name:Docs'
tagsTag(s) assigned to the roleKVPsdm admin roles update 'Test Role' --tags 'env=dev'

Potential Resource Type Values

This section provides the accepted values for each resource type.

Datasources

This table provides the values for each datasource type.

Datasource typeValue
Amazon ESamazones
Amazon MQ (AMQP 0.9.1)amazonmq-amqp-091
Athenaathena
Aurora MySQLaurora-mysql
Aurora PostgreSQLaurora-postgres
Azure Database for MySQLazuremysql
BigQuerybigquery
Cassandracassandra
Cituscitus
Clustrixclustrix
CockroachDBcockroach
DocumentDB (replica set)documentdbreplicaset
DocumentDB (single host)documentdbhost
Druiddruid
DynamoDBdynamo
ElastiCache Redisecredis
Elasticsearchelastic
Greenplumgreenplum
Mariamaria
Memcachedmemcached
MemSQLmemsql
Microsoft SQL Servermssql
Microsoft SQL Server (Azure AD)mssqlAzureAD
Microsoft SQL Server (Kerberos)mssqlKerberos
MongoDB (replica set)mongo-replicaset
MongoDB (single host)mongo
MySQLmysql
Neptuneneptune
Neptune (IAM)neptuneiam
Oracleoracle
PostgreSQLpostgres
PostgreSQL (mTLS)mTLSPostgres
Prestopresto
RabbitMQ (AMQP 0.9.1)rabbitmq-amqp-091
Redisredis
Redshiftredshift
SingleStoresinglestore
Snowflakesnowflake
Sybase ASEsybase
Sybase IQsybase-iq, sybaseiq
Teradatateradata

Servers

This table provides the values for each server type.

Server typeValue
RDPrdp
SSH (Public Key)ssh
SSH (Certificate Based)ssh-cert, sshCert
SSH (Customer Managed Key)ssh-customer-key
TCPrawtcp

Clusters

This table provides the values for each cluster type.

Cluster typeValue
AKSaks
AKS (HTTP Basic Auth)aks-basic, aksbasic
AKS (Service Account)aks-service, aksservice
AKS (Service Account - User Impersonation)aks-service-ui, aksserviceui
AKS (User Impersonation)aks-ui
Elastic Kubernetes Serviceamazon-eks, amazoneks, eks
Elastic Kubernetes Service (User Impersonation)amazon-eks-ui, amazoneksui, eksui
Google Kubernetes Enginegke
Google Kubernetes Engine (User Impersonation)gke-ui
Kubernetesk8s, kubernetes
Kubernetes (HTTP Basic Auth)k8s-basic, k8sbasic
Kubernetes (Service Account)k8s-service, k8sservice
Kubernetes (Service Account - User Impersonation)k8s-service-ui, k8sserviceui
Kubernetes (User Impersonation)k8s-ui, kubernetesui

Clouds

This table provides the values for each cloud type.

Cloud typeValue
AWSaws
Azure (Certificate)azurecert
Azure (Password)azure
GCPgcp

Websites

This table provides the values for each website type.

Website typeValue
HTTPhttp, httpNoAuth, http-no-auth
HTTP Basic Authhttp-basic, httpBasic, basicauth
HTTP Custom Authhttp-header-auth, headerauth
Top