opencybersecurityalliance/firepit

Firepit - STIX Columnar Storage

Firepit - STIX Columnar Storage

Columnar storage for STIX 2.0 observations.

• Free software: Apache Software License 2.0
• Documentation: https://firepit.readthedocs.io.

Features

• Transforms STIX Observation SDOs to a columnar format
• Inserts those transformed observations into SQL (currently sqlite3 and PostgreSQL)

Motivation

STIX 2.0 JSON is a graph-like data format. There aren't many popular tools for working with graph-like data, but there are numerous tools for working with data from SQL databases. Firepit attempts to make those tools usable with STIX data obtained from stix-shifter.

Firepit also supports STIX 2.1

Firepit is primarily designed for use with the Kestrel Threat Hunting Language.

Credits

This package was created with Cookiecutter and the audreyr/cookiecutter-pypackage project template.

firepit's Issues

AttributeError: 'dict' object has no attribute 'split'

With azure_sentinel data and 2.3.26:

  File "/home/pcoccoli/github/firepit/firepit/aio/ingest.py", line 258, in translate
    new_col = _make_colname(col_mapping)
  File "/home/pcoccoli/github/firepit/firepit/aio/ingest.py", line 63, in _make_colname
    parts = shifter_name.split('.')
AttributeError: 'dict' object has no attribute 'split'

DependentObjectsStillExist: cannot drop view because other objects depend on it

Found while testing Kestrel with PostgreSQL:

  File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 419, in assign
    self.assign_query(viewname, query)
  File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 799, in assign_query
    cursor = self._create_view(viewname, stmt, sco_type, deps=deps)
  File "/home/pcoccoli/github/firepit/firepit/pgstorage.py", line 333, in _create_view
    self._execute(f'DROP VIEW IF EXISTS "{viewname}"', cursor)
  File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 152, in _execute
    cursor.execute(statement)
  File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/p392/lib/python3.9/site-packages/psycopg2_binary-2.8.6-py3.9-linux-x86_64.egg/psycopg2/extras.py", line 251, in execute
    return super(RealDictCursor, self).execute(query, vars)
psycopg2.errors.DependentObjectsStillExist: cannot drop view conns because other objects depend on it
DETAIL:  view ts_conns depends on view conns
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Does not happen on sqlite3.

Can reproduce with this Kestrel script:

# Create var
conns = GET network-traffic
        FROM file:///home/pcoccoli/Data/THL/dns_tunneling.json
	WHERE [network-traffic:dst_port = 53 AND network-traffic:dst_ref.value NOT ISSUBSET '192.168.1.0/24']

# Create dependent var
ts_conns = TIMESTAMPED(conns)

# Now try to recreate original var
conns = SORT conns BY src_port

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "__columns_otype_path_key"

There's a unique constraint on the __columns table but no ON CONFLICT in the INSERT statements. This can produce the above exception when using multiple threads.

Multiple unit test failures with postgresql

FAILED tests/test_errors.py::test_empty_results - firepit.exceptions.UnknownViewname: relation "my_findings" does not exist
FAILED tests/test_storage.py::test_ops[url-value-MATCHES-^.example.com/page/1[0-9]$-http://www26.example.com/page/176-http://www67.example.com/page/264] - AssertionErro...
FAILED tests/test_storage.py::test_grouping - firepit.exceptions.IncompatibleType
FAILED tests/test_storage.py::test_extract - firepit.exceptions.IncompatibleType
FAILED tests/test_storage.py::test_reassign - KeyError: 'x_enrich'
FAILED tests/test_storage.py::test_appdata - TypeError: the JSON object must be str, bytes or bytearray, not NoneType
FAILED tests/test_storage.py::test_sort_same_name - psycopg2.errors.DependentObjectsStillExist: cannot drop view gmirloov because other objects depend on it

unexpected exception from store.columns()

While doing a small refactoring for commands.py in Kestrel, I find an existing unit test does not behave as I thought.

The unit test: https://github.com/opencybersecurityalliance/kestrel-lang/blob/develop/tests/test_timestamped.py#L85

The huntflow to reproduce the exception and the stack:

conns = GET network-traffic
        FROM https://raw.githubusercontent.com/opencybersecurityalliance/kestrel-lang/develop/tests/test_bundle.json
	    WHERE dst_ref.value NOT ISSUBSET '192.168.0.0/16'

grp_conns = GROUP conns BY dst_ref.value WITH COUNT(dst_ref) AS count

ts_grp_conns = TIMESTAMPED(grp_conns)

The error when running the huntflow:

Traceback (most recent call last):
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/firepit/sqlitestorage.py", line 161, in _do_execute
    cursor.execute(query)
sqlite3.OperationalError: no such column: grp_conns.id

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/codegen/summary.py", line 97, in get_variable_entity_count
    columns = variable.store.columns(variable.entity_table)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/firepit/sqlitestorage.py", line 290, in columns
    cursor = self._execute(stmt)
             ^^^^^^^^^^^^^^^^^^^
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/firepit/sqlitestorage.py", line 185, in _execute
    return self._do_execute(statement, cursor=cursor)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/firepit/sqlitestorage.py", line 168, in _do_execute
    raise InvalidAttr(m) from e
firepit.exceptions.InvalidAttr: invalid attribute: grp_conns.id

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/subx/venv/kestrel-dev/bin/kestrel", line 9, in <module>
    runpy.run_module("kestrel", run_name="__main__")
  File "<frozen runpy>", line 229, in run_module
  File "<frozen runpy>", line 88, in _run_code
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/__main__.py", line 32, in <module>
    outputs = session.execute(huntflow)
              ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/session.py", line 274, in execute
    return self._execute_ast(ast)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/session.py", line 427, in _execute_ast
    output_var_struct, display = execute_cmd(stmt, self)
                                 ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/codegen/commands.py", line 102, in wrapper
    return func(stmt, session)
           ^^^^^^^^^^^^^^^^^^^
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/codegen/commands.py", line 66, in wrapper
    var_struct = new_var(
                 ^^^^^^^^
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/symboltable/variable.py", line 128, in new_var
    return VarStruct(
           ^^^^^^^^^^
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/symboltable/variable.py", line 38, in __init__
    self.length = get_variable_entity_count(self)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/codegen/summary.py", line 102, in get_variable_entity_count
    raise MissingEntityAttribute(table_name, attr) from e
kestrel.exceptions.MissingEntityAttribute: [ERROR] MissingEntityAttribute: variable "grp_conns" does not have required attribute "id"
remove transform or specify different variable in the Kestrel command.

The strange: it is the function store.columns() that hit the InvalidAttr exception in firepit when running ts_grp_conns = TIMESTAMPED(grp_conns). Should it just return all columns?

UnexpectedError: near "JOIN": syntax error

The extract call generates invalid SQL when the STIX object path contains a ref list:

LOGLEVEL=DEBUG firepit extract msgs email-message email_qid "[email-message:to_refs[*].value = '[email protected]']"
DEBUG:firepit.sqlitestorage:Connection to SQLite DB stix.db successful
DEBUG:firepit.sqlitestorage:Executing query: SELECT value FROM "__metadata" WHERE name = 'dbversion'
DEBUG:firepit.sqlstorage:Extract email-message as msgs from email_qid with [email-message:to_refs[*].value = '[email protected]']
DEBUG:firepit.sqlstorage:stix2sql produced " JOIN "__reflist" AS "r" ON "email-message"."id" = "r"."source_ref" WHERE "r"."target_ref" IN (SELECT "id" FROM "email-addr" WHERE "value"  = '[email protected]')"
DEBUG:firepit.sqlitestorage:Executing query: BEGIN;
DEBUG:firepit.sqlitestorage:_create_view: "msgs" stmt "SELECT "email-message".* FROM "email-message" WHERE "id" IN (SELECT "email-message".id FROM "email-message"  INNER JOIN __queries ON "email-message".id = __queries.sco_id  WHERE query_id = 'email_qid' AND ( JOIN "__reflist" AS "r" ON "email-message"."id" = "r"."source_ref" WHERE "r"."target_ref" IN (SELECT "id" FROM "email-addr" WHERE "value"  = '[email protected]')));"
DEBUG:firepit.sqlitestorage:Executing query: SELECT sql from sqlite_master WHERE type='view' and name=?
DEBUG:firepit.sqlitestorage:Executing query: CREATE VIEW "msgs" AS SELECT "email-message".* FROM "email-message" WHERE "id" IN (SELECT "email-message".id FROM "email-message"  INNER JOIN __queries ON "email-message".id = __queries.sco_id  WHERE query_id = 'email_qid' AND ( JOIN "__reflist" AS "r" ON "email-message"."id" = "r"."source_ref" WHERE "r"."target_ref" IN (SELECT "id" FROM "email-addr" WHERE "value"  = '[email protected]')));
ERROR:firepit.sqlitestorage:CREATE VIEW "msgs" AS SELECT "email-message".* FROM "email-message" WHERE "id" IN (SELECT "email-message".id FROM "email-message"  INNER JOIN __queries ON "email-message".id = __queries.sco_id  WHERE query_id = 'email_qid' AND ( JOIN "__reflist" AS "r" ON "email-message"."id" = "r"."source_ref" WHERE "r"."target_ref" IN (SELECT "id" FROM "email-addr" WHERE "value"  = '[email protected]')));: near "JOIN": syntax error
╭─────────────────────────────── Traceback (most recent call last) ────────────────────────────────╮
│                                                                                                  │
│ /home/pcoccoli/github/firepit/firepit/sqlitestorage.py:161 in _do_execute                        │
│                                                                                                  │
│   158 │   │   try:                                                                               │
│   159 │   │   │   logger.debug('Executing query: %s', query)                                     │
│   160 │   │   │   if not values:                                                                 │
│ ❱ 161 │   │   │   │   cursor.execute(query)                                                      │
│   162 │   │   │   else:                                                                          │
│   163 │   │   │   │   cursor.execute(query, values)                                              │
│   164 │   │   except sqlite3.OperationalError as e:                                              │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │ cursor = <sqlite3.Cursor object at 0x7fe266db6a40>                                           │ │
│ │  query = 'CREATE VIEW "msgs" AS SELECT "email-message".* FROM "email-message" WHERE "id"     │ │
│ │          I'+312                                                                              │ │
│ │   self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0>                      │ │
│ │ values = None                                                                                │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
╰──────────────────────────────────────────────────────────────────────────────────────────────────╯
OperationalError: near "JOIN": syntax error

The above exception was the direct cause of the following exception:

╭─────────────────────────────── Traceback (most recent call last) ────────────────────────────────╮
│                                                                                                  │
│ /home/pcoccoli/github/firepit/firepit/cli.py:82 in extract                                       │
│                                                                                                  │
│    79 ):                                                                                         │
│    80 │   """Create a view of a subset of cached data"""                                         │
│    81 │   db = get_storage(state['dbname'], state['session'])                                    │
│ ❱  82 │   db.extract(name, sco_type, query_id, pattern)                                          │
│    83                                                                                            │
│    84                                                                                            │
│    85 @app.command()                                                                             │
│                                                                                                  │
│ ╭───────────────────────────────── locals ──────────────────────────────────╮                    │
│ │       db = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0> │                    │
│ │     name = 'msgs'                                                         │                    │
│ │  pattern = "[email-message:to_refs[*].value = '[email protected]']"        │                    │
│ │ query_id = 'email_qid'                                                    │                    │
│ │ sco_type = 'email-message'                                                │                    │
│ ╰───────────────────────────────────────────────────────────────────────────╯                    │
│ /home/pcoccoli/github/firepit/firepit/sqlstorage.py:579 in extract                               │
│                                                                                                  │
│    576 │   │   validate_name(viewname)                                                           │
│    577 │   │   logger.debug('Extract %s as %s from %s with %s',                                  │
│    578 │   │   │   │   │    sco_type, viewname, query_id, pattern)                               │
│ ❱  579 │   │   self._extract(viewname, sco_type, sco_type, pattern, query_id)                    │
│    580 │                                                                                         │
│    581 │   def filter(self, viewname, sco_type, input_view, pattern):                            │
│    582 │   │   """                                                                               │
│                                                                                                  │
│ ╭───────────────────────────────── locals ──────────────────────────────────╮                    │
│ │  pattern = "[email-message:to_refs[*].value = '[email protected]']"        │                    │
│ │ query_id = 'email_qid'                                                    │                    │
│ │ sco_type = 'email-message'                                                │                    │
│ │     self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0> │                    │
│ │ viewname = 'msgs'                                                         │                    │
│ ╰───────────────────────────────────────────────────────────────────────────╯                    │
│                                                                                                  │
│ /home/pcoccoli/github/firepit/firepit/sqlstorage.py:364 in _extract                              │
│                                                                                                  │
│    361 │   │   │   │     f'  INNER JOIN __queries ON "{sco_type}".id = __queries.sco_id'         │
│    362 │   │   │   │     f'  WHERE {where});')                                                   │
│    363 │   │                                                                                     │
│ ❱  364 │   │   cursor = self._create_view(viewname, select, sco_type, deps=[tablename], cursor=  │
│    365 │   │   self.connection.commit()                                                          │
│    366 │   │   cursor.close()                                                                    │
│    367                                                                                           │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │    clause = "query_id = 'email_qid'"                                                         │ │
│ │    cursor = <sqlite3.Cursor object at 0x7fe266db6a40>                                        │ │
│ │   pattern = "[email-message:to_refs[*].value = '[email protected]']"                          │ │
│ │  query_id = 'email_qid'                                                                      │ │
│ │  sco_type = 'email-message'                                                                  │ │
│ │    select = 'SELECT "email-message".* FROM "email-message" WHERE "id" IN (SELECT             │ │
│ │             "email-messa'+290                                                                │ │
│ │      self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0>                   │ │
│ │ tablename = 'email-message'                                                                  │ │
│ │  viewname = 'msgs'                                                                           │ │
│ │     where = 'query_id = \'email_qid\' AND ( JOIN "__reflist" AS "r" ON "email-message"."id"  │ │
│ │             = "'+110                                                                         │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
│                                                                                                  │
│ /home/pcoccoli/github/firepit/firepit/sqlitestorage.py:216 in _create_view                       │
│                                                                                                  │
│   213 │   │   if self._is_sql_view(viewname, cursor):                                            │
│   214 │   │   │   is_new = False                                                                 │
│   215 │   │   │   self._execute(f'DROP VIEW IF EXISTS "{viewname}"', cursor)                     │
│ ❱ 216 │   │   self._execute(f'CREATE VIEW "{viewname}" AS {select}', cursor)                     │
│   217 │   │   if is_new:                                                                         │
│   218 │   │   │   self._new_name(cursor, viewname, sco_type)                                     │
│   219 │   │   return cursor                                                                      │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │   cursor = <sqlite3.Cursor object at 0x7fe266db6a40>                                         │ │
│ │     deps = ['email-message']                                                                 │ │
│ │   is_new = True                                                                              │ │
│ │ sco_type = 'email-message'                                                                   │ │
│ │   select = 'SELECT "email-message".* FROM "email-message" WHERE "id" IN (SELECT              │ │
│ │            "email-messa'+290                                                                 │ │
│ │     self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0>                    │ │
│ │ viewname = 'msgs'                                                                            │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
│                                                                                                  │
│ /home/pcoccoli/github/firepit/firepit/sqlitestorage.py:185 in _execute                           │
│                                                                                                  │
│   182 │   │   return cursor                                                                      │
│   183 │                                                                                          │
│   184 │   def _execute(self, statement, cursor=None):                                            │
│ ❱ 185 │   │   return self._do_execute(statement, cursor=cursor)                                  │
│   186 │                                                                                          │
│   187 │   def _query(self, query, values=None, cursor=None):                                     │
│   188 │   │   cursor = self._do_execute(query, values=values, cursor=cursor)                     │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │    cursor = <sqlite3.Cursor object at 0x7fe266db6a40>                                        │ │
│ │      self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0>                   │ │
│ │ statement = 'CREATE VIEW "msgs" AS SELECT "email-message".* FROM "email-message" WHERE "id"  │ │
│ │             I'+312                                                                           │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
│                                                                                                  │
│ /home/pcoccoli/github/firepit/firepit/sqlitestorage.py:176 in _do_execute                        │
│                                                                                                  │
│   173 │   │   │   │   raise UnknownViewname(e.args[0]) from e                                    │
│   174 │   │   │   elif e.args[0].endswith("syntax error"):                                       │
│   175 │   │   │   │   # We see this on SQL injection attempts                                    │
│ ❱ 176 │   │   │   │   raise UnexpectedError(e.args[0]) from e                                    │
│   177 │   │   │   elif e.args[0].endswith("table") and e.args[0].endswith(" already exists"):    │
│   178 │   │   │   │   tablename = e.args[0].split('"')[1]                                        │
│   179 │   │   │   │   raise DuplicateTable(tablename) from e                                     │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │ cursor = <sqlite3.Cursor object at 0x7fe266db6a40>                                           │ │
│ │  query = 'CREATE VIEW "msgs" AS SELECT "email-message".* FROM "email-message" WHERE "id"     │ │
│ │          I'+312                                                                              │ │
│ │   self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0>                      │ │
│ │ values = None                                                                                │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
╰──────────────────────────────────────────────────────────────────────────────────────────────────╯
UnexpectedError: near "JOIN": syntax error

CardinalityViolation: ON CONFLICT DO UPDATE command cannot affect row a second time

With PostgreSQL, duplicate identity objects (which you would naturally have if your data comes from stix-shifter and is broken up into pages where each page has an identity object for the stix-shifter connector configuration hat produced it) will raise the above exception.
For identity objects, we should just ignore duplicates.

NumericValueOutOfRange with large src_byte_count

With file https://github.com/opencybersecurityalliance/stix-shifter/blob/develop/data/cybox/20000.json:

firepit cache --session test-20k 20k /home/pcoccoli/github/stix-shifter/data/cybox/20000.json
Traceback (most recent call last):
  File "/home/pcoccoli/.pyenv/versions/gh392/bin/firepit", line 33, in <module>
    sys.exit(load_entry_point('firepit', 'console_scripts', 'firepit')())
  File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/typer/main.py", line 214, in __call__
    return get_command(self)(*args, **kwargs)
  File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/typer/main.py", line 497, in wrapper
    return callback(**use_params)  # type: ignore
  File "/home/pcoccoli/github/firepit/firepit/cli.py", line 48, in cache
    db.cache(query_id, filenames)
  File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 246, in cache
    splitter.write(obj)
  File "/home/pcoccoli/github/firepit/firepit/splitter.py", line 220, in write
    self.writer.write_records(obj_type, self.records[obj_type], schema, self.replace, self.query_id)
  File "/home/pcoccoli/github/firepit/firepit/splitter.py", line 153, in write_records
    self.store.upsert(cursor, tablename, obj, query_id)
  File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 224, in upsert
    cursor.execute(stmt, values)
  File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/psycopg2/extras.py", line 236, in execute
    return super().execute(query, vars)
psycopg2.errors.NumericValueOutOfRange: integer out of range

We use type INTEGER but it looks like maybe the value "4400235700" on line 303 is too big.

Missing tables with "fast translation"

When a stix-shifter connector's "to_stix_map" doesn't use an object name in a mapping, those objects could be silently dropped by async translate/ingest. This happens e.g. with qradar's software:name mapping.
You can tell by inspecting the DB (in this case PostgreSQL but also happens with sqlite3):

  otype   | path | shortname | dtype 
----------+------+-----------+-------
 software | name | name      | str
(1 row)
# \d
              List of relations
 Schema  |      Name       | Type  |  Owner   
---------+-----------------+-------+----------
 flat-id | __columns       | table | postgres
 flat-id | __contains      | table | postgres
 flat-id | __metadata      | table | postgres
 flat-id | __queries       | table | postgres
 flat-id | __symtable      | table | postgres
 flat-id | artifact        | table | postgres
 flat-id | domain-name     | table | postgres
 flat-id | email-message   | table | postgres
 flat-id | file            | table | postgres
 flat-id | identity        | table | postgres
 flat-id | ipv4-addr       | table | postgres
 flat-id | ipv6-addr       | table | postgres
 flat-id | network-traffic | table | postgres
 flat-id | observed-data   | table | postgres
 flat-id | url             | table | postgres
 flat-id | x-ibm-finding   | table | postgres
 flat-id | x-oca-event     | table | postgres
 flat-id | x-qradar        | table | postgres
(18 rows)

From this example you can see that firepit recorded a software:name column (meaning it was in the native qradar data passed into the translate function) which means there should be a software table with id and name columns, but listing the tables in the database shows that software is missing.

UndefinedFunction for ifnull on postgresql

The join operation uses ifnull, but that function only exists on sqlite3 and not postgresql.

TypeError: 'float' object is not iterable

With aws_guardduty results and 2.3.26:

    df[txf_col] = df[txf_col].apply(_to_protocols)
  File "/home/pcoccoli/.pyenv/versions/gh392/lib/python3.9/site-packages/pandas/core/series.py", line 4771, in apply
    return SeriesApply(self, func, convert_dtype, args, kwargs).apply()
  File "/home/pcoccoli/.pyenv/versions/gh392/lib/python3.9/site-packages/pandas/core/apply.py", line 1105, in apply
    return self.apply_standard()
  File "/home/pcoccoli/.pyenv/versions/gh392/lib/python3.9/site-packages/pandas/core/apply.py", line 1156, in apply_standard
    mapped = lib.map_infer(
  File "pandas/_libs/lib.pyx", line 2918, in pandas._libs.lib.map_infer
  File "/home/pcoccoli/github/firepit/firepit/aio/ingest.py", line 135, in _to_protocols
    value = [str(i).lower() for i in value if i != '']
TypeError: 'float' object is not iterable```

fast translation error

Looks like I get an error with fast translation enabled (executed successfully with fast translation disabled).

proc = GET process FROM stixshifter://host101
       WHERE name = 'cmd.exe'
       START 2021-09-29T00:00:00Z STOP 2021-09-30T00:00:00Z

Where host101 points to our rainbow instance with indexes winlogbeat-* and beats dialects enabled (dialect is not an issue since it works with fast translation disabled).

Error

Log

  File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/kestrel/codegen/commands.py", line 622, in _prefetch
    resp = ds_manager.query(data_source, stix_pattern, session_id, store)
  File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/kestrel/datasource/manager.py", line 33, in query
    rs = i.query(uri, pattern, session_id, c, store)
  File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/kestrel_datasource_stixshifter/interface.py", line 288, in query
    fast_translate(
  File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/kestrel_datasource_stixshifter/interface.py", line 358, in fast_translate
    loop.run_until_complete(
  File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/nest_asyncio.py", line 90, in run_until_complete
    return f.result()
  File "/Applications/Xcode.app/Contents/Developer/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/asyncio/futures.py", line 201, in result
    raise self._exception
  File "/Applications/Xcode.app/Contents/Developer/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/asyncio/tasks.py", line 256, in __step
    result = coro.send(None)
  File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/firepit/aio/ingest.py", line 620, in ingest
    odf.columns = [c.rpartition(':')[2] for c in cols]
  File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/pandas/core/generic.py", line 6002, in __setattr__
    return object.__setattr__(self, name, value)
  File "pandas/_libs/properties.pyx", line 69, in pandas._libs.properties.AxisProperty.__set__
  File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/pandas/core/generic.py", line 730, in _set_axis
    self._mgr.set_axis(axis, labels)
  File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/pandas/core/internals/managers.py", line 225, in set_axis
    self._validate_set_axis(axis, new_labels)
  File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/pandas/core/internals/base.py", line 70, in _validate_set_axis
    raise ValueError(
ValueError: Length mismatch: Expected axis has 7 elements, new values have 5 elements

Plugin mechanism

Currently, the psycopg2 package is required for postgresql support. However, if you're using sqlite3, you shouldn't need to install psycopg2. Using a plugin mechanism to separate out different "back ends" would solve this.

Sorting on referenced properties is broken

See opencybersecurityalliance/kestrel-lang#435

You can reproduce with something like:

   query = Query([
       Table('conns'),
       Order([('dst_ref.value', Order.ASC)]),
   ])
   store.assign_query('sconns', query)
   _ = store.lookup('sconns')

This will fail with firepit.exceptions.InvalidAttr: invalid attribute: sconns.dst_ref.value

psycopg2.errors.SyntaxError: each UNION query must have the same number of columns

Traceback (most recent call last):
  File "/home/pcoccoli/.pyenv/versions/gh392/bin/kestrel", line 7, in <module>
    exec(compile(f.read(), __file__, 'exec'))
  File "/home/pcoccoli/github/kestrel-lang/bin/kestrel", line 8, in <module>
    runpy.run_module('kestrel', run_name='__main__')
  File "/home/pcoccoli/.pyenv/versions/3.9.2/lib/python3.9/runpy.py", line 213, in run_module
    return _run_code(code, {}, init_globals, run_name, mod_spec)
  File "/home/pcoccoli/.pyenv/versions/3.9.2/lib/python3.9/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/home/pcoccoli/github/kestrel-lang/src/kestrel/__main__.py", line 54, in <module>
    outputs = session.execute(huntflow)
  File "/home/pcoccoli/github/kestrel-lang/src/kestrel/session.py", line 262, in execute
    return self._execute_ast(ast)
  File "/home/pcoccoli/github/kestrel-lang/src/kestrel/session.py", line 437, in _execute_ast
    output_var_struct, display = execute_cmd(stmt, self)
  File "/home/pcoccoli/github/kestrel-lang/src/kestrel/codegen/commands.py", line 92, in wrapper
    return func(stmt, session)
  File "/home/pcoccoli/github/kestrel-lang/src/kestrel/codegen/commands.py", line 60, in wrapper
    ret = func(stmt, session)
  File "/home/pcoccoli/github/kestrel-lang/src/kestrel/codegen/commands.py", line 262, in get
    session.store.merge(
  File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 532, in merge
    cursor = self._create_view(viewname, stmt, sco_type, deps=input_views)
  File "/home/pcoccoli/github/firepit/firepit/pgstorage.py", line 141, in _create_view
    self._execute(f'CREATE OR REPLACE VIEW "{viewname}" AS {select}', cursor)
  File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 82, in _execute
    cursor.execute(statement)
  File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/psycopg2/extras.py", line 236, in execute
    return super().execute(query, vars)
psycopg2.errors.SyntaxError: each UNION query must have the same number of columns
LINE 43: ...mbership.var = 'cmd_local'::text))) UNION  SELECT process.ty...
                                                              ^

DependentObjectsStillExist execption with postgresql when trying to reuse a view name

One way to recreate:

firepit cache my_data ~/tmp/stix_bundle.json
firepit extract addrs1 ipv4-addr my_data "[ipv4-addr:value ISSUBSET '192.168.0.0/16']"
firepit assign addrs2 --op sort --by value addrs1
firepit extract addrs1 ipv4-addr my_data "[ipv4-addr:value ISSUBSET '192.168.1.0/24']"

The second time we extract and try to reuse the name addrs1 an exception is raised:

Traceback (most recent call last):
  File "/home/pcoccoli/.pyenv/versions/gh36/bin/firepit", line 33, in <module>
    sys.exit(load_entry_point('firepit', 'console_scripts', 'firepit')())
  File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/typer/main.py", line 214, in __call__
    return get_command(self)(*args, **kwargs)
  File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/typer/main.py", line 497, in wrapper
    return callback(**use_params)  # type: ignore
  File "/home/pcoccoli/github/firepit/firepit/cli.py", line 60, in extract
    db.extract(name, sco_type, query_id, pattern)
  File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 370, in extract
    self._extract(viewname, sco_type, sco_type, pattern, query_id)
  File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 187, in _extract
    cursor = self._create_view(viewname, select, deps=[tablename], cursor=cursor)
  File "/home/pcoccoli/github/firepit/firepit/pgstorage.py", line 116, in _create_view
    self._execute(f'DROP VIEW IF EXISTS "{viewname}";', cursor)
  File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 80, in _execute
    cursor.execute(statement)
  File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/psycopg2/extras.py", line 251, in execute
    return super(RealDictCursor, self).execute(query, vars)
psycopg2.errors.DependentObjectsStillExist: cannot drop view addrs1 because other objects depend on it
DETAIL:  view addrs2 depends on view addrs1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

setup.cfg update

When running pytest with Python 3.9.5, get the following warning:

========================================================================================================== warnings summary ===========================================================================================================
../../venv/kestrel39/lib/python3.9/site-packages/_pytest/config/__init__.py:1233
  /workspace/venv/kestrel39/lib/python3.9/site-packages/_pytest/config/__init__.py:1233: PytestConfigWarning: Unknown config option: collect_ignore
  
    self._warn_or_fail_if_strict(f"Unknown config option: {key}\n")

-- Docs: https://docs.pytest.org/en/stable/warnings.html
=================================================================================================== 106 passed, 1 warning in 1.88s ====================================================================================================

This seems to be a cookiecutter template issue:
audreyfeldroy/cookiecutter-pypackage#608

Consider removing the following lines in setup.cfg:

[tool:pytest]
collect_ignore = ['setup.py']

missing dependency specification

requests used at raft.py seems missing in requirement specification.

support for object_refs

Hi there,
I noticed that for objects like note,report,opion and similar that relies on object_refs, the stix id are not normalized in the database.
Is this a known issue?

asyncstorage tries to use sync functions from sqlstorage

In AsyncStorage.lookup():

            if not col_dict:
                col_dict = _get_col_dict(self)

But _get_col_dict comes from sqlstorage.py and is not async:

def _get_col_dict(store):
    q = Query('__columns')
    col_dict = defaultdict(list)
    results = store.run_query(q).fetchall()
    for result in results:
        col_dict[result['otype']].append(result['path'])
    return col_dict

Results in a backtrace with "AttributeError: 'coroutine' object has no attribute 'fetchall'"

A workaround is to pass in a col_dict to lookup and avoid the bug. To generate col_dict (if you haven't already):

dbcache = await get_dbcache(store)
col_dict = dbcache.col_dict

This is probably more efficient anyway.

The real fix is to re-implement an async version of _get_col_dict in asyncstorage.py.

NameError: name 'InvalidAttr' is not defined

Introduced in 1.0.7

  File "/opt/modules/lib/python3.9/site-packages/firepit/sqlstorage.py", line 518, in run_query
    return self._query(query_text, query_values)
  File "/opt/modules/lib/python3.9/site-packages/firepit/pgstorage.py", line 108, in _query
    raise InvalidAttr(str(e)) from e
NameError: name 'InvalidAttr' is not defined

macOS installation error

pip install firepit failed with macOS 10.15 and Python 3.9. Lower Python version works.

Testing script: https://github.com/subbyte/github-action-test/blob/main/.github/workflows/firepit-install.yml
Error with Python 3.9: https://github.com/subbyte/github-action-test/runs/3078492170?check_suite_focus=true

Run python -m pip install --upgrade pip
Requirement already satisfied: pip in /Users/runner/hostedtoolcache/Python/3.9.6/x64/lib/python3.9/site-packages (21.1.3)
Requirement already satisfied: setuptools in /Users/runner/hostedtoolcache/Python/3.9.6/x64/lib/python3.9/site-packages (56.0.0)
Collecting setuptools
  Downloading setuptools-57.2.0-py3-none-any.whl (818 kB)
Installing collected packages: setuptools
  Attempting uninstall: setuptools
    Found existing installation: setuptools 56.0.0
    Uninstalling setuptools-56.0.0:
      Successfully uninstalled setuptools-56.0.0
Successfully installed setuptools-57.2.0
Collecting firepit
  Downloading firepit-1.0.14-py2.py3-none-any.whl (33 kB)
Collecting lark-parser
  Downloading lark-parser-0.11.3.tar.gz (229 kB)
Collecting ijson
  Downloading ijson-3.1.4-cp39-cp39-macosx_10_9_x86_64.whl (52 kB)
Collecting orjson==3.3.1
  Downloading orjson-3.3.1.tar.gz (655 kB)
  Installing build dependencies: started
  Installing build dependencies: finished with status 'done'
  Getting requirements to build wheel: started
  Getting requirements to build wheel: finished with status 'done'
    Preparing wheel metadata: started
    Preparing wheel metadata: finished with status 'error'
    ERROR: Command errored out with exit status 1:
     command: /Users/runner/hostedtoolcache/Python/3.9.6/x64/bin/python /Users/runner/hostedtoolcache/Python/3.9.6/x64/lib/python3.9/site-packages/pip/_vendor/pep517/in_process/_in_process.py prepare_metadata_for_build_wheel /var/folders/24/8k48jl6d249_n_qfxwsl6xvm0000gn/T/tmpoj1meudm
         cwd: /private/var/folders/24/8k48jl6d249_n_qfxwsl6xvm0000gn/T/pip-install-b17aksbl/orjson_8be9df0c073140d4bd3b219ebd07017a
    Complete output (13 lines):
    💥 maturin failed
      Caused by: Cargo metadata failed. Do you have cargo in your PATH?
      Caused by: Error during execution of `cargo metadata`: error: failed to run `rustc` to learn about target-specific information
    
    Caused by:
      process didn't exit successfully: `rustc - --crate-name ___ --print=file-names -Z mutable-noalias --crate-type bin --crate-type rlib --crate-type dylib --crate-type cdylib --crate-type staticlib --crate-type proc-macro --print=sysroot --print=cfg` (exit status: 1)
      --- stderr
      error: the option `Z` is only accepted on the nightly compiler
    
    
    Checking for Rust toolchain....
    Running `maturin pep517 write-dist-info --metadata-directory /private/var/folders/24/8k48jl6d249_n_qfxwsl6xvm0000gn/T/pip-modern-metadata-3baxp3mn --interpreter /Users/runner/hostedtoolcache/Python/3.9.6/x64/bin/python --manylinux=off --strip=on`
    Error: Command '['maturin', 'pep517', 'write-dist-info', '--metadata-directory', '/private/var/folders/24/8k48jl6d249_n_qfxwsl6xvm0000gn/T/pip-modern-metadata-3baxp3mn', '--interpreter', '/Users/runner/hostedtoolcache/Python/3.9.6/x64/bin/python', '--manylinux=off', '--strip=on']' returned non-zero exit status 1.
    ----------------------------------------
WARNING: Discarding https://files.pythonhosted.org/packages/23/c9/d4db467d5b5c91f5ff3ff3be5c731eb7fcc2b8b2c31540d5faeca794bf4d/orjson-3.3.1.tar.gz#sha256=149d6a2bc71514826979b9d053f3df0c2397a99e2b87213ba71605a1626d662c (from https://pypi.org/simple/orjson/) (requires-python:>=3.6). Command errored out with exit status 1: /Users/runner/hostedtoolcache/Python/3.9.6/x64/bin/python /Users/runner/hostedtoolcache/Python/3.9.6/x64/lib/python3.9/site-packages/pip/_vendor/pep517/in_process/_in_process.py prepare_metadata_for_build_wheel /var/folders/24/8k48jl6d249_n_qfxwsl6xvm0000gn/T/tmpoj1meudm Check the logs for full command output.
Collecting firepit
  Downloading firepit-1.0.13-py2.py3-none-any.whl (33 kB)
  Downloading firepit-1.0.12-py2.py3-none-any.whl (33 kB)
  Downloading firepit-1.0.11-py2.py3-none-any.whl (33 kB)
  Downloading firepit-1.0.10-py2.py3-none-any.whl (33 kB)
  Downloading firepit-1.0.9-py2.py3-none-any.whl (33 kB)
  Downloading firepit-1.0.8-py2.py3-none-any.whl (32 kB)
  Downloading firepit-1.0.7-py2.py3-none-any.whl (32 kB)
  Downloading firepit-1.0.6-py2.py3-none-any.whl (32 kB)
  Downloading firepit-1.0.4-py2.py3-none-any.whl (30 kB)
  Downloading firepit-1.0.3-py2.py3-none-any.whl (30 kB)
  Downloading firepit-1.0.2-py2.py3-none-any.whl (30 kB)
  Downloading firepit-1.0.1-py2.py3-none-any.whl (30 kB)
  Downloading firepit-1.0.0-py2.py3-none-any.whl (30 kB)
ERROR: Cannot install firepit==1.0.0, firepit==1.0.1, firepit==1.0.10, firepit==1.0.11, firepit==1.0.12, firepit==1.0.13, firepit==1.0.14, firepit==1.0.2, firepit==1.0.3, firepit==1.0.4, firepit==1.0.6, firepit==1.0.7, firepit==1.0.8 and firepit==1.0.9 because these package versions have conflicting dependencies.

The conflict is caused by:
    firepit 1.0.14 depends on orjson==3.3.1
    firepit 1.0.13 depends on orjson==3.3.1
    firepit 1.0.12 depends on orjson==3.3.1
    firepit 1.0.11 depends on orjson==3.3.1
    firepit 1.0.10 depends on orjson==3.3.1
    firepit 1.0.9 depends on orjson==3.3.1
    firepit 1.0.8 depends on orjson==3.3.1
    firepit 1.0.7 depends on orjson==3.3.1
    firepit 1.0.6 depends on orjson==3.3.1
    firepit 1.0.4 depends on orjson==3.3.1
    firepit 1.0.3 depends on orjson==3.3.1
    firepit 1.0.2 depends on orjson==3.3.1
    firepit 1.0.1 depends on orjson==3.3.1
    firepit 1.0.0 depends on orjson==3.3.1

To fix this you could try to:
1. loosen the range of package versions you've specified
2. remove package versions to allow pip attempt to solve the dependency conflict

ERROR: ResolutionImpossible: for help visit https://pip.pypa.io/en/latest/user_guide/#fixing-conflicting-dependencies
Error: Process completed with exit code 1.

Parentheses in STIX patterns using properties outside projected entity causes error

Passing a STIX pattern such as [(url:value LIKE '%page/1%' AND x-oca-asset:hostName LIKE 'pc%')] to extract with a different entity type would generate invalid SQL:

ERROR    firepit.sqlitestorage:sqlitestorage.py:165 CREATE VIEW "nt" AS SELECT "network-traffic".* FROM "network-traffic" WHERE "id" IN (SELECT "network-traffic".id FROM "network-traffic"  INNER JOIN __queries ON "network-traffic".id = __queries.sco_id  WHERE query_id = 'q1' AND (()));: near ")": syntax error

STIX MATCHES operator is translated to SQL incorrectly

firepit just passes MATCHES through, but I think we need to use MATCH for SQLite3 (https://www.sqlite.org/lang_expr.html) and ~ for PostgreSQL (https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP)

A new method for sqlstorage Module

I am adding a new transformer to the Kestrel, which lists all objects after appending the id of the SCO to which the object belongs. To this end, I think a new method, similar to the timestamped but simpler, should be added to the SQLStorage module. I am planning to contribute to firepit by adding this method. If any one think that the problem can be solved without extending firepit, I would be grateful to hear (@subbyte).

ValueError: invalid literal for int() with base 10: '0.0'

From this line:

File \"/usr/local/lib/python3.9/site-packages/firepit/aio/ingest.py\", line 308, in translate
    df[txf_col] = df[txf_col].dropna().astype('int')

Can't convert the string "0.0" to int. E.g. in ipython:

In [26]: int(0.0)
Out[26]: 0

In [27]: int("0.0")
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Input In [27], in <module>
----> 1 int("0.0")

ValueError: invalid literal for int() with base 10: '0.0'

exception on Windows: NotImplementedError: c

Reported by user on Windows.

File "C:\Users\redacted\pyenv\redacted\lib\site-packages\firepit\__init__.py", line 30, in get_storage
   raise NotImplementedError(url.scheme)
NotImplementedError: c

I think what happened here is that a Windows path was passed in as the url to get_storage. The urlparse looks at it and thinks c is the "scheme" but we only support "" or "postgresql". We could add some code to try and detect a Windows path and then skip urlparse.

support variable number of sub-second digits in `to_datetime()`

The current to_datetime() function in firepit.timestamp only supports 0,3,6 sub-second digits, which is the limitation of datetime.datetime.fromisoformat().

>>> import datetime

>>> datetime.datetime.fromisoformat("2022-02-01T00:00:00")
datetime.datetime(2022, 2, 1, 0, 0)
>>> datetime.datetime.fromisoformat("2022-02-01T00:00:00.123")
datetime.datetime(2022, 2, 1, 0, 0, 0, 123000)
>>> datetime.datetime.fromisoformat("2020-06-30T19:29:59.986123")
datetime.datetime(2020, 6, 30, 19, 29, 59, 986123)

>>> datetime.datetime.fromisoformat("2022-02-01T00:00:00.1")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: Invalid isoformat string: '2022-02-01T00:00:00.1'
>>> datetime.datetime.fromisoformat("2022-02-01T00:00:00.12")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: Invalid isoformat string: '2022-02-01T00:00:00.12'
>>> datetime.datetime.fromisoformat("2020-06-30T19:29:59.96346")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: Invalid isoformat string: '2020-06-30T19:29:59.96346'

Help needed to make it work on variable number of sub-second digits input.