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"