19 package org.sleuthkit.datamodel;
21 import java.sql.ResultSet;
22 import java.sql.SQLException;
23 import java.sql.Statement;
24 import java.util.ArrayList;
25 import java.util.Arrays;
26 import java.util.Collection;
27 import java.util.Collections;
28 import java.util.HashMap;
29 import java.util.HashSet;
30 import java.util.List;
33 import java.util.concurrent.ConcurrentHashMap;
34 import java.util.logging.Level;
35 import java.util.logging.Logger;
50 private final Map<
Account.
Type, Integer> accountTypeToTypeIdMap
52 private final Map<String,
Account.
Type> typeNameToAccountTypeMap
56 private final static Set<Integer> RELATIONSHIP_ARTIFACT_TYPE_IDS
57 =
new HashSet<Integer>(Arrays.asList(
63 private static final String RELATIONSHIP_ARTIFACT_TYPE_IDS_CSV_STR
64 = StringUtils.buildCSVString(RELATIONSHIP_ARTIFACT_TYPE_IDS);
86 CaseDbConnection connection = db.getConnection();
88 Statement statement = null;
89 ResultSet resultSet = null;
92 statement = connection.createStatement();
94 int count = readAccountTypes();
99 statement.execute(
"INSERT INTO account_types (type_name, display_name) VALUES ( '" + type.getTypeName() +
"', '" + type.getDisplayName() +
"')");
100 }
catch (SQLException ex) {
101 resultSet = connection.executeQuery(statement,
"SELECT COUNT(*) AS count FROM account_types WHERE type_name = '" + type.getTypeName() +
"'");
103 if (resultSet.getLong(
"count") == 0) {
109 ResultSet rs2 = connection.executeQuery(statement,
"SELECT account_type_id FROM account_types WHERE type_name = '" + type.getTypeName() +
"'");
111 int typeID = rs2.getInt(
"account_type_id");
115 this.accountTypeToTypeIdMap.put(accountType, typeID);
116 this.typeNameToAccountTypeMap.put(type.getTypeName(), accountType);
119 }
catch (SQLException ex) {
120 LOGGER.log(Level.SEVERE,
"Failed to add row to account_types", ex);
122 closeResultSet(resultSet);
123 closeStatement(statement);
138 CaseDbConnection connection = db.getConnection();
140 Statement statement = null;
141 ResultSet resultSet = null;
145 statement = connection.createStatement();
148 resultSet = connection.executeQuery(statement,
"SELECT COUNT(*) AS count FROM account_types");
150 if (resultSet.getLong(
"count") > 0) {
153 resultSet = connection.executeQuery(statement,
"SELECT * FROM account_types");
154 while (resultSet.next()) {
155 Account.
Type accountType =
new Account.
Type(resultSet.getString(
"type_name"), resultSet.getString(
"display_name"));
156 this.accountTypeToTypeIdMap.put(accountType, resultSet.getInt(
"account_type_id"));
157 this.typeNameToAccountTypeMap.put(accountType.getTypeName(), accountType);
159 count = this.typeNameToAccountTypeMap.size();
162 }
catch (SQLException ex) {
165 closeResultSet(resultSet);
166 closeStatement(statement);
200 if (this.accountTypeToTypeIdMap.containsKey(accountType)) {
204 CaseDbConnection connection = db.getConnection();
209 connection.beginTransaction();
210 s = connection.createStatement();
211 rs = connection.executeQuery(s,
"SELECT * FROM account_types WHERE type_name = '" + accountTypeName +
"'");
215 s.execute(
"INSERT INTO account_types (type_name, display_name) VALUES ( '" + accountTypeName +
"', '" + displayName +
"')");
218 rs = connection.executeQuery(s,
"SELECT * FROM account_types WHERE type_name = '" + accountTypeName +
"'");
221 int typeID = rs.getInt(
"account_type_id");
222 accountType =
new Account.
Type(rs.getString(
"type_name"), rs.getString(
"display_name"));
224 this.accountTypeToTypeIdMap.put(accountType, typeID);
225 this.typeNameToAccountTypeMap.put(accountTypeName, accountType);
227 connection.commitTransaction();
231 int typeID = rs.getInt(
"account_type_id");
233 accountType =
new Account.
Type(rs.getString(
"type_name"), rs.getString(
"display_name"));
234 this.accountTypeToTypeIdMap.put(accountType, typeID);
238 }
catch (SQLException ex) {
239 connection.rollbackTransaction();
270 Account account = getOrCreateAccount(accountType, normalizeAccountID(accountType, accountUniqueID));
278 BlackboardArtifact accountArtifact = getOrCreateAccountFileInstanceArtifact(accountType, normalizeAccountID(accountType, accountUniqueID), moduleName, sourceFile);
304 CaseDbConnection connection = db.getConnection();
309 s = connection.createStatement();
310 rs = connection.executeQuery(s,
"SELECT * FROM accounts WHERE account_type_id = " + getAccountTypeId(accountType)
311 +
" AND account_unique_identifier = '" + normalizeAccountID(accountType, accountUniqueID) +
"'");
314 account =
new Account(rs.getInt(
"account_id"), accountType,
315 rs.getString(
"account_unique_identifier"));
317 }
catch (SQLException ex) {
355 if (relationshipType.isCreatableFrom(sourceArtifact) ==
false) {
356 throw new TskDataException(
"Can not make a " + relationshipType.getDisplayName()
357 +
" relationship from a" + sourceArtifact.getDisplayName());
366 List<Long> accountIDs =
new ArrayList<Long>();
368 if (null != sender) {
369 accountIDs.add(sender.getAccount().getAccountID());
370 if (sender.getDataSourceObjectID() != sourceArtifact.getDataSourceObjectID()) {
371 throw new TskDataException(
"Sender and relationship are from different data sources :"
372 +
"Sender source ID" + sender.getDataSourceObjectID() +
" != relationship source ID" + sourceArtifact.getDataSourceObjectID());
377 accountIDs.add(recipient.getAccount().getAccountID());
378 if (recipient.getDataSourceObjectID() != sourceArtifact.getDataSourceObjectID()) {
379 throw new TskDataException(
"Recipient and relationship are from different data sources :"
380 +
"Recipient source ID" + recipient.getDataSourceObjectID() +
" != relationship source ID" + sourceArtifact.getDataSourceObjectID());
384 for (
int i = 0; i < accountIDs.size(); i++) {
385 for (
int j = i + 1; j < accountIDs.size(); j++) {
387 addAccountsRelationship(accountIDs.get(i), accountIDs.get(j),
388 sourceArtifact, relationshipType, dateTime);
389 }
catch (TskCoreException ex) {
391 LOGGER.log(Level.WARNING,
"Error adding relationship", ex);
409 private Account getOrCreateAccount(
Account.
Type accountType, String accountUniqueID)
throws TskCoreException {
411 if (null == account) {
412 String query =
" INTO accounts (account_type_id, account_unique_identifier) "
413 +
"VALUES ( " + getAccountTypeId(accountType) +
", '"
414 + normalizeAccountID(accountType, accountUniqueID) +
"'" +
")";
417 query =
"INSERT " + query +
" ON CONFLICT DO NOTHING";
420 query =
"INSERT OR IGNORE " + query;
423 throw new TskCoreException(
"Unknown DB Type: " + db.
getDatabaseType().name());
426 CaseDbConnection connection = db.getConnection();
431 connection.beginTransaction();
432 s = connection.createStatement();
436 connection.commitTransaction();
437 account =
getAccount(accountType, accountUniqueID);
438 }
catch (SQLException ex) {
439 connection.rollbackTransaction();
440 throw new TskCoreException(
"Error adding an account", ex);
467 BlackboardArtifact getOrCreateAccountFileInstanceArtifact(Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile)
throws TskCoreException {
470 BlackboardArtifact accountArtifact = getAccountFileInstanceArtifact(accountType, accountUniqueID, sourceFile);
471 if (null != accountArtifact) {
472 return accountArtifact;
476 accountArtifact = db.
newBlackboardArtifact(BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT, sourceFile.getId());
478 Collection<BlackboardAttribute> attributes =
new ArrayList<BlackboardAttribute>();
479 attributes.add(
new BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE, moduleName, accountType.getTypeName()));
480 attributes.add(
new BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID, moduleName, accountUniqueID));
483 return accountArtifact;
499 private BlackboardArtifact getAccountFileInstanceArtifact(Account.Type accountType, String accountUniqueID, Content sourceFile)
throws TskCoreException {
500 BlackboardArtifact accountArtifact = null;
501 CaseDbConnection connection = db.getConnection();
507 s = connection.createStatement();
508 String queryStr =
"SELECT artifacts.artifact_id AS artifact_id,"
509 +
" artifacts.obj_id AS obj_id,"
510 +
" artifacts.artifact_obj_id AS artifact_obj_id,"
511 +
" artifacts.data_source_obj_id AS data_source_obj_id,"
512 +
" artifacts.artifact_type_id AS artifact_type_id,"
513 +
" artifacts.review_status_id AS review_status_id"
514 +
" FROM blackboard_artifacts AS artifacts"
515 +
" JOIN blackboard_attributes AS attr_account_type"
516 +
" ON artifacts.artifact_id = attr_account_type.artifact_id"
517 +
" JOIN blackboard_attributes AS attr_account_id"
518 +
" ON artifacts.artifact_id = attr_account_id.artifact_id"
519 +
" AND attr_account_id.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID.getTypeID()
520 +
" AND attr_account_id.value_text = '" + accountUniqueID +
"'"
521 +
" WHERE artifacts.artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT.getTypeID()
522 +
" AND attr_account_type.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE.getTypeID()
523 +
" AND attr_account_type.value_text = '" + accountType.getTypeName() +
"'"
524 +
" AND artifacts.obj_id = " + sourceFile.getId();
526 rs = connection.executeQuery(s, queryStr);
528 BlackboardArtifact.Type bbartType = db.
getArtifactType(rs.getInt(
"artifact_type_id"));
530 accountArtifact =
new BlackboardArtifact(db, rs.getLong(
"artifact_id"), rs.getLong(
"obj_id"), rs.getLong(
"artifact_obj_id"), rs.getLong(
"data_source_obj_id"),
531 bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
532 BlackboardArtifact.ReviewStatus.withID(rs.getInt(
"review_status_id")));
534 }
catch (SQLException ex) {
535 throw new TskCoreException(
"Error getting account", ex);
543 return accountArtifact;
557 if (this.typeNameToAccountTypeMap.containsKey(accountTypeName)) {
558 return this.typeNameToAccountTypeMap.get(accountTypeName);
561 CaseDbConnection connection = db.getConnection();
567 s = connection.createStatement();
568 rs = connection.executeQuery(s,
"SELECT account_type_id, type_name, display_name FROM account_types WHERE type_name = '" + accountTypeName +
"'");
571 accountType =
new Account.
Type(accountTypeName, rs.getString(
"display_name"));
572 this.accountTypeToTypeIdMap.put(accountType, rs.getInt(
"account_type_id"));
573 this.typeNameToAccountTypeMap.put(accountTypeName, accountType);
576 }
catch (SQLException ex) {
577 throw new TskCoreException(
"Error getting account type id", ex);
599 private void addAccountsRelationship(
long account1_id,
long account2_id,
BlackboardArtifact relationshipaArtifact,
Relationship.
Type relationshipType,
long dateTime)
throws TskCoreException {
600 CaseDbConnection connection = db.getConnection();
606 String dateTimeValStr = (dateTime > 0) ? Long.toString(dateTime) :
"NULL";
608 connection.beginTransaction();
609 s = connection.createStatement();
610 String query =
"INTO account_relationships (account1_id, account2_id, relationship_source_obj_id, date_time, relationship_type, data_source_obj_id ) "
611 +
"VALUES ( " + account1_id +
", " + account2_id +
", " + relationshipaArtifact.getId() +
", " + dateTimeValStr +
", " + relationshipType.getTypeID() +
", " + relationshipaArtifact.getDataSourceObjectID() +
")";
614 query =
"INSERT " + query +
" ON CONFLICT DO NOTHING";
617 query =
"INSERT OR IGNORE " + query;
620 throw new TskCoreException(
"Unknown DB Type: " + db.
getDatabaseType().name());
623 connection.commitTransaction();
624 }
catch (SQLException ex) {
625 connection.rollbackTransaction();
626 throw new TskCoreException(
"Error adding accounts relationship", ex);
650 CaseDbConnection connection = db.getConnection();
656 s = connection.createStatement();
659 Set<String> applicableInnerQueryFilters =
new HashSet<String>(Arrays.asList(
664 String relationshipFilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
666 String relationshipLimitSQL = getMostRecentFilterLimitSQL(filter);
668 String relTblfilterQuery =
670 +
"FROM account_relationships as relationships"
671 + (relationshipFilterSQL.isEmpty() ?
"" :
" WHERE " + relationshipFilterSQL)
672 + (relationshipLimitSQL.isEmpty() ?
"" : relationshipLimitSQL);
674 String uniqueAccountQueryTemplate
675 =
" SELECT %1$1s as account_id,"
676 +
" data_source_obj_id"
677 +
" FROM ( " + relTblfilterQuery +
")AS %2$s";
679 String relationshipTableFilterQuery1 = String.format(uniqueAccountQueryTemplate,
"account1_id",
"union_query_1");
680 String relationshipTableFilterQuery2 = String.format(uniqueAccountQueryTemplate,
"account2_id",
"union_query_2");
683 String uniqueAccountQuery
684 =
"SELECT DISTINCT account_id, data_source_obj_id"
685 +
" FROM ( " + relationshipTableFilterQuery1 +
" UNION " + relationshipTableFilterQuery2 +
" ) AS inner_union"
686 +
" GROUP BY account_id, data_source_obj_id";
689 Set<String> applicableFilters =
new HashSet<String>(Arrays.asList(
693 String accountTypeFilterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
697 " accounts.account_id AS account_id,"
698 +
" accounts.account_unique_identifier AS account_unique_identifier,"
700 +
" account_types.type_name AS type_name,"
702 +
" data_source_info.device_id AS device_id"
703 +
" FROM ( " + uniqueAccountQuery +
" ) AS account_device_instances"
704 +
" JOIN accounts AS accounts"
705 +
" ON accounts.account_id = account_device_instances.account_id"
706 +
" JOIN account_types AS account_types"
707 +
" ON accounts.account_type_id = account_types.account_type_id"
708 +
" JOIN data_source_info AS data_source_info"
709 +
" ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
710 + (accountTypeFilterSQL.isEmpty() ?
"" :
" WHERE " + accountTypeFilterSQL);
714 queryStr =
"SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
717 queryStr =
"SELECT " + queryStr +
" GROUP BY accounts.account_id, data_source_info.device_id";
720 throw new TskCoreException(
"Unknown DB Type: " + db.
getDatabaseType().name());
723 rs = connection.executeQuery(s, queryStr);
724 ArrayList<AccountDeviceInstance> accountDeviceInstances =
new ArrayList<AccountDeviceInstance>();
726 long account_id = rs.getLong(
"account_id");
727 String deviceID = rs.getString(
"device_id");
728 final String type_name = rs.getString(
"type_name");
729 final String account_unique_identifier = rs.getString(
"account_unique_identifier");
731 Account.
Type accountType = typeNameToAccountTypeMap.get(type_name);
732 Account account =
new Account(account_id, accountType, account_unique_identifier);
736 return accountDeviceInstances;
737 }
catch (SQLException ex) {
738 throw new TskCoreException(
"Error getting account device instances. " + ex.getMessage(), ex);
768 Set<Long> accountIDs =
new HashSet<Long>();
769 Set<String> accountDeviceIDs =
new HashSet<String>();
771 accountIDs.add(adi.getAccount().getAccountID());
772 accountDeviceIDs.add(
"'" + adi.getDeviceId() +
"'");
775 Set<String> applicableFilters =
new HashSet<String>(Arrays.asList(
781 String accountIDsCSL = StringUtils.buildCSVString(accountIDs);
782 String accountDeviceIDsCSL = StringUtils.buildCSVString(accountDeviceIDs);
783 String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
785 final String queryString
786 =
" SELECT count(DISTINCT relationships.relationship_source_obj_id) AS count,"
787 +
" data_source_info.device_id AS device_id,"
789 +
" accounts1.account_id AS account1_id,"
790 +
" accounts1.account_unique_identifier AS account1_unique_identifier,"
791 +
" account_types1.type_name AS type_name1,"
792 +
" account_types1.display_name AS display_name1,"
794 +
" accounts2.account_id AS account2_id,"
795 +
" accounts2.account_unique_identifier AS account2_unique_identifier,"
796 +
" account_types2.type_name AS type_name2,"
797 +
" account_types2.display_name AS display_name2"
798 +
" FROM account_relationships AS relationships"
799 +
" JOIN data_source_info AS data_source_info"
800 +
" ON relationships.data_source_obj_id = data_source_info.obj_id "
802 +
" JOIN accounts AS accounts1 "
803 +
" ON accounts1.account_id = relationships.account1_id"
804 +
" JOIN account_types AS account_types1"
805 +
" ON accounts1.account_type_id = account_types1.account_type_id"
807 +
" JOIN accounts AS accounts2 "
808 +
" ON accounts2.account_id = relationships.account2_id"
809 +
" JOIN account_types AS account_types2"
810 +
" ON accounts2.account_type_id = account_types2.account_type_id"
811 +
" WHERE (( relationships.account1_id IN (" + accountIDsCSL +
")) "
812 +
" AND ( relationships.account2_id IN ( " + accountIDsCSL +
" ))"
813 +
" AND ( data_source_info.device_id IN (" + accountDeviceIDsCSL +
"))) "
814 + (filterSQL.isEmpty() ?
"" :
" AND " + filterSQL)
815 +
" GROUP BY data_source_info.device_id, "
816 +
" accounts1.account_id, "
817 +
" account_types1.type_name, "
818 +
" account_types1.display_name, "
819 +
" accounts2.account_id, "
820 +
" account_types2.type_name, "
821 +
" account_types2.display_name";
822 CaseDbConnection connection = db.getConnection();
827 Map<AccountPair, Long> results =
new HashMap<AccountPair, Long>();
830 s = connection.createStatement();
831 rs = connection.executeQuery(s, queryString);
837 rs.getString(
"account1_unique_identifier")),
838 rs.getString(
"device_id"));
843 rs.getString(
"account2_unique_identifier")),
844 rs.getString(
"device_id"));
847 long count = rs.getLong(
"count");
850 Long oldCount = results.get(relationshipKey);
851 if (oldCount != null) {
854 results.put(relationshipKey, count);
857 }
catch (SQLException ex) {
858 throw new TskCoreException(
"Error getting relationships between accounts. " + ex.getMessage(), ex);
884 long account_id = accountDeviceInstance.getAccount().getAccountID();
887 String datasourceObjIdsCSV = StringUtils.buildCSVString(
888 db.getDataSourceObjIds(accountDeviceInstance.getDeviceId()));
891 Set<String> applicableFilters =
new HashSet<String>(Arrays.asList(
895 String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
897 CaseDbConnection connection = db.getConnection();
903 s = connection.createStatement();
905 String innerQuery =
" account_relationships AS relationships";
906 String limitStr = getMostRecentFilterLimitSQL(filter);
908 if(!limitStr.isEmpty()) {
909 innerQuery =
"(SELECT * FROM account_relationships as relationships " + limitStr +
") as relationships";
913 =
"SELECT count(DISTINCT relationships.relationship_source_obj_id) as count "
914 +
" FROM" + innerQuery
915 +
" WHERE relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV +
" )"
916 +
" AND ( relationships.account1_id = " + account_id
917 +
" OR relationships.account2_id = " + account_id +
" )"
918 + (filterSQL.isEmpty() ?
"" :
" AND " + filterSQL);
920 rs = connection.executeQuery(s, queryStr);
922 return (rs.getLong(
"count"));
923 }
catch (SQLException ex) {
924 throw new TskCoreException(
"Error getting relationships count for account device instance. " + ex.getMessage(), ex);
950 if (accountDeviceInstanceList.isEmpty()) {
952 return Collections.emptySet();
955 Map<Long, Set<Long>> accountIdToDatasourceObjIdMap =
new HashMap<Long, Set<Long>>();
957 long accountID = accountDeviceInstance.getAccount().getAccountID();
958 List<Long> dataSourceObjIds = db.getDataSourceObjIds(accountDeviceInstance.getDeviceId());
960 if (accountIdToDatasourceObjIdMap.containsKey(accountID)) {
961 accountIdToDatasourceObjIdMap.get(accountID).addAll(dataSourceObjIds);
963 accountIdToDatasourceObjIdMap.put(accountID,
new HashSet<Long>(dataSourceObjIds));
967 List<String> adiSQLClauses =
new ArrayList<String>();
968 for (Map.Entry<Long, Set<Long>> entry : accountIdToDatasourceObjIdMap.entrySet()) {
969 final Long accountID = entry.getKey();
970 String datasourceObjIdsCSV = StringUtils.buildCSVString(entry.getValue());
973 "( ( relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV +
" ) )"
974 +
" AND ( relationships.account1_id = " + accountID
975 +
" OR relationships.account2_id = " + accountID +
" ) )"
978 String adiSQLClause = StringUtils.joinAsStrings(adiSQLClauses,
" OR ");
981 Set<String> applicableFilters =
new HashSet<String>(Arrays.asList(
987 String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
989 String limitQuery =
" account_relationships AS relationships";
990 String limitStr = getMostRecentFilterLimitSQL(filter);
991 if(!limitStr.isEmpty()) {
992 limitQuery =
"(SELECT * FROM account_relationships as relationships " + limitStr +
") as relationships";
995 CaseDbConnection connection = db.getConnection();
1001 s = connection.createStatement();
1003 =
"SELECT DISTINCT artifacts.artifact_id AS artifact_id,"
1004 +
" artifacts.obj_id AS obj_id,"
1005 +
" artifacts.artifact_obj_id AS artifact_obj_id,"
1006 +
" artifacts.data_source_obj_id AS data_source_obj_id, "
1007 +
" artifacts.artifact_type_id AS artifact_type_id, "
1008 +
" artifacts.review_status_id AS review_status_id "
1009 +
" FROM blackboard_artifacts as artifacts"
1010 +
" JOIN " + limitQuery
1011 +
" ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
1013 +
" WHERE (" + adiSQLClause +
" )"
1015 + (filterSQL.isEmpty() ?
"" :
" AND (" + filterSQL +
" )");
1017 rs = connection.executeQuery(s, queryStr);
1018 Set<Content> relationshipSources =
new HashSet<Content>();
1022 rs.getLong(
"obj_id"), rs.getLong(
"artifact_obj_id"),
1023 rs.getLong(
"data_source_obj_id"), bbartType.getTypeID(),
1024 bbartType.getTypeName(), bbartType.getDisplayName(),
1028 return relationshipSources;
1029 }
catch (SQLException ex) {
1030 throw new TskCoreException(
"Error getting relationships for account. " + ex.getMessage(), ex);
1055 final List<Long> dataSourceObjIds
1056 = getSleuthkitCase().getDataSourceObjIds(accountDeviceInstance.getDeviceId());
1059 Set<String> applicableInnerQueryFilters =
new HashSet<String>(Arrays.asList(
1065 String innerQueryfilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
1067 String innerQueryTemplate
1068 =
" SELECT %1$1s as account_id,"
1069 +
" data_source_obj_id"
1070 +
" FROM account_relationships as relationships"
1071 +
" WHERE %2$1s = " + accountDeviceInstance.getAccount().getAccountID() +
""
1072 +
" AND data_source_obj_id IN (" + StringUtils.buildCSVString(dataSourceObjIds) +
")"
1073 + (innerQueryfilterSQL.isEmpty() ?
"" :
" AND " + innerQueryfilterSQL);
1075 String innerQuery1 = String.format(innerQueryTemplate,
"account1_id",
"account2_id");
1076 String innerQuery2 = String.format(innerQueryTemplate,
"account2_id",
"account1_id");
1079 String combinedInnerQuery
1080 =
"SELECT account_id, data_source_obj_id "
1081 +
" FROM ( " + innerQuery1 +
" UNION " + innerQuery2 +
" ) AS inner_union"
1082 +
" GROUP BY account_id, data_source_obj_id";
1085 Set<String> applicableFilters =
new HashSet<String>(Arrays.asList(
1089 String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
1093 " accounts.account_id AS account_id,"
1094 +
" accounts.account_unique_identifier AS account_unique_identifier,"
1096 +
" account_types.type_name AS type_name,"
1098 +
" data_source_info.device_id AS device_id"
1099 +
" FROM ( " + combinedInnerQuery +
" ) AS account_device_instances"
1100 +
" JOIN accounts AS accounts"
1101 +
" ON accounts.account_id = account_device_instances.account_id"
1102 +
" JOIN account_types AS account_types"
1103 +
" ON accounts.account_type_id = account_types.account_type_id"
1104 +
" JOIN data_source_info AS data_source_info"
1105 +
" ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
1106 + (filterSQL.isEmpty() ?
"" :
" WHERE " + filterSQL);
1110 queryStr =
"SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
1113 queryStr =
"SELECT " + queryStr +
" GROUP BY accounts.account_id, data_source_info.device_id";
1116 throw new TskCoreException(
"Unknown DB Type: " + db.
getDatabaseType().name());
1119 CaseDbConnection connection = db.getConnection();
1122 ResultSet rs = null;
1125 s = connection.createStatement();
1127 rs = connection.executeQuery(s, queryStr);
1128 ArrayList<AccountDeviceInstance> accountDeviceInstances =
new ArrayList<AccountDeviceInstance>();
1130 long account_id = rs.getLong(
"account_id");
1131 String deviceID = rs.getString(
"device_id");
1132 final String type_name = rs.getString(
"type_name");
1133 final String account_unique_identifier = rs.getString(
"account_unique_identifier");
1135 Account.
Type accountType = typeNameToAccountTypeMap.get(type_name);
1136 Account account =
new Account(account_id, accountType, account_unique_identifier);
1140 return accountDeviceInstances;
1141 }
catch (SQLException ex) {
1142 throw new TskCoreException(
"Error getting account device instances. " + ex.getMessage(), ex);
1170 Set<String> applicableFilters =
new HashSet<String>(Arrays.asList(
1176 String limitQuery =
" account_relationships AS relationships";
1177 String limitStr = getMostRecentFilterLimitSQL(filter);
1178 if(!limitStr.isEmpty()) {
1179 limitQuery =
"(SELECT * FROM account_relationships as relationships " + limitStr +
") as relationships";
1182 String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
1183 final String queryString =
"SELECT artifacts.artifact_id AS artifact_id,"
1184 +
" artifacts.obj_id AS obj_id,"
1185 +
" artifacts.artifact_obj_id AS artifact_obj_id,"
1186 +
" artifacts.data_source_obj_id AS data_source_obj_id,"
1187 +
" artifacts.artifact_type_id AS artifact_type_id,"
1188 +
" artifacts.review_status_id AS review_status_id"
1189 +
" FROM blackboard_artifacts AS artifacts"
1190 +
" JOIN " + limitQuery
1191 +
" ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
1192 +
" WHERE (( relationships.account1_id = " + account1.getAccount().getAccountID()
1193 +
" AND relationships.account2_id = " + account2.getAccount().getAccountID()
1194 +
" ) OR ( relationships.account2_id = " + account1.getAccount().getAccountID()
1195 +
" AND relationships.account1_id =" + account2.getAccount().getAccountID() +
" ))"
1196 + (filterSQL.isEmpty() ?
"" :
" AND " + filterSQL);
1197 CaseDbConnection connection = db.getConnection();
1200 ResultSet rs = null;
1202 s = connection.createStatement();
1203 rs = connection.executeQuery(s, queryString);
1205 ArrayList<Content> artifacts =
new ArrayList<Content>();
1208 artifacts.add(
new BlackboardArtifact(db, rs.getLong(
"artifact_id"), rs.getLong(
"obj_id"), rs.getLong(
"artifact_obj_id"), rs.getLong(
"data_source_obj_id"),
1209 bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
1214 }
catch (SQLException ex) {
1215 throw new TskCoreException(
"Error getting relationships between accounts. " + ex.getMessage(), ex);
1235 List<AccountFileInstance> accountFileInstanceList =
new ArrayList<>();
1239 if(artifactList != null && !artifactList.isEmpty()) {
1245 if(!accountFileInstanceList.isEmpty()) {
1246 return accountFileInstanceList;
1261 CaseDbConnection connection = db.getConnection();
1264 ResultSet rs = null;
1265 List<
Account.
Type> inUseAccounts =
new ArrayList<>();
1268 String query =
"SELECT DISTINCT accounts.account_type_id, type_name, display_name FROM accounts JOIN account_types ON accounts.account_type_id = account_types.account_type_id";
1269 s = connection.createStatement();
1270 rs = connection.executeQuery(s, query);
1273 String accountTypeName = rs.getString(
"type_name");
1274 accountType = this.typeNameToAccountTypeMap.get(accountTypeName);
1276 if(accountType == null) {
1277 accountType =
new Account.
Type(accountTypeName, rs.getString(
"display_name"));
1278 this.accountTypeToTypeIdMap.put(accountType, rs.getInt(
"account_type_id"));
1281 inUseAccounts.add(accountType);
1283 return inUseAccounts;
1284 }
catch (SQLException ex) {
1285 throw new TskCoreException(
"Error getting account type id", ex);
1302 if (accountTypeToTypeIdMap.containsKey(accountType)) {
1303 return accountTypeToTypeIdMap.get(accountType);
1318 private String normalizeAccountID(Account.Type accountType, String accountUniqueID) {
1319 String normailzeAccountID = accountUniqueID;
1321 if (accountType.equals(Account.Type.PHONE)) {
1322 normailzeAccountID = normalizePhoneNum(accountUniqueID);
1323 }
else if (accountType.equals(Account.Type.EMAIL)) {
1324 normailzeAccountID = normalizeEmailAddress(accountUniqueID);
1327 return normailzeAccountID;
1338 private String normalizePhoneNum(String phoneNum) {
1339 String normailzedPhoneNum = phoneNum.replaceAll(
"\\D",
"");
1341 if (phoneNum.startsWith(
"+")) {
1342 normailzedPhoneNum =
"+" + normailzedPhoneNum;
1345 if(normailzedPhoneNum.isEmpty()) {
1346 normailzedPhoneNum = phoneNum;
1349 return normailzedPhoneNum;
1359 private String normalizeEmailAddress(String emailAddress) {
1360 String normailzedEmailAddr = emailAddress.toLowerCase();
1362 return normailzedEmailAddr;
1377 private String getCommunicationsFilterSQL(CommunicationsFilter commFilter, Set<String> applicableFilters) {
1378 if (null == commFilter || commFilter.getAndFilters().isEmpty()) {
1383 StringBuilder sqlSB =
new StringBuilder();
1384 boolean first =
true;
1385 for (CommunicationsFilter.SubFilter subFilter : commFilter.getAndFilters()) {
1388 if (applicableFilters.contains(subFilter.getClass().getName())) {
1389 String subfilterSQL = subFilter.getSQL(
this);
1390 if (!subfilterSQL.isEmpty()) {
1394 sqlSB.append(
" AND ");
1397 sqlSB.append(subfilterSQL);
1403 if (!sqlSB.toString().isEmpty()) {
1404 sqlStr =
"( " + sqlSB.toString() +
" )";
1416 private String getMostRecentFilterLimitSQL(CommunicationsFilter filter) {
1417 String limitStr =
"";
1419 if (filter != null && !filter.getAndFilters().isEmpty()) {
1421 for (CommunicationsFilter.SubFilter subFilter : filter.getAndFilters()) {
1422 if(subFilter.getClass().getName().equals(CommunicationsFilter.MostRecentFilter.class.getName())) {
1423 limitStr = subFilter.getSQL(
this);
Set< Content > getRelationshipSources(Set< AccountDeviceInstance > accountDeviceInstanceList, CommunicationsFilter filter)
ArrayList< BlackboardArtifact > getBlackboardArtifacts(int artifactTypeID)
void addAttributes(Collection< BlackboardAttribute > attributes)
List< AccountDeviceInstance > getRelatedAccountDeviceInstances(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName)
AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile)
org.sleuthkit.datamodel.Account.Type addAccountType(String accountTypeName, String displayName)
String getTypeSpecificID()
Map< AccountPair, Long > getRelationshipCountsPairwise(Set< AccountDeviceInstance > accounts, CommunicationsFilter filter)
void releaseSingleUserCaseReadLock()
List< Account.Type > getAccountTypesInUse()
BlackboardArtifact newBlackboardArtifact(int artifactTypeID, long obj_id)
static final List< Account.Type > PREDEFINED_ACCOUNT_TYPES
void acquireSingleUserCaseWriteLock()
BlackboardArtifact.Type getArtifactType(String artTypeName)
void releaseSingleUserCaseWriteLock()
List< Content > getRelationshipSources(AccountDeviceInstance account1, AccountDeviceInstance account2, CommunicationsFilter filter)
long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
void acquireSingleUserCaseReadLock()
List< AccountDeviceInstance > getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter)
static ReviewStatus withID(int id)
void addRelationships(AccountFileInstance sender, List< AccountFileInstance > recipients, BlackboardArtifact sourceArtifact, org.sleuthkit.datamodel.Relationship.Type relationshipType, long dateTime)
Account getAccount(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID)
List< AccountFileInstance > getAccountFileInstances(Account account)