Sleuth Kit Java Bindings (JNI)  4.11.0
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  + " tsk_data_artifacts.os_account_obj_id AS os_account_obj_id"
537  + " FROM blackboard_artifacts AS artifacts"
538  + " JOIN blackboard_attributes AS attr_account_type"
539  + " ON artifacts.artifact_id = attr_account_type.artifact_id"
540  + " JOIN blackboard_attributes AS attr_account_id"
541  + " ON artifacts.artifact_id = attr_account_id.artifact_id"
542  + " AND attr_account_id.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID.getTypeID()
543  + " AND attr_account_id.value_text = '" + accountUniqueID + "'"
544  + " LEFT JOIN tsk_data_artifacts ON tsk_data_artifacts.artifact_obj_id = artifacts.artifact_obj_id"
545  + " WHERE artifacts.artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT.getTypeID()
546  + " AND attr_account_type.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE.getTypeID()
547  + " AND attr_account_type.value_text = '" + accountType.getTypeName() + "'"
548  + " AND artifacts.obj_id = " + sourceFile.getId(); //NON-NLS
549 
550  db.acquireSingleUserCaseReadLock();
551  try (CaseDbConnection connection = db.getConnection();
552  Statement s = connection.createStatement();
553  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
554  if (rs.next()) {
555  BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
556 
557  accountArtifact = new DataArtifact(db, rs.getLong("artifact_id"), rs.getLong("obj_id"), rs.getLong("artifact_obj_id"),
558  rs.getObject("data_source_obj_id") != null ? rs.getLong("data_source_obj_id") : null,
559  bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
560  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id")), rs.getLong("os_account_obj_id"), false);
561  }
562  } catch (SQLException ex) {
563  throw new TskCoreException("Error getting account", ex);
564  } finally {
565  db.releaseSingleUserCaseReadLock();
566  }
567 
568  return accountArtifact;
569  }
570 
580  // NOTE: Full name given for Type for doxygen linking
581  public org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName) throws TskCoreException {
582  if (this.typeNameToAccountTypeMap.containsKey(accountTypeName)) {
583  return this.typeNameToAccountTypeMap.get(accountTypeName);
584  }
585 
586  db.acquireSingleUserCaseReadLock();
587  try (CaseDbConnection connection = db.getConnection();
588  Statement s = connection.createStatement();
589  ResultSet rs = connection.executeQuery(s, "SELECT account_type_id, type_name, display_name FROM account_types WHERE type_name = '" + accountTypeName + "'");) { //NON-NLS
590  Account.Type accountType = null;
591  if (rs.next()) {
592  accountType = new Account.Type(accountTypeName, rs.getString("display_name"));
593  this.accountTypeToTypeIdMap.put(accountType, rs.getInt("account_type_id"));
594  this.typeNameToAccountTypeMap.put(accountTypeName, accountType);
595  }
596  return accountType;
597  } catch (SQLException ex) {
598  throw new TskCoreException("Error getting account type id", ex);
599  } finally {
600  db.releaseSingleUserCaseReadLock();
601  }
602  }
603 
618  public List<AccountDeviceInstance> getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter) throws TskCoreException {
619 
620  //set up applicable filters
621  Set<String> applicableInnerQueryFilters = new HashSet<String>(Arrays.asList(
622  CommunicationsFilter.DateRangeFilter.class.getName(),
623  CommunicationsFilter.DeviceFilter.class.getName(),
624  CommunicationsFilter.RelationshipTypeFilter.class.getName()
625  ));
626  String relationshipFilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
627 
628  String relationshipLimitSQL = getMostRecentFilterLimitSQL(filter);
629 
630  String relTblfilterQuery
631  = "SELECT * "
632  + "FROM account_relationships as relationships"
633  + (relationshipFilterSQL.isEmpty() ? "" : " WHERE " + relationshipFilterSQL)
634  + (relationshipLimitSQL.isEmpty() ? "" : relationshipLimitSQL);
635 
636  String uniqueAccountQueryTemplate
637  = " SELECT %1$1s as account_id,"
638  + " data_source_obj_id"
639  + " FROM ( " + relTblfilterQuery + ")AS %2$s";
640 
641  String relationshipTableFilterQuery1 = String.format(uniqueAccountQueryTemplate, "account1_id", "union_query_1");
642  String relationshipTableFilterQuery2 = String.format(uniqueAccountQueryTemplate, "account2_id", "union_query_2");
643 
644  //this query groups by account_id and data_source_obj_id across both innerQueries
645  String uniqueAccountQuery
646  = "SELECT DISTINCT account_id, data_source_obj_id"
647  + " FROM ( " + relationshipTableFilterQuery1 + " UNION " + relationshipTableFilterQuery2 + " ) AS inner_union"
648  + " GROUP BY account_id, data_source_obj_id";
649 
650  // set up applicable filters
651  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
652  CommunicationsFilter.AccountTypeFilter.class.getName()
653  ));
654 
655  String accountTypeFilterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
656 
657  String queryStr
658  = //account info
659  " accounts.account_id AS account_id,"
660  + " accounts.account_unique_identifier AS account_unique_identifier,"
661  //account type info
662  + " account_types.type_name AS type_name,"
663  //Account device instance info
664  + " data_source_info.device_id AS device_id"
665  + " FROM ( " + uniqueAccountQuery + " ) AS account_device_instances"
666  + " JOIN accounts AS accounts"
667  + " ON accounts.account_id = account_device_instances.account_id"
668  + " JOIN account_types AS account_types"
669  + " ON accounts.account_type_id = account_types.account_type_id"
670  + " JOIN data_source_info AS data_source_info"
671  + " ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
672  + (accountTypeFilterSQL.isEmpty() ? "" : " WHERE " + accountTypeFilterSQL);
673 
674  switch (db.getDatabaseType()) {
675  case POSTGRESQL:
676  queryStr = "SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
677  break;
678  case SQLITE:
679  queryStr = "SELECT " + queryStr + " GROUP BY accounts.account_id, data_source_info.device_id";
680  break;
681  default:
682  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
683  }
684 
685  db.acquireSingleUserCaseReadLock();
686  try (CaseDbConnection connection = db.getConnection();
687  Statement s = connection.createStatement();
688  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
689  ArrayList<AccountDeviceInstance> accountDeviceInstances = new ArrayList<AccountDeviceInstance>();
690  while (rs.next()) {
691  long account_id = rs.getLong("account_id");
692  String deviceID = rs.getString("device_id");
693  final String type_name = rs.getString("type_name");
694  final String account_unique_identifier = rs.getString("account_unique_identifier");
695 
696  Account.Type accountType = typeNameToAccountTypeMap.get(type_name);
697  Account account = new Account(account_id, accountType, account_unique_identifier);
698  accountDeviceInstances.add(new AccountDeviceInstance(account, deviceID));
699  }
700 
701  return accountDeviceInstances;
702  } catch (SQLException ex) {
703  throw new TskCoreException("Error getting account device instances. " + ex.getMessage(), ex);
704  } finally {
705  db.releaseSingleUserCaseReadLock();
706  }
707  }
708 
728  public Map<AccountPair, Long> getRelationshipCountsPairwise(Set<AccountDeviceInstance> accounts, CommunicationsFilter filter) throws TskCoreException {
729 
730  Set<Long> accountIDs = new HashSet<Long>();
731  Set<String> accountDeviceIDs = new HashSet<String>();
732  for (AccountDeviceInstance adi : accounts) {
733  accountIDs.add(adi.getAccount().getAccountID());
734  accountDeviceIDs.add("'" + adi.getDeviceId() + "'");
735  }
736  //set up applicable filters
737  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
738  CommunicationsFilter.DateRangeFilter.class.getName(),
739  CommunicationsFilter.DeviceFilter.class.getName(),
740  CommunicationsFilter.RelationshipTypeFilter.class.getName()
741  ));
742 
743  String accountIDsCSL = StringUtils.buildCSVString(accountIDs);
744  String accountDeviceIDsCSL = StringUtils.buildCSVString(accountDeviceIDs);
745  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
746 
747  final String queryString
748  = " SELECT count(DISTINCT relationships.relationship_source_obj_id) AS count," //realtionship count
749  + " data_source_info.device_id AS device_id,"
750  //account 1 info
751  + " accounts1.account_id AS account1_id,"
752  + " accounts1.account_unique_identifier AS account1_unique_identifier,"
753  + " account_types1.type_name AS type_name1,"
754  + " account_types1.display_name AS display_name1,"
755  //account 2 info
756  + " accounts2.account_id AS account2_id,"
757  + " accounts2.account_unique_identifier AS account2_unique_identifier,"
758  + " account_types2.type_name AS type_name2,"
759  + " account_types2.display_name AS display_name2"
760  + " FROM account_relationships AS relationships"
761  + " JOIN data_source_info AS data_source_info"
762  + " ON relationships.data_source_obj_id = data_source_info.obj_id "
763  //account1 aliases
764  + " JOIN accounts AS accounts1 "
765  + " ON accounts1.account_id = relationships.account1_id"
766  + " JOIN account_types AS account_types1"
767  + " ON accounts1.account_type_id = account_types1.account_type_id"
768  //account2 aliases
769  + " JOIN accounts AS accounts2 "
770  + " ON accounts2.account_id = relationships.account2_id"
771  + " JOIN account_types AS account_types2"
772  + " ON accounts2.account_type_id = account_types2.account_type_id"
773  + " WHERE (( relationships.account1_id IN (" + accountIDsCSL + ")) "
774  + " AND ( relationships.account2_id IN ( " + accountIDsCSL + " ))"
775  + " AND ( data_source_info.device_id IN (" + accountDeviceIDsCSL + "))) "
776  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL)
777  + " GROUP BY data_source_info.device_id, "
778  + " accounts1.account_id, "
779  + " account_types1.type_name, "
780  + " account_types1.display_name, "
781  + " accounts2.account_id, "
782  + " account_types2.type_name, "
783  + " account_types2.display_name";
784 
785  Map<AccountPair, Long> results = new HashMap<AccountPair, Long>();
786 
787  db.acquireSingleUserCaseReadLock();
788  try (CaseDbConnection connection = db.getConnection();
789  Statement s = connection.createStatement();
790  ResultSet rs = connection.executeQuery(s, queryString);) { //NON-NLS
791 
792  while (rs.next()) {
793  //make account 1
794  Account.Type type1 = new Account.Type(rs.getString("type_name1"), rs.getString("display_name1"));
795  AccountDeviceInstance adi1 = new AccountDeviceInstance(new Account(rs.getLong("account1_id"), type1,
796  rs.getString("account1_unique_identifier")),
797  rs.getString("device_id"));
798 
799  //make account 2
800  Account.Type type2 = new Account.Type(rs.getString("type_name2"), rs.getString("display_name2"));
801  AccountDeviceInstance adi2 = new AccountDeviceInstance(new Account(rs.getLong("account2_id"), type2,
802  rs.getString("account2_unique_identifier")),
803  rs.getString("device_id"));
804 
805  AccountPair relationshipKey = new AccountPair(adi1, adi2);
806  long count = rs.getLong("count");
807 
808  //merge counts for relationships that have the accounts flipped.
809  Long oldCount = results.get(relationshipKey);
810  if (oldCount != null) {
811  count += oldCount;
812  }
813  results.put(relationshipKey, count);
814  }
815  return results;
816  } catch (SQLException ex) {
817  throw new TskCoreException("Error getting relationships between accounts. " + ex.getMessage(), ex);
818  } finally {
819  db.releaseSingleUserCaseReadLock();
820  }
821  }
822 
838  public long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter) throws TskCoreException {
839 
840  long account_id = accountDeviceInstance.getAccount().getAccountID();
841 
842  // Get the list of Data source objects IDs correpsonding to this DeviceID.
843  String datasourceObjIdsCSV = StringUtils.buildCSVString(
844  db.getDataSourceObjIds(accountDeviceInstance.getDeviceId()));
845 
846  // set up applicable filters
847  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
848  CommunicationsFilter.RelationshipTypeFilter.class.getName(),
849  CommunicationsFilter.DateRangeFilter.class.getName()
850  ));
851  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
852 
853  String innerQuery = " account_relationships AS relationships";
854  String limitStr = getMostRecentFilterLimitSQL(filter);
855 
856  if (!limitStr.isEmpty()) {
857  innerQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
858  }
859 
860  String queryStr
861  = "SELECT count(DISTINCT relationships.relationship_source_obj_id) as count "
862  + " FROM" + innerQuery
863  + " WHERE relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV + " )"
864  + " AND ( relationships.account1_id = " + account_id
865  + " OR relationships.account2_id = " + account_id + " )"
866  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL);
867 
868  db.acquireSingleUserCaseReadLock();
869  try (CaseDbConnection connection = db.getConnection();
870  Statement s = connection.createStatement();
871  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
872  rs.next();
873  return (rs.getLong("count"));
874  } catch (SQLException ex) {
875  throw new TskCoreException("Error getting relationships count for account device instance. " + ex.getMessage(), ex);
876  } finally {
877  db.releaseSingleUserCaseReadLock();
878  }
879  }
880 
897  public Set<Content> getRelationshipSources(Set<AccountDeviceInstance> accountDeviceInstanceList, CommunicationsFilter filter) throws TskCoreException {
898 
899  if (accountDeviceInstanceList.isEmpty()) {
900  //log this?
901  return Collections.emptySet();
902  }
903 
904  Map<Long, Set<Long>> accountIdToDatasourceObjIdMap = new HashMap<>();
905  for (AccountDeviceInstance accountDeviceInstance : accountDeviceInstanceList) {
906  long accountID = accountDeviceInstance.getAccount().getAccountID();
907  List<Long> dataSourceObjIds = db.getDataSourceObjIds(accountDeviceInstance.getDeviceId());
908 
909  if (accountIdToDatasourceObjIdMap.containsKey(accountID)) {
910  accountIdToDatasourceObjIdMap.get(accountID).addAll(dataSourceObjIds);
911  } else {
912  accountIdToDatasourceObjIdMap.put(accountID, new HashSet<>(dataSourceObjIds));
913  }
914  }
915 
916  List<String> adiSQLClauses = new ArrayList<>();
917  for (Map.Entry<Long, Set<Long>> entry : accountIdToDatasourceObjIdMap.entrySet()) {
918  final Long accountID = entry.getKey();
919  String datasourceObjIdsCSV = StringUtils.buildCSVString(entry.getValue());
920 
921  adiSQLClauses.add(
922  "( ( relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV + " ) )"
923  + " AND ( relationships.account1_id = " + accountID
924  + " OR relationships.account2_id = " + accountID + " ) )"
925  );
926  }
927  String adiSQLClause = StringUtils.joinAsStrings(adiSQLClauses, " OR ");
928 
929  // set up applicable filters
930  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
931  CommunicationsFilter.RelationshipTypeFilter.class
932  .getName(),
933  CommunicationsFilter.DateRangeFilter.class
934  .getName()
935  ));
936  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
937 
938  String limitQuery = " account_relationships AS relationships";
939  String limitStr = getMostRecentFilterLimitSQL(filter);
940  if (!limitStr.isEmpty()) {
941  limitQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
942  }
943 
944  String queryStr
945  = "SELECT DISTINCT artifacts.artifact_id AS artifact_id,"
946  + " artifacts.obj_id AS obj_id,"
947  + " artifacts.artifact_obj_id AS artifact_obj_id,"
948  + " artifacts.data_source_obj_id AS data_source_obj_id, "
949  + " artifacts.artifact_type_id AS artifact_type_id, "
950  + " artifacts.review_status_id AS review_status_id,"
951  + " tsk_data_artifacts.os_account_obj_id as os_account_obj_id"
952  + " FROM blackboard_artifacts as artifacts"
953  + " JOIN " + limitQuery
954  + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
955  + " LEFT JOIN tsk_data_artifacts ON artifacts.artifact_obj_id = tsk_data_artifacts.artifact_obj_id"
956  // append sql to restrict search to specified account device instances
957  + " WHERE (" + adiSQLClause + " )"
958  // plus other filters
959  + (filterSQL.isEmpty() ? "" : " AND (" + filterSQL + " )");
960 
961  db.acquireSingleUserCaseReadLock();
962  try (CaseDbConnection connection = db.getConnection();
963  Statement s = connection.createStatement();
964  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
965  Set<Content> relationshipSources = new HashSet<>();
966  relationshipSources.addAll(getDataArtifactsFromResult(rs));
967  return relationshipSources;
968  } catch (SQLException ex) {
969  throw new TskCoreException("Error getting relationships for account. " + ex.getMessage(), ex);
970  } finally {
971  db.releaseSingleUserCaseReadLock();
972  }
973  }
974 
990  public List<AccountDeviceInstance> getRelatedAccountDeviceInstances(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter) throws TskCoreException {
991  final List<Long> dataSourceObjIds
992  = getSleuthkitCase().getDataSourceObjIds(accountDeviceInstance.getDeviceId());
993 
994  //set up applicable filters
995  Set<String> applicableInnerQueryFilters = new HashSet<String>(Arrays.asList(
996  CommunicationsFilter.DateRangeFilter.class.getName(),
997  CommunicationsFilter.DeviceFilter.class.getName(),
998  CommunicationsFilter.RelationshipTypeFilter.class.getName()
999  ));
1000 
1001  String innerQueryfilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
1002 
1003  String innerQueryTemplate
1004  = " SELECT %1$1s as account_id,"
1005  + " data_source_obj_id"
1006  + " FROM account_relationships as relationships"
1007  + " WHERE %2$1s = " + accountDeviceInstance.getAccount().getAccountID() + ""
1008  + " AND data_source_obj_id IN (" + StringUtils.buildCSVString(dataSourceObjIds) + ")"
1009  + (innerQueryfilterSQL.isEmpty() ? "" : " AND " + innerQueryfilterSQL);
1010 
1011  String innerQuery1 = String.format(innerQueryTemplate, "account1_id", "account2_id");
1012  String innerQuery2 = String.format(innerQueryTemplate, "account2_id", "account1_id");
1013 
1014  //this query groups by account_id and data_source_obj_id across both innerQueries
1015  String combinedInnerQuery
1016  = "SELECT account_id, data_source_obj_id "
1017  + " FROM ( " + innerQuery1 + " UNION " + innerQuery2 + " ) AS inner_union"
1018  + " GROUP BY account_id, data_source_obj_id";
1019 
1020  // set up applicable filters
1021  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
1022  CommunicationsFilter.AccountTypeFilter.class.getName()
1023  ));
1024 
1025  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
1026 
1027  String queryStr
1028  = //account info
1029  " accounts.account_id AS account_id,"
1030  + " accounts.account_unique_identifier AS account_unique_identifier,"
1031  //account type info
1032  + " account_types.type_name AS type_name,"
1033  //Account device instance info
1034  + " data_source_info.device_id AS device_id"
1035  + " FROM ( " + combinedInnerQuery + " ) AS account_device_instances"
1036  + " JOIN accounts AS accounts"
1037  + " ON accounts.account_id = account_device_instances.account_id"
1038  + " JOIN account_types AS account_types"
1039  + " ON accounts.account_type_id = account_types.account_type_id"
1040  + " JOIN data_source_info AS data_source_info"
1041  + " ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
1042  + (filterSQL.isEmpty() ? "" : " WHERE " + filterSQL);
1043 
1044  switch (db.getDatabaseType()) {
1045  case POSTGRESQL:
1046  queryStr = "SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
1047  break;
1048  case SQLITE:
1049  queryStr = "SELECT " + queryStr + " GROUP BY accounts.account_id, data_source_info.device_id";
1050  break;
1051  default:
1052  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
1053  }
1054 
1055  db.acquireSingleUserCaseReadLock();
1056  try (CaseDbConnection connection = db.getConnection();
1057  Statement s = connection.createStatement();
1058  ResultSet rs = connection.executeQuery(s, queryStr);) {
1059  ArrayList<AccountDeviceInstance> accountDeviceInstances = new ArrayList<AccountDeviceInstance>();
1060  while (rs.next()) {
1061  long account_id = rs.getLong("account_id");
1062  String deviceID = rs.getString("device_id");
1063  final String type_name = rs.getString("type_name");
1064  final String account_unique_identifier = rs.getString("account_unique_identifier");
1065 
1066  Account.Type accountType = typeNameToAccountTypeMap.get(type_name);
1067  Account account = new Account(account_id, accountType, account_unique_identifier);
1068  accountDeviceInstances.add(new AccountDeviceInstance(account, deviceID));
1069  }
1070 
1071  return accountDeviceInstances;
1072  } catch (SQLException ex) {
1073  throw new TskCoreException("Error getting account device instances. " + ex.getMessage(), ex);
1074  } finally {
1075  db.releaseSingleUserCaseReadLock();
1076  }
1077  }
1078 
1095  public List<Content> getRelationshipSources(AccountDeviceInstance account1, AccountDeviceInstance account2, CommunicationsFilter filter) throws TskCoreException {
1096 
1097  //set up applicable filters
1098  Set<String> applicableFilters = new HashSet<>(Arrays.asList(
1099  CommunicationsFilter.DateRangeFilter.class.getName(),
1100  CommunicationsFilter.DeviceFilter.class.getName(),
1101  CommunicationsFilter.RelationshipTypeFilter.class.getName()
1102  ));
1103 
1104  String limitQuery = " account_relationships AS relationships";
1105  String limitStr = getMostRecentFilterLimitSQL(filter);
1106  if (!limitStr.isEmpty()) {
1107  limitQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
1108  }
1109 
1110  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
1111  final String queryString = "SELECT artifacts.artifact_id AS artifact_id,"
1112  + " artifacts.obj_id AS obj_id,"
1113  + " artifacts.artifact_obj_id AS artifact_obj_id,"
1114  + " artifacts.data_source_obj_id AS data_source_obj_id,"
1115  + " artifacts.artifact_type_id AS artifact_type_id,"
1116  + " artifacts.review_status_id AS review_status_id,"
1117  + " tsk_data_artifacts.os_account_obj_id AS os_account_obj_id"
1118  + " FROM blackboard_artifacts AS artifacts"
1119  + " JOIN " + limitQuery
1120  + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
1121  + " LEFT JOIN tsk_data_artifacts ON artifacts.artifact_obj_id = tsk_data_artifacts.artifact_obj_id"
1122  + " WHERE (( relationships.account1_id = " + account1.getAccount().getAccountID()
1123  + " AND relationships.account2_id = " + account2.getAccount().getAccountID()
1124  + " ) OR ( relationships.account2_id = " + account1.getAccount().getAccountID()
1125  + " AND relationships.account1_id =" + account2.getAccount().getAccountID() + " ))"
1126  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL);
1127 
1128  db.acquireSingleUserCaseReadLock();
1129  try (CaseDbConnection connection = db.getConnection();
1130  Statement s = connection.createStatement();
1131  ResultSet rs = connection.executeQuery(s, queryString);) {
1132 
1133  ArrayList<Content> artifacts = new ArrayList<>();
1134  artifacts.addAll(getDataArtifactsFromResult(rs));
1135  return artifacts;
1136  } catch (SQLException ex) {
1137  throw new TskCoreException("Error getting relationships between accounts. " + ex.getMessage(), ex);
1138  } finally {
1139  db.releaseSingleUserCaseReadLock();
1140  }
1141  }
1142 
1154  public List<AccountFileInstance> getAccountFileInstances(Account account) throws TskCoreException {
1155  List<AccountFileInstance> accountFileInstanceList = new ArrayList<>();
1156  @SuppressWarnings("deprecation")
1157  List<BlackboardArtifact> artifactList = getSleuthkitCase().getBlackboardArtifacts(BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT, BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID, account.getTypeSpecificID());
1158 
1159  if (artifactList != null && !artifactList.isEmpty()) {
1160  for (BlackboardArtifact artifact : artifactList) {
1161  accountFileInstanceList.add(new AccountFileInstance(artifact, account));
1162  }
1163  }
1164 
1165  if (!accountFileInstanceList.isEmpty()) {
1166  return accountFileInstanceList;
1167  } else {
1168  return null;
1169  }
1170  }
1171 
1180  public List<Account.Type> getAccountTypesInUse() throws TskCoreException {
1181 
1182  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";
1183  List<Account.Type> inUseAccounts = new ArrayList<>();
1184 
1185  db.acquireSingleUserCaseReadLock();
1186  try (CaseDbConnection connection = db.getConnection();
1187  Statement s = connection.createStatement();
1188  ResultSet rs = connection.executeQuery(s, query);) {
1189  Account.Type accountType;
1190  while (rs.next()) {
1191  String accountTypeName = rs.getString("type_name");
1192  accountType = this.typeNameToAccountTypeMap.get(accountTypeName);
1193 
1194  if (accountType == null) {
1195  accountType = new Account.Type(accountTypeName, rs.getString("display_name"));
1196  this.accountTypeToTypeIdMap.put(accountType, rs.getInt("account_type_id"));
1197  }
1198 
1199  inUseAccounts.add(accountType);
1200  }
1201  return inUseAccounts;
1202  } catch (SQLException ex) {
1203  throw new TskCoreException("Error getting account type id", ex);
1204  } finally {
1205  db.releaseSingleUserCaseReadLock();
1206  }
1207  }
1208 
1218  public List<Account> getAccountsRelatedToArtifact(BlackboardArtifact artifact) throws TskCoreException {
1219  if (artifact == null) {
1220  throw new IllegalArgumentException("null arugment passed to getAccountsRelatedToArtifact");
1221  }
1222 
1223  List<Account> accountList = new ArrayList<>();
1224  db.acquireSingleUserCaseReadLock();
1225  try (CaseDbConnection connection = db.getConnection()) {
1226  try {
1227  // In order to get a list of all the unique accounts in a relationship with the given aritfact
1228  // we must first union a list of the unique account1_id in the relationship with artifact
1229  // then the unique account2_id (inner select with union). The outter select assures the list
1230  // of the inner select only contains unique accounts.
1231  String query = String.format("SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1232  + " FROM ("
1233  + " SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1234  + " FROM accounts"
1235  + " JOIN account_relationships ON account1_id = account_id"
1236  + " WHERE relationship_source_obj_id = %d"
1237  + " UNION "
1238  + " SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1239  + " FROM accounts"
1240  + " JOIN account_relationships ON account2_id = account_id"
1241  + " WHERE relationship_source_obj_id = %d) AS unionOfRelationships", artifact.getId(), artifact.getId());
1242  try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
1243  while (rs.next()) {
1244  Account.Type accountType = null;
1245  int accountTypeId = rs.getInt("account_type_id");
1246  for (Map.Entry<Account.Type, Integer> entry : accountTypeToTypeIdMap.entrySet()) {
1247  if (entry.getValue() == accountTypeId) {
1248  accountType = entry.getKey();
1249  break;
1250  }
1251  }
1252 
1253  accountList.add(new Account(rs.getInt("account_id"), accountType, rs.getString("account_unique_identifier")));
1254  }
1255  } catch (SQLException ex) {
1256  throw new TskCoreException("Unable to get account list for give artifact " + artifact.getId(), ex);
1257  }
1258 
1259  } finally {
1260  db.releaseSingleUserCaseReadLock();
1261  }
1262  }
1263 
1264  return accountList;
1265  }
1266 
1274  int getAccountTypeId(Account.Type accountType) {
1275  if (accountTypeToTypeIdMap.containsKey(accountType)) {
1276  return accountTypeToTypeIdMap.get(accountType);
1277  }
1278 
1279  return 0;
1280  }
1281 
1293  private String normalizeAccountID(Account.Type accountType, String accountUniqueID) throws InvalidAccountIDException {
1294 
1295  if (accountUniqueID == null || accountUniqueID.isEmpty()) {
1296  throw new InvalidAccountIDException("Account id is null or empty.");
1297  }
1298 
1299  String normalizedAccountID;
1300  if (accountType.equals(Account.Type.PHONE)) {
1301  normalizedAccountID = CommunicationsUtils.normalizePhoneNum(accountUniqueID);
1302  } else if (accountType.equals(Account.Type.EMAIL)) {
1303  normalizedAccountID = CommunicationsUtils.normalizeEmailAddress(accountUniqueID);
1304  } else {
1305  normalizedAccountID = accountUniqueID.toLowerCase().trim();
1306  }
1307 
1308  return normalizedAccountID;
1309  }
1310 
1323  private String getCommunicationsFilterSQL(CommunicationsFilter commFilter, Set<String> applicableFilters) {
1324  if (null == commFilter || commFilter.getAndFilters().isEmpty()) {
1325  return "";
1326  }
1327 
1328  String sqlStr = "";
1329  StringBuilder sqlSB = new StringBuilder();
1330  boolean first = true;
1331  for (CommunicationsFilter.SubFilter subFilter : commFilter.getAndFilters()) {
1332 
1333  // If the filter is applicable
1334  if (applicableFilters.contains(subFilter.getClass().getName())) {
1335  String subfilterSQL = subFilter.getSQL(this);
1336  if (!subfilterSQL.isEmpty()) {
1337  if (first) {
1338  first = false;
1339  } else {
1340  sqlSB.append(" AND ");
1341  }
1342  sqlSB.append("( ");
1343  sqlSB.append(subfilterSQL);
1344  sqlSB.append(" )");
1345  }
1346  }
1347  }
1348 
1349  if (!sqlSB.toString().isEmpty()) {
1350  sqlStr = "( " + sqlSB.toString() + " )";
1351  }
1352  return sqlStr;
1353  }
1354 
1363  private String getMostRecentFilterLimitSQL(CommunicationsFilter filter) {
1364  String limitStr = "";
1365 
1366  if (filter != null && !filter.getAndFilters().isEmpty()) {
1367 
1368  for (CommunicationsFilter.SubFilter subFilter : filter.getAndFilters()) {
1369  if (subFilter.getClass().getName().equals(CommunicationsFilter.MostRecentFilter.class.getName())) {
1370  limitStr = subFilter.getSQL(this);
1371  break;
1372  }
1373  }
1374  }
1375 
1376  return limitStr;
1377  }
1378 
1390  private List<BlackboardArtifact> getDataArtifactsFromResult(ResultSet resultSet) throws SQLException, TskCoreException {
1391  List<BlackboardArtifact> artifacts = new ArrayList<>();
1392  while (resultSet.next()) {
1393  BlackboardArtifact.Type bbartType = db.getArtifactType(resultSet.getInt("artifact_type_id"));
1394  artifacts.add(new DataArtifact(db, resultSet.getLong("artifact_id"),
1395  resultSet.getLong("obj_id"), resultSet.getLong("artifact_obj_id"),
1396  resultSet.getObject("data_source_obj_id") != null ? resultSet.getLong("data_source_obj_id") : null,
1397  bbartType.getTypeID(),
1398  bbartType.getTypeName(), bbartType.getDisplayName(),
1399  BlackboardArtifact.ReviewStatus.withID(resultSet.getInt("review_status_id")),
1400  resultSet.getLong("os_account_obj_id"), false));
1401  }
1402 
1403  return artifacts;
1404  }
1405 }
org.sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID
TSK_ID
Definition: BlackboardAttribute.java:1420
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSources
Set< Content > getRelationshipSources(Set< AccountDeviceInstance > accountDeviceInstanceList, CommunicationsFilter filter)
Definition: CommunicationsManager.java:897
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:3493
org.sleuthkit.datamodel.CommunicationsManager.getRelatedAccountDeviceInstances
List< AccountDeviceInstance > getRelatedAccountDeviceInstances(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
Definition: CommunicationsManager.java:990
org.sleuthkit.datamodel.CommunicationsManager.getAccountType
org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName)
Definition: CommunicationsManager.java:581
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_MESSAGE
TSK_MESSAGE
Definition: BlackboardArtifact.java:1695
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:1687
org.sleuthkit.datamodel.CommunicationsManager.getAccountsRelatedToArtifact
List< Account > getAccountsRelatedToArtifact(BlackboardArtifact artifact)
Definition: CommunicationsManager.java:1218
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipCountsPairwise
Map< AccountPair, Long > getRelationshipCountsPairwise(Set< AccountDeviceInstance > accounts, CommunicationsFilter filter)
Definition: CommunicationsManager.java:728
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT
TSK_ACCOUNT
Definition: BlackboardArtifact.java:1776
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG
TSK_EMAIL_MSG
Definition: BlackboardArtifact.java:1624
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:4835
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSources
List< Content > getRelationshipSources(AccountDeviceInstance account1, AccountDeviceInstance account2, CommunicationsFilter filter)
Definition: CommunicationsManager.java:1095
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_CALLLOG
TSK_CALLLOG
Definition: BlackboardArtifact.java:1703
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSourcesCount
long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
Definition: CommunicationsManager.java:838
org.sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE
Definition: BlackboardAttribute.java:1021
org.sleuthkit.datamodel.CommunicationsManager.getAccountDeviceInstancesWithRelationships
List< AccountDeviceInstance > getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter)
Definition: CommunicationsManager.java:618
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE
Definition: BlackboardArtifact.java:1547
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:1154

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.