Elasticsearch: Sync the search database#

Run the eiq-platform search sync-data command to sync the Elasticsearch indexing and search database with the PostgreSQL main database.

Over time, the Elasticsearch indexing and search database may go out of sync with the PostgreSQL main database. The causes can be different: large re-ingestion operations, database migrations following product upgrades, malformed source data, or operational wear and tear.

For example:

  • An entity may exist in Elasticsearch, but not in PostgreSQL.

    It is possible to search for the entity, but the entity data no longer exists in the PostgreSQL database.

  • An entity may exist in PostgreSQL, but not in Elasticsearch.

    It is not possible to search for the entity, even though the entity data does exist in the PostgreSQL database.

  • An entity in Elasticsearch may have a different timestamp value than the same entity in PostgreSQL.

If you notice that the same entity has different last_updated_at timestamp values in the indexing and search database and in the main database, run the eiq-platform search sync-data command to sync the indexing and search database with the main one.

Sync the search database#

The eiq-platform search sync-data command checks the value of an entity last_updated_at timestamp field to assess if the entity in the Elasticsearch indexing and search database is in sync with the same entity in the PostgreSQL main database.

The command iterates over all entities in the Elasticsearch indexing and search database, and in the PostgreSQL main database.

Since PostgreSQL is the single source of truth for the platform data, any discrepancies are resolved by copying data from PostgreSQL to Elasticsearch.

Depending on system load and the total amount of entities in the platform, the command can take several hours to complete execution.

eiq-platform search sync-data checks the value of an entity last_updated_at timestamp field to check if the entity needs syncing. For more information about the command, append --help to the command.

Usage#

eiq-platform search sync-data

Parameters

Type

Description

Required

Default

--check-results-file

String

You must specify a file name and a path to location to save the report with the summary and the out-of-sync entities.

The report file format is JSON (.json).

Yes

-

--batch-size

Integer

The command analyzes entities in batches.

Specify the maximum amount of entities to group in a batch.

Very large batches may slow down the process, or produce timeouts.

No

1000

--changed-after

Date

Checks only entities whose last_updated_at timestamp is more recent than the date you specify here.

If you specify date and time, date and time values must be wrapped in quotes.

Allowed formats:

  • "yyyy-mm-dd hh:mm:ss"

  • "yyyy-mm-dd hh:mm"

  • yyyy-mm-dd

No

-

--changed-before

Date

Checks only entities whose last_updated_at timestamp is older than the date you specify here.

If you specify date and time, date and time values must be wrapped in quotes.

Allowed formats:

  • "yyyy-mm-dd hh:mm:ss"

  • "yyyy-mm-dd hh:mm"

  • yyyy-mm-dd

No

-

--dry-run

Boolean

Checks syncing inconsistencies between PostgreSQL and Elasticsearch.

It returns a report with the results.

It does not sync the databases.

No

False

--entity-types

String

To limit the sync check only to the selected entity types, specify one or more entity types.

If you specify multiple entity types, separate them with a comma (,).

To check and, if necessary, sync entity relationships, specify relation.

The entity type names correspond to the data.type values.

Allowed values:

  • campaign

  • course-of-action

  • eclecticiq-sighting

  • exploit-target

  • incident

  • indicator

  • package

  • report

  • threat-actor

  • ttp

No

-

--print-diffs

Boolean

Includes in the results an overview of the detected differences in PostgreSQL and Elasticsearch for outdated and strange entities.

If you turn on this option, the operation may take longer.

Use it for debugging purposes.

No

False

Example output of eiq-platform search sync-data --check-results-file log.json:

{
  "event":"Starting data check",
  "level":"info",
  "logger":"eiq.platform.scripts.sync_data_commons",
  "timestamp":"2019-11-29T15:34:10.294127Z"
}
{
  "event":"Data check finished",
  "level":"info",
  "logger":"eiq.platform.scripts.sync_data_commons",
  "n_missing":0,
  "n_not_deleted":0,
  "n_ok":0,
  "n_outdated":0,
  "n_strange":0,
  "timestamp":"2019-11-29T15:34:10.441502Z"
}
{
  "event":"Writing check_results file",
  "filename":"/tmp/search-sync.txt",
  "level":"info",
  "logger":"eiq.platform.scripts.sync_data_commons",
  "timestamp":"2019-11-29T15:34:10.441910Z"
}
{
  "event":"Deleting 0 entities that should not be in Elasticsearch",
  "level":"info",
  "logger":"eiq.platform.scripts.sync_data_in_elasticsearch",
  "timestamp":"2019-11-29T15:34:10.442565Z"
}
{
  "event":"Triggering reindexing of batch 1 of 0 in Elasticsearch",
  "level":"info",
  "logger":"eiq.platform.scripts.sync_data_in_elasticsearch",
  "timestamp":"2019-11-29T15:34:10.442789Z"
}
{
  "event":"reindexing.tasks_to_run",
  "index":"stix",
  "level":"info",
  "logger":"eiq.platform.scripts.sync_data_in_elasticsearch",
  "tasks":0,
  "timestamp":"2019-11-29T15:34:10.443004Z"
}
{
  "event":"sync-data.done for entities",
  "level":"info",
  "logger":"eiq.platform.scripts.sync_data_in_elasticsearch",
  "timestamp":"2019-11-29T15:34:10.443531Z",
  "took":"1.677e-05m",
  "total":0
}

The resulting results file (log.json) should contain output similar to this:

{
  "entity_ids_to_delete": [],
  "entity_ids_to_reindex": [],
  "entity_types": [
    "relation"
  ],
  "n_missing": 0,
  "n_not_deleted": 0,
  "n_ok": 0,
  "n_outdated": 0,
  "n_strange": 0
}

The following table describes possible fields in the results file:

Field

Type

Value

entity_ids_to_delete

Array

Lists IDs of all the entities in the Elasticsearch indexing and search database that do not exist also in the PostgreSQL main database.

Entity IDs are alphanumeric strings corresponding to the data.id and id field values in the entity JSON structure.

These entities are deleted from the indexing and search database.

entity_ids_to_reindex

Array

Lists IDs of all the entities that need re-indexing in the Elasticsearch indexing and search database because of data inconsistencies with regard to the same data in the PostgreSQL main database.

Entity IDs are alphanumeric strings corresponding to the data.id and id field values in the entity JSON structure.

These entities are synced in Elasticsearch by retrieving the corresponding data from PostgreSQL.

n_ok

Integer

Returns the total number of in-sync entities between the two databases.

n_outdated

Integer

Returns the total number of entities whose last_updated_at timestamp is older in Elasticsearch than in PostgreSQL.

These entities are synced in Elasticsearch by retrieving the corresponding data from PostgreSQL.

n_missing

Integer

Returns the total number of entities that are stored in PostgreSQL, but not in Elasticsearch.

These entities are synced in Elasticsearch by retrieving the corresponding data from PostgreSQL.

n_not_deleted

Integer

Returns the total number of entities that are stored in Elasticsearch, but not in PostgreSQL.

These are entities that were deleted from the main database, and that were left behind in the indexing and search database.

These entities are deleted from the Elasticsearch indexing and search database.

n_strange

Integer

Returns the total number of entities whose last_updated_at timestamp is more recent in Elasticsearch than in PostgreSQL.

This is an anomaly that should not occur under normal operational circumstances.

These entities are synced in Elasticsearch by retrieving the corresponding data from PostgreSQL.

Example

eiq-platform search sync-data` --check-results-file log.json --dry-run --entity-types indicator,ttp,relation