Table: aws_elasticsearch_domains

SELECT
  'Connections to Elasticsearch domains should be encrypted using TLS 1.2'
    AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN domain_endpoint_options->>'TLSSecurityPolicy'
  IS DISTINCT FROM 'Policy-Min-TLS-1-2-2019-07'
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_elasticsearch_domains;

SELECT
  'Elasticsearch domain error logging to CloudWatch Logs should be enabled'
    AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN log_publishing_options->'ES_APPLICATION_LOGS'->'Enabled'
  IS DISTINCT FROM 'true'
  OR (
      log_publishing_options->'ES_APPLICATION_LOGS'->'CloudWatchLogsLogGroupArn'
    ) IS NULL
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_elasticsearch_domains;

SELECT
  'Elasticsearch domains should be configured with at least three dedicated master nodes'
    AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN (elasticsearch_cluster_config->>'DedicatedMasterEnabled')::BOOL
  IS NOT true
  OR (elasticsearch_cluster_config->>'DedicatedMasterCount')::INT8 IS NULL
  OR (elasticsearch_cluster_config->>'DedicatedMasterCount')::INT8 < 3
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_elasticsearch_domains;

SELECT
  'Elasticsearch domains should be in a VPC' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN (vpc_options->>'VPCId') IS NULL THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_elasticsearch_domains;

SELECT
  'Elasticsearch domains should encrypt data sent between nodes' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN (node_to_node_encryption_options->>'Enabled')::BOOL IS NOT true
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_elasticsearch_domains;

SELECT
  'Elasticsearch domains should have at least three data nodes' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN NOT (elasticsearch_cluster_config->>'ZoneAwarenessEnabled')::BOOL
  OR (elasticsearch_cluster_config->>'InstanceCount')::INT8 IS NULL
  OR (elasticsearch_cluster_config->>'InstanceCount')::INT8 < 3
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_elasticsearch_domains;

SELECT
  'Elasticsearch domains should have audit logging enabled' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN log_publishing_options->'AUDIT_LOGS'->'Enabled' IS DISTINCT FROM 'true'
  OR (log_publishing_options->'AUDIT_LOGS'->'CloudWatchLogsLogGroupArn') IS NULL
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_elasticsearch_domains;

SELECT
  'Elasticsearch domains should have encryption at rest enabled' AS title,
  account_id,
  arn AS resource_id,
  CASE
  WHEN (encryption_at_rest_options->>'Enabled')::BOOL IS NOT true THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_elasticsearch_domains;