DDSQL Common Queries and Use Cases

This page is not yet available in Spanish. We are working on its translation.
If you have any questions or feedback about our current translation project, feel free to reach out to us!

General queries

List all libraries across services in production

SELECT
    c.service_name,
    c.team,
    lib.library_name,
    lib.eol,
    lib.last_commit,
    lib.newer_versions_number,
    lib.library_version,
    lib.latest_version,
    lib.ecosystem,
    lib.language,
    lib.license,
    lib.license_type
FROM service_definition c
JOIN library lib ON lib.asset_name = c.service_name
WHERE
    lib.env = 'production'
    AND lib.relation = 'DIRECT'
    AND LOWER(c.domain) = 'domain_name'
    AND LOWER(c.vertical) = 'account'
    AND LOWER(c.type) = 'service'
ORDER BY lib.newer_versions_number DESC

List services running an old version of the tracer

SELECT *
FROM service_config
WHERE client_library_version < '1.31.0';

AWS

List of RDS instances that require SSL/TLS certificate rotation

SELECT account_id,
  aws_organisation,
  aws_environment db_instance_identifier,
  display_name,
  ca_certificate_identifier,
  engine,
  engine_version,
  tags
FROM aws_rds_instance
WHERE ca_certificate_identifier like 'rds-ca-2019'

List EBS snapshots in progress

SELECT description,
  account_id,
  progress
FROM aws_ebs_snapshot
WHERE LOWER(state) != 'completed'
  and LOWER(state) != 'available'

List lambda functions with a specific outdated runtime (in this case python 2.7)

SELECT *
FROM aws_lambda_function
WHERE runtime = 'python2.7'
LIMIT 100;

Further reading

Additional helpful documentation, links, and articles: