Sleuth Kit Java Bindings (JNI)  4.6.0
Java bindings for using The Sleuth Kit
CommunicationsManager.java
Go to the documentation of this file.
1 /*
2  * Sleuth Kit Data Model
3  *
4  * Copyright 2017 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.ResultSet;
22 import java.sql.SQLException;
23 import java.sql.Statement;
24 import java.util.ArrayList;
25 import java.util.Arrays;
26 import java.util.Collection;
27 import java.util.Collections;
28 import java.util.HashMap;
29 import java.util.HashSet;
30 import java.util.Iterator;
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.SleuthkitCase.CaseDbConnection;
38 import static org.sleuthkit.datamodel.SleuthkitCase.closeResultSet;
39 import static org.sleuthkit.datamodel.SleuthkitCase.closeStatement;
40 
45 public class CommunicationsManager {
46 
47  private static final Logger LOGGER = Logger.getLogger(CommunicationsManager.class.getName());
48 
49  private final SleuthkitCase db;
50 
51  private final Map<Account.Type, Integer> accountTypeToTypeIdMap
52  = new ConcurrentHashMap<Account.Type, Integer>();
53  private final Map<String, Account.Type> typeNameToAccountTypeMap
54  = new ConcurrentHashMap<String, Account.Type>();
55 
56  // Artifact types that represent a relationship between accounts
57  private final static Set<Integer> RELATIONSHIP_ARTIFACT_TYPE_IDS
58  = new HashSet<Integer>(Arrays.asList(
59  BlackboardArtifact.ARTIFACT_TYPE.TSK_MESSAGE.getTypeID(),
60  BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG.getTypeID(),
61  BlackboardArtifact.ARTIFACT_TYPE.TSK_CONTACT.getTypeID(),
62  BlackboardArtifact.ARTIFACT_TYPE.TSK_CALLLOG.getTypeID()
63  ));
64  private static final String RELATIONSHIP_ARTIFACT_TYPE_IDS_CSV_STR
65  = StringUtils.buildCSVString(RELATIONSHIP_ARTIFACT_TYPE_IDS);
66 
67  CommunicationsManager(SleuthkitCase db) throws TskCoreException {
68  this.db = db;
69 
70  initAccountTypes();
71  }
72 
78  private void initAccountTypes() throws TskCoreException {
79  CaseDbConnection connection = db.getConnection();
80  db.acquireSingleUserCaseWriteLock();
81  Statement statement = null;
82  ResultSet resultSet = null;
83 
84  try {
85  statement = connection.createStatement();
86  // Read the table
87  int count = readAccountTypes();
88  if (0 == count) {
89  // Table is empty, populate it with predefined types
90  for (Account.Type type : Account.Type.PREDEFINED_ACCOUNT_TYPES) {
91  try {
92  statement.execute("INSERT INTO account_types (type_name, display_name) VALUES ( '" + type.getTypeName() + "', '" + type.getDisplayName() + "')"); //NON-NLS
93  } catch (SQLException ex) {
94  resultSet = connection.executeQuery(statement, "SELECT COUNT(*) AS count FROM account_types WHERE type_name = '" + type.getTypeName() + "'"); //NON-NLS
95  resultSet.next();
96  if (resultSet.getLong("count") == 0) {
97  throw ex;
98  }
99  resultSet.close();
100  resultSet = null;
101  }
102 
103  ResultSet rs2 = connection.executeQuery(statement, "SELECT account_type_id FROM account_types WHERE type_name = '" + type.getTypeName() + "'"); //NON-NLS
104  rs2.next();
105  int typeID = rs2.getInt("account_type_id");
106  rs2.close();
107 
108  Account.Type accountType = new Account.Type(type.getTypeName(), type.getDisplayName());
109  this.accountTypeToTypeIdMap.put(accountType, typeID);
110  this.typeNameToAccountTypeMap.put(type.getTypeName(), accountType);
111  }
112  }
113  } catch (SQLException ex) {
114  LOGGER.log(Level.SEVERE, "Failed to add row to account_types", ex);
115  } finally {
116  closeResultSet(resultSet);
117  closeStatement(statement);
118  connection.close();
119  db.releaseSingleUserCaseWriteLock();
120  }
121  }
122 
133  private int readAccountTypes() throws SQLException, TskCoreException {
134  CaseDbConnection connection = db.getConnection();
135  db.acquireSingleUserCaseReadLock();
136  Statement statement = null;
137  ResultSet resultSet = null;
138  int count = 0;
139 
140  try {
141  statement = connection.createStatement();
142 
143  // If the account_types table is already populated, say when opening a case, then load it
144  resultSet = connection.executeQuery(statement, "SELECT COUNT(*) AS count FROM account_types"); //NON-NLS
145  resultSet.next();
146  if (resultSet.getLong("count") > 0) {
147 
148  resultSet.close();
149  resultSet = connection.executeQuery(statement, "SELECT * FROM account_types");
150  while (resultSet.next()) {
151  Account.Type accountType = new Account.Type(resultSet.getString("type_name"), resultSet.getString("display_name"));
152  this.accountTypeToTypeIdMap.put(accountType, resultSet.getInt("account_type_id"));
153  this.typeNameToAccountTypeMap.put(accountType.getTypeName(), accountType);
154  }
155  count = this.typeNameToAccountTypeMap.size();
156  }
157 
158  } catch (SQLException ex) {
159  LOGGER.log(Level.SEVERE, "Failed to read account_types", ex);
160  } finally {
161  closeResultSet(resultSet);
162  closeStatement(statement);
163  connection.close();
164  db.releaseSingleUserCaseReadLock();
165  }
166 
167  return count;
168  }
169 
175  SleuthkitCase getSleuthkitCase() {
176  return this.db;
177  }
178 
192  // NOTE: Full name given for Type for doxygen linking
193  public org.sleuthkit.datamodel.Account.Type addAccountType(String accountTypeName, String displayName) throws TskCoreException {
194  Account.Type accountType = new Account.Type(accountTypeName, displayName);
195 
196  // check if already in map
197  if (this.accountTypeToTypeIdMap.containsKey(accountType)) {
198  return accountType;
199  }
200 
201  CaseDbConnection connection = db.getConnection();
202  db.acquireSingleUserCaseWriteLock();
203  Statement s = null;
204  ResultSet rs = null;
205  try {
206  connection.beginTransaction();
207  s = connection.createStatement();
208  rs = connection.executeQuery(s, "SELECT * FROM account_types WHERE type_name = '" + accountTypeName + "'"); //NON-NLS
209  if (!rs.next()) {
210  rs.close();
211 
212  s.execute("INSERT INTO account_types (type_name, display_name) VALUES ( '" + accountTypeName + "', '" + displayName + "')"); //NON-NLS
213 
214  // Read back the typeID
215  rs = connection.executeQuery(s, "SELECT * FROM account_types WHERE type_name = '" + accountTypeName + "'"); //NON-NLS
216  rs.next();
217 
218  int typeID = rs.getInt("account_type_id");
219  accountType = new Account.Type(rs.getString("type_name"), rs.getString("display_name"));
220 
221  this.accountTypeToTypeIdMap.put(accountType, typeID);
222  this.typeNameToAccountTypeMap.put(accountTypeName, accountType);
223 
224  connection.commitTransaction();
225 
226  return accountType;
227  } else {
228  int typeID = rs.getInt("account_type_id");
229 
230  accountType = new Account.Type(rs.getString("type_name"), rs.getString("display_name"));
231  this.accountTypeToTypeIdMap.put(accountType, typeID);
232 
233  return accountType;
234  }
235  } catch (SQLException ex) {
236  connection.rollbackTransaction();
237  throw new TskCoreException("Error adding account type", ex);
238  } finally {
239  closeResultSet(rs);
240  closeStatement(s);
241  connection.close();
242  db.releaseSingleUserCaseWriteLock();
243  }
244  }
245 
263  // NOTE: Full name given for Type for doxygen linking
264  public AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile) throws TskCoreException {
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);
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 
298  // NOTE: Full name given for Type for doxygen linking
299  public Account getAccount(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID) throws TskCoreException {
300  Account account = null;
301  CaseDbConnection connection = db.getConnection();
302  db.acquireSingleUserCaseReadLock();
303  Statement s = null;
304  ResultSet rs = null;
305  try {
306  s = connection.createStatement();
307  rs = connection.executeQuery(s, "SELECT * FROM accounts WHERE account_type_id = " + getAccountTypeId(accountType)
308  + " AND account_unique_identifier = '" + normalizeAccountID(accountType, accountUniqueID) + "'"); //NON-NLS
309 
310  if (rs.next()) {
311  account = new Account(rs.getInt("account_id"), accountType,
312  rs.getString("account_unique_identifier"));
313  }
314  } catch (SQLException ex) {
315  throw new TskCoreException("Error getting account type id", ex);
316  } finally {
317  closeResultSet(rs);
318  closeStatement(s);
319  connection.close();
320  db.releaseSingleUserCaseReadLock();
321  }
322 
323  return account;
324  }
325 
326 // public AccountFileInstance getAccountFileInstance(BlackboardArtifact artifact) throws TskCoreException {
327 // AccountFileInstance accountInstance = null;
328 // if (artifact.getArtifactTypeID() == BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT.getTypeID()) {
329 // String accountTypeStr = artifact.getAttribute(new BlackboardAttribute.Type(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE)).getValueString();
330 // String accountID = artifact.getAttribute(new BlackboardAttribute.Type(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID)).getValueString();
331 // Account.Type accountType = getAccountType(accountTypeStr);
332 //
333 // Account account = getAccount(accountType, accountID);
334 // accountInstance = new AccountFileInstance(artifact, account);
335 // } else {
336 // throw new TskCoreException("Unexpected Artifact type = " + artifact.getArtifactTypeID());
337 // }
338 //
339 // return accountInstance;
340 // }
341 
363  // NOTE: Full name given for Type for doxygen linking
364  public void addRelationships(AccountFileInstance sender, List<AccountFileInstance> recipients,
365  BlackboardArtifact sourceArtifact, org.sleuthkit.datamodel.Relationship.Type relationshipType, long dateTime) throws TskCoreException, TskDataException {
366 
367  if (relationshipType.isCreatableFrom(sourceArtifact) == false) {
368  throw new TskDataException("Can not make a " + relationshipType.getDisplayName()
369  + " relationship from a" + sourceArtifact.getDisplayName());
370  }
371 
372  /*
373  * Enforce that all accounts and the relationship between them are from
374  * the same 'source'. This is required for the queries to work
375  * correctly.
376  */
377  // Currently we do not save the direction of communication
378  List<Long> accountIDs = new ArrayList<Long>();
379 
380  if (null != sender) {
381  accountIDs.add(sender.getAccount().getAccountID());
382  if (sender.getDataSourceObjectID() != sourceArtifact.getDataSourceObjectID()) {
383  throw new TskDataException("Sender and relationship are from different data sources :"
384  + "Sender source ID" + sender.getDataSourceObjectID() + " != relationship source ID" + sourceArtifact.getDataSourceObjectID());
385  }
386  }
387 
388  for (AccountFileInstance recipient : recipients) {
389  accountIDs.add(recipient.getAccount().getAccountID());
390  if (recipient.getDataSourceObjectID() != sourceArtifact.getDataSourceObjectID()) {
391  throw new TskDataException("Recipient and relationship are from different data sources :"
392  + "Recipient source ID" + recipient.getDataSourceObjectID() + " != relationship source ID" + sourceArtifact.getDataSourceObjectID());
393  }
394  }
395 
396  Set<UnorderedAccountPair> relationships = listToUnorderedPairs(accountIDs);
397  Iterator<UnorderedAccountPair> iter = relationships.iterator();
398 
399  while (iter.hasNext()) {
400  try {
401  UnorderedAccountPair accountPair = iter.next();
402  addAccountsRelationship(accountPair.getFirst(), accountPair.getSecond(),
403  sourceArtifact, relationshipType, dateTime);
404  } catch (TskCoreException ex) {
405  // @@@ This should probably not be caught and instead we stop adding
406  LOGGER.log(Level.WARNING, "Error adding relationship", ex); //NON-NLS
407  }
408  }
409  }
410 
423  private Account getOrCreateAccount(Account.Type accountType, String accountUniqueID) throws TskCoreException {
424  Account account = getAccount(accountType, accountUniqueID);
425  if (null == account) {
426  String query = " INTO accounts (account_type_id, account_unique_identifier) "
427  + "VALUES ( " + getAccountTypeId(accountType) + ", '"
428  + normalizeAccountID(accountType, accountUniqueID) + "'" + ")";
429  switch (db.getDatabaseType()) {
430  case POSTGRESQL:
431  query = "INSERT " + query + " ON CONFLICT DO NOTHING"; //NON-NLS
432  break;
433  case SQLITE:
434  query = "INSERT OR IGNORE " + query;
435  break;
436  default:
437  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
438  }
439 
440  CaseDbConnection connection = db.getConnection();
441  db.acquireSingleUserCaseWriteLock();
442  Statement s = null;
443  ResultSet rs = null;
444  try {
445  connection.beginTransaction();
446  s = connection.createStatement();
447 
448  s.execute(query);
449 
450  connection.commitTransaction();
451  account = getAccount(accountType, accountUniqueID);
452  } catch (SQLException ex) {
453  connection.rollbackTransaction();
454  throw new TskCoreException("Error adding an account", ex);
455  } finally {
456  closeResultSet(rs);
457  closeStatement(s);
458  connection.close();
459  db.releaseSingleUserCaseWriteLock();
460  }
461  }
462 
463  return account;
464  }
465 
480  BlackboardArtifact getOrCreateAccountFileInstanceArtifact(Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile) throws TskCoreException {
481 
482  // see if it already exists
483  BlackboardArtifact accountArtifact = getAccountFileInstanceArtifact(accountType, accountUniqueID, sourceFile);
484  if (null != accountArtifact) {
485  return accountArtifact;
486  }
487 
488  // Create a new artifact.
489  accountArtifact = db.newBlackboardArtifact(BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT, sourceFile.getId());
490 
491  Collection<BlackboardAttribute> attributes = new ArrayList<BlackboardAttribute>();
492  attributes.add(new BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE, moduleName, accountType.getTypeName()));
493  attributes.add(new BlackboardAttribute(BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID, moduleName, accountUniqueID));
494  accountArtifact.addAttributes(attributes);
495 
496  return accountArtifact;
497  }
498 
512  private BlackboardArtifact getAccountFileInstanceArtifact(Account.Type accountType, String accountUniqueID, Content sourceFile) throws TskCoreException {
513  BlackboardArtifact accountArtifact = null;
514  CaseDbConnection connection = db.getConnection();
515  db.acquireSingleUserCaseReadLock();
516  Statement s = null;
517  ResultSet rs = null;
518 
519  try {
520  s = connection.createStatement();
521  String queryStr = "SELECT artifacts.artifact_id AS artifact_id,"
522  + " artifacts.obj_id AS obj_id,"
523  + " artifacts.artifact_obj_id AS artifact_obj_id,"
524  + " artifacts.data_source_obj_id AS data_source_obj_id,"
525  + " artifacts.artifact_type_id AS artifact_type_id,"
526  + " artifacts.review_status_id AS review_status_id"
527  + " FROM blackboard_artifacts AS artifacts"
528  + " JOIN blackboard_attributes AS attr_account_type"
529  + " ON artifacts.artifact_id = attr_account_type.artifact_id"
530  + " JOIN blackboard_attributes AS attr_account_id"
531  + " ON artifacts.artifact_id = attr_account_id.artifact_id"
532  + " AND attr_account_id.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ID.getTypeID()
533  + " AND attr_account_id.value_text = '" + accountUniqueID + "'"
534  + " WHERE artifacts.artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_ACCOUNT.getTypeID()
535  + " AND attr_account_type.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE.getTypeID()
536  + " AND attr_account_type.value_text = '" + accountType.getTypeName() + "'"
537  + " AND artifacts.obj_id = " + sourceFile.getId(); //NON-NLS
538 
539  rs = connection.executeQuery(s, queryStr); //NON-NLS
540  if (rs.next()) {
541  BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
542 
543  accountArtifact = new BlackboardArtifact(db, rs.getLong("artifact_id"), rs.getLong("obj_id"), rs.getLong("artifact_obj_id"), rs.getLong("data_source_obj_id"),
544  bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
545  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id")));
546  }
547  } catch (SQLException ex) {
548  throw new TskCoreException("Error getting account", ex);
549  } finally {
550  closeResultSet(rs);
551  closeStatement(s);
552  connection.close();
553  db.releaseSingleUserCaseReadLock();
554  }
555 
556  return accountArtifact;
557  }
558 
569  // NOTE: Full name given for Type for doxygen linking
570  public org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName) throws TskCoreException {
571  if (this.typeNameToAccountTypeMap.containsKey(accountTypeName)) {
572  return this.typeNameToAccountTypeMap.get(accountTypeName);
573  }
574 
575  CaseDbConnection connection = db.getConnection();
576  db.acquireSingleUserCaseReadLock();
577  Statement s = null;
578  ResultSet rs = null;
579 
580  try {
581  s = connection.createStatement();
582  rs = connection.executeQuery(s, "SELECT account_type_id, type_name, display_name, value_type FROM account_types WHERE type_name = '" + accountTypeName + "'"); //NON-NLS
583  Account.Type accountType = null;
584  if (rs.next()) {
585  accountType = new Account.Type(accountTypeName, rs.getString("display_name"));
586  this.accountTypeToTypeIdMap.put(accountType, rs.getInt("account_type_id"));
587  this.typeNameToAccountTypeMap.put(accountTypeName, accountType);
588  }
589  return accountType;
590  } catch (SQLException ex) {
591  throw new TskCoreException("Error getting account type id", ex);
592  } finally {
593  closeResultSet(rs);
594  closeStatement(s);
595  connection.close();
596  db.releaseSingleUserCaseReadLock();
597  }
598  }
599 
611  private Account getAccount(long account_id) throws TskCoreException {
612  Account account = null;
613  CaseDbConnection connection = db.getConnection();
614  db.acquireSingleUserCaseReadLock();
615  Statement s = null;
616  ResultSet rs = null;
617 
618  try {
619  s = connection.createStatement();
620  rs = connection.executeQuery(s, "SELECT account_types.type_name as type_name,"
621  + " account_types.display_name as display_name,"
622  + " accounts.account_id as account_id,"
623  + " accounts.account_unique_identifier as account_unique_identifier"
624  + " FROM accounts as accounts"
625  + " JOIN account_types as account_types"
626  + " ON accounts.account_type_id = account_types.account_type_id"
627  + " WHERE accounts.account_id = " + account_id); //NON-NLS
628 
629  if (rs.next()) {
630  Account.Type accountType = new Account.Type(rs.getString("type_name"), rs.getString("display_name"));
631  account = new Account(rs.getInt("account_id"), accountType, rs.getString("account_unique_identifier"));
632  }
633  } catch (SQLException ex) {
634  throw new TskCoreException("Error getting account from account_id", ex);
635  } finally {
636  closeResultSet(rs);
637  closeStatement(s);
638  connection.close();
639  db.releaseSingleUserCaseReadLock();
640  }
641 
642  return account;
643  }
644 
658  private void addAccountsRelationship(long account1_id, long account2_id, BlackboardArtifact relationshipaArtifact, Relationship.Type relationshipType, long dateTime) throws TskCoreException {
659  CaseDbConnection connection = db.getConnection();
660  db.acquireSingleUserCaseWriteLock();
661  Statement s = null;
662  ResultSet rs = null;
663 
664  try {
665  String dateTimeValStr = (dateTime > 0) ? Long.toString(dateTime) : "NULL";
666 
667  connection.beginTransaction();
668  s = connection.createStatement();
669  String query = "INTO account_relationships (account1_id, account2_id, relationship_source_obj_id, date_time, relationship_type, data_source_obj_id ) "
670  + "VALUES ( " + account1_id + ", " + account2_id + ", " + relationshipaArtifact.getId() + ", " + dateTimeValStr + ", " + relationshipType.getTypeID() + ", " + relationshipaArtifact.getDataSourceObjectID() + ")";
671  switch (db.getDatabaseType()) {
672  case POSTGRESQL:
673  query = "INSERT " + query + " ON CONFLICT DO NOTHING";
674  break;
675  case SQLITE:
676  query = "INSERT OR IGNORE " + query;
677  break;
678  default:
679  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
680  }
681  s.execute(query); //NON-NLS
682  connection.commitTransaction();
683  } catch (SQLException ex) {
684  connection.rollbackTransaction();
685  throw new TskCoreException("Error adding accounts relationship", ex);
686  } finally {
687  closeResultSet(rs);
688  closeStatement(s);
689  connection.close();
690  db.releaseSingleUserCaseWriteLock();
691  }
692  }
693 
708  public List<AccountDeviceInstance> getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter) throws TskCoreException {
709  CaseDbConnection connection = db.getConnection();
710  db.acquireSingleUserCaseReadLock();
711  Statement s = null;
712  ResultSet rs = null;
713 
714  try {
715  s = connection.createStatement();
716 
717  //set up applicable filters
718  Set<String> applicableInnerQueryFilters = new HashSet<String>(Arrays.asList(
719  CommunicationsFilter.DateRangeFilter.class.getName(),
720  CommunicationsFilter.DeviceFilter.class.getName(),
721  CommunicationsFilter.RelationshipTypeFilter.class.getName()
722  ));
723  String innerQueryfilterSQL = getCommunicationsFilterSQL(filter, applicableInnerQueryFilters);
724 
725  String innerQueryTemplate
726  = " SELECT %1$1s as account_id,"
727  + " data_source_obj_id"
728  + " FROM account_relationships as relationships"
729  + (innerQueryfilterSQL.isEmpty() ? "" : " WHERE " + innerQueryfilterSQL);
730 
731  String innerQuery1 = String.format(innerQueryTemplate, "account1_id");
732  String innerQuery2 = String.format(innerQueryTemplate, "account2_id");
733 
734  //this query groups by account_id and data_source_obj_id across both innerQueries
735  String combinedInnerQuery
736  = "SELECT count(*) as relationship_count, account_id, data_source_obj_id "
737  + " FROM ( " + innerQuery1 + " UNION " + innerQuery2 + " ) AS inner_union"
738  + " GROUP BY account_id, data_source_obj_id";
739 
740  // set up applicable filters
741  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
742  CommunicationsFilter.AccountTypeFilter.class.getName()
743  ));
744 
745  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
746 
747  String queryStr
748  = //account info
749  " accounts.account_id AS account_id,"
750  + " accounts.account_unique_identifier AS account_unique_identifier,"
751  //account type info
752  + " account_types.type_name AS type_name,"
753  //Account device instance info
754  + " relationship_count,"
755  + " data_source_info.device_id AS device_id"
756  + " FROM ( " + combinedInnerQuery + " ) AS account_device_instances"
757  + " JOIN accounts AS accounts"
758  + " ON accounts.account_id = account_device_instances.account_id"
759  + " JOIN account_types AS account_types"
760  + " ON accounts.account_type_id = account_types.account_type_id"
761  + " JOIN data_source_info AS data_source_info"
762  + " ON account_device_instances.data_source_obj_id = data_source_info.obj_id"
763  + (filterSQL.isEmpty() ? "" : " WHERE " + filterSQL);
764 
765  switch (db.getDatabaseType()) {
766  case POSTGRESQL:
767  queryStr = "SELECT DISTINCT ON ( accounts.account_id, data_source_info.device_id) " + queryStr;
768  break;
769  case SQLITE:
770  queryStr = "SELECT " + queryStr + " GROUP BY accounts.account_id, data_source_info.device_id";
771  break;
772  default:
773  throw new TskCoreException("Unknown DB Type: " + db.getDatabaseType().name());
774  }
775 
776  rs = connection.executeQuery(s, queryStr); //NON-NLS
777  ArrayList<AccountDeviceInstance> accountDeviceInstances = new ArrayList<AccountDeviceInstance>();
778  while (rs.next()) {
779  long account_id = rs.getLong("account_id");
780  String deviceID = rs.getString("device_id");
781  final String type_name = rs.getString("type_name");
782  final String account_unique_identifier = rs.getString("account_unique_identifier");
783 
784  Account.Type accountType = typeNameToAccountTypeMap.get(type_name);
785  Account account = new Account(account_id, accountType, account_unique_identifier);
786  accountDeviceInstances.add(new AccountDeviceInstance(account, deviceID));
787  }
788 
789  return accountDeviceInstances;
790  } catch (SQLException ex) {
791  throw new TskCoreException("Error getting account device instances. " + ex.getMessage(), ex);
792  } finally {
793  closeResultSet(rs);
794  closeStatement(s);
795  connection.close();
796  db.releaseSingleUserCaseReadLock();
797  }
798  }
799 
814  public long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter) throws TskCoreException {
815 
816  long account_id = accountDeviceInstance.getAccount().getAccountID();
817 
818  // Get the list of Data source objects IDs correpsonding to this DeviceID.
819  String datasourceObjIdsCSV = StringUtils.buildCSVString(
820  db.getDataSourceObjIds(accountDeviceInstance.getDeviceId()));
821 
822  // set up applicable filters
823  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
824  CommunicationsFilter.RelationshipTypeFilter.class.getName(),
825  CommunicationsFilter.DateRangeFilter.class.getName()
826  ));
827  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
828 
829  CaseDbConnection connection = db.getConnection();
830  db.acquireSingleUserCaseReadLock();
831  Statement s = null;
832  ResultSet rs = null;
833 
834  try {
835  s = connection.createStatement();
836 
837  String queryStr
838  = "SELECT count(DISTINCT relationships.relationship_source_obj_id) as count "
839  + " FROM account_relationships AS relationships"
840  + " WHERE relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV + " )"
841  + " AND ( relationships.account1_id = " + account_id
842  + " OR relationships.account2_id = " + account_id + " )"
843  + (filterSQL.isEmpty() ? "" : " AND " + filterSQL);
844 
845  rs = connection.executeQuery(s, queryStr); //NON-NLS
846  rs.next();
847  return (rs.getLong("count"));
848  } catch (SQLException ex) {
849  throw new TskCoreException("Error getting relationships count for account device instance. " + ex.getMessage(), ex);
850  } finally {
851  closeResultSet(rs);
852  closeStatement(s);
853  connection.close();
854  db.releaseSingleUserCaseReadLock();
855  }
856  }
857 
872  public Set<Content> getRelationshipSources(Set<AccountDeviceInstance> accountDeviceInstanceList, CommunicationsFilter filter) throws TskCoreException {
873 
874  if (accountDeviceInstanceList.isEmpty()) {
875  //log this?
876  return Collections.emptySet();
877  }
878 
879  Map<Long, Set<Long>> accountIdToDatasourceObjIdMap = new HashMap<Long, Set<Long>>();
880  for (AccountDeviceInstance accountDeviceInstance : accountDeviceInstanceList) {
881  long accountID = accountDeviceInstance.getAccount().getAccountID();
882  List<Long> dataSourceObjIds = db.getDataSourceObjIds(accountDeviceInstance.getDeviceId());
883 
884  if (accountIdToDatasourceObjIdMap.containsKey(accountID)) {
885  accountIdToDatasourceObjIdMap.get(accountID).addAll(dataSourceObjIds);
886  } else {
887  accountIdToDatasourceObjIdMap.put(accountID, new HashSet<Long>(dataSourceObjIds));
888  }
889  }
890 
891  List<String> adiSQLClauses = new ArrayList<String>();
892  for (Map.Entry<Long, Set<Long>> entry : accountIdToDatasourceObjIdMap.entrySet()) {
893  final Long accountID = entry.getKey();
894  String datasourceObjIdsCSV = StringUtils.buildCSVString(entry.getValue());
895 
896  adiSQLClauses.add(
897  "( ( relationships.data_source_obj_id IN ( " + datasourceObjIdsCSV + " ) )"
898  + " AND ( relationships.account1_id = " + accountID
899  + " OR relationships.account2_id = " + accountID + " ) )"
900  );
901  }
902  String adiSQLClause = StringUtils.joinAsStrings(adiSQLClauses, " OR ");
903 
904  // set up applicable filters
905  Set<String> applicableFilters = new HashSet<String>(Arrays.asList(
906  CommunicationsFilter.RelationshipTypeFilter.class.getName(),
907  CommunicationsFilter.DateRangeFilter.class.getName()
908  ));
909  String filterSQL = getCommunicationsFilterSQL(filter, applicableFilters);
910 
911  CaseDbConnection connection = db.getConnection();
912  db.acquireSingleUserCaseReadLock();
913  Statement s = null;
914  ResultSet rs = null;
915 
916  try {
917  s = connection.createStatement();
918  String queryStr
919  = "SELECT DISTINCT artifacts.artifact_id AS artifact_id,"
920  + " artifacts.obj_id AS obj_id,"
921  + " artifacts.artifact_obj_id AS artifact_obj_id,"
922  + " artifacts.data_source_obj_id AS data_source_obj_id, "
923  + " artifacts.artifact_type_id AS artifact_type_id, "
924  + " artifacts.review_status_id AS review_status_id "
925  + " FROM blackboard_artifacts as artifacts"
926  + " JOIN account_relationships AS relationships"
927  + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
928  // append sql to restrict search to specified account device instances
929  + " WHERE (" + adiSQLClause + " )"
930  // plus other filters
931  + (filterSQL.isEmpty() ? "" : " AND (" + filterSQL + " )");
932 
933  rs = connection.executeQuery(s, queryStr); //NON-NLS
934  Set<Content> relationshipSources = new HashSet<Content>();
935  while (rs.next()) {
936  BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
937  relationshipSources.add(new BlackboardArtifact(db, rs.getLong("artifact_id"),
938  rs.getLong("obj_id"), rs.getLong("artifact_obj_id"),
939  rs.getLong("data_source_obj_id"), bbartType.getTypeID(),
940  bbartType.getTypeName(), bbartType.getDisplayName(),
941  BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id"))));
942  }
943 
944  return relationshipSources;
945  } catch (SQLException ex) {
946  throw new TskCoreException("Error getting relationships for account. " + ex.getMessage(), ex);
947  } finally {
948  closeResultSet(rs);
949  closeStatement(s);
950  connection.close();
951  db.releaseSingleUserCaseReadLock();
952  }
953  }
954 
962  int getAccountTypeId(Account.Type accountType) {
963  if (accountTypeToTypeIdMap.containsKey(accountType)) {
964  return accountTypeToTypeIdMap.get(accountType);
965  }
966 
967  return 0;
968  }
969 
977  private Set<UnorderedAccountPair> listToUnorderedPairs(List<Long> account_ids) {
978  Set<UnorderedAccountPair> relationships = new HashSet<UnorderedAccountPair>();
979 
980  for (int i = 0; i < account_ids.size(); i++) {
981  for (int j = i + 1; j < account_ids.size(); j++) {
982  relationships.add(new UnorderedAccountPair(account_ids.get(i), account_ids.get(j)));
983  }
984  }
985 
986  return relationships;
987  }
988 
989  private String normalizeAccountID(Account.Type accountType, String accountUniqueID) {
990  String normailzeAccountID = accountUniqueID;
991 
992  if (accountType.equals(Account.Type.PHONE)) {
993  normailzeAccountID = normalizePhoneNum(accountUniqueID);
994  } else if (accountType.equals(Account.Type.EMAIL)) {
995  normailzeAccountID = normalizeEmailAddress(accountUniqueID);
996  }
997 
998  return normailzeAccountID;
999  }
1000 
1001  private String normalizePhoneNum(String phoneNum) {
1002  String normailzedPhoneNum = phoneNum.replaceAll("\\D", "");
1003 
1004  if (phoneNum.startsWith("+")) {
1005  normailzedPhoneNum = "+" + normailzedPhoneNum;
1006  }
1007 
1008  return normailzedPhoneNum;
1009  }
1010 
1011  private String normalizeEmailAddress(String emailAddress) {
1012  String normailzedEmailAddr = emailAddress.toLowerCase();
1013 
1014  return normailzedEmailAddr;
1015  }
1016 
1029  private String getCommunicationsFilterSQL(CommunicationsFilter commFilter, Set<String> applicableFilters) {
1030  if (null == commFilter || commFilter.getAndFilters().isEmpty()) {
1031  return "";
1032  }
1033 
1034  String sqlStr = "";
1035  StringBuilder sqlSB = new StringBuilder();
1036  boolean first = true;
1037  for (CommunicationsFilter.SubFilter subFilter : commFilter.getAndFilters()) {
1038 
1039  // If the filter is applicable
1040  if (applicableFilters.contains(subFilter.getClass().getName())) {
1041  String subfilterSQL = subFilter.getSQL(this);
1042  if (!subfilterSQL.isEmpty()) {
1043  if (first) {
1044  first = false;
1045  } else {
1046  sqlSB.append(" AND ");
1047  }
1048  sqlSB.append("( ");
1049  sqlSB.append(subfilterSQL);
1050  sqlSB.append(" )");
1051  }
1052  }
1053  }
1054 
1055  if (!sqlSB.toString().isEmpty()) {
1056  sqlStr = "( " + sqlSB.toString() + " )";
1057  }
1058  return sqlStr;
1059  }
1060 
1065  private final class UnorderedAccountPair {
1066 
1067  private final long account1_id;
1068  private final long account2_id;
1069 
1070  UnorderedAccountPair(long account1_id, long account2_id) {
1071  this.account1_id = account1_id;
1072  this.account2_id = account2_id;
1073  }
1074 
1075  @Override
1076  public int hashCode() {
1077  return new Long(account1_id).hashCode() + new Long(account2_id).hashCode();
1078  }
1079 
1080  @Override
1081  public boolean equals(Object other) {
1082  if (other == this) {
1083  return true;
1084  }
1085  if (!(other instanceof UnorderedAccountPair)) {
1086  return false;
1087  }
1088 
1089  UnorderedAccountPair otherPair = (UnorderedAccountPair) other;
1090  return ((account1_id == otherPair.account1_id && account2_id == otherPair.account2_id)
1091  || (account1_id == otherPair.account2_id && account2_id == otherPair.account1_id));
1092  }
1093 
1094  public long getFirst() {
1095  return account1_id;
1096  }
1097 
1098  public long getSecond() {
1099  return account2_id;
1100  }
1101  }
1102 
1103 // /**
1104 // * Get all account instances of a given type
1105 // *
1106 // * @param accountType account type
1107 // *
1108 // * @return List <Account.Type>, list of accounts
1109 // *
1110 // * @throws TskCoreException exception thrown if a critical error occurs
1111 // * within TSK core
1112 // */
1113 // public List<AccountInstance> getAccountInstances(Account.Type accountType) throws TskCoreException {
1114 // List<AccountInstance> accountInstances = new ArrayList<AccountInstance>();
1115 //
1116 // // First get all accounts of the type
1117 // List<Account> accounts = getAccounts(accountType);
1118 //
1119 // // get all instances for each account
1120 // for (Account account : accounts) {
1121 // List<Long> accountInstanceIds = getAccountInstanceIds(account.getAccountID());
1122 //
1123 // for (long artifact_id : accountInstanceIds) {
1124 // accountInstances.add(new AccountFileInstance(db, db.getBlackboardArtifact(artifact_id), account));
1125 // }
1126 // }
1127 //
1128 // return accountInstances;
1129 // }
1130 // /**
1131 // * Given an account ID, returns the ids of all instances of the account
1132 // *
1133 // * @param account_id account id
1134 // *
1135 // * @return List <Long>, list of account instance IDs
1136 // *
1137 // * @throws TskCoreException exception thrown if a critical error occurs
1138 // * within TSK core
1139 // */
1140 // List<Long> getAccountInstanceIds(long account_id) throws TskCoreException {
1141 // ArrayList<Long> accountInstanceIDs = new ArrayList<Long>();
1142 // CaseDbConnection connection = db.getConnection();
1143 // db.acquireSingleUserCaseReadLock();
1144 // Statement s = null;
1145 // ResultSet rs = null;
1146 //
1147 // try {
1148 // s = connection.createStatement();
1149 // rs = connection.executeQuery(s, "SELECT * FROM account_to_instances_map WHERE account_id = " + account_id); //NON-NLS
1150 // while (rs.next()) {
1151 // accountInstanceIDs.add(rs.getLong("account_instance_id"));
1152 // }
1153 // return accountInstanceIDs;
1154 // } catch (SQLException ex) {
1155 // throw new TskCoreException("Error getting account_instance_id from by account_id. " + ex.getMessage(), ex);
1156 // } finally {
1157 // closeResultSet(rs);
1158 // closeStatement(s);
1159 // connection.close();
1160 // db.releaseSingleUserCaseReadLock();
1161 // }
1162 // }
1163 // /**
1164 // * Get all account types in use
1165 // *
1166 // * @return List <Account.Type>, list of account types in use
1167 // *
1168 // * @throws TskCoreException exception thrown if a critical error occurs
1169 // * within TSK core
1170 // */
1171 // public List<Account.Type> getAccountTypesInUse() throws TskCoreException {
1172 // String query = "SELECT DISTINCT value_text FROM blackboard_attributes "
1173 // + "WHERE attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_ACCOUNT_TYPE.getTypeID();
1174 // CaseDbConnection connection = db.getConnection();
1175 // db.acquireSingleUserCaseReadLock();
1176 // Statement s = null;
1177 // ResultSet rs = null;
1178 //
1179 // try {
1180 // s = connection.createStatement();
1181 // rs = connection.executeQuery(s, query);
1182 // ArrayList<Account.Type> usedAccountTypes = new ArrayList<Account.Type>();
1183 // while (rs.next()) {
1184 // String accountTypeString = rs.getString("value_text");
1185 // usedAccountTypes.add(getAccountType(accountTypeString));
1186 // }
1187 //
1188 // return usedAccountTypes;
1189 // } catch (SQLException ex) {
1190 // throw new TskCoreException("Error getting account types in use", ex);
1191 // } finally {
1192 // closeResultSet(rs);
1193 // closeStatement(s);
1194 // connection.close();
1195 // db.releaseSingleUserCaseReadLock();
1196 // }
1197 // }
1198 // /**
1199 // * Get all accounts of given type
1200 // *
1201 // * @param accountType account type
1202 // *
1203 // * @return List <Account.Type>, list of accounts
1204 // *
1205 // * @throws TskCoreException exception thrown if a critical error occurs
1206 // * within TSK core
1207 // */
1208 // public List<Account> getAccounts(Account.Type accountType) throws TskCoreException {
1209 // ArrayList<Account> accounts = new ArrayList<Account>();
1210 // CaseDbConnection connection = db.getConnection();
1211 // db.acquireSingleUserCaseReadLock();
1212 // Statement s = null;
1213 // ResultSet rs = null;
1214 //
1215 // try {
1216 // s = connection.createStatement();
1217 // rs = connection.executeQuery(s, "SELECT * FROM accounts WHERE account_type_id = " + getAccountTypeId(accountType)); //NON-NLS
1218 // while (rs.next()) {
1219 // accounts.add(new Account(rs.getInt("account_id"), accountType,
1220 // rs.getString("account_unique_identifier")));
1221 //
1222 // }
1223 //
1224 // return accounts;
1225 // } catch (SQLException ex) {
1226 // throw new TskCoreException("Error getting accounts by type. " + ex.getMessage(), ex);
1227 // } finally {
1228 // closeResultSet(rs);
1229 // closeStatement(s);
1230 // connection.close();
1231 // db.releaseSingleUserCaseReadLock();
1232 // }
1233 // }
1234 // /**
1235 // * Get all accounts that have a relationship with the given account
1236 // *
1237 // * @param account account for which to search relationships
1238 // *
1239 // * @return list of accounts with relationships to the given account
1240 // *
1241 // * @throws TskCoreException exception thrown if a critical error occurs
1242 // * within TSK core
1243 // */
1244 // public List<Account> getAccountsWithRelationship(Account account) throws TskCoreException {
1245 // return getAccountsWithRelationship(account.getAccountID());
1246 // }
1247 // /**
1248 // * Get all account that have a relationship with a given account
1249 // *
1250 // * @param account_id account id
1251 // *
1252 // * @return List<Account>, list of accounts
1253 // *
1254 // * @throws TskCoreException exception thrown if a critical error occurs
1255 // * within TSK core
1256 // */
1257 // List<Account> getAccountsWithRelationship(long accountID) throws TskCoreException {
1258 // CaseDbConnection connection = db.getConnection();
1259 // db.acquireSingleUserCaseReadLock();
1260 // Statement s = null;
1261 // ResultSet rs = null;
1262 //
1263 // try {
1264 // s = connection.createStatement();
1265 // rs = connection.executeQuery(s, "SELECT account1_id, account2_id "
1266 // + " FROM relationships"
1267 // + " WHERE account1_id = " + accountID
1268 // + " OR account2_id = " + accountID); //NON-NLS
1269 //
1270 // ArrayList<Account> accounts = new ArrayList<Account>();
1271 // while (rs.next()) {
1272 // long otherAccountID = (accountID == rs.getLong("account1_id")) ? rs.getLong("account2_id") : rs.getLong("account1_id");
1273 // Account account = getAccount(otherAccountID);
1274 // if (null != account) {
1275 // accounts.add(account);
1276 // }
1277 // }
1278 //
1279 // return accounts;
1280 // } catch (SQLException ex) {
1281 // throw new TskCoreException("Error getting relationships by account by ID. " + ex.getMessage(), ex);
1282 // } finally {
1283 // closeResultSet(rs);
1284 // closeStatement(s);
1285 // connection.close();
1286 // db.releaseSingleUserCaseReadLock();
1287 // }
1288 // }
1289 // /**
1290 // * Return folders found in the email source file
1291 // *
1292 // * @param srcObjID pbjectID of the email PST/Mbox source file
1293 // *
1294 // * @return list of message folders
1295 // *
1296 // * @throws TskCoreException exception thrown if a critical error occurs
1297 // * within TSK core
1298 // */
1299 // public List<MessageFolder> getMessageFolders(long srcObjID) throws TskCoreException {
1300 // CaseDbConnection connection = db.getConnection();
1301 // db.acquireSingleUserCaseReadLock();
1302 // Statement s = null;
1303 // ResultSet rs = null;
1304 //
1305 // try {
1306 // s = connection.createStatement();
1307 // rs = connection.executeQuery(s, "SELECT DISTINCT attributes.value_text AS folder_path"
1308 // + " FROM blackboard_artifacts AS artifacts"
1309 // + " JOIN blackboard_attributes AS attributes"
1310 // + " ON artifacts.artifact_id = attributes.artifact_id"
1311 // + " AND artifacts.obj_id = " + srcObjID
1312 // + " WHERE artifacts.artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG.getTypeID()
1313 // + " AND attributes.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PATH.getTypeID()
1314 // ); //NON-NLS
1315 //
1316 // ArrayList<MessageFolder> messageFolders = new ArrayList<MessageFolder>();
1317 // while (rs.next()) {
1318 // String folder = rs.getString("folder_path");
1319 //
1320 // // TBD: check if this folder has subfolders and set hasSubFolders accordingly.
1321 // messageFolders.add(new MessageFolder(folder, srcObjID));
1322 // }
1323 //
1324 // return messageFolders;
1325 // } catch (SQLException ex) {
1326 // throw new TskCoreException("Error getting message folders. " + ex.getMessage(), ex);
1327 // } finally {
1328 // closeResultSet(rs);
1329 // closeStatement(s);
1330 // connection.close();
1331 // db.releaseSingleUserCaseReadLock();
1332 // }
1333 //
1334 // }
1335 //
1336 // /**
1337 // * Return subfolders found in the email source file under the specified
1338 // * folder
1339 // *
1340 // * @param srcObjID objectID of the email PST/Mbox source file
1341 // * @param parentfolder parent folder of messages to return
1342 // *
1343 // * @return list of message sub-folders
1344 // *
1345 // * @throws TskCoreException exception thrown if a critical error occurs
1346 // * within TSK core
1347 // */
1348 // public List<MessageFolder> getMessageFolders(long srcObjID, MessageFolder parentfolder) throws TskCoreException {
1349 // CaseDbConnection connection = db.getConnection();
1350 // db.acquireSingleUserCaseReadLock();
1351 // Statement s = null;
1352 // ResultSet rs = null;
1353 //
1354 // try {
1355 // s = connection.createStatement();
1356 // rs = connection.executeQuery(s, "SELECT DISTINCT attributes.value_text AS folder_path"
1357 // + " FROM blackboard_artifacts AS artifacts"
1358 // + " JOIN blackboard_attributes AS attributes"
1359 // + " ON artifacts.artifact_id = attributes.artifact_id"
1360 // + " AND artifacts.obj_id = " + srcObjID
1361 // + " WHERE artifacts.artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG.getTypeID()
1362 // + " AND attributes.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PATH.getTypeID()
1363 // + " AND attributes.value_text LIKE '" + parentfolder.getName() + "%'"
1364 // ); //NON-NLS
1365 //
1366 // ArrayList<MessageFolder> messageFolders = new ArrayList<MessageFolder>();
1367 // while (rs.next()) {
1368 // String folder = rs.getString("folder_path");
1369 // messageFolders.add(new MessageFolder(folder, srcObjID));
1370 // }
1371 //
1372 // return messageFolders;
1373 // } catch (SQLException ex) {
1374 // throw new TskCoreException("Error getting message folders. " + ex.getMessage(), ex);
1375 // } finally {
1376 // closeResultSet(rs);
1377 // closeStatement(s);
1378 // connection.close();
1379 // db.releaseSingleUserCaseReadLock();
1380 // }
1381 //
1382 // }
1383 // /**
1384 // * Return email messages under given folder
1385 // *
1386 // * @param parentfolder parent folder of messages to return
1387 // *
1388 // * @return list of messages
1389 // *
1390 // * @throws TskCoreException exception thrown if a critical error occurs
1391 // * within TSK core
1392 // */
1393 // public List<BlackboardArtifact> getMessages(MessageFolder parentfolder) throws TskCoreException {
1394 // CaseDbConnection connection = db.getConnection();
1395 // db.acquireSingleUserCaseReadLock();
1396 // Statement s = null;
1397 // ResultSet rs = null;
1398 //
1399 // try {
1400 // s = connection.createStatement();
1401 // rs = connection.executeQuery(s, "SELECT artifacts.artifact_id AS artifact_id,"
1402 // + " artifacts.obj_id AS obj_id,"
1403 // + " artifacts.artifact_obj_id AS artifact_obj_id,"
1404 // + " artifacts.data_source_obj_id AS data_source_obj_id, "
1405 // + " artifacts.artifact_type_id AS artifact_type_id, "
1406 // + " artifacts.review_status_id AS review_status_id, "
1407 // + " FROM blackboard_artifacts AS artifacts"
1408 // + " JOIN blackboard_attributes AS attributes"
1409 // + " ON artifacts.artifact_id = attributes.artifact_id"
1410 // + " AND artifacts.artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG.getTypeID()
1411 // + " WHERE attributes.attribute_type_id = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_PATH.getTypeID()
1412 // + " AND attributes.value_text = '" + parentfolder.getName() + "' "
1413 // ); //NON-NLS
1414 //
1415 // ArrayList<BlackboardArtifact> artifacts = new ArrayList<BlackboardArtifact>();
1416 // while (rs.next()) {
1417 // BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
1418 // artifacts.add(new BlackboardArtifact(db, rs.getLong("artifact_id"), rs.getLong("obj_id"), rs.getLong("artifact_obj_id"), rs.getLong("data_source_obj_id"),
1419 // bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
1420 // BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id"))));
1421 // }
1422 //
1423 // return artifacts;
1424 // } catch (SQLException ex) {
1425 // throw new TskCoreException("Error getting messages. " + ex.getMessage(), ex);
1426 // } finally {
1427 // closeResultSet(rs);
1428 // closeStatement(s);
1429 // connection.close();
1430 // db.releaseSingleUserCaseReadLock();
1431 // }
1432 //
1433 // }
1434 // /**
1435 // * Returns unique relation types between two accounts
1436 // *
1437 // * @param account1 account
1438 // * @param account2 account
1439 // *
1440 // * @return list of unique relationship types between two accounts
1441 // *
1442 // * @throws TskCoreException exception thrown if a critical error occurs
1443 // * within TSK core
1444 // */
1445 // public List<BlackboardArtifact.Type> getRelationshipTypes(Account account1, Account account2) throws TskCoreException {
1446 // return getRelationshipTypes(account1.getAccountID(), account2.getAccountID());
1447 // }
1448 // /**
1449 // * Returns unique relation types between two accounts
1450 // *
1451 // * @param account1_id account1 artifact ID
1452 // * @param account2_id account2 artifact ID
1453 // *
1454 // * @throws TskCoreException exception thrown if a critical error occurs
1455 // * within TSK core
1456 // */
1457 // List<BlackboardArtifact.Type> getRelationshipTypes(long account1_id, long account2_id) throws TskCoreException {
1458 // CaseDbConnection connection = db.getConnection();
1459 // db.acquireSingleUserCaseReadLock();
1460 // Statement s = null;
1461 // ResultSet rs = null;
1462 //
1463 // try {
1464 // s = connection.createStatement();
1465 // rs = connection.executeQuery(s, "SELECT DISTINCT artifacts.artifact_id AS artifact_id,"
1466 // + " artifacts.obj_id AS obj_id,"
1467 // + " artifacts.artifact_obj_id AS artifact_obj_id,"
1468 // + " artifacts.artifact_type_id AS artifact_type_id,"
1469 // + " artifacts.review_status_id AS review_status_id"
1470 // + " FROM blackboard_artifacts AS artifacts"
1471 // + " JOIN relationships AS relationships"
1472 // + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
1473 // + " WHERE relationships.account1_id IN ( " + account1_id + ", " + account2_id + " )"
1474 // + " AND relationships.account2_id IN ( " + account1_id + ", " + account2_id + " )"
1475 // ); //NON-NLS
1476 //
1477 // ArrayList<BlackboardArtifact.Type> artifactTypes = new ArrayList<BlackboardArtifact.Type>();
1478 // while (rs.next()) {
1479 // BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
1480 // artifactTypes.add(bbartType);
1481 // }
1482 //
1483 // return artifactTypes;
1484 // } catch (SQLException ex) {
1485 // throw new TskCoreException("Error getting relationship types." + ex.getMessage(), ex);
1486 // } finally {
1487 // closeResultSet(rs);
1488 // closeStatement(s);
1489 // connection.close();
1490 // db.releaseSingleUserCaseReadLock();
1491 // }
1492 // }
1493 // /**
1494 // * Returns relationships between two accounts
1495 // *
1496 // * @param account1_id account_id for account1
1497 // * @param account2_id account_id for account2
1498 // *
1499 // * @throws TskCoreException exception thrown if a critical error occurs
1500 // * within TSK core
1501 // */
1502 // public List<BlackboardArtifact> getRelationships(long account1_id, long account2_id) throws TskCoreException {
1503 // CaseDbConnection connection = db.getConnection();
1504 // db.acquireSingleUserCaseReadLock();
1505 // Statement s = null;
1506 // ResultSet rs = null;
1507 //
1508 // try {
1509 // s = connection.createStatement();
1510 // rs = connection.executeQuery(s, "SELECT artifacts.artifact_id AS artifact_id,"
1511 // + " artifacts.obj_id AS obj_id,"
1512 // + " artifacts.artifact_obj_id AS artifact_obj_id,"
1513 // + " artifacts.data_source_obj_id AS data_source_obj_id,"
1514 // + " artifacts.artifact_type_id AS artifact_type_id,"
1515 // + " artifacts.review_status_id AS review_status_id"
1516 // + " FROM blackboard_artifacts AS artifacts"
1517 // + " JOIN relationships AS relationships"
1518 // + " ON artifacts.artifact_obj_id = relationships.relationship_source_obj_id"
1519 // + " WHERE relationships.account1_id IN ( " + account1_id + ", " + account2_id + " )"
1520 // + " AND relationships.account2_id IN ( " + account1_id + ", " + account2_id + " )"
1521 // ); //NON-NLS
1522 //
1523 // ArrayList<BlackboardArtifact> artifacts = new ArrayList<BlackboardArtifact>();
1524 // while (rs.next()) {
1525 // BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
1526 // artifacts.add(new BlackboardArtifact(db, rs.getLong("artifact_id"), rs.getLong("obj_id"), rs.getLong("artifact_obj_id"), rs.getLong("data_source_obj_id"),
1527 // bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
1528 // BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id"))));
1529 // }
1530 //
1531 // return artifacts;
1532 // } catch (SQLException ex) {
1533 // throw new TskCoreException("Error getting relationships bteween accounts. " + ex.getMessage(), ex);
1534 // } finally {
1535 // closeResultSet(rs);
1536 // closeStatement(s);
1537 // connection.close();
1538 // db.releaseSingleUserCaseReadLock();
1539 // }
1540 // }
1541 // /**
1542 // * Returns relationships, of given type, between two accounts
1543 // *
1544 // * @param account1_id account1 artifact ID
1545 // * @param account2_id account2 artifact ID
1546 // * @param artifactType artifact type
1547 // *
1548 // *
1549 // * @throws TskCoreException exception thrown if a critical error occurs
1550 // * within TSK core
1551 // */
1552 // public List<BlackboardArtifact> getRelationshipsOfType(long account1_id, long account2_id, BlackboardArtifact.Type artifactType) throws TskCoreException {
1553 // CaseDbConnection connection = db.getConnection();
1554 // db.acquireSingleUserCaseReadLock();
1555 // Statement s = null;
1556 // ResultSet rs = null;
1557 //
1558 // try {
1559 // s = connection.createStatement();
1560 // rs = connection.executeQuery(s, "SELECT artifacts.artifact_id AS artifact_id,"
1561 // + " artifacts.obj_id AS obj_id,"
1562 // + " artifacts.artifact_obj_id AS artifact_obj_id,"
1563 // + " artifacts.data_source_obj_id AS data_source_obj_id,"
1564 // + " artifacts.artifact_type_id AS artifact_type_id,"
1565 // + " artifacts.review_status_id AS review_status_id"
1566 // + " FROM relationships AS relationships"
1567 // + " WHERE artifacts.artifact_type_id = " + artifactType.getTypeID()
1568 // + " AND relationships.account1_id IN ( " + account1_id + ", " + account2_id + " )"
1569 // + " AND relationships.account2_id IN ( " + account1_id + ", " + account2_id + " )"
1570 // ); //NON-NLS
1571 //
1572 // ArrayList<BlackboardArtifact> artifacts = new ArrayList<BlackboardArtifact>();
1573 // while (rs.next()) {
1574 // BlackboardArtifact.Type bbartType = db.getArtifactType(rs.getInt("artifact_type_id"));
1575 // artifacts.add(new BlackboardArtifact(db, rs.getLong("artifact_id"), rs.getLong("obj_id"), rs.getLong("artifact_obj_id"), rs.getLong("data_source_obj_id"),
1576 // bbartType.getTypeID(), bbartType.getTypeName(), bbartType.getDisplayName(),
1577 // BlackboardArtifact.ReviewStatus.withID(rs.getInt("review_status_id"))));
1578 // }
1579 //
1580 // return artifacts;
1581 // } catch (SQLException ex) {
1582 // throw new TskCoreException("Error getting relationships bteween accounts. " + ex.getMessage(), ex);
1583 // } finally {
1584 // closeResultSet(rs);
1585 // closeStatement(s);
1586 // connection.close();
1587 // db.releaseSingleUserCaseReadLock();
1588 // }
1589 // }
1590 // /**
1591 // * Get the number of relationships found on the given device
1592 // *
1593 // * @param deviceId device to look up
1594 // *
1595 // * RAMAN TBD: add filter param
1596 // *
1597 // * @return number of account relationships found on this device
1598 // *
1599 // * @throws org.sleuthkit.datamodel.TskCoreException
1600 // */
1601 // public long getRelationshipsCountByDevice(String deviceId) throws TskCoreException {
1602 // List<Long> ds_ids = db.getDataSourceObjIds(deviceId);
1603 // String datasource_obj_ids_list = buildCSVString(ds_ids);
1604 // CaseDbConnection connection = db.getConnection();
1605 // db.acquireSingleUserCaseReadLock();
1606 // Statement s = null;
1607 // ResultSet rs = null;
1608 //
1609 // try {
1610 // s = connection.createStatement();
1611 // String queryStr = "SELECT COUNT(*) AS count "
1612 // + " FROM blackboard_artifacts as artifacts"
1613 // + " JOIN relationships AS relationships"
1614 // + " ON artifacts.artifact_id = relationships.relationship_source_obj_id"
1615 // + " WHERE artifacts.data_source_obj_id IN ( " + datasource_obj_ids_list + " )"
1616 // + " AND artifacts.artifact_type_id IN ( " + RELATIONSHIP_ARTIFACT_TYPE_IDS_CSV_STR + " )";
1617 //
1618 // System.out.println("RAMAN QueryStr = " + queryStr);
1619 // System.out.println("");
1620 //
1621 // // RAMAN TBD: add SQL from filters
1622 // rs = connection.executeQuery(s, queryStr); //NON-NLS
1623 // rs.next();
1624 //
1625 // return (rs.getLong("count"));
1626 // } catch (SQLException ex) {
1627 // throw new TskCoreException("Error getting relationships bteween accounts. " + ex.getMessage(), ex);
1628 // } finally {
1629 // closeResultSet(rs);
1630 // closeStatement(s);
1631 // connection.close();
1632 // db.releaseSingleUserCaseReadLock();
1633 // }
1634 // }
1635 // /**
1636 // *
1637 // * @param accountId - database row id
1638 // * @param accountInstanceId - Artifact ID of instance
1639 // *
1640 // * @throws TskCoreException
1641 // */
1642 // private void addAccountFileInstanceMapping(long accountId, long accountInstanceId) throws TskCoreException {
1643 // CaseDbConnection connection = db.getConnection();
1644 // db.acquireSingleUserCaseWriteLock();
1645 // Statement s = null;
1646 // ResultSet rs = null;
1647 //
1648 // try {
1649 // connection.beginTransaction();
1650 // s = connection.createStatement();
1651 //
1652 // s.execute("INSERT INTO account_to_instances_map (account_id, account_instance_id) VALUES ( " + accountId + ", " + accountInstanceId + " )"); //NON-NLS
1653 // connection.commitTransaction();
1654 // } catch (SQLException ex) {
1655 // connection.rollbackTransaction();
1656 // throw new TskCoreException("Error adding an account to instance mapping", ex);
1657 // } finally {
1658 // closeResultSet(rs);
1659 // closeStatement(s);
1660 // connection.close();
1661 // db.releaseSingleUserCaseWriteLock();
1662 // }
1663 // }
1664 }
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSources
Set< Content > getRelationshipSources(Set< AccountDeviceInstance > accountDeviceInstanceList, CommunicationsFilter filter)
Definition: CommunicationsManager.java:872
org.sleuthkit.datamodel.BlackboardArtifact.addAttributes
void addAttributes(Collection< BlackboardAttribute > attributes)
Definition: BlackboardArtifact.java:373
org.sleuthkit.datamodel.CommunicationsManager.getAccountType
org.sleuthkit.datamodel.Account.Type getAccountType(String accountTypeName)
Definition: CommunicationsManager.java:570
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_MESSAGE
TSK_MESSAGE
Definition: BlackboardArtifact.java:1061
org.sleuthkit.datamodel.CommunicationsManager.createAccountFileInstance
AccountFileInstance createAccountFileInstance(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID, String moduleName, Content sourceFile)
Definition: CommunicationsManager.java:264
org.sleuthkit.datamodel.CommunicationsManager.addAccountType
org.sleuthkit.datamodel.Account.Type addAccountType(String accountTypeName, String displayName)
Definition: CommunicationsManager.java:193
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_CONTACT
TSK_CONTACT
Definition: BlackboardArtifact.java:1055
org.sleuthkit.datamodel.SleuthkitCase.newBlackboardArtifact
BlackboardArtifact newBlackboardArtifact(int artifactTypeID, long obj_id)
Definition: SleuthkitCase.java:3366
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_EMAIL_MSG
TSK_EMAIL_MSG
Definition: BlackboardArtifact.java:995
org.sleuthkit.datamodel.Account.Type.PREDEFINED_ACCOUNT_TYPES
static final List< Account.Type > PREDEFINED_ACCOUNT_TYPES
Definition: Account.java:59
org.sleuthkit.datamodel.Content
Definition: Content.java:32
org.sleuthkit.datamodel.SleuthkitCase.getArtifactType
BlackboardArtifact.Type getArtifactType(String artTypeName)
Definition: SleuthkitCase.java:3082
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_CALLLOG
TSK_CALLLOG
Definition: BlackboardArtifact.java:1066
org.sleuthkit.datamodel.CommunicationsManager.getRelationshipSourcesCount
long getRelationshipSourcesCount(AccountDeviceInstance accountDeviceInstance, CommunicationsFilter filter)
Definition: CommunicationsManager.java:814
org.sleuthkit.datamodel.CommunicationsManager.getAccountDeviceInstancesWithRelationships
List< AccountDeviceInstance > getAccountDeviceInstancesWithRelationships(CommunicationsFilter filter)
Definition: CommunicationsManager.java:708
org.sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE
Definition: BlackboardArtifact.java:929
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:364
org.sleuthkit.datamodel.CommunicationsManager.getAccount
Account getAccount(org.sleuthkit.datamodel.Account.Type accountType, String accountUniqueID)
Definition: CommunicationsManager.java:299

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