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 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"
Last modified 1yr ago