Links

Standard SQL Queries

Getting started with SQL queries in Resmo
SQL is a well-known and powerful language. Resmo users can query their resources and changes using standard SQL statements with auto-complete support.

Query examples

All SQL queries are standard Select statements.
Simple SQL query
List all Jira users
SELECT * FROM jira_user

SQL query with where clause

List all active Jira users
SELECT accountId, displayName, emailAddress FROM jira_user WHERE active = true AND accountType = 'atlassian'

SQL query with Group By clause

Jira users per group
SELECT g, COUNT(*) FROM jira_user u, u.groups g GROUP BY gSimple SQL query with where statement
SQL query with IN statement to run cross resource queries
NewRelic users that don’t exist on Google Workspace
SELECT name, email FROM newrelic_user WHERE email NOT IN (SELECT VALUE primaryEmail FROM gsuite_user)
SQL query response filtered for a field in an object
Print aliasTager's dnsName:
SELECT accountId,accountName,hostedZoneName,name, aliasTarget.dnsName FROM aws_route53_hosted_zone_record
SQL query with nested objects
Most configuration data is nested and it's easy to refer to inner objects with the dot notation.
SELECT metadata.namespace, metadata.name FROM kubernetes_replicaset r, r.spec.template.spec.containers container

Functions

Functions help simplify queries that are hard to write.
The following function with aws_sg_allows function shortens the SQL query a lot.
SELECT accountId, region, vpcId, name, description FROM aws_vpc_security_group WHERE aws_sg_allows(ingress, '0.0.0.0', 25, 3389)
The following functions are available at the moment:
  • TBA

Supported SQL keywords

All standard keywords supported in Select SQL queries are available to use.
Some common keywords are:
  • DISTINCT
  • SUM
  • COUNT
  • COALESCE

Reserved Keywords

Keyword list
"absolute", "action", "add", "all", "allocate", "alter", "and", "any", "are", "as", "asc", "assertion", "at", "authorization", "avg", "begin", "between", "bit", "bit_length", "by", "cascade", "cascaded", "case", "cast", "catalog", "char", "character", "character_length", "char_length", "check", "close", "coalesce", "collate", "collation", "column", "commit", "connect", "connection", "constraint", "constraints", "continue", "convert", "corresponding", "count", "create", "cross", "current", "current_date", "current_time", "current_timestamp", "current_user", "cursor", "date", "deallocate", "dec", "decimal", "declare", "default", "deferrable", "deferred", "delete", "desc", "describe", "descriptor", "diagnostics", "disconnect", "distinct", "domain", "double", "drop", "else", "end", "end-exec", "escape", "except", "exception", "exec", "execute", "exists", "external", "extract", "date_add", "date_diff", "false", "fetch", "first", "float", "for", "foreign", "found", "from", "full", "get", "global", "go", "goto", "grant", "group", "having", "identity", "immediate", "in", "indicator", "initially", "inner", "input", "insensitive", "insert", "int", "integer", "intersect", "interval", "into", "is", "isolation", "join", "key", "language", "last", "left", "level", "like", "local", "lower", "match", "max", "min", "module", "names", "national", "natural", "nchar", "next", "no", "not", "null", "nullif", "coalesce", "numeric", "octet_length", "of", "on", "only", "open", "option", "or", "order", "outer", "output", "overlaps", "pad", "partial", "position", "precision", "prepare", "preserve", "primary", "prior", "privileges", "procedure", "public", "read", "real", "references", "relative", "restrict", "revoke", "right", "rollback", "rows", "schema", "scroll", "section", "select", "session", "session_user", "set", "size", "smallint", "some", "space", "sql", "sqlcode", "sqlerror", "sqlstate", "substring", "sum", "system_user", "table", "temporary", "then", "time", "timestamp", "to", "transaction", "translate", "translation", "trim", "true", "union", "unique", "unknown", "update", "upper", "usage", "user", "using", "value", "values", "varchar", "varying", "view", "when", "whenever", "where", "with", "work", "write", "zone"