Sleuth Kit Java Bindings (JNI)  4.10.2
Java bindings for using The Sleuth Kit
CommunicationsManager.java
Go to the documentation of this file.
1 /*
2  * Sleuth Kit Data Model
3  *
4  * Copyright 2017-2021 Basis Technology Corp.
5  * Contact: carrier <at> sleuthkit <dot> org
6  *
7  * Licensed under the Apache License, Version 2.0 (the "License");
8  * you may not use this file except in compliance with the License.
9  * You may obtain a copy of the License at
10  *
11  * http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing, software
14  * distributed under the License is distributed on an "AS IS" BASIS,
15  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16  * See the License for the specific language governing permissions and
17  * limitations under the License.
18  */
19 package org.sleuthkit.datamodel;
20 
21 import java.sql.PreparedStatement;
22 import java.sql.ResultSet;
23 import java.sql.SQLException;
24 import java.sql.Statement;
25 import java.util.ArrayList;
26 import java.util.Arrays;
27 import java.util.Collections;
28 import java.util.HashMap;
29 import java.util.HashSet;
30 import java.util.List;
31 import java.util.Map;
32 import java.util.Set;
33 import java.util.concurrent.ConcurrentHashMap;
34 import java.util.logging.Level;
35 import java.util.logging.Logger;
36 import org.sleuthkit.datamodel.Blackboard.BlackboardException;
37 import org.sleuthkit.datamodel.SleuthkitCase.CaseDbConnection;
38 import org.sleuthkit.datamodel.SleuthkitCase.CaseDbTransaction;
39 import static org.sleuthkit.datamodel.SleuthkitCase.closeConnection;
40 import static org.sleuthkit.datamodel.SleuthkitCase.closeResultSet;
41 import static org.sleuthkit.datamodel.SleuthkitCase.closeStatement;
42 
47 public final class CommunicationsManager {
48 
49  private static final Logger LOGGER = Logger.getLogger(CommunicationsManager.class.getName());
50  private static final BlackboardArtifact.Type ACCOUNT_TYPE = new BlackboardArtifact.Type(BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT);
51  private final SleuthkitCase db;
52 
53  private final Map<Account.Type, Integer> accountTypeToTypeIdMap
54  = new ConcurrentHashMap<>();
55  private final Map<String, Account.Type> typeNameToAccountTypeMap
56  = new ConcurrentHashMap<>();
57 
58  // Artifact types that can represent a relationship between accounts.
59  private static final Set<Integer> RELATIONSHIP_ARTIFACT_TYPE_IDS = new HashSet<Integer>(Arrays.asList(
60  BlackboardArtifact.ARTIFACT_TYPE.TSK_MESSAGE.getTypeID(),
61  BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG.getTypeID(),
62  BlackboardArtifact.ARTIFACT_TYPE.TSK_CONTACT.getTypeID(),
63  BlackboardArtifact.ARTIFACT_TYPE.TSK_CALLLOG.getTypeID()
64  ));
65  private static final String RELATIONSHIP_ARTIFACT_TYPE_IDS_CSV_STR = StringUtils.buildCSVString(RELATIONSHIP_ARTIFACT_TYPE_IDS);
66 
75  CommunicationsManager(SleuthkitCase skCase) throws TskCoreException {
76  this.db = skCase;
77  initAccountTypes();
78  }
79 
86  private void initAccountTypes() throws TskCoreException {
87  db.acquireSingleUserCaseWriteLock();
88  try (CaseDbConnection connection = db.getConnection();
89  Statement statement = connection.createStatement();) {
90  // Read the table
91  int count = readAccountTypes();
92  if (0 == count) {
93  // Table is empty, populate it with predefined types
94  for (Account.Type type : Account.Type.PREDEFINED_ACCOUNT_TYPES) {
95  try {
96  statement.execute("INSERT INTO account_types (type_name, display_name) VALUES ( '" + type.getTypeName() + "', '" + type.getDisplayName() + "')"); //NON-NLS
97  } catch (SQLException ex) {
98  try (ResultSet resultSet = connection.executeQuery(statement, "SELECT COUNT(*) AS count FROM account_types WHERE type_name = '" + type.getTypeName() + "'")) { //NON-NLS
99  resultSet.next();
100  if (resultSet.getLong("count") == 0) {
101  throw ex;
102  }
103  }
104  }
105 
106  try (ResultSet rs2 = connection.executeQuery(statement, "SELECT account_type_id FROM account_types WHERE type_name = '" + type.getTypeName() + "'")) { //NON-NLS
107  rs2.next();
108  int typeID = rs2.getInt("account_type_id");
109 
110  Account.Type accountType = new Account.Type(type.getTypeName(), type.getDisplayName());
111  this.accountTypeToTypeIdMap.put(accountType, typeID);
112  this.typeNameToAccountTypeMap.put(type.getTypeName(), accountType);
113  }
114  }
115  }
116  } catch (SQLException ex) {
117  LOGGER.log(Level.SEVERE, "Failed to add row to account_types", ex);
118  } finally {
119  db.releaseSingleUserCaseWriteLock();
120  }
121  }
122 
131  private int readAccountTypes() throws TskCoreException {
132  CaseDbConnection connection = null;
133  Statement statement = null;
134  ResultSet resultSet = null;
135  int count = 0;
136 
137  db.acquireSingleUserCaseReadLock();
138  try {
139  connection = db.getConnection();
140  statement = connection.createStatement();
141 
142  // If the account_types table is already populated, say when opening a case, then load it
143  resultSet = connection.executeQuery(statement, "SELECT COUNT(*) AS count FROM account_types"); //NON-NLS
144  resultSet.next();
145  if (resultSet.getLong("count") > 0) {
146 
147  resultSet.close();
148  resultSet = connection.executeQuery(statement, "SELECT * FROM account_types");
149  while (resultSet.next()) {
150  Account.Type accountType = new Account.Type(resultSet.getString("type_name"), resultSet.getString("display_name"));
151  this.accountTypeToTypeIdMap.put(accountType, resultSet.getInt("account_type_id"));
152  this.typeNameToAccountTypeMap.put(accountType.getTypeName(), accountType);
153  }
154  count = this.typeNameToAccountTypeMap.size();
155  }
156 
157  } catch (SQLException ex) {
158  throw new TskCoreException("Failed to read account_types", ex);
159  } finally {
160  closeResultSet(resultSet);
161  closeStatement(statement);
162  closeConnection(connection);
163  db.releaseSingleUserCaseReadLock();
164  }
165 
166  return count;
167  }
168 
174  SleuthkitCase getSleuthkitCase() {
175  return this.db;
176  }
177 
190  // NOTE: Full name given for Type for doxygen linking
191  public org.sleuthkit.datamodel.Account.Type addAccountType(String accountTypeName, String displayName) throws TskCoreException {
192  Account.Type accountType = new Account.Type(accountTypeName, displayName);
193 
194  // check if already in map
195  if (this.accountTypeToTypeIdMap.containsKey(accountType)) {
196  return accountType;
197  }
198 
199  CaseDbTransaction trans = db.beginTransaction();
200  Statement s = null;
201  ResultSet rs = null;
202  try {
203  s = trans.getConnection().createStatement();
204  rs = trans.getConnection().executeQuery(s, "SELECT * FROM account_types WHERE type_name = '" + accountTypeName + "'"); //NON-NLS
205  if (!rs.next()) {
206  rs.close();
207 
208  s.execute("INSERT INTO account_types (type_name, display_name) VALUES ( '" + accountTypeName + "', '" + displayName + "')"); //NON-NLS
209 
210  // Read back the typeID
211  rs = trans.getConnection().executeQuery(s, "SELECT * FROM account_types WHERE type_name = '" + accountTypeName + "'"); //NON-NLS
212  rs.next();
213 
214  int typeID = rs.getInt("account_type_id");
215  accountType = new Account.Type(rs.getString("type_name"), rs.getString("display_name"));
216 
217  this.accountTypeToTypeIdMap.put(accountType, typeID);
218  this.typeNameToAccountTypeMap.put(accountTypeName, accountType);
219 
220  trans.commit();
221 
222  return accountType;
223  } else {
224  int typeID = rs.getInt("account_type_id");
225 
226  accountType = new Account.Type(rs.getString("type_name"), rs.getString("display_name"));
227  this.accountTypeToTypeIdMap.put(accountType, typeID);
228 
229  return accountType;
230  }
231  } catch (SQLException ex) {
232  trans.rollback();
233  throw new TskCoreException("Error adding account type", ex);
234  } finally {
235  closeResultSet(rs);
236  closeStatement(s);
237  }
238  }
239 
258  // NOTE: Full name given for Type for doxygen linking
259  public AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile) throws TskCoreException, InvalidAccountIDException {
260 
261  // make or get the Account (unique at the case-level)
262  Account account = getOrCreateAccount(accountType, normalizeAccountID(accountType, accountUniqueID));
263 
264  /*
265  * make or get the artifact. Will not create one if it already exists
266  * for the sourceFile. Such as an email PST that has the same email
267  * address multiple times. Only one artifact is created for each email
268  * message in that PST.
269  */
270  BlackboardArtifact accountArtifact = getOrCreateAccountFileInstanceArtifact(accountType, normalizeAccountID(accountType, accountUniqueID), moduleName, sourceFile);
271 
272  // The account instance map was unused so we have removed it from the database,
273  // but we expect we may need it so I am preserving this method comment and usage here.
274  // add a row to Accounts to Instances mapping table
275  // @@@ BC: Seems like we should only do this if we had to create the artifact.
276  // But, it will probably fail to create a new one based on unique constraints.
277  // addAccountFileInstanceMapping(account.getAccountID(), accountArtifact.getArtifactID());
278  return new AccountFileInstance(accountArtifact, account);
279  }
280 
294  // NOTE: Full name given for Type for doxygen linking
295  public Account getAccount(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID) throws TskCoreException, InvalidAccountIDException {
296  Account account = null;
297  db.acquireSingleUserCaseReadLock();
298  try (CaseDbConnection connection = db.getConnection();
299  Statement s = connection.createStatement();
300  ResultSet rs = connection.executeQuery(s, "SELECT * FROM accounts WHERE account_type_id = " + getAccountTypeId(accountType)
301  + " AND account_unique_identifier = '" + normalizeAccountID(accountType, accountUniqueID) + "'");) { //NON-NLS
302 
303  if (rs.next()) {
304  account = new Account(rs.getInt("account_id"), accountType,
305  rs.getString("account_unique_identifier"));
306  }
307  } catch (SQLException ex) {
308  throw new TskCoreException("Error getting account type id", ex);
309  } finally {
310  db.releaseSingleUserCaseReadLock();
311  }
312 
313  return account;
314  }
315 
338  // NOTE: Full name given for Type for doxygen linking
339  public void addRelationships(AccountFileInstance sender, List<AccountFileInstance> recipients,
340  BlackboardArtifact sourceArtifact, org.sleuthkit.datamodel.Relationship.Type relationshipType, long dateTime) throws TskCoreException, TskDataException {
341 
342  if (sourceArtifact.getDataSourceObjectID() == null) {
343  throw new TskDataException("Source Artifact does not have a valid data source.");
344  }
345 
346  if (relationshipType.isCreatableFrom(sourceArtifact) == false) {
347  throw new TskDataException("Can not make a " + relationshipType.getDisplayName()
348  + " relationship from a" + sourceArtifact.getDisplayName());
349  }
350 
351  /*
352  * Enforce that all accounts and the relationship between them are from
353  * the same 'source'. This is required for the queries to work
354  * correctly.
355  */
356  // Currently we do not save the direction of communication
357  List<Long> accountIDs = new ArrayList<>();
358 
359  if (null != sender) {
360  accountIDs.add(sender.getAccount().getAccountID());
361  if (!sender.getDataSourceObjectID().equals(sourceArtifact.getDataSourceObjectID())) {
362  throw new TskDataException("Sender and relationship are from different data sources :"
363  + "Sender source ID" + sender.getDataSourceObjectID() + " != relationship source ID" + sourceArtifact.getDataSourceObjectID());
364  }
365  }
366 
367  for (AccountFileInstance recipient : recipients) {
368  accountIDs.add(recipient.getAccount().getAccountID());
369  if (!recipient.getDataSourceObjectID().equals(sourceArtifact.getDataSourceObjectID())) {
370  throw new TskDataException("Recipient and relationship are from different data sources :"
371  + "Recipient source ID" + recipient.getDataSourceObjectID() + " != relationship source ID" + sourceArtifact.getDataSourceObjectID());
372  }
373  }
374 
375  // Set up the query for the prepared statement
376  String query = "INTO account_relationships (account1_id, account2_id, relationship_source_obj_id, date_time, relationship_type, data_source_obj_id ) "
377  + "VALUES (?,?,?,?,?,?)";
378  switch (db.getDatabaseType()) {
379  case POSTGRESQL:
380  query = "INSERT " + query + " ON CONFLICT DO NOTHING";
381  break;
382  case SQLITE:
383  query = "INSERT OR IGNORE " + query;
384  break;
385  default:
386  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
387  }
388 
389  CaseDbTransaction trans = db.beginTransaction();
390  try {
391  SleuthkitCase.CaseDbConnection connection = trans.getConnection();
392  PreparedStatement preparedStatement = connection.getPreparedStatement(query, Statement.NO_GENERATED_KEYS);
393 
394  for (int i = 0; i < accountIDs.size(); i++) {
395  for (int j = i + 1; j < accountIDs.size(); j++) {
396  long account1_id = accountIDs.get(i);
397  long account2_id = accountIDs.get(j);
398 
399  preparedStatement.clearParameters();
400  preparedStatement.setLong(1, account1_id);
401  preparedStatement.setLong(2, account2_id);
402  preparedStatement.setLong(3, sourceArtifact.getId());
403  if (dateTime > 0) {
404  preparedStatement.setLong(4, dateTime);
405  } else {
406  preparedStatement.setNull(4, java.sql.Types.BIGINT);
407  }
408  preparedStatement.setInt(5, relationshipType.getTypeID());
409  preparedStatement.setLong(6, sourceArtifact.getDataSourceObjectID());
410 
411  connection.executeUpdate(preparedStatement);
412  }
413  }
414  trans.commit();
415  } catch (SQLException ex) {
416  trans.rollback();
417  throw new TskCoreException("Error adding accounts relationship", ex);
418  }
419  }
420 
435  private Account getOrCreateAccount(Account.Type accountType, String accountUniqueID) throws TskCoreException, InvalidAccountIDException {
436  Account account = getAccount(accountType, accountUniqueID);
437  if (null == account) {
438  String query = " INTO accounts (account_type_id, account_unique_identifier) "
439  + "VALUES ( " + getAccountTypeId(accountType) + ", '"
440  + normalizeAccountID(accountType, accountUniqueID) + "'" + ")";
441  switch (db.getDatabaseType()) {
442  case POSTGRESQL:
443  query = "INSERT " + query + " ON CONFLICT DO NOTHING"; //NON-NLS
444  break;
445  case SQLITE:
446  query = "INSERT OR IGNORE " + query;
447  break;
448  default:
449  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
450  }
451 
452  CaseDbTransaction trans = db.beginTransaction();
453  Statement s = null;
454  ResultSet rs = null;
455  try {
456  s = trans.getConnection().createStatement();
457 
458  s.execute(query);
459 
460  trans.commit();
461  account = getAccount(accountType, accountUniqueID);
462  } catch (SQLException ex) {
463  trans.rollback();
464  throw new TskCoreException("Error adding an account", ex);
465  } finally {
466  closeResultSet(rs);
467  closeStatement(s);
468  }
469  }
470 
471  return account;
472  }
473 
491  private BlackboardArtifact getOrCreateAccountFileInstanceArtifact(Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile) throws TskCoreException {
492  if (sourceFile == null) {
493  throw new TskCoreException("Source file not provided.");
494  }
495 
496  BlackboardArtifact accountArtifact = getAccountFileInstanceArtifact(accountType, accountUniqueID, sourceFile);
497  if (accountArtifact == null) {
498  List<BlackboardAttribute> attributes = Arrays.asList(
499  new BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE, moduleName, accountType.getTypeName()),
500  new BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID, moduleName, accountUniqueID)
501  );
502 
503  accountArtifact = sourceFile.newDataArtifact(ACCOUNT_TYPE, attributes);
504 
505  try {
506  db.getBlackboard().postArtifact(accountArtifact, moduleName);
507  } catch (BlackboardException ex) {
508  LOGGER.log(Level.SEVERE, String.format("Error posting new account artifact to the blackboard (object ID = %d)", accountArtifact.getId()), ex);
509  }
510  }
511  return accountArtifact;
512  }
513 
527  private BlackboardArtifact getAccountFileInstanceArtifact(Account.Type accountType, String accountUniqueID, Content sourceFile) throws TskCoreException {
528  BlackboardArtifact accountArtifact = null;
529 
530  String queryStr = "SELECT artifacts.artifact_id AS artifact_id,"
531  + " artifacts.obj_id AS obj_id,"
532  + " artifacts.artifact_obj_id AS artifact_obj_id,"
533  + " artifacts.data_source_obj_id AS data_source_obj_id,"
534  + " artifacts.artifact_type_id AS artifact_type_id,"
535  + " artifacts.review_status_id AS review_status_id"
536  + " FROM blackboard_artifacts AS artifacts"
537  + " JOIN blackboard_attributes AS attr_account_type"
538  + " ON artifacts.artifact_id = attr_account_type.artifact_id"
539  + " JOIN blackboard_attributes AS attr_account_id"
540  + " ON artifacts.artifact_id = attr_account_id.artifact_id"
541  + " AND attr_account_id.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID.getTypeID()
542  + " AND attr_account_id.value_text = '" + accountUniqueID + "'"
543  + " WHERE artifacts.artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT.getTypeID()
544  + " AND attr_account_type.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE.getTypeID()
545  + " AND attr_account_type.value_text = '" + accountType.getTypeName() + "'"
546  + " AND artifacts.obj_id = " + sourceFile.getId(); //NON-NLS
547 
548  db.acquireSingleUserCaseReadLock();
549  try (CaseDbConnection connection = db.getConnection();
550  Statement s = connection.createStatement();
551  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
552  if (rs.next()) {
553  BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
554 
555  accountArtifact = new BlackboardArtifact(db, rs.getLong("artifact_id"), rs.getLong("obj_id"), rs.getLong("artifact_obj_id"),
556  rs.getObject("data_source_obj_id") != null ? rs.getLong("data_source_obj_id") : null,
557  bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
558  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id")));
559  }
560  } catch (SQLException ex) {
561  throw new TskCoreException("Error getting account", ex);
562  } finally {
563  db.releaseSingleUserCaseReadLock();
564  }
565 
566  return accountArtifact;
567  }
568 
578  // NOTE: Full name given for Type for doxygen linking
579  public org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName) throws TskCoreException {
580  if (this.typeNameToAccountTypeMap.containsKey(accountTypeName)) {
581  return this.typeNameToAccountTypeMap.get(accountTypeName);
582  }
583 
584  db.acquireSingleUserCaseReadLock();
585  try (CaseDbConnection connection = db.getConnection();
586  Statement s = connection.createStatement();
587  ResultSet rs = connection.executeQuery(s, "SELECT account_type_id, type_name, display_name FROM account_types WHERE type_name = '" + accountTypeName + "'");) { //NON-NLS
588  Account.Type accountType = null;
589  if (rs.next()) {
590  accountType = new Account.Type(accountTypeName, rs.getString("display_name"));
591  this.accountTypeToTypeIdMap.put(accountType, rs.getInt("account_type_id"));
592  this.typeNameToAccountTypeMap.put(accountTypeName, accountType);
593  }
594  return accountType;
595  } catch (SQLException ex) {
596  throw new TskCoreException("Error getting account type id", ex);
597  } finally {
598  db.releaseSingleUserCaseReadLock();
599  }
600  }
601 
616  public List<AccountDeviceInstance> getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter) throws TskCoreException {
617 
618  //set up applicable filters
619  Set<String> applicableInnerQueryFilters = new HashSet<String>(Arrays.asList(
620  CommunicationsFilter.DateRangeFilter.class.getName(),
621  CommunicationsFilter.DeviceFilter.class.getName(),
622  CommunicationsFilter.RelationshipTypeFilter.class.getName()
623  ));
624  String relationshipFilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
625 
626  String relationshipLimitSQL = getMostRecentFilterLimitSQL(filter);
627 
628  String relTblfilterQuery
629  = "SELECT * "
630  + "FROM account_relationships as relationships"
631  + (relationshipFilterSQL.isEmpty() ? "" : " WHERE " + relationshipFilterSQL)
632  + (relationshipLimitSQL.isEmpty() ? "" : relationshipLimitSQL);
633 
634  String uniqueAccountQueryTemplate
635  = " SELECT %1$1s as account_id,"
636  + " data_source_obj_id"
637  + " FROM ( " + relTblfilterQuery + ")AS %2$s";
638 
639  String relationshipTableFilterQuery1 = String.format(uniqueAccountQueryTemplate, "account1_id", "union_query_1");
640  String relationshipTableFilterQuery2 = String.format(uniqueAccountQueryTemplate, "account2_id", "union_query_2");
641 
642  //this query groups by account_id and data_source_obj_id across both innerQueries
643  String uniqueAccountQuery
644  = "SELECT DISTINCT account_id, data_source_obj_id"
645  + " FROM ( " + relationshipTableFilterQuery1 + " UNION " + relationshipTableFilterQuery2 + " ) AS inner_union"
646  + " GROUP BY account_id, data_source_obj_id";
647 
648  // set up applicable filters
649  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
650  CommunicationsFilter.AccountTypeFilter.class.getName()
651  ));
652 
653  String accountTypeFilterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
654 
655  String queryStr
656  = //account info
657  " accounts.account_id AS account_id,"
658  + " accounts.account_unique_identifier AS account_unique_identifier,"
659  //account type info
660  + " account_types.type_name AS type_name,"
661  //Account device instance info
662  + " data_source_info.device_id AS device_id"
663  + " FROM ( " + uniqueAccountQuery + " ) AS account_device_instances"
664  + " JOIN accounts AS accounts"
665  + " ON accounts.account_id = account_device_instances.account_id"
666  + " JOIN account_types AS account_types"
667  + " ON accounts.account_type_id = account_types.account_type_id"
668  + " JOIN data_source_info AS data_source_info"
669  + " ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
670  + (accountTypeFilterSQL.isEmpty() ? "" : " WHERE " + accountTypeFilterSQL);
671 
672  switch (db.getDatabaseType()) {
673  case POSTGRESQL:
674  queryStr = "SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
675  break;
676  case SQLITE:
677  queryStr = "SELECT " + queryStr + " GROUP BY accounts.account_id, data_source_info.device_id";
678  break;
679  default:
680  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
681  }
682 
683  db.acquireSingleUserCaseReadLock();
684  try (CaseDbConnection connection = db.getConnection();
685  Statement s = connection.createStatement();
686  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
687  ArrayList<AccountDeviceInstance> accountDeviceInstances = new ArrayList<AccountDeviceInstance>();
688  while (rs.next()) {
689  long account_id = rs.getLong("account_id");
690  String deviceID = rs.getString("device_id");
691  final String type_name = rs.getString("type_name");
692  final String account_unique_identifier = rs.getString("account_unique_identifier");
693 
694  Account.Type accountType = typeNameToAccountTypeMap.get(type_name);
695  Account account = new Account(account_id, accountType, account_unique_identifier);
696  accountDeviceInstances.add(new AccountDeviceInstance(account, deviceID));
697  }
698 
699  return accountDeviceInstances;
700  } catch (SQLException ex) {
701  throw new TskCoreException("Error getting account device instances. " + ex.getMessage(), ex);
702  } finally {
703  db.releaseSingleUserCaseReadLock();
704  }
705  }
706 
726  public Map<AccountPair, Long> getRelationshipCountsPairwise(Set<AccountDeviceInstance> accounts, CommunicationsFilter filter) throws TskCoreException {
727 
728  Set<Long> accountIDs = new HashSet<Long>();
729  Set<String> accountDeviceIDs = new HashSet<String>();
730  for (AccountDeviceInstance adi : accounts) {
731  accountIDs.add(adi.getAccount().getAccountID());
732  accountDeviceIDs.add("'" + adi.getDeviceId() + "'");
733  }
734  //set up applicable filters
735  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
736  CommunicationsFilter.DateRangeFilter.class.getName(),
737  CommunicationsFilter.DeviceFilter.class.getName(),
738  CommunicationsFilter.RelationshipTypeFilter.class.getName()
739  ));
740 
741  String accountIDsCSL = StringUtils.buildCSVString(accountIDs);
742  String accountDeviceIDsCSL = StringUtils.buildCSVString(accountDeviceIDs);
743  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
744 
745  final String queryString
746  = " SELECT count(DISTINCT relationships.relationship_source_obj_id) AS count," //realtionship count
747  + " data_source_info.device_id AS device_id,"
748  //account 1 info
749  + " accounts1.account_id AS account1_id,"
750  + " accounts1.account_unique_identifier AS account1_unique_identifier,"
751  + " account_types1.type_name AS type_name1,"
752  + " account_types1.display_name AS display_name1,"
753  //account 2 info
754  + " accounts2.account_id AS account2_id,"
755  + " accounts2.account_unique_identifier AS account2_unique_identifier,"
756  + " account_types2.type_name AS type_name2,"
757  + " account_types2.display_name AS display_name2"
758  + " FROM account_relationships AS relationships"
759  + " JOIN data_source_info AS data_source_info"
760  + " ON relationships.data_source_obj_id = data_source_info.obj_id "
761  //account1 aliases
762  + " JOIN accounts AS accounts1 "
763  + " ON accounts1.account_id = relationships.account1_id"
764  + " JOIN account_types AS account_types1"
765  + " ON accounts1.account_type_id = account_types1.account_type_id"
766  //account2 aliases
767  + " JOIN accounts AS accounts2 "
768  + " ON accounts2.account_id = relationships.account2_id"
769  + " JOIN account_types AS account_types2"
770  + " ON accounts2.account_type_id = account_types2.account_type_id"
771  + " WHERE (( relationships.account1_id IN (" + accountIDsCSL + ")) "
772  + " AND ( relationships.account2_id IN ( " + accountIDsCSL + " ))"
773  + " AND ( data_source_info.device_id IN (" + accountDeviceIDsCSL + "))) "
774  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL)
775  + " GROUP BY data_source_info.device_id, "
776  + " accounts1.account_id, "
777  + " account_types1.type_name, "
778  + " account_types1.display_name, "
779  + " accounts2.account_id, "
780  + " account_types2.type_name, "
781  + " account_types2.display_name";
782 
783  Map<AccountPair, Long> results = new HashMap<AccountPair, Long>();
784 
785  db.acquireSingleUserCaseReadLock();
786  try (CaseDbConnection connection = db.getConnection();
787  Statement s = connection.createStatement();
788  ResultSet rs = connection.executeQuery(s, queryString);) { //NON-NLS
789 
790  while (rs.next()) {
791  //make account 1
792  Account.Type type1 = new Account.Type(rs.getString("type_name1"), rs.getString("display_name1"));
793  AccountDeviceInstance adi1 = new AccountDeviceInstance(new Account(rs.getLong("account1_id"), type1,
794  rs.getString("account1_unique_identifier")),
795  rs.getString("device_id"));
796 
797  //make account 2
798  Account.Type type2 = new Account.Type(rs.getString("type_name2"), rs.getString("display_name2"));
799  AccountDeviceInstance adi2 = new AccountDeviceInstance(new Account(rs.getLong("account2_id"), type2,
800  rs.getString("account2_unique_identifier")),
801  rs.getString("device_id"));
802 
803  AccountPair relationshipKey = new AccountPair(adi1, adi2);
804  long count = rs.getLong("count");
805 
806  //merge counts for relationships that have the accounts flipped.
807  Long oldCount = results.get(relationshipKey);
808  if (oldCount != null) {
809  count += oldCount;
810  }
811  results.put(relationshipKey, count);
812  }
813  return results;
814  } catch (SQLException ex) {
815  throw new TskCoreException("Error getting relationships between accounts. " + ex.getMessage(), ex);
816  } finally {
817  db.releaseSingleUserCaseReadLock();
818  }
819  }
820 
836  public long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter) throws TskCoreException {
837 
838  long account_id = accountDeviceInstance.getAccount().getAccountID();
839 
840  // Get the list of Data source objects IDs correpsonding to this DeviceID.
841  String datasourceObjIdsCSV = StringUtils.buildCSVString(
842  db.getDataSourceObjIds(accountDeviceInstance.getDeviceId()));
843 
844  // set up applicable filters
845  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
846  CommunicationsFilter.RelationshipTypeFilter.class.getName(),
847  CommunicationsFilter.DateRangeFilter.class.getName()
848  ));
849  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
850 
851  String innerQuery = " account_relationships AS relationships";
852  String limitStr = getMostRecentFilterLimitSQL(filter);
853 
854  if (!limitStr.isEmpty()) {
855  innerQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
856  }
857 
858  String queryStr
859  = "SELECT count(DISTINCT relationships.relationship_source_obj_id) as count "
860  + " FROM" + innerQuery
861  + " WHERE relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV + " )"
862  + " AND ( relationships.account1_id = " + account_id
863  + " OR relationships.account2_id = " + account_id + " )"
864  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL);
865 
866  db.acquireSingleUserCaseReadLock();
867  try (CaseDbConnection connection = db.getConnection();
868  Statement s = connection.createStatement();
869  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
870  rs.next();
871  return (rs.getLong("count"));
872  } catch (SQLException ex) {
873  throw new TskCoreException("Error getting relationships count for account device instance. " + ex.getMessage(), ex);
874  } finally {
875  db.releaseSingleUserCaseReadLock();
876  }
877  }
878 
895  public Set<Content> getRelationshipSources(Set<AccountDeviceInstance> accountDeviceInstanceList, CommunicationsFilter filter) throws TskCoreException {
896 
897  if (accountDeviceInstanceList.isEmpty()) {
898  //log this?
899  return Collections.emptySet();
900  }
901 
902  Map<Long, Set<Long>> accountIdToDatasourceObjIdMap = new HashMap<Long, Set<Long>>();
903  for (AccountDeviceInstance accountDeviceInstance : accountDeviceInstanceList) {
904  long accountID = accountDeviceInstance.getAccount().getAccountID();
905  List<Long> dataSourceObjIds = db.getDataSourceObjIds(accountDeviceInstance.getDeviceId());
906 
907  if (accountIdToDatasourceObjIdMap.containsKey(accountID)) {
908  accountIdToDatasourceObjIdMap.get(accountID).addAll(dataSourceObjIds);
909  } else {
910  accountIdToDatasourceObjIdMap.put(accountID, new HashSet<Long>(dataSourceObjIds));
911  }
912  }
913 
914  List<String> adiSQLClauses = new ArrayList<String>();
915  for (Map.Entry<Long, Set<Long>> entry : accountIdToDatasourceObjIdMap.entrySet()) {
916  final Long accountID = entry.getKey();
917  String datasourceObjIdsCSV = StringUtils.buildCSVString(entry.getValue());
918 
919  adiSQLClauses.add(
920  "( ( relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV + " ) )"
921  + " AND ( relationships.account1_id = " + accountID
922  + " OR relationships.account2_id = " + accountID + " ) )"
923  );
924  }
925  String adiSQLClause = StringUtils.joinAsStrings(adiSQLClauses, " OR ");
926 
927  // set up applicable filters
928  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
929  CommunicationsFilter.RelationshipTypeFilter.class
930  .getName(),
931  CommunicationsFilter.DateRangeFilter.class
932  .getName()
933  ));
934  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
935 
936  String limitQuery = " account_relationships AS relationships";
937  String limitStr = getMostRecentFilterLimitSQL(filter);
938  if (!limitStr.isEmpty()) {
939  limitQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
940  }
941 
942  String queryStr
943  = "SELECT DISTINCT artifacts.artifact_id AS artifact_id,"
944  + " artifacts.obj_id AS obj_id,"
945  + " artifacts.artifact_obj_id AS artifact_obj_id,"
946  + " artifacts.data_source_obj_id AS data_source_obj_id, "
947  + " artifacts.artifact_type_id AS artifact_type_id, "
948  + " artifacts.review_status_id AS review_status_id "
949  + " FROM blackboard_artifacts as artifacts"
950  + " JOIN " + limitQuery
951  + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
952  // append sql to restrict search to specified account device instances
953  + " WHERE (" + adiSQLClause + " )"
954  // plus other filters
955  + (filterSQL.isEmpty() ? "" : " AND (" + filterSQL + " )");
956 
957 
958  db.acquireSingleUserCaseReadLock();
959  try (CaseDbConnection connection = db.getConnection();
960  Statement s = connection.createStatement();
961  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
962  Set<Content> relationshipSources = new HashSet<Content>();
963  while (rs.next()) {
964  BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
965  relationshipSources.add(new BlackboardArtifact(db, rs.getLong("artifact_id"),
966  rs.getLong("obj_id"), rs.getLong("artifact_obj_id"),
967  rs.getObject("data_source_obj_id") != null ? rs.getLong("data_source_obj_id") : null,
968  bbartType.getTypeID(),
969  bbartType.getTypeName(), bbartType.getDisplayName(),
970  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id"))));
971  }
972 
973  return relationshipSources;
974  } catch (SQLException ex) {
975  throw new TskCoreException("Error getting relationships for account. " + ex.getMessage(), ex);
976  } finally {
977  db.releaseSingleUserCaseReadLock();
978  }
979  }
980 
996  public List<AccountDeviceInstance> getRelatedAccountDeviceInstances(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter) throws TskCoreException {
997  final List<Long> dataSourceObjIds
998  = getSleuthkitCase().getDataSourceObjIds(accountDeviceInstance.getDeviceId());
999 
1000  //set up applicable filters
1001  Set<String> applicableInnerQueryFilters = new HashSet<String>(Arrays.asList(
1002  CommunicationsFilter.DateRangeFilter.class.getName(),
1003  CommunicationsFilter.DeviceFilter.class.getName(),
1004  CommunicationsFilter.RelationshipTypeFilter.class.getName()
1005  ));
1006 
1007  String innerQueryfilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
1008 
1009  String innerQueryTemplate
1010  = " SELECT %1$1s as account_id,"
1011  + " data_source_obj_id"
1012  + " FROM account_relationships as relationships"
1013  + " WHERE %2$1s = " + accountDeviceInstance.getAccount().getAccountID() + ""
1014  + " AND data_source_obj_id IN (" + StringUtils.buildCSVString(dataSourceObjIds) + ")"
1015  + (innerQueryfilterSQL.isEmpty() ? "" : " AND " + innerQueryfilterSQL);
1016 
1017  String innerQuery1 = String.format(innerQueryTemplate, "account1_id", "account2_id");
1018  String innerQuery2 = String.format(innerQueryTemplate, "account2_id", "account1_id");
1019 
1020  //this query groups by account_id and data_source_obj_id across both innerQueries
1021  String combinedInnerQuery
1022  = "SELECT account_id, data_source_obj_id "
1023  + " FROM ( " + innerQuery1 + " UNION " + innerQuery2 + " ) AS inner_union"
1024  + " GROUP BY account_id, data_source_obj_id";
1025 
1026  // set up applicable filters
1027  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
1028  CommunicationsFilter.AccountTypeFilter.class.getName()
1029  ));
1030 
1031  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
1032 
1033  String queryStr
1034  = //account info
1035  " accounts.account_id AS account_id,"
1036  + " accounts.account_unique_identifier AS account_unique_identifier,"
1037  //account type info
1038  + " account_types.type_name AS type_name,"
1039  //Account device instance info
1040  + " data_source_info.device_id AS device_id"
1041  + " FROM ( " + combinedInnerQuery + " ) AS account_device_instances"
1042  + " JOIN accounts AS accounts"
1043  + " ON accounts.account_id = account_device_instances.account_id"
1044  + " JOIN account_types AS account_types"
1045  + " ON accounts.account_type_id = account_types.account_type_id"
1046  + " JOIN data_source_info AS data_source_info"
1047  + " ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
1048  + (filterSQL.isEmpty() ? "" : " WHERE " + filterSQL);
1049 
1050  switch (db.getDatabaseType()) {
1051  case POSTGRESQL:
1052  queryStr = "SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
1053  break;
1054  case SQLITE:
1055  queryStr = "SELECT " + queryStr + " GROUP BY accounts.account_id, data_source_info.device_id";
1056  break;
1057  default:
1058  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
1059  }
1060 
1061  db.acquireSingleUserCaseReadLock();
1062  try (CaseDbConnection connection = db.getConnection();
1063  Statement s = connection.createStatement();
1064  ResultSet rs = connection.executeQuery(s, queryStr);) {
1065  ArrayList<AccountDeviceInstance> accountDeviceInstances = new ArrayList<AccountDeviceInstance>();
1066  while (rs.next()) {
1067  long account_id = rs.getLong("account_id");
1068  String deviceID = rs.getString("device_id");
1069  final String type_name = rs.getString("type_name");
1070  final String account_unique_identifier = rs.getString("account_unique_identifier");
1071 
1072  Account.Type accountType = typeNameToAccountTypeMap.get(type_name);
1073  Account account = new Account(account_id, accountType, account_unique_identifier);
1074  accountDeviceInstances.add(new AccountDeviceInstance(account, deviceID));
1075  }
1076 
1077  return accountDeviceInstances;
1078  } catch (SQLException ex) {
1079  throw new TskCoreException("Error getting account device instances. " + ex.getMessage(), ex);
1080  } finally {
1081  db.releaseSingleUserCaseReadLock();
1082  }
1083  }
1084 
1101  public List<Content> getRelationshipSources(AccountDeviceInstance account1, AccountDeviceInstance account2, CommunicationsFilter filter) throws TskCoreException {
1102 
1103  //set up applicable filters
1104  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
1105  CommunicationsFilter.DateRangeFilter.class.getName(),
1106  CommunicationsFilter.DeviceFilter.class.getName(),
1107  CommunicationsFilter.RelationshipTypeFilter.class.getName()
1108  ));
1109 
1110  String limitQuery = " account_relationships AS relationships";
1111  String limitStr = getMostRecentFilterLimitSQL(filter);
1112  if (!limitStr.isEmpty()) {
1113  limitQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
1114  }
1115 
1116  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
1117  final String queryString = "SELECT artifacts.artifact_id AS artifact_id,"
1118  + " artifacts.obj_id AS obj_id,"
1119  + " artifacts.artifact_obj_id AS artifact_obj_id,"
1120  + " artifacts.data_source_obj_id AS data_source_obj_id,"
1121  + " artifacts.artifact_type_id AS artifact_type_id,"
1122  + " artifacts.review_status_id AS review_status_id"
1123  + " FROM blackboard_artifacts AS artifacts"
1124  + " JOIN " + limitQuery
1125  + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
1126  + " WHERE (( relationships.account1_id = " + account1.getAccount().getAccountID()
1127  + " AND relationships.account2_id = " + account2.getAccount().getAccountID()
1128  + " ) OR ( relationships.account2_id = " + account1.getAccount().getAccountID()
1129  + " AND relationships.account1_id =" + account2.getAccount().getAccountID() + " ))"
1130  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL);
1131 
1132  db.acquireSingleUserCaseReadLock();
1133  try (CaseDbConnection connection = db.getConnection();
1134  Statement s = connection.createStatement();
1135  ResultSet rs = connection.executeQuery(s, queryString);) {
1136 
1137  ArrayList<Content> artifacts = new ArrayList<Content>();
1138  while (rs.next()) {
1139  BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
1140  artifacts.add(new BlackboardArtifact(db, rs.getLong("artifact_id"), rs.getLong("obj_id"), rs.getLong("artifact_obj_id"),
1141  rs.getObject("data_source_obj_id") != null ? rs.getLong("data_source_obj_id") : null,
1142  bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
1143  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id"))));
1144  }
1145 
1146  return artifacts;
1147  } catch (SQLException ex) {
1148  throw new TskCoreException("Error getting relationships between accounts. " + ex.getMessage(), ex);
1149  } finally {
1150  db.releaseSingleUserCaseReadLock();
1151  }
1152  }
1153 
1164  public List<AccountFileInstance> getAccountFileInstances(Account account) throws TskCoreException {
1165  List<AccountFileInstance> accountFileInstanceList = new ArrayList<>();
1166 
1167  List<BlackboardArtifact> artifactList = getSleuthkitCase().getBlackboardArtifacts(BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT, BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID, account.getTypeSpecificID());
1168 
1169  if (artifactList != null && !artifactList.isEmpty()) {
1170  for (BlackboardArtifact artifact : artifactList) {
1171  accountFileInstanceList.add(new AccountFileInstance(artifact, account));
1172  }
1173  }
1174 
1175  if (!accountFileInstanceList.isEmpty()) {
1176  return accountFileInstanceList;
1177  } else {
1178  return null;
1179  }
1180  }
1181 
1190  public List<Account.Type> getAccountTypesInUse() throws TskCoreException {
1191 
1192  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";
1193  List<Account.Type> inUseAccounts = new ArrayList<>();
1194 
1195  db.acquireSingleUserCaseReadLock();
1196  try (CaseDbConnection connection = db.getConnection();
1197  Statement s = connection.createStatement();
1198  ResultSet rs = connection.executeQuery(s, query);) {
1199  Account.Type accountType;
1200  while (rs.next()) {
1201  String accountTypeName = rs.getString("type_name");
1202  accountType = this.typeNameToAccountTypeMap.get(accountTypeName);
1203 
1204  if (accountType == null) {
1205  accountType = new Account.Type(accountTypeName, rs.getString("display_name"));
1206  this.accountTypeToTypeIdMap.put(accountType, rs.getInt("account_type_id"));
1207  }
1208 
1209  inUseAccounts.add(accountType);
1210  }
1211  return inUseAccounts;
1212  } catch (SQLException ex) {
1213  throw new TskCoreException("Error getting account type id", ex);
1214  } finally {
1215  db.releaseSingleUserCaseReadLock();
1216  }
1217  }
1218 
1228  public List<Account> getAccountsRelatedToArtifact(BlackboardArtifact artifact) throws TskCoreException {
1229  if (artifact == null) {
1230  throw new IllegalArgumentException("null arugment passed to getAccountsRelatedToArtifact");
1231  }
1232 
1233  List<Account> accountList = new ArrayList<>();
1234  db.acquireSingleUserCaseReadLock();
1235  try (CaseDbConnection connection = db.getConnection()) {
1236  try {
1237  // In order to get a list of all the unique accounts in a relationship with the given aritfact
1238  // we must first union a list of the unique account1_id in the relationship with artifact
1239  // then the unique account2_id (inner select with union). The outter select assures the list
1240  // of the inner select only contains unique accounts.
1241  String query = String.format("SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1242  + " FROM ("
1243  + " SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1244  + " FROM accounts"
1245  + " JOIN account_relationships ON account1_id = account_id"
1246  + " WHERE relationship_source_obj_id = %d"
1247  + " UNION "
1248  + " SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1249  + " FROM accounts"
1250  + " JOIN account_relationships ON account2_id = account_id"
1251  + " WHERE relationship_source_obj_id = %d) AS unionOfRelationships", artifact.getId(), artifact.getId());
1252  try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
1253  while (rs.next()) {
1254  Account.Type accountType = null;
1255  int accountTypeId = rs.getInt("account_type_id");
1256  for (Map.Entry<Account.Type, Integer> entry : accountTypeToTypeIdMap.entrySet()) {
1257  if (entry.getValue() == accountTypeId) {
1258  accountType = entry.getKey();
1259  break;
1260  }
1261  }
1262 
1263  accountList.add(new Account(rs.getInt("account_id"), accountType, rs.getString("account_unique_identifier")));
1264  }
1265  } catch (SQLException ex) {
1266  throw new TskCoreException("Unable to get account list for give artifact " + artifact.getId(), ex);
1267  }
1268 
1269  } finally {
1270  db.releaseSingleUserCaseReadLock();
1271  }
1272  }
1273 
1274  return accountList;
1275  }
1276 
1284  int getAccountTypeId(Account.Type accountType) {
1285  if (accountTypeToTypeIdMap.containsKey(accountType)) {
1286  return accountTypeToTypeIdMap.get(accountType);
1287  }
1288 
1289  return 0;
1290  }
1291 
1303  private String normalizeAccountID(Account.Type accountType, String accountUniqueID) throws InvalidAccountIDException {
1304 
1305  if (accountUniqueID == null || accountUniqueID.isEmpty()) {
1306  throw new InvalidAccountIDException("Account id is null or empty.");
1307  }
1308 
1309  String normalizedAccountID;
1310  if (accountType.equals(Account.Type.PHONE)) {
1311  normalizedAccountID = CommunicationsUtils.normalizePhoneNum(accountUniqueID);
1312  } else if (accountType.equals(Account.Type.EMAIL)) {
1313  normalizedAccountID = CommunicationsUtils.normalizeEmailAddress(accountUniqueID);
1314  } else {
1315  normalizedAccountID = accountUniqueID.toLowerCase().trim();
1316  }
1317 
1318  return normalizedAccountID;
1319  }
1320 
1333  private String getCommunicationsFilterSQL(CommunicationsFilter commFilter, Set<String> applicableFilters) {
1334  if (null == commFilter || commFilter.getAndFilters().isEmpty()) {
1335  return "";
1336  }
1337 
1338  String sqlStr = "";
1339  StringBuilder sqlSB = new StringBuilder();
1340  boolean first = true;
1341  for (CommunicationsFilter.SubFilter subFilter : commFilter.getAndFilters()) {
1342 
1343  // If the filter is applicable
1344  if (applicableFilters.contains(subFilter.getClass().getName())) {
1345  String subfilterSQL = subFilter.getSQL(this);
1346  if (!subfilterSQL.isEmpty()) {
1347  if (first) {
1348  first = false;
1349  } else {
1350  sqlSB.append(" AND ");
1351  }
1352  sqlSB.append("( ");
1353  sqlSB.append(subfilterSQL);
1354  sqlSB.append(" )");
1355  }
1356  }
1357  }
1358 
1359  if (!sqlSB.toString().isEmpty()) {
1360  sqlStr = "( " + sqlSB.toString() + " )";
1361  }
1362  return sqlStr;
1363  }
1364 
1373  private String getMostRecentFilterLimitSQL(CommunicationsFilter filter) {
1374  String limitStr = "";
1375 
1376  if (filter != null && !filter.getAndFilters().isEmpty()) {
1377 
1378  for (CommunicationsFilter.SubFilter subFilter : filter.getAndFilters()) {
1379  if (subFilter.getClass().getName().equals(CommunicationsFilter.MostRecentFilter.class.getName())) {
1380  limitStr = subFilter.getSQL(this);
1381  break;
1382  }
1383  }
1384  }
1385 
1386  return limitStr;
1387  }
1388 }
org.sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID
TSK_ID
Definition: BlackboardAttribute.java:1413
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSources
Set< Content > getRelationshipSources(Set< AccountDeviceInstance > accountDeviceInstanceList, CommunicationsFilter filter)
Definition: CommunicationsManager.java:895
org.sleuthkit.datamodel.Blackboard.postArtifact
void postArtifact(BlackboardArtifact artifact, String moduleName)
Definition: Blackboard.java:71
org.sleuthkit.datamodel.SleuthkitCase.getBlackboardArtifacts
ArrayList< BlackboardArtifact > getBlackboardArtifacts(int artifactTypeID)
Definition: SleuthkitCase.java:3417
org.sleuthkit.datamodel.CommunicationsManager.getRelatedAccountDeviceInstances
List< AccountDeviceInstance > getRelatedAccountDeviceInstances(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
Definition: CommunicationsManager.java:996
org.sleuthkit.datamodel.CommunicationsManager.getAccountType
org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName)
Definition: CommunicationsManager.java:579
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_MESSAGE
TSK_MESSAGE
Definition: BlackboardArtifact.java:1623
org.sleuthkit.datamodel.CommunicationsManager.createAccountFileInstance
AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile)
Definition: CommunicationsManager.java:259
org.sleuthkit.datamodel.CommunicationsManager.addAccountType
org.sleuthkit.datamodel.Account.Type addAccountType(String accountTypeName, String displayName)
Definition: CommunicationsManager.java:191
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_CONTACT
TSK_CONTACT
Definition: BlackboardArtifact.java:1615
org.sleuthkit.datamodel.CommunicationsManager.getAccountsRelatedToArtifact
List< Account > getAccountsRelatedToArtifact(BlackboardArtifact artifact)
Definition: CommunicationsManager.java:1228
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipCountsPairwise
Map< AccountPair, Long > getRelationshipCountsPairwise(Set< AccountDeviceInstance > accounts, CommunicationsFilter filter)
Definition: CommunicationsManager.java:726
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT
TSK_ACCOUNT
Definition: BlackboardArtifact.java:1704
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG
TSK_EMAIL_MSG
Definition: BlackboardArtifact.java:1552
org.sleuthkit.datamodel.Account.Type.PREDEFINED_ACCOUNT_TYPES
static final List< Account.Type > PREDEFINED_ACCOUNT_TYPES
Definition: Account.java:70
org.sleuthkit.datamodel.Content
Definition: Content.java:33
org.sleuthkit.datamodel.SleuthkitCase.getArtifactType
BlackboardArtifact.Type getArtifactType(String artTypeName)
Definition: SleuthkitCase.java:4751
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSources
List< Content > getRelationshipSources(AccountDeviceInstance account1, AccountDeviceInstance account2, CommunicationsFilter filter)
Definition: CommunicationsManager.java:1101
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_CALLLOG
TSK_CALLLOG
Definition: BlackboardArtifact.java:1631
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSourcesCount
long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
Definition: CommunicationsManager.java:836
org.sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE
Definition: BlackboardAttribute.java:1014
org.sleuthkit.datamodel.CommunicationsManager.getAccountDeviceInstancesWithRelationships
List< AccountDeviceInstance > getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter)
Definition: CommunicationsManager.java:616
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE
Definition: BlackboardArtifact.java:1475
org.sleuthkit.datamodel.CommunicationsManager.addRelationships
void addRelationships(AccountFileInstance sender, List< AccountFileInstance > recipients, BlackboardArtifact sourceArtifact, org.sleuthkit.datamodel.Relationship.Type relationshipType, long dateTime)
Definition: CommunicationsManager.java:339
org.sleuthkit.datamodel.CommunicationsManager.getAccount
Account getAccount(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID)
Definition: CommunicationsManager.java:295
org.sleuthkit.datamodel.CommunicationsManager.getAccountFileInstances
List< AccountFileInstance > getAccountFileInstances(Account account)
Definition: CommunicationsManager.java:1164

Copyright © 2011-2021 Brian Carrier. (carrier -at- sleuthkit -dot- org)
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.