Sleuth Kit Java Bindings (JNI)  4.11.1
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 = CommManagerSqlStringUtils.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 
262  // NOTE: Full name given for Type for doxygen linking
263  public AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID,
264  String moduleName, Content sourceFile, List<BlackboardAttribute> attributes, Long ingestJobId) throws TskCoreException, InvalidAccountIDException {
265 
266  // make or get the Account (unique at the case-level)
267  Account account = getOrCreateAccount(accountType, normalizeAccountID(accountType, accountUniqueID));
268 
269  /*
270  * make or get the artifact. Will not create one if it already exists
271  * for the sourceFile. Such as an email PST that has the same email
272  * address multiple times. Only one artifact is created for each email
273  * message in that PST.
274  */
275  BlackboardArtifact accountArtifact = getOrCreateAccountFileInstanceArtifact(accountType, normalizeAccountID(accountType, accountUniqueID), moduleName, sourceFile, attributes, ingestJobId);
276 
277  // The account instance map was unused so we have removed it from the database,
278  // but we expect we may need it so I am preserving this method comment and usage here.
279  // add a row to Accounts to Instances mapping table
280  // @@@ BC: Seems like we should only do this if we had to create the artifact.
281  // But, it will probably fail to create a new one based on unique constraints.
282  // addAccountFileInstanceMapping(account.getAccountID(), accountArtifact.getArtifactID());
283  return new AccountFileInstance(accountArtifact, account);
284  }
285 
309  @Deprecated
310  // NOTE: Full name given for Type for doxygen linking
311  public AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile) throws TskCoreException, InvalidAccountIDException {
312  return createAccountFileInstance(accountType, accountUniqueID, moduleName, sourceFile, null, null);
313  }
314 
328  // NOTE: Full name given for Type for doxygen linking
329  public Account getAccount(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID) throws TskCoreException, InvalidAccountIDException {
330  Account account = null;
331  db.acquireSingleUserCaseReadLock();
332  try (CaseDbConnection connection = db.getConnection();
333  Statement s = connection.createStatement();
334  ResultSet rs = connection.executeQuery(s, "SELECT * FROM accounts WHERE account_type_id = " + getAccountTypeId(accountType)
335  + " AND account_unique_identifier = '" + normalizeAccountID(accountType, accountUniqueID) + "'");) { //NON-NLS
336 
337  if (rs.next()) {
338  account = new Account(rs.getInt("account_id"), accountType,
339  rs.getString("account_unique_identifier"));
340  }
341  } catch (SQLException ex) {
342  throw new TskCoreException("Error getting account type id", ex);
343  } finally {
344  db.releaseSingleUserCaseReadLock();
345  }
346 
347  return account;
348  }
349 
372  // NOTE: Full name given for Type for doxygen linking
373  public void addRelationships(AccountFileInstance sender, List<AccountFileInstance> recipients,
374  BlackboardArtifact sourceArtifact, org.sleuthkit.datamodel.Relationship.Type relationshipType, long dateTime) throws TskCoreException, TskDataException {
375 
376  if (sourceArtifact.getDataSourceObjectID() == null) {
377  throw new TskDataException("Source Artifact does not have a valid data source.");
378  }
379 
380  if (relationshipType.isCreatableFrom(sourceArtifact) == false) {
381  throw new TskDataException("Can not make a " + relationshipType.getDisplayName()
382  + " relationship from a" + sourceArtifact.getDisplayName());
383  }
384 
385  /*
386  * Enforce that all accounts and the relationship between them are from
387  * the same 'source'. This is required for the queries to work
388  * correctly.
389  */
390  // Currently we do not save the direction of communication
391  List<Long> accountIDs = new ArrayList<>();
392 
393  if (null != sender) {
394  accountIDs.add(sender.getAccount().getAccountID());
395  if (!sender.getDataSourceObjectID().equals(sourceArtifact.getDataSourceObjectID())) {
396  throw new TskDataException("Sender and relationship are from different data sources :"
397  + "Sender source ID" + sender.getDataSourceObjectID() + " != relationship source ID" + sourceArtifact.getDataSourceObjectID());
398  }
399  }
400 
401  for (AccountFileInstance recipient : recipients) {
402  accountIDs.add(recipient.getAccount().getAccountID());
403  if (!recipient.getDataSourceObjectID().equals(sourceArtifact.getDataSourceObjectID())) {
404  throw new TskDataException("Recipient and relationship are from different data sources :"
405  + "Recipient source ID" + recipient.getDataSourceObjectID() + " != relationship source ID" + sourceArtifact.getDataSourceObjectID());
406  }
407  }
408 
409  // Set up the query for the prepared statement
410  String query = "INTO account_relationships (account1_id, account2_id, relationship_source_obj_id, date_time, relationship_type, data_source_obj_id ) "
411  + "VALUES (?,?,?,?,?,?)";
412  switch (db.getDatabaseType()) {
413  case POSTGRESQL:
414  query = "INSERT " + query + " ON CONFLICT DO NOTHING";
415  break;
416  case SQLITE:
417  query = "INSERT OR IGNORE " + query;
418  break;
419  default:
420  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
421  }
422 
423  CaseDbTransaction trans = db.beginTransaction();
424  try {
425  SleuthkitCase.CaseDbConnection connection = trans.getConnection();
426  PreparedStatement preparedStatement = connection.getPreparedStatement(query, Statement.NO_GENERATED_KEYS);
427 
428  for (int i = 0; i < accountIDs.size(); i++) {
429  for (int j = i + 1; j < accountIDs.size(); j++) {
430  long account1_id = accountIDs.get(i);
431  long account2_id = accountIDs.get(j);
432 
433  preparedStatement.clearParameters();
434  preparedStatement.setLong(1, account1_id);
435  preparedStatement.setLong(2, account2_id);
436  preparedStatement.setLong(3, sourceArtifact.getId());
437  if (dateTime > 0) {
438  preparedStatement.setLong(4, dateTime);
439  } else {
440  preparedStatement.setNull(4, java.sql.Types.BIGINT);
441  }
442  preparedStatement.setInt(5, relationshipType.getTypeID());
443  preparedStatement.setLong(6, sourceArtifact.getDataSourceObjectID());
444 
445  connection.executeUpdate(preparedStatement);
446  }
447  }
448  trans.commit();
449  } catch (SQLException ex) {
450  trans.rollback();
451  throw new TskCoreException("Error adding accounts relationship", ex);
452  }
453  }
454 
469  private Account getOrCreateAccount(Account.Type accountType, String accountUniqueID) throws TskCoreException, InvalidAccountIDException {
470  Account account = getAccount(accountType, accountUniqueID);
471  if (null == account) {
472  String query = " INTO accounts (account_type_id, account_unique_identifier) "
473  + "VALUES ( " + getAccountTypeId(accountType) + ", '"
474  + normalizeAccountID(accountType, accountUniqueID) + "'" + ")";
475  switch (db.getDatabaseType()) {
476  case POSTGRESQL:
477  query = "INSERT " + query + " ON CONFLICT DO NOTHING"; //NON-NLS
478  break;
479  case SQLITE:
480  query = "INSERT OR IGNORE " + query;
481  break;
482  default:
483  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
484  }
485 
486  CaseDbTransaction trans = db.beginTransaction();
487  Statement s = null;
488  ResultSet rs = null;
489  try {
490  s = trans.getConnection().createStatement();
491 
492  s.execute(query);
493 
494  trans.commit();
495  account = getAccount(accountType, accountUniqueID);
496  } catch (SQLException ex) {
497  trans.rollback();
498  throw new TskCoreException("Error adding an account", ex);
499  } finally {
500  closeResultSet(rs);
501  closeStatement(s);
502  }
503  }
504 
505  return account;
506  }
507 
528  private BlackboardArtifact getOrCreateAccountFileInstanceArtifact(Account.Type accountType, String accountUniqueID, String moduleName,
529  Content sourceFile, List<BlackboardAttribute> originalAttrs, Long ingestJobId) throws TskCoreException {
530  if (sourceFile == null) {
531  throw new TskCoreException("Source file not provided.");
532  }
533 
534  BlackboardArtifact accountArtifact = getAccountFileInstanceArtifact(accountType, accountUniqueID, sourceFile);
535  if (accountArtifact == null) {
536  List<BlackboardAttribute> attributes = new ArrayList<>();
537  attributes.add(new BlackboardAttribute(BlackboardAttribute.Type.TSK_ACCOUNT_TYPE, moduleName, accountType.getTypeName()));
538  attributes.add(new BlackboardAttribute(BlackboardAttribute.Type.TSK_ID, moduleName, accountUniqueID));
539  if (originalAttrs != null) {
540  attributes.addAll(originalAttrs);
541  }
542 
543  accountArtifact = sourceFile.newDataArtifact(ACCOUNT_TYPE, attributes);
544 
545  try {
546  db.getBlackboard().postArtifact(accountArtifact, moduleName, ingestJobId);
547  } catch (BlackboardException ex) {
548  LOGGER.log(Level.SEVERE, String.format("Error posting new account artifact to the blackboard (object ID = %d)", accountArtifact.getId()), ex);
549  }
550  }
551  return accountArtifact;
552  }
553 
567  private BlackboardArtifact getAccountFileInstanceArtifact(Account.Type accountType, String accountUniqueID, Content sourceFile) throws TskCoreException {
568  BlackboardArtifact accountArtifact = null;
569 
570  String queryStr = "SELECT artifacts.artifact_id AS artifact_id,"
571  + " artifacts.obj_id AS obj_id,"
572  + " artifacts.artifact_obj_id AS artifact_obj_id,"
573  + " artifacts.data_source_obj_id AS data_source_obj_id,"
574  + " artifacts.artifact_type_id AS artifact_type_id,"
575  + " artifacts.review_status_id AS review_status_id,"
576  + " tsk_data_artifacts.os_account_obj_id AS os_account_obj_id"
577  + " FROM blackboard_artifacts AS artifacts"
578  + " JOIN blackboard_attributes AS attr_account_type"
579  + " ON artifacts.artifact_id = attr_account_type.artifact_id"
580  + " JOIN blackboard_attributes AS attr_account_id"
581  + " ON artifacts.artifact_id = attr_account_id.artifact_id"
582  + " AND attr_account_id.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID.getTypeID()
583  + " AND attr_account_id.value_text = '" + accountUniqueID + "'"
584  + " LEFT JOIN tsk_data_artifacts ON tsk_data_artifacts.artifact_obj_id = artifacts.artifact_obj_id"
585  + " WHERE artifacts.artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT.getTypeID()
586  + " AND attr_account_type.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE.getTypeID()
587  + " AND attr_account_type.value_text = '" + accountType.getTypeName() + "'"
588  + " AND artifacts.obj_id = " + sourceFile.getId(); //NON-NLS
589 
590  db.acquireSingleUserCaseReadLock();
591  try (CaseDbConnection connection = db.getConnection();
592  Statement s = connection.createStatement();
593  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
594  if (rs.next()) {
595  BlackboardArtifact.Type bbartType = db.getBlackboard().getArtifactType(rs.getInt("artifact_type_id"));
596 
597  accountArtifact = new DataArtifact(db, rs.getLong("artifact_id"), rs.getLong("obj_id"), rs.getLong("artifact_obj_id"),
598  rs.getObject("data_source_obj_id") != null ? rs.getLong("data_source_obj_id") : null,
599  bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
600  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id")), rs.getLong("os_account_obj_id"), false);
601  }
602  } catch (SQLException ex) {
603  throw new TskCoreException("Error getting account", ex);
604  } finally {
605  db.releaseSingleUserCaseReadLock();
606  }
607 
608  return accountArtifact;
609  }
610 
620  // NOTE: Full name given for Type for doxygen linking
621  public org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName) throws TskCoreException {
622  if (this.typeNameToAccountTypeMap.containsKey(accountTypeName)) {
623  return this.typeNameToAccountTypeMap.get(accountTypeName);
624  }
625 
626  db.acquireSingleUserCaseReadLock();
627  try (CaseDbConnection connection = db.getConnection();
628  Statement s = connection.createStatement();
629  ResultSet rs = connection.executeQuery(s, "SELECT account_type_id, type_name, display_name FROM account_types WHERE type_name = '" + accountTypeName + "'");) { //NON-NLS
630  Account.Type accountType = null;
631  if (rs.next()) {
632  accountType = new Account.Type(accountTypeName, rs.getString("display_name"));
633  this.accountTypeToTypeIdMap.put(accountType, rs.getInt("account_type_id"));
634  this.typeNameToAccountTypeMap.put(accountTypeName, accountType);
635  }
636  return accountType;
637  } catch (SQLException ex) {
638  throw new TskCoreException("Error getting account type id", ex);
639  } finally {
640  db.releaseSingleUserCaseReadLock();
641  }
642  }
643 
658  public List<AccountDeviceInstance> getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter) throws TskCoreException {
659 
660  //set up applicable filters
661  Set<String> applicableInnerQueryFilters = new HashSet<String>(Arrays.asList(
662  CommunicationsFilter.DateRangeFilter.class.getName(),
663  CommunicationsFilter.DeviceFilter.class.getName(),
664  CommunicationsFilter.RelationshipTypeFilter.class.getName()
665  ));
666  String relationshipFilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
667 
668  String relationshipLimitSQL = getMostRecentFilterLimitSQL(filter);
669 
670  String relTblfilterQuery
671  = "SELECT * "
672  + "FROM account_relationships as relationships"
673  + (relationshipFilterSQL.isEmpty() ? "" : " WHERE " + relationshipFilterSQL)
674  + (relationshipLimitSQL.isEmpty() ? "" : relationshipLimitSQL);
675 
676  String uniqueAccountQueryTemplate
677  = " SELECT %1$1s as account_id,"
678  + " data_source_obj_id"
679  + " FROM ( " + relTblfilterQuery + ")AS %2$s";
680 
681  String relationshipTableFilterQuery1 = String.format(uniqueAccountQueryTemplate, "account1_id", "union_query_1");
682  String relationshipTableFilterQuery2 = String.format(uniqueAccountQueryTemplate, "account2_id", "union_query_2");
683 
684  //this query groups by account_id and data_source_obj_id across both innerQueries
685  String uniqueAccountQuery
686  = "SELECT DISTINCT account_id, data_source_obj_id"
687  + " FROM ( " + relationshipTableFilterQuery1 + " UNION " + relationshipTableFilterQuery2 + " ) AS inner_union"
688  + " GROUP BY account_id, data_source_obj_id";
689 
690  // set up applicable filters
691  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
692  CommunicationsFilter.AccountTypeFilter.class.getName()
693  ));
694 
695  String accountTypeFilterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
696 
697  String queryStr
698  = //account info
699  " accounts.account_id AS account_id,"
700  + " accounts.account_unique_identifier AS account_unique_identifier,"
701  //account type info
702  + " account_types.type_name AS type_name,"
703  //Account device instance info
704  + " data_source_info.device_id AS device_id"
705  + " FROM ( " + uniqueAccountQuery + " ) AS account_device_instances"
706  + " JOIN accounts AS accounts"
707  + " ON accounts.account_id = account_device_instances.account_id"
708  + " JOIN account_types AS account_types"
709  + " ON accounts.account_type_id = account_types.account_type_id"
710  + " JOIN data_source_info AS data_source_info"
711  + " ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
712  + (accountTypeFilterSQL.isEmpty() ? "" : " WHERE " + accountTypeFilterSQL);
713 
714  switch (db.getDatabaseType()) {
715  case POSTGRESQL:
716  queryStr = "SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
717  break;
718  case SQLITE:
719  queryStr = "SELECT " + queryStr + " GROUP BY accounts.account_id, data_source_info.device_id";
720  break;
721  default:
722  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
723  }
724 
725  db.acquireSingleUserCaseReadLock();
726  try (CaseDbConnection connection = db.getConnection();
727  Statement s = connection.createStatement();
728  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
729  ArrayList<AccountDeviceInstance> accountDeviceInstances = new ArrayList<AccountDeviceInstance>();
730  while (rs.next()) {
731  long account_id = rs.getLong("account_id");
732  String deviceID = rs.getString("device_id");
733  final String type_name = rs.getString("type_name");
734  final String account_unique_identifier = rs.getString("account_unique_identifier");
735 
736  Account.Type accountType = typeNameToAccountTypeMap.get(type_name);
737  Account account = new Account(account_id, accountType, account_unique_identifier);
738  accountDeviceInstances.add(new AccountDeviceInstance(account, deviceID));
739  }
740 
741  return accountDeviceInstances;
742  } catch (SQLException ex) {
743  throw new TskCoreException("Error getting account device instances. " + ex.getMessage(), ex);
744  } finally {
745  db.releaseSingleUserCaseReadLock();
746  }
747  }
748 
768  public Map<AccountPair, Long> getRelationshipCountsPairwise(Set<AccountDeviceInstance> accounts, CommunicationsFilter filter) throws TskCoreException {
769 
770  Set<Long> accountIDs = new HashSet<Long>();
771  Set<String> accountDeviceIDs = new HashSet<String>();
772  for (AccountDeviceInstance adi : accounts) {
773  accountIDs.add(adi.getAccount().getAccountID());
774  accountDeviceIDs.add("'" + adi.getDeviceId() + "'");
775  }
776  //set up applicable filters
777  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
778  CommunicationsFilter.DateRangeFilter.class.getName(),
779  CommunicationsFilter.DeviceFilter.class.getName(),
780  CommunicationsFilter.RelationshipTypeFilter.class.getName()
781  ));
782 
783  String accountIDsCSL = CommManagerSqlStringUtils.buildCSVString(accountIDs);
784  String accountDeviceIDsCSL = CommManagerSqlStringUtils.buildCSVString(accountDeviceIDs);
785  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
786 
787  final String queryString
788  = " SELECT count(DISTINCT relationships.relationship_source_obj_id) AS count," //realtionship count
789  + " data_source_info.device_id AS device_id,"
790  //account 1 info
791  + " accounts1.account_id AS account1_id,"
792  + " accounts1.account_unique_identifier AS account1_unique_identifier,"
793  + " account_types1.type_name AS type_name1,"
794  + " account_types1.display_name AS display_name1,"
795  //account 2 info
796  + " accounts2.account_id AS account2_id,"
797  + " accounts2.account_unique_identifier AS account2_unique_identifier,"
798  + " account_types2.type_name AS type_name2,"
799  + " account_types2.display_name AS display_name2"
800  + " FROM account_relationships AS relationships"
801  + " JOIN data_source_info AS data_source_info"
802  + " ON relationships.data_source_obj_id = data_source_info.obj_id "
803  //account1 aliases
804  + " JOIN accounts AS accounts1 "
805  + " ON accounts1.account_id = relationships.account1_id"
806  + " JOIN account_types AS account_types1"
807  + " ON accounts1.account_type_id = account_types1.account_type_id"
808  //account2 aliases
809  + " JOIN accounts AS accounts2 "
810  + " ON accounts2.account_id = relationships.account2_id"
811  + " JOIN account_types AS account_types2"
812  + " ON accounts2.account_type_id = account_types2.account_type_id"
813  + " WHERE (( relationships.account1_id IN (" + accountIDsCSL + ")) "
814  + " AND ( relationships.account2_id IN ( " + accountIDsCSL + " ))"
815  + " AND ( data_source_info.device_id IN (" + accountDeviceIDsCSL + "))) "
816  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL)
817  + " GROUP BY data_source_info.device_id, "
818  + " accounts1.account_id, "
819  + " account_types1.type_name, "
820  + " account_types1.display_name, "
821  + " accounts2.account_id, "
822  + " account_types2.type_name, "
823  + " account_types2.display_name";
824 
825  Map<AccountPair, Long> results = new HashMap<AccountPair, Long>();
826 
827  db.acquireSingleUserCaseReadLock();
828  try (CaseDbConnection connection = db.getConnection();
829  Statement s = connection.createStatement();
830  ResultSet rs = connection.executeQuery(s, queryString);) { //NON-NLS
831 
832  while (rs.next()) {
833  //make account 1
834  Account.Type type1 = new Account.Type(rs.getString("type_name1"), rs.getString("display_name1"));
835  AccountDeviceInstance adi1 = new AccountDeviceInstance(new Account(rs.getLong("account1_id"), type1,
836  rs.getString("account1_unique_identifier")),
837  rs.getString("device_id"));
838 
839  //make account 2
840  Account.Type type2 = new Account.Type(rs.getString("type_name2"), rs.getString("display_name2"));
841  AccountDeviceInstance adi2 = new AccountDeviceInstance(new Account(rs.getLong("account2_id"), type2,
842  rs.getString("account2_unique_identifier")),
843  rs.getString("device_id"));
844 
845  AccountPair relationshipKey = new AccountPair(adi1, adi2);
846  long count = rs.getLong("count");
847 
848  //merge counts for relationships that have the accounts flipped.
849  Long oldCount = results.get(relationshipKey);
850  if (oldCount != null) {
851  count += oldCount;
852  }
853  results.put(relationshipKey, count);
854  }
855  return results;
856  } catch (SQLException ex) {
857  throw new TskCoreException("Error getting relationships between accounts. " + ex.getMessage(), ex);
858  } finally {
859  db.releaseSingleUserCaseReadLock();
860  }
861  }
862 
878  public long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter) throws TskCoreException {
879 
880  long account_id = accountDeviceInstance.getAccount().getAccountID();
881 
882  // Get the list of Data source objects IDs correpsonding to this DeviceID.
883  String datasourceObjIdsCSV = CommManagerSqlStringUtils.buildCSVString(
884  db.getDataSourceObjIds(accountDeviceInstance.getDeviceId()));
885 
886  // set up applicable filters
887  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
888  CommunicationsFilter.RelationshipTypeFilter.class.getName(),
889  CommunicationsFilter.DateRangeFilter.class.getName()
890  ));
891  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
892 
893  String innerQuery = " account_relationships AS relationships";
894  String limitStr = getMostRecentFilterLimitSQL(filter);
895 
896  if (!limitStr.isEmpty()) {
897  innerQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
898  }
899 
900  String queryStr
901  = "SELECT count(DISTINCT relationships.relationship_source_obj_id) as count "
902  + " FROM" + innerQuery
903  + " WHERE relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV + " )"
904  + " AND ( relationships.account1_id = " + account_id
905  + " OR relationships.account2_id = " + account_id + " )"
906  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL);
907 
908  db.acquireSingleUserCaseReadLock();
909  try (CaseDbConnection connection = db.getConnection();
910  Statement s = connection.createStatement();
911  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
912  rs.next();
913  return (rs.getLong("count"));
914  } catch (SQLException ex) {
915  throw new TskCoreException("Error getting relationships count for account device instance. " + ex.getMessage(), ex);
916  } finally {
917  db.releaseSingleUserCaseReadLock();
918  }
919  }
920 
937  public Set<Content> getRelationshipSources(Set<AccountDeviceInstance> accountDeviceInstanceList, CommunicationsFilter filter) throws TskCoreException {
938 
939  if (accountDeviceInstanceList.isEmpty()) {
940  LOGGER.log(Level.WARNING, "Empty accountDeviceInstanceList passed to getRelationshipSources");
941  return Collections.emptySet();
942  }
943 
944  // Build a map of account ids to device ids. For each account id there
945  // will be a set of device ids.
946  Map<Long, Set<Long>> accountIdToDatasourceObjIdMap = new HashMap<>();
947  for (AccountDeviceInstance accountDeviceInstance : accountDeviceInstanceList) {
948  long accountID = accountDeviceInstance.getAccount().getAccountID();
949  List<Long> dataSourceObjIds = db.getDataSourceObjIds(accountDeviceInstance.getDeviceId());
950 
951  if (accountIdToDatasourceObjIdMap.containsKey(accountID)) {
952  accountIdToDatasourceObjIdMap.get(accountID).addAll(dataSourceObjIds);
953  } else {
954  accountIdToDatasourceObjIdMap.put(accountID, new HashSet<>(dataSourceObjIds));
955  }
956  }
957 
958  // Create the OR cause that limits the accounts for a given data source.
959  List<String> adiSQLClauses = new ArrayList<>();
960  for (Map.Entry<Long, Set<Long>> entry : accountIdToDatasourceObjIdMap.entrySet()) {
961  final Long accountID = entry.getKey();
962  String datasourceObjIdsCSV = CommManagerSqlStringUtils.buildCSVString(entry.getValue());
963 
964  adiSQLClauses.add(
965  "( "
966  + (!datasourceObjIdsCSV.isEmpty() ? "( relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV + " ) ) AND" : "")
967  + " ( relationships.account1_id = " + accountID
968  + " OR relationships.account2_id = " + accountID + " ) )"
969  );
970  }
971  String adiSQLClause = CommManagerSqlStringUtils.joinAsStrings(adiSQLClauses, " OR ");
972 
973  if(adiSQLClause.isEmpty()) {
974  LOGGER.log(Level.SEVERE, "There set of AccountDeviceInstances had no valid data source ids.");
975  return Collections.emptySet();
976  }
977 
978  // Build the filter part of the query.
979  Set<String> applicableFilters = new HashSet<>(Arrays.asList(
980  CommunicationsFilter.RelationshipTypeFilter.class
981  .getName(),
982  CommunicationsFilter.DateRangeFilter.class
983  .getName()
984  ));
985  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
986 
987  // Basic join.
988  String limitQuery = " account_relationships AS relationships";
989 
990  // If the user set filters expand this to be a subquery that selects
991  // accounts based on the filter.
992  String limitStr = getMostRecentFilterLimitSQL(filter);
993  if (!limitStr.isEmpty()) {
994  limitQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
995  }
996 
997  String queryStr
998  = "SELECT DISTINCT artifacts.artifact_id AS artifact_id,"
999  + " artifacts.obj_id AS obj_id,"
1000  + " artifacts.artifact_obj_id AS artifact_obj_id,"
1001  + " artifacts.data_source_obj_id AS data_source_obj_id, "
1002  + " artifacts.artifact_type_id AS artifact_type_id, "
1003  + " artifacts.review_status_id AS review_status_id,"
1004  + " tsk_data_artifacts.os_account_obj_id as os_account_obj_id"
1005  + " FROM blackboard_artifacts as artifacts"
1006  + " JOIN " + limitQuery
1007  + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
1008  + " LEFT JOIN tsk_data_artifacts ON artifacts.artifact_obj_id = tsk_data_artifacts.artifact_obj_id"
1009  // append sql to restrict search to specified account device instances
1010  + " WHERE (" + adiSQLClause + " )"
1011  // plus other filters
1012  + (filterSQL.isEmpty() ? "" : " AND (" + filterSQL + " )");
1013 
1014  db.acquireSingleUserCaseReadLock();
1015  try (CaseDbConnection connection = db.getConnection();
1016  Statement s = connection.createStatement();
1017  ResultSet rs = connection.executeQuery(s, queryStr);) { //NON-NLS
1018  Set<Content> relationshipSources = new HashSet<>();
1019  relationshipSources.addAll(getDataArtifactsFromResult(rs));
1020  return relationshipSources;
1021  } catch (SQLException ex) {
1022  throw new TskCoreException("Error getting relationships for account. " + ex.getMessage(), ex);
1023  } finally {
1024  db.releaseSingleUserCaseReadLock();
1025  }
1026  }
1027 
1043  public List<AccountDeviceInstance> getRelatedAccountDeviceInstances(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter) throws TskCoreException {
1044  final List<Long> dataSourceObjIds
1045  = getSleuthkitCase().getDataSourceObjIds(accountDeviceInstance.getDeviceId());
1046 
1047  //set up applicable filters
1048  Set<String> applicableInnerQueryFilters = new HashSet<String>(Arrays.asList(
1049  CommunicationsFilter.DateRangeFilter.class.getName(),
1050  CommunicationsFilter.DeviceFilter.class.getName(),
1051  CommunicationsFilter.RelationshipTypeFilter.class.getName()
1052  ));
1053 
1054  String innerQueryfilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
1055 
1056  String innerQueryTemplate
1057  = " SELECT %1$1s as account_id,"
1058  + " data_source_obj_id"
1059  + " FROM account_relationships as relationships"
1060  + " WHERE %2$1s = " + accountDeviceInstance.getAccount().getAccountID() + ""
1061  + " AND data_source_obj_id IN (" + CommManagerSqlStringUtils.buildCSVString(dataSourceObjIds) + ")"
1062  + (innerQueryfilterSQL.isEmpty() ? "" : " AND " + innerQueryfilterSQL);
1063 
1064  String innerQuery1 = String.format(innerQueryTemplate, "account1_id", "account2_id");
1065  String innerQuery2 = String.format(innerQueryTemplate, "account2_id", "account1_id");
1066 
1067  //this query groups by account_id and data_source_obj_id across both innerQueries
1068  String combinedInnerQuery
1069  = "SELECT account_id, data_source_obj_id "
1070  + " FROM ( " + innerQuery1 + " UNION " + innerQuery2 + " ) AS inner_union"
1071  + " GROUP BY account_id, data_source_obj_id";
1072 
1073  // set up applicable filters
1074  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
1075  CommunicationsFilter.AccountTypeFilter.class.getName()
1076  ));
1077 
1078  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
1079 
1080  String queryStr
1081  = //account info
1082  " accounts.account_id AS account_id,"
1083  + " accounts.account_unique_identifier AS account_unique_identifier,"
1084  //account type info
1085  + " account_types.type_name AS type_name,"
1086  //Account device instance info
1087  + " data_source_info.device_id AS device_id"
1088  + " FROM ( " + combinedInnerQuery + " ) AS account_device_instances"
1089  + " JOIN accounts AS accounts"
1090  + " ON accounts.account_id = account_device_instances.account_id"
1091  + " JOIN account_types AS account_types"
1092  + " ON accounts.account_type_id = account_types.account_type_id"
1093  + " JOIN data_source_info AS data_source_info"
1094  + " ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
1095  + (filterSQL.isEmpty() ? "" : " WHERE " + filterSQL);
1096 
1097  switch (db.getDatabaseType()) {
1098  case POSTGRESQL:
1099  queryStr = "SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
1100  break;
1101  case SQLITE:
1102  queryStr = "SELECT " + queryStr + " GROUP BY accounts.account_id, data_source_info.device_id";
1103  break;
1104  default:
1105  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
1106  }
1107 
1108  db.acquireSingleUserCaseReadLock();
1109  try (CaseDbConnection connection = db.getConnection();
1110  Statement s = connection.createStatement();
1111  ResultSet rs = connection.executeQuery(s, queryStr);) {
1112  ArrayList<AccountDeviceInstance> accountDeviceInstances = new ArrayList<AccountDeviceInstance>();
1113  while (rs.next()) {
1114  long account_id = rs.getLong("account_id");
1115  String deviceID = rs.getString("device_id");
1116  final String type_name = rs.getString("type_name");
1117  final String account_unique_identifier = rs.getString("account_unique_identifier");
1118 
1119  Account.Type accountType = typeNameToAccountTypeMap.get(type_name);
1120  Account account = new Account(account_id, accountType, account_unique_identifier);
1121  accountDeviceInstances.add(new AccountDeviceInstance(account, deviceID));
1122  }
1123 
1124  return accountDeviceInstances;
1125  } catch (SQLException ex) {
1126  throw new TskCoreException("Error getting account device instances. " + ex.getMessage(), ex);
1127  } finally {
1128  db.releaseSingleUserCaseReadLock();
1129  }
1130  }
1131 
1148  public List<Content> getRelationshipSources(AccountDeviceInstance account1, AccountDeviceInstance account2, CommunicationsFilter filter) throws TskCoreException {
1149 
1150  //set up applicable filters
1151  Set<String> applicableFilters = new HashSet<>(Arrays.asList(
1152  CommunicationsFilter.DateRangeFilter.class.getName(),
1153  CommunicationsFilter.DeviceFilter.class.getName(),
1154  CommunicationsFilter.RelationshipTypeFilter.class.getName()
1155  ));
1156 
1157  String limitQuery = " account_relationships AS relationships";
1158  String limitStr = getMostRecentFilterLimitSQL(filter);
1159  if (!limitStr.isEmpty()) {
1160  limitQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
1161  }
1162 
1163  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
1164  final String queryString = "SELECT artifacts.artifact_id AS artifact_id,"
1165  + " artifacts.obj_id AS obj_id,"
1166  + " artifacts.artifact_obj_id AS artifact_obj_id,"
1167  + " artifacts.data_source_obj_id AS data_source_obj_id,"
1168  + " artifacts.artifact_type_id AS artifact_type_id,"
1169  + " artifacts.review_status_id AS review_status_id,"
1170  + " tsk_data_artifacts.os_account_obj_id AS os_account_obj_id"
1171  + " FROM blackboard_artifacts AS artifacts"
1172  + " JOIN " + limitQuery
1173  + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
1174  + " LEFT JOIN tsk_data_artifacts ON artifacts.artifact_obj_id = tsk_data_artifacts.artifact_obj_id"
1175  + " WHERE (( relationships.account1_id = " + account1.getAccount().getAccountID()
1176  + " AND relationships.account2_id = " + account2.getAccount().getAccountID()
1177  + " ) OR ( relationships.account2_id = " + account1.getAccount().getAccountID()
1178  + " AND relationships.account1_id =" + account2.getAccount().getAccountID() + " ))"
1179  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL);
1180 
1181  db.acquireSingleUserCaseReadLock();
1182  try (CaseDbConnection connection = db.getConnection();
1183  Statement s = connection.createStatement();
1184  ResultSet rs = connection.executeQuery(s, queryString);) {
1185 
1186  ArrayList<Content> artifacts = new ArrayList<>();
1187  artifacts.addAll(getDataArtifactsFromResult(rs));
1188  return artifacts;
1189  } catch (SQLException ex) {
1190  throw new TskCoreException("Error getting relationships between accounts. " + ex.getMessage(), ex);
1191  } finally {
1192  db.releaseSingleUserCaseReadLock();
1193  }
1194  }
1195 
1206  public List<AccountFileInstance> getAccountFileInstances(Account account) throws TskCoreException {
1207  List<AccountFileInstance> accountFileInstanceList = new ArrayList<>();
1208  @SuppressWarnings("deprecation")
1209  List<BlackboardArtifact> artifactList = getSleuthkitCase().getBlackboardArtifacts(BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT, BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID, account.getTypeSpecificID());
1210 
1211  if (artifactList != null && !artifactList.isEmpty()) {
1212  for (BlackboardArtifact artifact : artifactList) {
1213  accountFileInstanceList.add(new AccountFileInstance(artifact, account));
1214  }
1215  }
1216 
1217  if (!accountFileInstanceList.isEmpty()) {
1218  return accountFileInstanceList;
1219  } else {
1220  return null;
1221  }
1222  }
1223 
1232  public List<Account.Type> getAccountTypesInUse() throws TskCoreException {
1233 
1234  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";
1235  List<Account.Type> inUseAccounts = new ArrayList<>();
1236 
1237  db.acquireSingleUserCaseReadLock();
1238  try (CaseDbConnection connection = db.getConnection();
1239  Statement s = connection.createStatement();
1240  ResultSet rs = connection.executeQuery(s, query);) {
1241  Account.Type accountType;
1242  while (rs.next()) {
1243  String accountTypeName = rs.getString("type_name");
1244  accountType = this.typeNameToAccountTypeMap.get(accountTypeName);
1245 
1246  if (accountType == null) {
1247  accountType = new Account.Type(accountTypeName, rs.getString("display_name"));
1248  this.accountTypeToTypeIdMap.put(accountType, rs.getInt("account_type_id"));
1249  }
1250 
1251  inUseAccounts.add(accountType);
1252  }
1253  return inUseAccounts;
1254  } catch (SQLException ex) {
1255  throw new TskCoreException("Error getting account type id", ex);
1256  } finally {
1257  db.releaseSingleUserCaseReadLock();
1258  }
1259  }
1260 
1270  public List<Account> getAccountsRelatedToArtifact(BlackboardArtifact artifact) throws TskCoreException {
1271  if (artifact == null) {
1272  throw new IllegalArgumentException("null arugment passed to getAccountsRelatedToArtifact");
1273  }
1274 
1275  List<Account> accountList = new ArrayList<>();
1276  db.acquireSingleUserCaseReadLock();
1277  try (CaseDbConnection connection = db.getConnection()) {
1278  try {
1279  // In order to get a list of all the unique accounts in a relationship with the given aritfact
1280  // we must first union a list of the unique account1_id in the relationship with artifact
1281  // then the unique account2_id (inner select with union). The outter select assures the list
1282  // of the inner select only contains unique accounts.
1283  String query = String.format("SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1284  + " FROM ("
1285  + " SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1286  + " FROM accounts"
1287  + " JOIN account_relationships ON account1_id = account_id"
1288  + " WHERE relationship_source_obj_id = %d"
1289  + " UNION "
1290  + " SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1291  + " FROM accounts"
1292  + " JOIN account_relationships ON account2_id = account_id"
1293  + " WHERE relationship_source_obj_id = %d) AS unionOfRelationships", artifact.getId(), artifact.getId());
1294  try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
1295  while (rs.next()) {
1296  Account.Type accountType = null;
1297  int accountTypeId = rs.getInt("account_type_id");
1298  for (Map.Entry<Account.Type, Integer> entry : accountTypeToTypeIdMap.entrySet()) {
1299  if (entry.getValue() == accountTypeId) {
1300  accountType = entry.getKey();
1301  break;
1302  }
1303  }
1304 
1305  accountList.add(new Account(rs.getInt("account_id"), accountType, rs.getString("account_unique_identifier")));
1306  }
1307  } catch (SQLException ex) {
1308  throw new TskCoreException("Unable to get account list for give artifact " + artifact.getId(), ex);
1309  }
1310 
1311  } finally {
1312  db.releaseSingleUserCaseReadLock();
1313  }
1314  }
1315 
1316  return accountList;
1317  }
1318 
1326  int getAccountTypeId(Account.Type accountType) {
1327  if (accountTypeToTypeIdMap.containsKey(accountType)) {
1328  return accountTypeToTypeIdMap.get(accountType);
1329  }
1330 
1331  return 0;
1332  }
1333 
1345  private String normalizeAccountID(Account.Type accountType, String accountUniqueID) throws InvalidAccountIDException {
1346 
1347  if (accountUniqueID == null || accountUniqueID.isEmpty()) {
1348  throw new InvalidAccountIDException("Account id is null or empty.");
1349  }
1350 
1351  String normalizedAccountID;
1352  if (accountType.equals(Account.Type.PHONE)) {
1353  normalizedAccountID = CommunicationsUtils.normalizePhoneNum(accountUniqueID);
1354  } else if (accountType.equals(Account.Type.EMAIL)) {
1355  normalizedAccountID = CommunicationsUtils.normalizeEmailAddress(accountUniqueID);
1356  } else {
1357  normalizedAccountID = accountUniqueID.toLowerCase().trim();
1358  }
1359 
1360  return normalizedAccountID;
1361  }
1362 
1375  private String getCommunicationsFilterSQL(CommunicationsFilter commFilter, Set<String> applicableFilters) {
1376  if (null == commFilter || commFilter.getAndFilters().isEmpty()) {
1377  return "";
1378  }
1379 
1380  String sqlStr = "";
1381  StringBuilder sqlSB = new StringBuilder();
1382  boolean first = true;
1383  for (CommunicationsFilter.SubFilter subFilter : commFilter.getAndFilters()) {
1384 
1385  // If the filter is applicable
1386  if (applicableFilters.contains(subFilter.getClass().getName())) {
1387  String subfilterSQL = subFilter.getSQL(this);
1388  if (!subfilterSQL.isEmpty()) {
1389  if (first) {
1390  first = false;
1391  } else {
1392  sqlSB.append(" AND ");
1393  }
1394  sqlSB.append("( ");
1395  sqlSB.append(subfilterSQL);
1396  sqlSB.append(" )");
1397  }
1398  }
1399  }
1400 
1401  if (!sqlSB.toString().isEmpty()) {
1402  sqlStr = "( " + sqlSB.toString() + " )";
1403  }
1404  return sqlStr;
1405  }
1406 
1415  private String getMostRecentFilterLimitSQL(CommunicationsFilter filter) {
1416  String limitStr = "";
1417 
1418  if (filter != null && !filter.getAndFilters().isEmpty()) {
1419 
1420  for (CommunicationsFilter.SubFilter subFilter : filter.getAndFilters()) {
1421  if (subFilter.getClass().getName().equals(CommunicationsFilter.MostRecentFilter.class.getName())) {
1422  limitStr = subFilter.getSQL(this);
1423  break;
1424  }
1425  }
1426  }
1427 
1428  return limitStr;
1429  }
1430 
1442  private List<BlackboardArtifact> getDataArtifactsFromResult(ResultSet resultSet) throws SQLException, TskCoreException {
1443  List<BlackboardArtifact> artifacts = new ArrayList<>();
1444  while (resultSet.next()) {
1445  BlackboardArtifact.Type bbartType = db.getBlackboard().getArtifactType(resultSet.getInt("artifact_type_id"));
1446  artifacts.add(new DataArtifact(db, resultSet.getLong("artifact_id"),
1447  resultSet.getLong("obj_id"), resultSet.getLong("artifact_obj_id"),
1448  resultSet.getObject("data_source_obj_id") != null ? resultSet.getLong("data_source_obj_id") : null,
1449  bbartType.getTypeID(),
1450  bbartType.getTypeName(), bbartType.getDisplayName(),
1451  BlackboardArtifact.ReviewStatus.withID(resultSet.getInt("review_status_id")),
1452  resultSet.getLong("os_account_obj_id"), false));
1453  }
1454 
1455  return artifacts;
1456  }
1457 }
org.sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID
TSK_ID
Definition: BlackboardAttribute.java:1419
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSources
Set< Content > getRelationshipSources(Set< AccountDeviceInstance > accountDeviceInstanceList, CommunicationsFilter filter)
Definition: CommunicationsManager.java:937
org.sleuthkit.datamodel.Blackboard.postArtifact
void postArtifact(BlackboardArtifact artifact, String moduleName)
Definition: Blackboard.java:99
org.sleuthkit.datamodel.SleuthkitCase.getBlackboardArtifacts
ArrayList< BlackboardArtifact > getBlackboardArtifacts(int artifactTypeID)
Definition: SleuthkitCase.java:3522
org.sleuthkit.datamodel.CommunicationsManager.getRelatedAccountDeviceInstances
List< AccountDeviceInstance > getRelatedAccountDeviceInstances(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
Definition: CommunicationsManager.java:1043
org.sleuthkit.datamodel.CommunicationsManager.getAccountType
org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName)
Definition: CommunicationsManager.java:621
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_MESSAGE
TSK_MESSAGE
Definition: BlackboardArtifact.java:1748
org.sleuthkit.datamodel.CommunicationsManager.createAccountFileInstance
AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile)
Definition: CommunicationsManager.java:311
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:1740
org.sleuthkit.datamodel.CommunicationsManager.getAccountsRelatedToArtifact
List< Account > getAccountsRelatedToArtifact(BlackboardArtifact artifact)
Definition: CommunicationsManager.java:1270
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipCountsPairwise
Map< AccountPair, Long > getRelationshipCountsPairwise(Set< AccountDeviceInstance > accounts, CommunicationsFilter filter)
Definition: CommunicationsManager.java:768
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT
TSK_ACCOUNT
Definition: BlackboardArtifact.java:1832
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG
TSK_EMAIL_MSG
Definition: BlackboardArtifact.java:1677
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.Blackboard.getArtifactType
BlackboardArtifact.Type getArtifactType(String artTypeName)
Definition: Blackboard.java:358
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSources
List< Content > getRelationshipSources(AccountDeviceInstance account1, AccountDeviceInstance account2, CommunicationsFilter filter)
Definition: CommunicationsManager.java:1148
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_CALLLOG
TSK_CALLLOG
Definition: BlackboardArtifact.java:1756
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSourcesCount
long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
Definition: CommunicationsManager.java:878
org.sleuthkit.datamodel.CommunicationsManager.createAccountFileInstance
AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile, List< BlackboardAttribute > attributes, Long ingestJobId)
Definition: CommunicationsManager.java:263
org.sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE
Definition: BlackboardAttribute.java:1020
org.sleuthkit.datamodel.CommunicationsManager.getAccountDeviceInstancesWithRelationships
List< AccountDeviceInstance > getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter)
Definition: CommunicationsManager.java:658
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE
Definition: BlackboardArtifact.java:1597
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:373
org.sleuthkit.datamodel.CommunicationsManager.getAccount
Account getAccount(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID)
Definition: CommunicationsManager.java:329
org.sleuthkit.datamodel.CommunicationsManager.getAccountFileInstances
List< AccountFileInstance > getAccountFileInstances(Account account)
Definition: CommunicationsManager.java:1206

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.