Sure, here is the schema and the query. I’m trying to make a query that is schema free, and searches through a list of attributes. Since it’s schema free I’m trying to avoid any information on attributes, so that’s why I’m mixing and matching attribute types.
I’ve noticed that DATETIME and DOUBLE attribute types can cause the GPE to go down when passing a conflicting STRING value.
Couldn’t attach non image files, so contents are below.
---------------------Schema.gsql--------------------------------------------------------------------------------------------
First create our empty graph
CREATE GRAPH demo_fc_graph()
Create schema_change job to include all vertex/edge types
CREATE SCHEMA_CHANGE JOB create_demo_fc_graph_schema FOR GRAPH demo_fc_graph {
# Create party vertex
ADD VERTEX party (
PRIMARY_ID party_id STRING,
party_name STRING,
first_name STRING,
last_name STRING,
party_desc STRING,
party_type STRING,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create address vertex
ADD VERTEX address (
PRIMARY_ID address_id STRING,
address_line_1 STRING,
address_line_2 STRING,
address_line_3 STRING,
city STRING,
state STRING,
postal_code STRING,
country_code STRING,
latitude DOUBLE,
longitude DOUBLE,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create street_address vertex
ADD VERTEX street_address (
PRIMARY_ID address_id STRING,
street STRING,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create alert vertex
ADD VERTEX alert (
PRIMARY_ID alert_id STRING,
alert_date DATETIME,
alert_rule_type STRING
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create internal account vertex
ADD VERTEX internal_account (
PRIMARY_ID account_id STRING,
account_number STRING,
account_name STRING,
account_status STRING,
account_type STRING,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create external account vertex
ADD VERTEX external_account (
PRIMARY_ID ext_account_id STRING,
bank_id STRING,
account_number STRING,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create bank vertex
ADD VERTEX bank (
PRIMARY_ID bank_id STRING,
bank_name STRING,
bank_number STRING,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create banking transaction vertex
ADD VERTEX banking_transaction (
PRIMARY_ID transaction_id STRING,
source_transaction_id STRING,
create_date DATETIME,
risk_score INT,
transaction_date DATETIME
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create wire transaction vertex
ADD VERTEX wire_message (
PRIMARY_ID wire_id STRING,
currency_amount STRING,
int_account_id STRING,
ext_account_id STRING,
sender_bank_name STRING,
receiving_bank_name STRING,
sender_bank_key STRING,
receiving_bank_key STRING,
create_date DATETIME,
wire_message_date DATETIME,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create Case vertex
ADD VERTEX case_item (
PRIMARY_ID case_id STRING,
case_date DATETIME
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create SAR vertex
ADD VERTEX sar (
PRIMARY_ID form_id STRING,
form_name STRING,
form_status STRING,
form_creator STRING,
form_created_date DATETIME
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create TIN vertex
ADD VERTEX tin (
PRIMARY_ID tin_id STRING,
tin_type STRING,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create Email vertex
ADD VERTEX email (
PRIMARY_ID email_id STRING,
relation_type_desc STRING,
first_seen DATETIME,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create phone vertex
ADD VERTEX phone (
PRIMARY_ID phone_id STRING,
relation_type_code STRING,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create device vertex
ADD VERTEX device_cookie (
PRIMARY_ID cookie_id STRING,
device_desc STRING,
mobile_id STRING,
transmit_device_mobile_app_id STRING,
last_seen DATETIME,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create session vertex
ADD VERTEX session (
PRIMARY_ID session_id STRING,
session_date DATETIME,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
# Create ip vertex
ADD VERTEX ip_address (
PRIMARY_ID ip_id STRING,
domain_name STRING,
risk_score INT
) WITH PRIMARY_ID_AS_ATTRIBUTE="true";
ADD DIRECTED EDGE located_at (FROM party, TO address | street_address) WITH REVERSE_EDGE="location_of";
ADD DIRECTED EDGE is_same_address (FROM address, TO street_address) WITH REVERSE_EDGE="is_same_street";
ADD DIRECTED EDGE is_for_transaction (FROM wire_message, TO banking_transaction) WITH REVERSE_EDGE="has_message";
ADD DIRECTED EDGE involved (FROM banking_transaction | wire_message, TO party | internal_account | external_account) WITH REVERSE_EDGE="produced";
ADD DIRECTED EDGE registered_at (FROM internal_account, TO address | street_address) WITH REVERSE_EDGE="is_location_of";
ADD DIRECTED EDGE manages (FROM party, TO party | internal_account | external_account) WITH REVERSE_EDGE="is_managed_by";
ADD DIRECTED EDGE part_of (FROM party, TO party) WITH REVERSE_EDGE="included_in";
ADD DIRECTED EDGE alert_for (FROM alert, TO party | internal_account | external_account) WITH REVERSE_EDGE="alerted_by";
ADD DIRECTED EDGE is_primary (FROM party, TO internal_account | external_account) WITH REVERSE_EDGE="primary_account_of";
ADD DIRECTED EDGE evidence_for (FROM case_item, TO internal_account | party | alert) WITH REVERSE_EDGE="has_evidence_of";
ADD DIRECTED EDGE recommended_for (FROM alert, TO case_item) WITH REVERSE_EDGE="recommended_by";
ADD DIRECTED EDGE led_to (FROM case_item, TO sar) WITH REVERSE_EDGE="caused_by";
ADD DIRECTED EDGE represented_by (FROM party | internal_account, TO tin) WITH REVERSE_EDGE="represents";
ADD DIRECTED EDGE created_for (FROM sar, TO tin) WITH REVERSE_EDGE="involved_in";
ADD DIRECTED EDGE includes (FROM bank, TO internal_account | external_account) WITH REVERSE_EDGE="preceded_by";
ADD DIRECTED EDGE performed (FROM bank, TO wire_message | banking_transaction, transaction_direction STRING) WITH REVERSE_EDGE="performed_by";
ADD DIRECTED EDGE is_a_phone (FROM device_cookie, TO phone) WITH REVERSE_EDGE="is_a_device";
ADD DIRECTED EDGE associated_with (FROM internal_account | party, TO phone | email | device_cookie | session, first_seen DATETIME) WITH REVERSE_EDGE="used_by";
ADD DIRECTED EDGE used (FROM session, TO device_cookie, last_heartbeat DATETIME) WITH REVERSE_EDGE="used_in";
ADD DIRECTED EDGE login_from (FROM session, TO ip_address, last_login DATETIME) WITH REVERSE_EDGE="login_to";
ADD DIRECTED EDGE visited (FROM party, TO ip_address, last_visit DATETIME) WITH REVERSE_EDGE="visited_by";
ADD DIRECTED EDGE has_sar (FROM party|internal_account, TO sar) WITH REVERSE_EDGE="exists_for";
}
Run schema_change job
RUN SCHEMA_CHANGE JOB create_demo_fc_graph_schema
---------------------------------Pattern Matching-------------------------------------
CREATE QUERY pattern_matching_sub_query(SET sources, STRING pattern, STRING attribute_name, STRING where_condition) FOR GRAPH demo_fc_graph RETURNS (SET) {
// Initialize variables
MapAccum<String,DOUBLE> @search_score;
MapAccum<STRING, STRING> @test;
// Initialize source vertices
Start = {sources};
// Search for vertex attributes that match give string pattern
// Assign a search score for each match by attribute
Results = SELECT s from Start:s
WHERE upper(s.getAttr(attribute_name, "STRING")) LIKE pattern
OR upper(to_string(s.getAttr(attribute_name, "INT"))) LIKE pattern
OR upper(to_string(s.getAttr(attribute_name, "UINT"))) LIKE pattern
OR upper(to_string(s.getAttr(attribute_name, "DOUBLE"))) LIKE pattern
OR upper(to_string(s.getAttr(attribute_name, "FLOAT"))) LIKE pattern
OR (s.getAttr(attribute_name, "BOOL") == TRUE AND "TRUE" LIKE pattern)
OR (s.getAttr(attribute_name, "BOOL") == FALSE AND "FALSE" LIKE pattern)
ACCUM s.@search_score += (attribute_name -> 1),
s.@test += ("INT" -> upper(to_string(s.getAttr(attribute_name, "INT")))),
s.@test += ("UINT" -> upper(to_string(s.getAttr(attribute_name, "UINT")))),
s.@test += ("DOUBLE" -> upper(to_string(s.getAttr(attribute_name, "DOUBLE")))),
s.@test += ("FLOAT" -> upper(to_string(s.getAttr(attribute_name, "FLOAT")))),
s.@test += ("STRING" -> upper(s.getAttr(attribute_name, "STRING")))
;
PRINT Results;
RETURN Results;
}
