need a strategy for storing non-filetype objects

[webstergd]

We need to develop a simple strategy for storing non-file types (email, ip addresses, domain names). Our current scheme could almost work. However, we cannot use SASI indexing on Cassandra due to the object name array. Additionally, we need a way to easily dedup entries.

@zhanif3 @cynexit what are your thoughts?

Current scheme for object as follows:
{
"_id": UUID,
"sha1: str,
"sha256": str,
"md5": str,
"mime": str,
"source": []str,
"obj_name": []str,
"submissions" []UUID,
}

Current scheme for submission as follows:
{
"_id": UUID
"object": UUID,
"user_id": str,
"source": str,
"date": ISO8601,
"obj_name": str,
"tags": []str,
"comment": str,
}

[webstergd]

Cassandra does have the InetAddressType which should probably be used. we could then use a string for domain and email. However, we still need some more logic on Domain as we will probably want to capture TLD and subdomains.

[webstergd]

Per discussions, we have the following options for the schemes.

Submissions remain as follows for either option.
submission

"_id": UUID,
"non_object": UUID,
"user_id": str,
"source": str,
"date": ISO8601,
"tags": []str,
"comment": str,

1. Single generic no_object type
   non_object

"_id": UUID,
"value": str, // client performs cleanup for the values. 
"sha1": str,
"sha256": str,
"md5": str,
"type": str, // ipv4, ipv6, domain, email
"source": []str,
"submissions": []UUID,

2. Unique objects for core types: ip, domain, email
   domain
   value is replaced by

"_id": UUID,
"fqdn": str,
"tld": str,
"subdomain": str

ip
value is replaced by

"_id": UUID,
"ip": InetAddressType,

email
value is replaced by

"_id": UUID,
"email": str,
"local-part": str,
"domain-part": str,
"sub-addressing": str,

• will need to identify how to handle email tagging

generic_non_object
same as the option 1

[webstergd]

From Datastax:
create a generic table like CREATE TABLE generic(id uuid, data_type text, text_value text, long_value bigint, decimal_value double, uuid_value uuid, date_value timestamp, blob_value blob, inet_value inet, PRIMARY KEY(id));
so depending on the object, most columns will be empty except the column that has the correct type
and you specify the type in the "data_type" column
For example if "data_type" contains value "long", you just need to read column long_value etc ...
If an object has multiple values and their type cannot be known before hand, just modify the table to add clustering column, like this : CREATE TABLE generic(id uuid, property_name text, property_data_type text, text_value text, long_value bigint, decimal_value double, uuid_value uuid, date_value timestamp, blob_value blob, inet_value inet, PRIMARY KEY((id), property_name));
For the same object "id", you can store many "property_name", each of the property has a "property_data_type" thats specifies the type of its value
the problem with this kind of schema is that for an object with 5 different fields, you'll need 5 inserts. But they can be batched together since the partition key is the same
to delete the whole object and all its values, just delete by partition key: DELETE FROM generic WHERE id = xxx;
and you can create UDT to store email fields
ANd add a column like email_value email

reason is that if we have multiple tables, reading the values of an object would require reading multiple tables, expensive

[webstergd]

Proposed solution is as follows:

"_id": UUID,
"type": str<<[file, domain, ip, email, generic]>>,
"submissions" []UUID,
"source": []str,
"created_date_time": timestamp,

"sha1": str,
"sha256": str,
"md5": str,

"file_mime": str,
"file_name": []str,

"domain_fqdn": str,
"domain_tld": str,
"domain_sub_domain": str,

"ip_address": InetAddressType,
"ip_v6": bool,

"email_address": str,
"email_local_part": str,
"email_domain_part": str,
"email_sub_addressing": str,

"generic_identifier": str,

[webstergd]

added
created_date_time timestamp
to the above. Allows for easier supporting on the database

[webstergd]

added boolean for IP address type