Sleuth Kit Java Bindings (JNI)  4.10.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-2020 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.Collection;
28 import java.util.Collections;
29 import java.util.HashMap;
30 import java.util.HashSet;
31 import java.util.List;
32 import java.util.Map;
33 import java.util.Set;
34 import java.util.concurrent.ConcurrentHashMap;
35 import java.util.logging.Level;
36 import java.util.logging.Logger;
37 import org.sleuthkit.datamodel.Blackboard.BlackboardException;
38 import org.sleuthkit.datamodel.SleuthkitCase.CaseDbConnection;
39 import org.sleuthkit.datamodel.SleuthkitCase.CaseDbTransaction;
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 
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  CaseDbConnection connection = db.getConnection();
88  db.acquireSingleUserCaseWriteLock();
89  Statement statement = null;
90  ResultSet resultSet = null;
91 
92  try {
93  statement = connection.createStatement();
94  // Read the table
95  int count = readAccountTypes();
96  if (0 == count) {
97  // Table is empty, populate it with predefined types
98  for (Account.Type type : Account.Type.PREDEFINED_ACCOUNT_TYPES) {
99  try {
100  statement.execute("INSERT INTO account_types (type_name, display_name) VALUES ( '" + type.getTypeName() + "', '" + type.getDisplayName() + "')"); //NON-NLS
101  } catch (SQLException ex) {
102  resultSet = connection.executeQuery(statement, "SELECT COUNT(*) AS count FROM account_types WHERE type_name = '" + type.getTypeName() + "'"); //NON-NLS
103  resultSet.next();
104  if (resultSet.getLong("count") == 0) {
105  throw ex;
106  }
107  resultSet.close();
108  }
109 
110  ResultSet rs2 = connection.executeQuery(statement, "SELECT account_type_id FROM account_types WHERE type_name = '" + type.getTypeName() + "'"); //NON-NLS
111  rs2.next();
112  int typeID = rs2.getInt("account_type_id");
113  rs2.close();
114 
115  Account.Type accountType = new Account.Type(type.getTypeName(), type.getDisplayName());
116  this.accountTypeToTypeIdMap.put(accountType, typeID);
117  this.typeNameToAccountTypeMap.put(type.getTypeName(), accountType);
118  }
119  }
120  } catch (SQLException ex) {
121  LOGGER.log(Level.SEVERE, "Failed to add row to account_types", ex);
122  } finally {
123  closeResultSet(resultSet);
124  closeStatement(statement);
125  connection.close();
126  db.releaseSingleUserCaseWriteLock();
127  }
128  }
129 
138  private int readAccountTypes() throws TskCoreException {
139  CaseDbConnection connection = db.getConnection();
140  db.acquireSingleUserCaseReadLock();
141  Statement statement = null;
142  ResultSet resultSet = null;
143  int count = 0;
144 
145  try {
146  statement = connection.createStatement();
147 
148  // If the account_types table is already populated, say when opening a case, then load it
149  resultSet = connection.executeQuery(statement, "SELECT COUNT(*) AS count FROM account_types"); //NON-NLS
150  resultSet.next();
151  if (resultSet.getLong("count") > 0) {
152 
153  resultSet.close();
154  resultSet = connection.executeQuery(statement, "SELECT * FROM account_types");
155  while (resultSet.next()) {
156  Account.Type accountType = new Account.Type(resultSet.getString("type_name"), resultSet.getString("display_name"));
157  this.accountTypeToTypeIdMap.put(accountType, resultSet.getInt("account_type_id"));
158  this.typeNameToAccountTypeMap.put(accountType.getTypeName(), accountType);
159  }
160  count = this.typeNameToAccountTypeMap.size();
161  }
162 
163  } catch (SQLException ex) {
164  throw new TskCoreException("Failed to read account_types", ex);
165  } finally {
166  closeResultSet(resultSet);
167  closeStatement(statement);
168  connection.close();
169  db.releaseSingleUserCaseReadLock();
170  }
171 
172  return count;
173  }
174 
180  SleuthkitCase getSleuthkitCase() {
181  return this.db;
182  }
183 
196  // NOTE: Full name given for Type for doxygen linking
197  public org.sleuthkit.datamodel.Account.Type addAccountType(String accountTypeName, String displayName) throws TskCoreException {
198  Account.Type accountType = new Account.Type(accountTypeName, displayName);
199 
200  // check if already in map
201  if (this.accountTypeToTypeIdMap.containsKey(accountType)) {
202  return accountType;
203  }
204 
205  CaseDbConnection connection = db.getConnection();
206  db.acquireSingleUserCaseWriteLock();
207  Statement s = null;
208  ResultSet rs = null;
209  try {
210  connection.beginTransaction();
211  s = connection.createStatement();
212  rs = connection.executeQuery(s, "SELECT * FROM account_types WHERE type_name = '" + accountTypeName + "'"); //NON-NLS
213  if (!rs.next()) {
214  rs.close();
215 
216  s.execute("INSERT INTO account_types (type_name, display_name) VALUES ( '" + accountTypeName + "', '" + displayName + "')"); //NON-NLS
217 
218  // Read back the typeID
219  rs = connection.executeQuery(s, "SELECT * FROM account_types WHERE type_name = '" + accountTypeName + "'"); //NON-NLS
220  rs.next();
221 
222  int typeID = rs.getInt("account_type_id");
223  accountType = new Account.Type(rs.getString("type_name"), rs.getString("display_name"));
224 
225  this.accountTypeToTypeIdMap.put(accountType, typeID);
226  this.typeNameToAccountTypeMap.put(accountTypeName, accountType);
227 
228  connection.commitTransaction();
229 
230  return accountType;
231  } else {
232  int typeID = rs.getInt("account_type_id");
233 
234  accountType = new Account.Type(rs.getString("type_name"), rs.getString("display_name"));
235  this.accountTypeToTypeIdMap.put(accountType, typeID);
236 
237  return accountType;
238  }
239  } catch (SQLException ex) {
240  connection.rollbackTransaction();
241  throw new TskCoreException("Error adding account type", ex);
242  } finally {
243  closeResultSet(rs);
244  closeStatement(s);
245  connection.close();
246  db.releaseSingleUserCaseWriteLock();
247  }
248  }
249 
268  // NOTE: Full name given for Type for doxygen linking
269  public AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile) throws TskCoreException, InvalidAccountIDException {
270 
271  // make or get the Account (unique at the case-level)
272  Account account = getOrCreateAccount(accountType, normalizeAccountID(accountType, accountUniqueID));
273 
274  /*
275  * make or get the artifact. Will not create one if it already exists
276  * for the sourceFile. Such as an email PST that has the same email
277  * address multiple times. Only one artifact is created for each email
278  * message in that PST.
279  */
280  BlackboardArtifact accountArtifact = getOrCreateAccountFileInstanceArtifact(accountType, normalizeAccountID(accountType, accountUniqueID), moduleName, sourceFile);
281 
282  // The account instance map was unused so we have removed it from the database,
283  // but we expect we may need it so I am preserving this method comment and usage here.
284  // add a row to Accounts to Instances mapping table
285  // @@@ BC: Seems like we should only do this if we had to create the artifact.
286  // But, it will probably fail to create a new one based on unique constraints.
287  // addAccountFileInstanceMapping(account.getAccountID(), accountArtifact.getArtifactID());
288  return new AccountFileInstance(accountArtifact, account);
289  }
290 
304  // NOTE: Full name given for Type for doxygen linking
305  public Account getAccount(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID) throws TskCoreException, InvalidAccountIDException {
306  Account account = null;
307  CaseDbConnection connection = db.getConnection();
308  db.acquireSingleUserCaseReadLock();
309  Statement s = null;
310  ResultSet rs = null;
311  try {
312  s = connection.createStatement();
313  rs = connection.executeQuery(s, "SELECT * FROM accounts WHERE account_type_id = " + getAccountTypeId(accountType)
314  + " AND account_unique_identifier = '" + normalizeAccountID(accountType, accountUniqueID) + "'"); //NON-NLS
315 
316  if (rs.next()) {
317  account = new Account(rs.getInt("account_id"), accountType,
318  rs.getString("account_unique_identifier"));
319  }
320  } catch (SQLException ex) {
321  throw new TskCoreException("Error getting account type id", ex);
322  } finally {
323  closeResultSet(rs);
324  closeStatement(s);
325  connection.close();
326  db.releaseSingleUserCaseReadLock();
327  }
328 
329  return account;
330  }
331 
354  // NOTE: Full name given for Type for doxygen linking
355  public void addRelationships(AccountFileInstance sender, List<AccountFileInstance> recipients,
356  BlackboardArtifact sourceArtifact, org.sleuthkit.datamodel.Relationship.Type relationshipType, long dateTime) throws TskCoreException, TskDataException {
357 
358  if (relationshipType.isCreatableFrom(sourceArtifact) == false) {
359  throw new TskDataException("Can not make a " + relationshipType.getDisplayName()
360  + " relationship from a" + sourceArtifact.getDisplayName());
361  }
362 
363  /*
364  * Enforce that all accounts and the relationship between them are from
365  * the same 'source'. This is required for the queries to work
366  * correctly.
367  */
368  // Currently we do not save the direction of communication
369  List<Long> accountIDs = new ArrayList<>();
370 
371  if (null != sender) {
372  accountIDs.add(sender.getAccount().getAccountID());
373  if (sender.getDataSourceObjectID() != sourceArtifact.getDataSourceObjectID()) {
374  throw new TskDataException("Sender and relationship are from different data sources :"
375  + "Sender source ID" + sender.getDataSourceObjectID() + " != relationship source ID" + sourceArtifact.getDataSourceObjectID());
376  }
377  }
378 
379  for (AccountFileInstance recipient : recipients) {
380  accountIDs.add(recipient.getAccount().getAccountID());
381  if (recipient.getDataSourceObjectID() != sourceArtifact.getDataSourceObjectID()) {
382  throw new TskDataException("Recipient and relationship are from different data sources :"
383  + "Recipient source ID" + recipient.getDataSourceObjectID() + " != relationship source ID" + sourceArtifact.getDataSourceObjectID());
384  }
385  }
386 
387  // Set up the query for the prepared statement
388  String query = "INTO account_relationships (account1_id, account2_id, relationship_source_obj_id, date_time, relationship_type, data_source_obj_id ) "
389  + "VALUES (?,?,?,?,?,?)";
390  switch (db.getDatabaseType()) {
391  case POSTGRESQL:
392  query = "INSERT " + query + " ON CONFLICT DO NOTHING";
393  break;
394  case SQLITE:
395  query = "INSERT OR IGNORE " + query;
396  break;
397  default:
398  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
399  }
400 
401  CaseDbTransaction trans = db.beginTransaction();
402  try {
403  SleuthkitCase.CaseDbConnection connection = trans.getConnection();
404  PreparedStatement preparedStatement = connection.getPreparedStatement(query, Statement.NO_GENERATED_KEYS);
405 
406  for (int i = 0; i < accountIDs.size(); i++) {
407  for (int j = i + 1; j < accountIDs.size(); j++) {
408  long account1_id = accountIDs.get(i);
409  long account2_id = accountIDs.get(j);
410 
411  preparedStatement.clearParameters();
412  preparedStatement.setLong(1, account1_id);
413  preparedStatement.setLong(2, account2_id);
414  preparedStatement.setLong(3, sourceArtifact.getId());
415  if (dateTime > 0) {
416  preparedStatement.setLong(4, dateTime);
417  } else {
418  preparedStatement.setNull(4, java.sql.Types.BIGINT);
419  }
420  preparedStatement.setInt(5, relationshipType.getTypeID());
421  preparedStatement.setLong(6, sourceArtifact.getDataSourceObjectID());
422 
423  connection.executeUpdate(preparedStatement);
424  }
425  }
426  trans.commit();
427  } catch (SQLException ex) {
428  trans.rollback();
429  throw new TskCoreException("Error adding accounts relationship", ex);
430  }
431  }
432 
447  private Account getOrCreateAccount(Account.Type accountType, String accountUniqueID) throws TskCoreException, InvalidAccountIDException {
448  Account account = getAccount(accountType, accountUniqueID);
449  if (null == account) {
450  String query = " INTO accounts (account_type_id, account_unique_identifier) "
451  + "VALUES ( " + getAccountTypeId(accountType) + ", '"
452  + normalizeAccountID(accountType, accountUniqueID) + "'" + ")";
453  switch (db.getDatabaseType()) {
454  case POSTGRESQL:
455  query = "INSERT " + query + " ON CONFLICT DO NOTHING"; //NON-NLS
456  break;
457  case SQLITE:
458  query = "INSERT OR IGNORE " + query;
459  break;
460  default:
461  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
462  }
463 
464  CaseDbConnection connection = db.getConnection();
465  db.acquireSingleUserCaseWriteLock();
466  Statement s = null;
467  ResultSet rs = null;
468  try {
469  connection.beginTransaction();
470  s = connection.createStatement();
471 
472  s.execute(query);
473 
474  connection.commitTransaction();
475  account = getAccount(accountType, accountUniqueID);
476  } catch (SQLException ex) {
477  connection.rollbackTransaction();
478  throw new TskCoreException("Error adding an account", ex);
479  } finally {
480  closeResultSet(rs);
481  closeStatement(s);
482  connection.close();
483  db.releaseSingleUserCaseWriteLock();
484  }
485  }
486 
487  return account;
488  }
489 
507  private BlackboardArtifact getOrCreateAccountFileInstanceArtifact(Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile) throws TskCoreException {
508  BlackboardArtifact accountArtifact = getAccountFileInstanceArtifact(accountType, accountUniqueID, sourceFile);
509  if (accountArtifact == null) {
510  accountArtifact = db.newBlackboardArtifact(BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT, sourceFile.getId());
511  Collection<BlackboardAttribute> attributes = new ArrayList<>();
512  attributes.add(new BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE, moduleName, accountType.getTypeName()));
513  attributes.add(new BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID, moduleName, accountUniqueID));
514  accountArtifact.addAttributes(attributes);
515  try {
516  db.getBlackboard().postArtifact(accountArtifact, moduleName);
517  } catch (BlackboardException ex) {
518  LOGGER.log(Level.SEVERE, String.format("Error posting new account artifact to the blackboard (object ID = %d)", accountArtifact.getId()), ex);
519  }
520  }
521  return accountArtifact;
522  }
523 
537  private BlackboardArtifact getAccountFileInstanceArtifact(Account.Type accountType, String accountUniqueID, Content sourceFile) throws TskCoreException {
538  BlackboardArtifact accountArtifact = null;
539  CaseDbConnection connection = db.getConnection();
540  db.acquireSingleUserCaseReadLock();
541  Statement s = null;
542  ResultSet rs = null;
543 
544  try {
545  s = connection.createStatement();
546  String queryStr = "SELECT artifacts.artifact_id AS artifact_id,"
547  + " artifacts.obj_id AS obj_id,"
548  + " artifacts.artifact_obj_id AS artifact_obj_id,"
549  + " artifacts.data_source_obj_id AS data_source_obj_id,"
550  + " artifacts.artifact_type_id AS artifact_type_id,"
551  + " artifacts.review_status_id AS review_status_id"
552  + " FROM blackboard_artifacts AS artifacts"
553  + " JOIN blackboard_attributes AS attr_account_type"
554  + " ON artifacts.artifact_id = attr_account_type.artifact_id"
555  + " JOIN blackboard_attributes AS attr_account_id"
556  + " ON artifacts.artifact_id = attr_account_id.artifact_id"
557  + " AND attr_account_id.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID.getTypeID()
558  + " AND attr_account_id.value_text = '" + accountUniqueID + "'"
559  + " WHERE artifacts.artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT.getTypeID()
560  + " AND attr_account_type.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE.getTypeID()
561  + " AND attr_account_type.value_text = '" + accountType.getTypeName() + "'"
562  + " AND artifacts.obj_id = " + sourceFile.getId(); //NON-NLS
563 
564  rs = connection.executeQuery(s, queryStr); //NON-NLS
565  if (rs.next()) {
566  BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
567 
568  accountArtifact = new BlackboardArtifact(db, rs.getLong("artifact_id"), rs.getLong("obj_id"), rs.getLong("artifact_obj_id"), rs.getLong("data_source_obj_id"),
569  bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
570  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id")));
571  }
572  } catch (SQLException ex) {
573  throw new TskCoreException("Error getting account", ex);
574  } finally {
575  closeResultSet(rs);
576  closeStatement(s);
577  connection.close();
578  db.releaseSingleUserCaseReadLock();
579  }
580 
581  return accountArtifact;
582  }
583 
593  // NOTE: Full name given for Type for doxygen linking
594  public org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName) throws TskCoreException {
595  if (this.typeNameToAccountTypeMap.containsKey(accountTypeName)) {
596  return this.typeNameToAccountTypeMap.get(accountTypeName);
597  }
598 
599  CaseDbConnection connection = db.getConnection();
600  db.acquireSingleUserCaseReadLock();
601  Statement s = null;
602  ResultSet rs = null;
603 
604  try {
605  s = connection.createStatement();
606  rs = connection.executeQuery(s, "SELECT account_type_id, type_name, display_name FROM account_types WHERE type_name = '" + accountTypeName + "'"); //NON-NLS
607  Account.Type accountType = null;
608  if (rs.next()) {
609  accountType = new Account.Type(accountTypeName, rs.getString("display_name"));
610  this.accountTypeToTypeIdMap.put(accountType, rs.getInt("account_type_id"));
611  this.typeNameToAccountTypeMap.put(accountTypeName, accountType);
612  }
613  return accountType;
614  } catch (SQLException ex) {
615  throw new TskCoreException("Error getting account type id", ex);
616  } finally {
617  closeResultSet(rs);
618  closeStatement(s);
619  connection.close();
620  db.releaseSingleUserCaseReadLock();
621  }
622  }
623 
638  public List<AccountDeviceInstance> getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter) throws TskCoreException {
639  CaseDbConnection connection = db.getConnection();
640  db.acquireSingleUserCaseReadLock();
641  Statement s = null;
642  ResultSet rs = null;
643 
644  try {
645  s = connection.createStatement();
646 
647  //set up applicable filters
648  Set<String> applicableInnerQueryFilters = new HashSet<String>(Arrays.asList(
649  CommunicationsFilter.DateRangeFilter.class.getName(),
650  CommunicationsFilter.DeviceFilter.class.getName(),
651  CommunicationsFilter.RelationshipTypeFilter.class.getName()
652  ));
653  String relationshipFilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
654 
655  String relationshipLimitSQL = getMostRecentFilterLimitSQL(filter);
656 
657  String relTblfilterQuery
658  = "SELECT * "
659  + "FROM account_relationships as relationships"
660  + (relationshipFilterSQL.isEmpty() ? "" : " WHERE " + relationshipFilterSQL)
661  + (relationshipLimitSQL.isEmpty() ? "" : relationshipLimitSQL);
662 
663  String uniqueAccountQueryTemplate
664  = " SELECT %1$1s as account_id,"
665  + " data_source_obj_id"
666  + " FROM ( " + relTblfilterQuery + ")AS %2$s";
667 
668  String relationshipTableFilterQuery1 = String.format(uniqueAccountQueryTemplate, "account1_id", "union_query_1");
669  String relationshipTableFilterQuery2 = String.format(uniqueAccountQueryTemplate, "account2_id", "union_query_2");
670 
671  //this query groups by account_id and data_source_obj_id across both innerQueries
672  String uniqueAccountQuery
673  = "SELECT DISTINCT account_id, data_source_obj_id"
674  + " FROM ( " + relationshipTableFilterQuery1 + " UNION " + relationshipTableFilterQuery2 + " ) AS inner_union"
675  + " GROUP BY account_id, data_source_obj_id";
676 
677  // set up applicable filters
678  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
679  CommunicationsFilter.AccountTypeFilter.class.getName()
680  ));
681 
682  String accountTypeFilterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
683 
684  String queryStr
685  = //account info
686  " accounts.account_id AS account_id,"
687  + " accounts.account_unique_identifier AS account_unique_identifier,"
688  //account type info
689  + " account_types.type_name AS type_name,"
690  //Account device instance info
691  + " data_source_info.device_id AS device_id"
692  + " FROM ( " + uniqueAccountQuery + " ) AS account_device_instances"
693  + " JOIN accounts AS accounts"
694  + " ON accounts.account_id = account_device_instances.account_id"
695  + " JOIN account_types AS account_types"
696  + " ON accounts.account_type_id = account_types.account_type_id"
697  + " JOIN data_source_info AS data_source_info"
698  + " ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
699  + (accountTypeFilterSQL.isEmpty() ? "" : " WHERE " + accountTypeFilterSQL);
700 
701  switch (db.getDatabaseType()) {
702  case POSTGRESQL:
703  queryStr = "SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
704  break;
705  case SQLITE:
706  queryStr = "SELECT " + queryStr + " GROUP BY accounts.account_id, data_source_info.device_id";
707  break;
708  default:
709  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
710  }
711 
712  rs = connection.executeQuery(s, queryStr); //NON-NLS
713  ArrayList<AccountDeviceInstance> accountDeviceInstances = new ArrayList<AccountDeviceInstance>();
714  while (rs.next()) {
715  long account_id = rs.getLong("account_id");
716  String deviceID = rs.getString("device_id");
717  final String type_name = rs.getString("type_name");
718  final String account_unique_identifier = rs.getString("account_unique_identifier");
719 
720  Account.Type accountType = typeNameToAccountTypeMap.get(type_name);
721  Account account = new Account(account_id, accountType, account_unique_identifier);
722  accountDeviceInstances.add(new AccountDeviceInstance(account, deviceID));
723  }
724 
725  return accountDeviceInstances;
726  } catch (SQLException ex) {
727  throw new TskCoreException("Error getting account device instances. " + ex.getMessage(), ex);
728  } finally {
729  closeResultSet(rs);
730  closeStatement(s);
731  connection.close();
732  db.releaseSingleUserCaseReadLock();
733  }
734  }
735 
755  public Map<AccountPair, Long> getRelationshipCountsPairwise(Set<AccountDeviceInstance> accounts, CommunicationsFilter filter) throws TskCoreException {
756 
757  Set<Long> accountIDs = new HashSet<Long>();
758  Set<String> accountDeviceIDs = new HashSet<String>();
759  for (AccountDeviceInstance adi : accounts) {
760  accountIDs.add(adi.getAccount().getAccountID());
761  accountDeviceIDs.add("'" + adi.getDeviceId() + "'");
762  }
763  //set up applicable filters
764  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
765  CommunicationsFilter.DateRangeFilter.class.getName(),
766  CommunicationsFilter.DeviceFilter.class.getName(),
767  CommunicationsFilter.RelationshipTypeFilter.class.getName()
768  ));
769 
770  String accountIDsCSL = StringUtils.buildCSVString(accountIDs);
771  String accountDeviceIDsCSL = StringUtils.buildCSVString(accountDeviceIDs);
772  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
773 
774  final String queryString
775  = " SELECT count(DISTINCT relationships.relationship_source_obj_id) AS count," //realtionship count
776  + " data_source_info.device_id AS device_id,"
777  //account 1 info
778  + " accounts1.account_id AS account1_id,"
779  + " accounts1.account_unique_identifier AS account1_unique_identifier,"
780  + " account_types1.type_name AS type_name1,"
781  + " account_types1.display_name AS display_name1,"
782  //account 2 info
783  + " accounts2.account_id AS account2_id,"
784  + " accounts2.account_unique_identifier AS account2_unique_identifier,"
785  + " account_types2.type_name AS type_name2,"
786  + " account_types2.display_name AS display_name2"
787  + " FROM account_relationships AS relationships"
788  + " JOIN data_source_info AS data_source_info"
789  + " ON relationships.data_source_obj_id = data_source_info.obj_id "
790  //account1 aliases
791  + " JOIN accounts AS accounts1 "
792  + " ON accounts1.account_id = relationships.account1_id"
793  + " JOIN account_types AS account_types1"
794  + " ON accounts1.account_type_id = account_types1.account_type_id"
795  //account2 aliases
796  + " JOIN accounts AS accounts2 "
797  + " ON accounts2.account_id = relationships.account2_id"
798  + " JOIN account_types AS account_types2"
799  + " ON accounts2.account_type_id = account_types2.account_type_id"
800  + " WHERE (( relationships.account1_id IN (" + accountIDsCSL + ")) "
801  + " AND ( relationships.account2_id IN ( " + accountIDsCSL + " ))"
802  + " AND ( data_source_info.device_id IN (" + accountDeviceIDsCSL + "))) "
803  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL)
804  + " GROUP BY data_source_info.device_id, "
805  + " accounts1.account_id, "
806  + " account_types1.type_name, "
807  + " account_types1.display_name, "
808  + " accounts2.account_id, "
809  + " account_types2.type_name, "
810  + " account_types2.display_name";
811  CaseDbConnection connection = db.getConnection();
812  db.acquireSingleUserCaseReadLock();
813  Statement s = null;
814  ResultSet rs = null;
815 
816  Map<AccountPair, Long> results = new HashMap<AccountPair, Long>();
817 
818  try {
819  s = connection.createStatement();
820  rs = connection.executeQuery(s, queryString); //NON-NLS
821 
822  while (rs.next()) {
823  //make account 1
824  Account.Type type1 = new Account.Type(rs.getString("type_name1"), rs.getString("display_name1"));
825  AccountDeviceInstance adi1 = new AccountDeviceInstance(new Account(rs.getLong("account1_id"), type1,
826  rs.getString("account1_unique_identifier")),
827  rs.getString("device_id"));
828 
829  //make account 2
830  Account.Type type2 = new Account.Type(rs.getString("type_name2"), rs.getString("display_name2"));
831  AccountDeviceInstance adi2 = new AccountDeviceInstance(new Account(rs.getLong("account2_id"), type2,
832  rs.getString("account2_unique_identifier")),
833  rs.getString("device_id"));
834 
835  AccountPair relationshipKey = new AccountPair(adi1, adi2);
836  long count = rs.getLong("count");
837 
838  //merge counts for relationships that have the accounts flipped.
839  Long oldCount = results.get(relationshipKey);
840  if (oldCount != null) {
841  count += oldCount;
842  }
843  results.put(relationshipKey, count);
844  }
845  return results;
846  } catch (SQLException ex) {
847  throw new TskCoreException("Error getting relationships between accounts. " + ex.getMessage(), ex);
848  } finally {
849  closeResultSet(rs);
850  closeStatement(s);
851  connection.close();
852  db.releaseSingleUserCaseReadLock();
853  }
854  }
855 
871  public long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter) throws TskCoreException {
872 
873  long account_id = accountDeviceInstance.getAccount().getAccountID();
874 
875  // Get the list of Data source objects IDs correpsonding to this DeviceID.
876  String datasourceObjIdsCSV = StringUtils.buildCSVString(
877  db.getDataSourceObjIds(accountDeviceInstance.getDeviceId()));
878 
879  // set up applicable filters
880  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
881  CommunicationsFilter.RelationshipTypeFilter.class.getName(),
882  CommunicationsFilter.DateRangeFilter.class.getName()
883  ));
884  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
885 
886  CaseDbConnection connection = db.getConnection();
887  db.acquireSingleUserCaseReadLock();
888  Statement s = null;
889  ResultSet rs = null;
890 
891  try {
892  s = connection.createStatement();
893 
894  String innerQuery = " account_relationships AS relationships";
895  String limitStr = getMostRecentFilterLimitSQL(filter);
896 
897  if (!limitStr.isEmpty()) {
898  innerQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
899  }
900 
901  String queryStr
902  = "SELECT count(DISTINCT relationships.relationship_source_obj_id) as count "
903  + " FROM" + innerQuery
904  + " WHERE relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV + " )"
905  + " AND ( relationships.account1_id = " + account_id
906  + " OR relationships.account2_id = " + account_id + " )"
907  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL);
908 
909  rs = connection.executeQuery(s, queryStr); //NON-NLS
910  rs.next();
911  return (rs.getLong("count"));
912  } catch (SQLException ex) {
913  throw new TskCoreException("Error getting relationships count for account device instance. " + ex.getMessage(), ex);
914  } finally {
915  closeResultSet(rs);
916  closeStatement(s);
917  connection.close();
918  db.releaseSingleUserCaseReadLock();
919  }
920  }
921 
938  public Set<Content> getRelationshipSources(Set<AccountDeviceInstance> accountDeviceInstanceList, CommunicationsFilter filter) throws TskCoreException {
939 
940  if (accountDeviceInstanceList.isEmpty()) {
941  //log this?
942  return Collections.emptySet();
943  }
944 
945  Map<Long, Set<Long>> accountIdToDatasourceObjIdMap = new HashMap<Long, Set<Long>>();
946  for (AccountDeviceInstance accountDeviceInstance : accountDeviceInstanceList) {
947  long accountID = accountDeviceInstance.getAccount().getAccountID();
948  List<Long> dataSourceObjIds = db.getDataSourceObjIds(accountDeviceInstance.getDeviceId());
949 
950  if (accountIdToDatasourceObjIdMap.containsKey(accountID)) {
951  accountIdToDatasourceObjIdMap.get(accountID).addAll(dataSourceObjIds);
952  } else {
953  accountIdToDatasourceObjIdMap.put(accountID, new HashSet<Long>(dataSourceObjIds));
954  }
955  }
956 
957  List<String> adiSQLClauses = new ArrayList<String>();
958  for (Map.Entry<Long, Set<Long>> entry : accountIdToDatasourceObjIdMap.entrySet()) {
959  final Long accountID = entry.getKey();
960  String datasourceObjIdsCSV = StringUtils.buildCSVString(entry.getValue());
961 
962  adiSQLClauses.add(
963  "( ( relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV + " ) )"
964  + " AND ( relationships.account1_id = " + accountID
965  + " OR relationships.account2_id = " + accountID + " ) )"
966  );
967  }
968  String adiSQLClause = StringUtils.joinAsStrings(adiSQLClauses, " OR ");
969 
970  // set up applicable filters
971  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
972  CommunicationsFilter.RelationshipTypeFilter.class
973  .getName(),
974  CommunicationsFilter.DateRangeFilter.class
975  .getName()
976  ));
977  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
978 
979  String limitQuery = " account_relationships AS relationships";
980  String limitStr = getMostRecentFilterLimitSQL(filter);
981  if (!limitStr.isEmpty()) {
982  limitQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
983  }
984 
985  CaseDbConnection connection = db.getConnection();
986  db.acquireSingleUserCaseReadLock();
987  Statement s = null;
988  ResultSet rs = null;
989 
990  try {
991  s = connection.createStatement();
992  String queryStr
993  = "SELECT DISTINCT artifacts.artifact_id AS artifact_id,"
994  + " artifacts.obj_id AS obj_id,"
995  + " artifacts.artifact_obj_id AS artifact_obj_id,"
996  + " artifacts.data_source_obj_id AS data_source_obj_id, "
997  + " artifacts.artifact_type_id AS artifact_type_id, "
998  + " artifacts.review_status_id AS review_status_id "
999  + " FROM blackboard_artifacts as artifacts"
1000  + " JOIN " + limitQuery
1001  + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
1002  // append sql to restrict search to specified account device instances
1003  + " WHERE (" + adiSQLClause + " )"
1004  // plus other filters
1005  + (filterSQL.isEmpty() ? "" : " AND (" + filterSQL + " )");
1006 
1007  rs = connection.executeQuery(s, queryStr); //NON-NLS
1008  Set<Content> relationshipSources = new HashSet<Content>();
1009  while (rs.next()) {
1010  BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
1011  relationshipSources.add(new BlackboardArtifact(db, rs.getLong("artifact_id"),
1012  rs.getLong("obj_id"), rs.getLong("artifact_obj_id"),
1013  rs.getLong("data_source_obj_id"), bbartType.getTypeID(),
1014  bbartType.getTypeName(), bbartType.getDisplayName(),
1015  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id"))));
1016  }
1017 
1018  return relationshipSources;
1019  } catch (SQLException ex) {
1020  throw new TskCoreException("Error getting relationships for account. " + ex.getMessage(), ex);
1021  } finally {
1022  closeResultSet(rs);
1023  closeStatement(s);
1024  connection.close();
1025  db.releaseSingleUserCaseReadLock();
1026  }
1027  }
1028 
1044  public List<AccountDeviceInstance> getRelatedAccountDeviceInstances(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter) throws TskCoreException {
1045  final List<Long> dataSourceObjIds
1046  = getSleuthkitCase().getDataSourceObjIds(accountDeviceInstance.getDeviceId());
1047 
1048  //set up applicable filters
1049  Set<String> applicableInnerQueryFilters = new HashSet<String>(Arrays.asList(
1050  CommunicationsFilter.DateRangeFilter.class.getName(),
1051  CommunicationsFilter.DeviceFilter.class.getName(),
1052  CommunicationsFilter.RelationshipTypeFilter.class.getName()
1053  ));
1054 
1055  String innerQueryfilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
1056 
1057  String innerQueryTemplate
1058  = " SELECT %1$1s as account_id,"
1059  + " data_source_obj_id"
1060  + " FROM account_relationships as relationships"
1061  + " WHERE %2$1s = " + accountDeviceInstance.getAccount().getAccountID() + ""
1062  + " AND data_source_obj_id IN (" + StringUtils.buildCSVString(dataSourceObjIds) + ")"
1063  + (innerQueryfilterSQL.isEmpty() ? "" : " AND " + innerQueryfilterSQL);
1064 
1065  String innerQuery1 = String.format(innerQueryTemplate, "account1_id", "account2_id");
1066  String innerQuery2 = String.format(innerQueryTemplate, "account2_id", "account1_id");
1067 
1068  //this query groups by account_id and data_source_obj_id across both innerQueries
1069  String combinedInnerQuery
1070  = "SELECT account_id, data_source_obj_id "
1071  + " FROM ( " + innerQuery1 + " UNION " + innerQuery2 + " ) AS inner_union"
1072  + " GROUP BY account_id, data_source_obj_id";
1073 
1074  // set up applicable filters
1075  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
1076  CommunicationsFilter.AccountTypeFilter.class.getName()
1077  ));
1078 
1079  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
1080 
1081  String queryStr
1082  = //account info
1083  " accounts.account_id AS account_id,"
1084  + " accounts.account_unique_identifier AS account_unique_identifier,"
1085  //account type info
1086  + " account_types.type_name AS type_name,"
1087  //Account device instance info
1088  + " data_source_info.device_id AS device_id"
1089  + " FROM ( " + combinedInnerQuery + " ) AS account_device_instances"
1090  + " JOIN accounts AS accounts"
1091  + " ON accounts.account_id = account_device_instances.account_id"
1092  + " JOIN account_types AS account_types"
1093  + " ON accounts.account_type_id = account_types.account_type_id"
1094  + " JOIN data_source_info AS data_source_info"
1095  + " ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
1096  + (filterSQL.isEmpty() ? "" : " WHERE " + filterSQL);
1097 
1098  switch (db.getDatabaseType()) {
1099  case POSTGRESQL:
1100  queryStr = "SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
1101  break;
1102  case SQLITE:
1103  queryStr = "SELECT " + queryStr + " GROUP BY accounts.account_id, data_source_info.device_id";
1104  break;
1105  default:
1106  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
1107  }
1108 
1109  CaseDbConnection connection = db.getConnection();
1110  db.acquireSingleUserCaseReadLock();
1111  Statement s = null;
1112  ResultSet rs = null;
1113 
1114  try {
1115  s = connection.createStatement();
1116 
1117  rs = connection.executeQuery(s, queryStr); //NON-NLS
1118  ArrayList<AccountDeviceInstance> accountDeviceInstances = new ArrayList<AccountDeviceInstance>();
1119  while (rs.next()) {
1120  long account_id = rs.getLong("account_id");
1121  String deviceID = rs.getString("device_id");
1122  final String type_name = rs.getString("type_name");
1123  final String account_unique_identifier = rs.getString("account_unique_identifier");
1124 
1125  Account.Type accountType = typeNameToAccountTypeMap.get(type_name);
1126  Account account = new Account(account_id, accountType, account_unique_identifier);
1127  accountDeviceInstances.add(new AccountDeviceInstance(account, deviceID));
1128  }
1129 
1130  return accountDeviceInstances;
1131  } catch (SQLException ex) {
1132  throw new TskCoreException("Error getting account device instances. " + ex.getMessage(), ex);
1133  } finally {
1134  closeResultSet(rs);
1135  closeStatement(s);
1136  connection.close();
1137  db.releaseSingleUserCaseReadLock();
1138  }
1139  }
1140 
1157  public List<Content> getRelationshipSources(AccountDeviceInstance account1, AccountDeviceInstance account2, CommunicationsFilter filter) throws TskCoreException {
1158 
1159  //set up applicable filters
1160  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
1161  CommunicationsFilter.DateRangeFilter.class.getName(),
1162  CommunicationsFilter.DeviceFilter.class.getName(),
1163  CommunicationsFilter.RelationshipTypeFilter.class.getName()
1164  ));
1165 
1166  String limitQuery = " account_relationships AS relationships";
1167  String limitStr = getMostRecentFilterLimitSQL(filter);
1168  if (!limitStr.isEmpty()) {
1169  limitQuery = "(SELECT * FROM account_relationships as relationships " + limitStr + ") as relationships";
1170  }
1171 
1172  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
1173  final String queryString = "SELECT artifacts.artifact_id AS artifact_id,"
1174  + " artifacts.obj_id AS obj_id,"
1175  + " artifacts.artifact_obj_id AS artifact_obj_id,"
1176  + " artifacts.data_source_obj_id AS data_source_obj_id,"
1177  + " artifacts.artifact_type_id AS artifact_type_id,"
1178  + " artifacts.review_status_id AS review_status_id"
1179  + " FROM blackboard_artifacts AS artifacts"
1180  + " JOIN " + limitQuery
1181  + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
1182  + " WHERE (( relationships.account1_id = " + account1.getAccount().getAccountID()
1183  + " AND relationships.account2_id = " + account2.getAccount().getAccountID()
1184  + " ) OR ( relationships.account2_id = " + account1.getAccount().getAccountID()
1185  + " AND relationships.account1_id =" + account2.getAccount().getAccountID() + " ))"
1186  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL);
1187  CaseDbConnection connection = db.getConnection();
1188  db.acquireSingleUserCaseReadLock();
1189  Statement s = null;
1190  ResultSet rs = null;
1191  try {
1192  s = connection.createStatement();
1193  rs = connection.executeQuery(s, queryString); //NON-NLS
1194 
1195  ArrayList<Content> artifacts = new ArrayList<Content>();
1196  while (rs.next()) {
1197  BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
1198  artifacts.add(new BlackboardArtifact(db, rs.getLong("artifact_id"), rs.getLong("obj_id"), rs.getLong("artifact_obj_id"), rs.getLong("data_source_obj_id"),
1199  bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
1200  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id"))));
1201  }
1202 
1203  return artifacts;
1204  } catch (SQLException ex) {
1205  throw new TskCoreException("Error getting relationships between accounts. " + ex.getMessage(), ex);
1206  } finally {
1207  closeResultSet(rs);
1208  closeStatement(s);
1209  connection.close();
1210  db.releaseSingleUserCaseReadLock();
1211  }
1212  }
1213 
1224  public List<AccountFileInstance> getAccountFileInstances(Account account) throws TskCoreException {
1225  List<AccountFileInstance> accountFileInstanceList = new ArrayList<>();
1226 
1227  List<BlackboardArtifact> artifactList = getSleuthkitCase().getBlackboardArtifacts(BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT, BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID, account.getTypeSpecificID());
1228 
1229  if (artifactList != null && !artifactList.isEmpty()) {
1230  for (BlackboardArtifact artifact : artifactList) {
1231  accountFileInstanceList.add(new AccountFileInstance(artifact, account));
1232  }
1233  }
1234 
1235  if (!accountFileInstanceList.isEmpty()) {
1236  return accountFileInstanceList;
1237  } else {
1238  return null;
1239  }
1240  }
1241 
1250  public List<Account.Type> getAccountTypesInUse() throws TskCoreException {
1251  CaseDbConnection connection = db.getConnection();
1252  db.acquireSingleUserCaseReadLock();
1253  Statement s = null;
1254  ResultSet rs = null;
1255  List<Account.Type> inUseAccounts = new ArrayList<>();
1256 
1257  try {
1258  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";
1259  s = connection.createStatement();
1260  rs = connection.executeQuery(s, query); //NON-NLS
1261  Account.Type accountType = null;
1262  while (rs.next()) {
1263  String accountTypeName = rs.getString("type_name");
1264  accountType = this.typeNameToAccountTypeMap.get(accountTypeName);
1265 
1266  if (accountType == null) {
1267  accountType = new Account.Type(accountTypeName, rs.getString("display_name"));
1268  this.accountTypeToTypeIdMap.put(accountType, rs.getInt("account_type_id"));
1269  }
1270 
1271  inUseAccounts.add(accountType);
1272  }
1273  return inUseAccounts;
1274  } catch (SQLException ex) {
1275  throw new TskCoreException("Error getting account type id", ex);
1276  } finally {
1277  closeResultSet(rs);
1278  closeStatement(s);
1279  connection.close();
1280  db.releaseSingleUserCaseReadLock();
1281  }
1282  }
1283 
1293  public List<Account> getAccountsRelatedToArtifact(BlackboardArtifact artifact) throws TskCoreException {
1294  if (artifact == null) {
1295  throw new IllegalArgumentException("null arugment passed to getAccountsRelatedToArtifact");
1296  }
1297 
1298  List<Account> accountList = new ArrayList<>();
1299  try (CaseDbConnection connection = db.getConnection()) {
1300  db.acquireSingleUserCaseReadLock();
1301  try {
1302  // In order to get a list of all the unique accounts in a relationship with the given aritfact
1303  // we must first union a list of the unique account1_id in the relationship with artifact
1304  // then the unique account2_id (inner select with union). The outter select assures the list
1305  // of the inner select only contains unique accounts.
1306  String query = String.format("SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1307  + " FROM ("
1308  + " SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1309  + " FROM accounts"
1310  + " JOIN account_relationships ON account1_id = account_id"
1311  + " WHERE relationship_source_obj_id = %d"
1312  + " UNION "
1313  + " SELECT DISTINCT (account_id), account_type_id, account_unique_identifier"
1314  + " FROM accounts"
1315  + " JOIN account_relationships ON account2_id = account_id"
1316  + " WHERE relationship_source_obj_id = %d) AS unionOfRelationships", artifact.getId(), artifact.getId());
1317  try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
1318  while (rs.next()) {
1319  Account.Type accountType = null;
1320  int accountTypeId = rs.getInt("account_type_id");
1321  for (Map.Entry<Account.Type, Integer> entry : accountTypeToTypeIdMap.entrySet()) {
1322  if (entry.getValue() == accountTypeId) {
1323  accountType = entry.getKey();
1324  break;
1325  }
1326  }
1327 
1328  accountList.add(new Account(rs.getInt("account_id"), accountType, rs.getString("account_unique_identifier")));
1329  }
1330  } catch (SQLException ex) {
1331  throw new TskCoreException("Unable to get account list for give artifact " + artifact.getId(), ex);
1332  }
1333 
1334  } finally {
1335  db.releaseSingleUserCaseReadLock();
1336  }
1337  }
1338 
1339  return accountList;
1340  }
1341 
1349  int getAccountTypeId(Account.Type accountType) {
1350  if (accountTypeToTypeIdMap.containsKey(accountType)) {
1351  return accountTypeToTypeIdMap.get(accountType);
1352  }
1353 
1354  return 0;
1355  }
1356 
1368  private String normalizeAccountID(Account.Type accountType, String accountUniqueID) throws InvalidAccountIDException {
1369 
1370  if (accountUniqueID == null || accountUniqueID.isEmpty()) {
1371  throw new InvalidAccountIDException("Account id is null or empty.");
1372  }
1373 
1374  String normalizedAccountID;
1375  if (accountType.equals(Account.Type.PHONE)) {
1376  normalizedAccountID = CommunicationsUtils.normalizePhoneNum(accountUniqueID);
1377  } else if (accountType.equals(Account.Type.EMAIL)) {
1378  normalizedAccountID = CommunicationsUtils.normalizeEmailAddress(accountUniqueID);
1379  } else {
1380  normalizedAccountID = accountUniqueID.toLowerCase().trim();
1381  }
1382 
1383  return normalizedAccountID;
1384  }
1385 
1398  private String getCommunicationsFilterSQL(CommunicationsFilter commFilter, Set<String> applicableFilters) {
1399  if (null == commFilter || commFilter.getAndFilters().isEmpty()) {
1400  return "";
1401  }
1402 
1403  String sqlStr = "";
1404  StringBuilder sqlSB = new StringBuilder();
1405  boolean first = true;
1406  for (CommunicationsFilter.SubFilter subFilter : commFilter.getAndFilters()) {
1407 
1408  // If the filter is applicable
1409  if (applicableFilters.contains(subFilter.getClass().getName())) {
1410  String subfilterSQL = subFilter.getSQL(this);
1411  if (!subfilterSQL.isEmpty()) {
1412  if (first) {
1413  first = false;
1414  } else {
1415  sqlSB.append(" AND ");
1416  }
1417  sqlSB.append("( ");
1418  sqlSB.append(subfilterSQL);
1419  sqlSB.append(" )");
1420  }
1421  }
1422  }
1423 
1424  if (!sqlSB.toString().isEmpty()) {
1425  sqlStr = "( " + sqlSB.toString() + " )";
1426  }
1427  return sqlStr;
1428  }
1429 
1438  private String getMostRecentFilterLimitSQL(CommunicationsFilter filter) {
1439  String limitStr = "";
1440 
1441  if (filter != null && !filter.getAndFilters().isEmpty()) {
1442 
1443  for (CommunicationsFilter.SubFilter subFilter : filter.getAndFilters()) {
1444  if (subFilter.getClass().getName().equals(CommunicationsFilter.MostRecentFilter.class.getName())) {
1445  limitStr = subFilter.getSQL(this);
1446  break;
1447  }
1448  }
1449  }
1450 
1451  return limitStr;
1452  }
1453 }
org.sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID
TSK_ID
Definition: BlackboardAttribute.java:1332
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSources
Set< Content > getRelationshipSources(Set< AccountDeviceInstance > accountDeviceInstanceList, CommunicationsFilter filter)
Definition: CommunicationsManager.java:938
org.sleuthkit.datamodel.Blackboard.postArtifact
void postArtifact(BlackboardArtifact artifact, String moduleName)
Definition: Blackboard.java:64
org.sleuthkit.datamodel.SleuthkitCase.getBlackboardArtifacts
ArrayList< BlackboardArtifact > getBlackboardArtifacts(int artifactTypeID)
Definition: SleuthkitCase.java:3006
org.sleuthkit.datamodel.BlackboardArtifact.addAttributes
void addAttributes(Collection< BlackboardAttribute > attributes)
Definition: BlackboardArtifact.java:375
org.sleuthkit.datamodel.CommunicationsManager.getRelatedAccountDeviceInstances
List< AccountDeviceInstance > getRelatedAccountDeviceInstances(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
Definition: CommunicationsManager.java:1044
org.sleuthkit.datamodel.CommunicationsManager.getAccountType
org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName)
Definition: CommunicationsManager.java:594
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_MESSAGE
TSK_MESSAGE
Definition: BlackboardArtifact.java:1072
org.sleuthkit.datamodel.CommunicationsManager.createAccountFileInstance
AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile)
Definition: CommunicationsManager.java:269
org.sleuthkit.datamodel.CommunicationsManager.addAccountType
org.sleuthkit.datamodel.Account.Type addAccountType(String accountTypeName, String displayName)
Definition: CommunicationsManager.java:197
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_CONTACT
TSK_CONTACT
Definition: BlackboardArtifact.java:1064
org.sleuthkit.datamodel.CommunicationsManager.getAccountsRelatedToArtifact
List< Account > getAccountsRelatedToArtifact(BlackboardArtifact artifact)
Definition: CommunicationsManager.java:1293
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipCountsPairwise
Map< AccountPair, Long > getRelationshipCountsPairwise(Set< AccountDeviceInstance > accounts, CommunicationsFilter filter)
Definition: CommunicationsManager.java:755
org.sleuthkit.datamodel.SleuthkitCase.newBlackboardArtifact
BlackboardArtifact newBlackboardArtifact(int artifactTypeID, long obj_id)
Definition: SleuthkitCase.java:4533
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT
TSK_ACCOUNT
Definition: BlackboardArtifact.java:1153
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG
TSK_EMAIL_MSG
Definition: BlackboardArtifact.java:1002
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:32
org.sleuthkit.datamodel.SleuthkitCase.getArtifactType
BlackboardArtifact.Type getArtifactType(String artTypeName)
Definition: SleuthkitCase.java:4248
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSources
List< Content > getRelationshipSources(AccountDeviceInstance account1, AccountDeviceInstance account2, CommunicationsFilter filter)
Definition: CommunicationsManager.java:1157
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_CALLLOG
TSK_CALLLOG
Definition: BlackboardArtifact.java:1080
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSourcesCount
long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
Definition: CommunicationsManager.java:871
org.sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE
Definition: BlackboardAttribute.java:933
org.sleuthkit.datamodel.CommunicationsManager.getAccountDeviceInstancesWithRelationships
List< AccountDeviceInstance > getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter)
Definition: CommunicationsManager.java:638
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE
Definition: BlackboardArtifact.java:925
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:355
org.sleuthkit.datamodel.CommunicationsManager.getAccount
Account getAccount(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID)
Definition: CommunicationsManager.java:305
org.sleuthkit.datamodel.CommunicationsManager.getAccountFileInstances
List< AccountFileInstance > getAccountFileInstances(Account account)
Definition: CommunicationsManager.java:1224

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