19 package org.sleuthkit.datamodel;
21 import com.google.common.annotations.Beta;
22 import com.google.common.collect.ImmutableList;
23 import com.google.common.collect.ImmutableMap;
24 import java.sql.PreparedStatement;
25 import java.sql.ResultSet;
26 import java.sql.SQLException;
27 import java.sql.Statement;
28 import java.sql.Types;
29 import java.time.Instant;
30 import java.util.ArrayList;
31 import java.util.Collection;
32 import java.util.Collections;
33 import java.util.HashMap;
34 import java.util.HashSet;
35 import java.util.List;
37 import java.util.Objects;
38 import java.util.Optional;
40 import java.util.logging.Level;
41 import java.util.logging.Logger;
42 import java.util.stream.Collectors;
43 import java.util.stream.Stream;
44 import org.joda.time.DateTimeZone;
45 import org.joda.time.Interval;
58 private static final Logger logger = Logger.getLogger(
TimelineManager.class.getName());
63 private static final ImmutableList<TimelineEventType> ROOT_CATEGORY_AND_FILESYSTEM_TYPES
80 private static final ImmutableList<TimelineEventType> PREDEFINED_EVENT_TYPES
88 .map(artType -> artType.getTypeID())
89 .collect(Collectors.toSet());
97 private static final Long MAX_TIMESTAMP_TO_ADD = Instant.now().getEpochSecond() + 394200000;
102 private final Map<Long, TimelineEventType> eventTypeIDMap =
new HashMap<>();
114 this.caseDB = caseDB;
116 List<TimelineEventType> fullList =
new ArrayList<>();
117 fullList.addAll(ROOT_CATEGORY_AND_FILESYSTEM_TYPES);
118 fullList.addAll(PREDEFINED_EVENT_TYPES);
121 try (
final CaseDbConnection con = caseDB.getConnection();
122 final PreparedStatement pStatement = con.prepareStatement(
123 insertOrIgnore(
" INTO tsk_event_types(event_type_id, display_name, super_type_id) VALUES (?, ?, ?)"),
124 Statement.NO_GENERATED_KEYS)) {
126 pStatement.setLong(1, type.getTypeID());
127 pStatement.setString(2, escapeSingleQuotes(type.getDisplayName()));
128 if (type != type.getParent()) {
129 pStatement.setLong(3, type.getParent().getTypeID());
131 pStatement.setNull(3, java.sql.Types.INTEGER);
134 con.executeUpdate(pStatement);
135 eventTypeIDMap.put(type.getTypeID(), type);
137 }
catch (SQLException ex) {
138 throw new TskCoreException(
"Failed to initialize timeline event types", ex);
156 if (eventIDs.isEmpty()) {
159 final String query =
"SELECT Min(time) as minTime, Max(time) as maxTime FROM tsk_events WHERE event_id IN (" + buildCSVString(eventIDs) +
")";
161 try (CaseDbConnection con = caseDB.getConnection();
162 Statement stmt = con.createStatement();
163 ResultSet results = stmt.executeQuery(query);) {
164 if (results.next()) {
165 return new Interval(results.getLong(
"minTime") * 1000, (results.getLong(
"maxTime") + 1) * 1000, DateTimeZone.UTC);
167 }
catch (SQLException ex) {
168 throw new TskCoreException(
"Error executing get spanning interval query: " + query, ex);
188 long start = timeRange.getStartMillis() / 1000;
189 long end = timeRange.getEndMillis() / 1000;
190 String sqlWhere = getSQLWhere(filter);
191 String augmentedEventsTablesSQL = getAugmentedEventsTablesSQL(filter);
192 String queryString =
" SELECT (SELECT Max(time) FROM " + augmentedEventsTablesSQL
193 +
" WHERE time <=" + start +
" AND " + sqlWhere +
") AS start,"
194 +
" (SELECT Min(time) FROM " + augmentedEventsTablesSQL
195 +
" WHERE time >= " + end +
" AND " + sqlWhere +
") AS end";
197 try (CaseDbConnection con = caseDB.getConnection();
198 Statement stmt = con.createStatement();
199 ResultSet results = stmt.executeQuery(queryString);) {
201 if (results.next()) {
202 long start2 = results.getLong(
"start");
203 long end2 = results.getLong(
"end");
208 return new Interval(start2 * 1000, (end2 + 1) * 1000, timeZone);
210 }
catch (SQLException ex) {
228 String sql =
"SELECT * FROM " + getAugmentedEventsTablesSQL(
false) +
" WHERE event_id = " + eventID;
230 try (CaseDbConnection con = caseDB.getConnection();
231 Statement stmt = con.createStatement();) {
232 try (ResultSet results = stmt.executeQuery(sql);) {
233 if (results.next()) {
234 int typeID = results.getInt(
"event_type_id");
237 results.getLong(
"data_source_obj_id"),
238 results.getLong(
"content_obj_id"),
239 results.getLong(
"artifact_id"),
240 results.getLong(
"time"),
241 type, results.getString(
"full_description"),
242 results.getString(
"med_description"),
243 results.getString(
"short_description"),
244 intToBoolean(results.getInt(
"hash_hit")),
245 intToBoolean(results.getInt(
"tagged")));
248 }
catch (SQLException sqlEx) {
268 Long startTime = timeRange.getStartMillis() / 1000;
269 Long endTime = timeRange.getEndMillis() / 1000;
271 if (Objects.equals(startTime, endTime)) {
275 ArrayList<Long> resultIDs =
new ArrayList<>();
277 String query =
"SELECT tsk_events.event_id AS event_id FROM " + getAugmentedEventsTablesSQL(filter)
278 +
" WHERE time >= " + startTime +
" AND time <" + endTime +
" AND " + getSQLWhere(filter) +
" ORDER BY time ASC";
280 try (CaseDbConnection con = caseDB.getConnection();
281 Statement stmt = con.createStatement();
282 ResultSet results = stmt.executeQuery(query);) {
283 while (results.next()) {
284 resultIDs.add(results.getLong(
"event_id"));
287 }
catch (SQLException sqlEx) {
288 throw new TskCoreException(
"Error while executing query " + query, sqlEx);
306 try (CaseDbConnection con = caseDB.getConnection();
307 Statement stms = con.createStatement();
308 ResultSet results = stms.executeQuery(STATEMENTS.GET_MAX_TIME.getSQL());) {
309 if (results.next()) {
310 return results.getLong(
"max");
312 }
catch (SQLException ex) {
313 throw new TskCoreException(
"Error while executing query " + STATEMENTS.GET_MAX_TIME.getSQL(), ex);
330 try (CaseDbConnection con = caseDB.getConnection();
331 Statement stms = con.createStatement();
332 ResultSet results = stms.executeQuery(STATEMENTS.GET_MIN_TIME.getSQL());) {
333 if (results.next()) {
334 return results.getLong(
"min");
336 }
catch (SQLException ex) {
337 throw new TskCoreException(
"Error while executing query " + STATEMENTS.GET_MAX_TIME.getSQL(), ex);
359 return Optional.ofNullable(eventTypeIDMap.get(eventTypeID));
368 return ImmutableList.copyOf(eventTypeIDMap.values());
371 private String insertOrIgnore(String query) {
374 return " INSERT " + query +
" ON CONFLICT DO NOTHING ";
376 return " INSERT OR IGNORE " + query;
378 throw new UnsupportedOperationException(
"Unsupported DB type: " + caseDB.
getDatabaseType().name());
385 private enum STATEMENTS {
387 GET_MAX_TIME(
"SELECT Max(time) AS max FROM tsk_events"),
388 GET_MIN_TIME(
"SELECT Min(time) AS min FROM tsk_events");
390 private final String sql;
392 private STATEMENTS(String sql) {
412 ArrayList<Long> eventIDs =
new ArrayList<>();
415 =
"SELECT event_id FROM tsk_events "
416 +
" LEFT JOIN tsk_event_descriptions on ( tsk_events.event_description_id = tsk_event_descriptions.event_description_id ) "
417 +
" WHERE artifact_id = " + artifact.getArtifactID();
419 try (CaseDbConnection con = caseDB.getConnection();
420 Statement stmt = con.createStatement();
421 ResultSet results = stmt.executeQuery(query);) {
422 while (results.next()) {
423 eventIDs.add(results.getLong(
"event_id"));
425 }
catch (SQLException ex) {
426 throw new TskCoreException(
"Error executing getEventIDsForArtifact query.", ex);
448 try (CaseDbConnection conn = caseDB.getConnection()) {
449 return getEventAndDescriptionIDs(conn, content.getId(), includeDerivedArtifacts).keySet();
473 private Long addEventDescription(
long dataSourceObjId,
long fileObjId, Long artifactID,
474 String fullDescription, String medDescription, String shortDescription,
475 boolean hasHashHits,
boolean tagged, CaseDbConnection connection)
throws TskCoreException, DuplicateException {
476 String tableValuesClause
477 =
"tsk_event_descriptions ( "
478 +
"data_source_obj_id, content_obj_id, artifact_id, "
479 +
" full_description, med_description, short_description, "
480 +
" hash_hit, tagged "
482 +
"(?, ?, ?, ?, ?, ?, ?, ?)";
484 String insertDescriptionSql = getSqlIgnoreConflict(tableValuesClause);
488 PreparedStatement insertDescriptionStmt = connection.getPreparedStatement(insertDescriptionSql, PreparedStatement.RETURN_GENERATED_KEYS);
489 insertDescriptionStmt.clearParameters();
490 insertDescriptionStmt.setLong(1, dataSourceObjId);
491 insertDescriptionStmt.setLong(2, fileObjId);
493 if (artifactID == null) {
494 insertDescriptionStmt.setNull(3, Types.INTEGER);
496 insertDescriptionStmt.setLong(3, artifactID);
499 insertDescriptionStmt.setString(4, fullDescription);
500 insertDescriptionStmt.setString(5, medDescription);
501 insertDescriptionStmt.setString(6, shortDescription);
502 insertDescriptionStmt.setInt(7, booleanToInt(hasHashHits));
503 insertDescriptionStmt.setInt(8, booleanToInt(tagged));
504 int row = insertDescriptionStmt.executeUpdate();
511 try (ResultSet generatedKeys = insertDescriptionStmt.getGeneratedKeys()) {
512 if (generatedKeys.next()) {
513 return generatedKeys.getLong(1);
518 }
catch (SQLException ex) {
519 throw new TskCoreException(
"Failed to insert event description.", ex);
538 private Long getEventDescription(
long dataSourceObjId,
long fileObjId, Long artifactID,
539 String fullDescription, CaseDbConnection connection)
throws TskCoreException {
541 String query =
"SELECT event_description_id FROM tsk_event_descriptions "
542 +
"WHERE data_source_obj_id = " + dataSourceObjId
543 +
" AND content_obj_id = " + fileObjId
544 +
" AND artifact_id " + (artifactID != null ?
" = " + artifactID :
"IS null")
545 +
" AND full_description " + (fullDescription != null ?
"= '"
546 + SleuthkitCase.escapeSingleQuotes(fullDescription) +
"'" :
"IS null");
549 try (ResultSet resultSet = connection.createStatement().executeQuery(query)) {
551 if (resultSet.next()) {
552 long id = resultSet.getLong(1);
555 }
catch (SQLException ex) {
556 throw new TskCoreException(String.format(
"Failed to get description, dataSource=%d, fileObjId=%d, artifactId=%d", dataSourceObjId, fileObjId, artifactID), ex);
564 Collection<TimelineEvent> addEventsForNewFile(AbstractFile file, CaseDbConnection connection)
throws TskCoreException {
565 Set<TimelineEvent> events = addEventsForNewFileQuiet(file, connection);
567 .map(TimelineEventAddedEvent::new)
568 .forEach(caseDB::fireTSKEvent);
587 Set<TimelineEvent> addEventsForNewFileQuiet(AbstractFile file, CaseDbConnection connection)
throws TskCoreException {
589 Map<TimelineEventType, Long> timeMap = ImmutableMap.of(TimelineEventType.FILE_CREATED, file.getCrtime(),
590 TimelineEventType.FILE_ACCESSED, file.getAtime(),
591 TimelineEventType.FILE_CHANGED, file.getCtime(),
592 TimelineEventType.FILE_MODIFIED, file.getMtime());
598 if (Collections.max(timeMap.values()) <= 0) {
599 return Collections.emptySet();
602 String description = file.getParentPath() + file.getName();
603 long fileObjId = file.getId();
604 Set<TimelineEvent> events =
new HashSet<>();
607 Long descriptionID = addEventDescription(file.getDataSourceObjectId(), fileObjId, null,
608 description, null, null,
false,
false, connection);
610 if(descriptionID == null) {
611 descriptionID = getEventDescription(file.getDataSourceObjectId(), fileObjId, null, description, connection);
613 if(descriptionID != null) {
614 for (Map.Entry<TimelineEventType, Long> timeEntry : timeMap.entrySet()) {
615 Long time = timeEntry.getValue();
616 if (time > 0 && time < MAX_TIMESTAMP_TO_ADD) {
617 TimelineEventType type = timeEntry.getKey();
618 long eventID = addEventWithExistingDescription(time, type, descriptionID, connection);
625 events.add(
new TimelineEvent(eventID, descriptionID, fileObjId, null, time, type,
626 description, null, null,
false,
false));
628 if (time >= MAX_TIMESTAMP_TO_ADD) {
629 logger.log(Level.WARNING, String.format(
"Date/Time discarded from Timeline for %s for file %s with Id %d", timeEntry.getKey().getDisplayName(), file.getParentPath() + file.getName(), file.getId()));
634 throw new TskCoreException(String.format(
"Failed to get event description for file id = %d", fileObjId));
636 }
catch (DuplicateException dupEx) {
637 logger.log(Level.SEVERE,
"Attempt to make file event duplicate.", dupEx);
658 Set<TimelineEvent> addArtifactEvents(BlackboardArtifact artifact)
throws TskCoreException {
659 Set<TimelineEvent> newEvents =
new HashSet<>();
666 if (artifact.getArtifactTypeID() == TSK_TL_EVENT.getTypeID()) {
667 TimelineEventType eventType;
668 BlackboardAttribute attribute = artifact.getAttribute(
new BlackboardAttribute.Type(TSK_TL_EVENT_TYPE));
669 if (attribute == null) {
670 eventType = TimelineEventType.STANDARD_ARTIFACT_CATCH_ALL;
672 long eventTypeID = attribute.getValueLong();
673 eventType = eventTypeIDMap.getOrDefault(eventTypeID, TimelineEventType.STANDARD_ARTIFACT_CATCH_ALL);
678 addArtifactEvent(((TimelineEventArtifactTypeImpl) TimelineEventType.STANDARD_ARTIFACT_CATCH_ALL).makeEventDescription(artifact), eventType, artifact)
679 .ifPresent(newEvents::add);
680 }
catch (DuplicateException ex) {
681 logger.log(Level.SEVERE, getDuplicateExceptionMessage(artifact,
"Attempt to make a timeline event artifact duplicate"), ex);
688 Set<TimelineEventArtifactTypeImpl> eventTypesForArtifact = eventTypeIDMap.values().stream()
689 .filter(TimelineEventArtifactTypeImpl.class::isInstance)
690 .map(TimelineEventArtifactTypeImpl.class::cast)
691 .filter(eventType -> eventType.getArtifactTypeID() == artifact.getArtifactTypeID())
692 .collect(Collectors.toSet());
694 boolean duplicateExists =
false;
695 for (TimelineEventArtifactTypeImpl eventType : eventTypesForArtifact) {
697 addArtifactEvent(eventType.makeEventDescription(artifact), eventType, artifact)
698 .ifPresent(newEvents::add);
699 }
catch (DuplicateException ex) {
700 duplicateExists =
true;
701 logger.log(Level.SEVERE, getDuplicateExceptionMessage(artifact,
"Attempt to make artifact event duplicate"), ex);
706 if (!duplicateExists && newEvents.isEmpty()) {
708 addOtherEventDesc(artifact).ifPresent(newEvents::add);
709 }
catch (DuplicateException ex) {
710 logger.log(Level.SEVERE, getDuplicateExceptionMessage(artifact,
"Attempt to make 'other' artifact event duplicate"), ex);
715 .map(TimelineEventAddedEvent::new)
716 .forEach(caseDB::fireTSKEvent);
732 private String getDuplicateExceptionMessage(BlackboardArtifact artifact, String error) {
733 String artifactIDStr = null;
734 String sourceStr = null;
736 if (artifact != null) {
737 artifactIDStr = Long.toString(artifact.getId());
740 sourceStr = artifact.getAttributes().stream()
741 .filter(attr -> attr != null && attr.getSources() != null && !attr.getSources().isEmpty())
742 .map(attr -> String.join(
",", attr.getSources()))
745 }
catch (TskCoreException ex) {
746 logger.log(Level.WARNING, String.format(
"Could not fetch artifacts for artifact id: %d.", artifact.getId()), ex);
750 artifactIDStr = (artifactIDStr == null) ?
"<null>" : artifactIDStr;
751 sourceStr = (sourceStr == null) ?
"<null>" : sourceStr;
753 return String.format(
"%s (artifactID=%s, Source=%s).", error, artifactIDStr, sourceStr);
767 private Optional<TimelineEvent> addOtherEventDesc(BlackboardArtifact artifact)
throws TskCoreException, DuplicateException {
768 if (artifact == null) {
769 return Optional.empty();
772 Long timeVal = artifact.getAttributes().stream()
773 .filter((attr) -> attr.getAttributeType().getValueType() == BlackboardAttribute.TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.DATETIME)
774 .map(attr -> attr.getValueLong())
778 if (timeVal == null) {
779 return Optional.empty();
782 String description = String.format(
"%s: %d", artifact.getDisplayName(), artifact.getId());
784 TimelineEventDescriptionWithTime evtWDesc =
new TimelineEventDescriptionWithTime(timeVal, description, description, description);
786 TimelineEventType evtType = (ARTIFACT_TYPE_IDS.contains(artifact.getArtifactTypeID()))
787 ? TimelineEventType.STANDARD_ARTIFACT_CATCH_ALL
788 : TimelineEventType.CUSTOM_ARTIFACT_CATCH_ALL;
790 return addArtifactEvent(evtWDesc, evtType, artifact);
806 private Optional<TimelineEvent> addArtifactEvent(TimelineEventDescriptionWithTime eventPayload,
807 TimelineEventType eventType, BlackboardArtifact artifact)
throws TskCoreException, DuplicateException {
809 if (eventPayload == null) {
810 return Optional.empty();
812 long time = eventPayload.getTime();
814 if (time <= 0 || time >= MAX_TIMESTAMP_TO_ADD) {
815 if (time >= MAX_TIMESTAMP_TO_ADD) {
816 logger.log(Level.WARNING, String.format(
"Date/Time discarded from Timeline for %s for artifact %s with id %d", artifact.getDisplayName(), eventPayload.getDescription(TimelineLevelOfDetail.HIGH), artifact.getId()));
818 return Optional.empty();
820 String fullDescription = eventPayload.getDescription(TimelineLevelOfDetail.HIGH);
821 String medDescription = eventPayload.getDescription(TimelineLevelOfDetail.MEDIUM);
822 String shortDescription = eventPayload.getDescription(TimelineLevelOfDetail.LOW);
823 long artifactID = artifact.getArtifactID();
824 long fileObjId = artifact.getObjectID();
825 Long dataSourceObjectID = artifact.getDataSourceObjectID();
827 if(dataSourceObjectID == null) {
828 logger.log(Level.SEVERE, String.format(
"Failed to create timeline event for artifact (%d), artifact data source was null"), artifact.getId());
829 return Optional.empty();
833 boolean hasHashHits =
false;
836 hasHashHits = isNotEmpty(file.getHashSetNames());
842 try (CaseDbConnection connection = caseDB.getConnection();) {
844 Long descriptionID = addEventDescription(dataSourceObjectID, fileObjId, artifactID,
845 fullDescription, medDescription, shortDescription,
846 hasHashHits, tagged, connection);
848 if(descriptionID == null) {
849 descriptionID = getEventDescription(dataSourceObjectID, fileObjId, artifactID,
850 fullDescription, connection);
853 if(descriptionID != null) {
854 long eventID = addEventWithExistingDescription(time, eventType, descriptionID, connection);
856 event =
new TimelineEvent(eventID, dataSourceObjectID, fileObjId, artifactID,
857 time, eventType, fullDescription, medDescription, shortDescription,
858 hasHashHits, tagged);
860 throw new TskCoreException(String.format(
"Failed to get event description for file id = %d, artifactId %d", fileObjId, artifactID));
866 return Optional.of(event);
869 private long addEventWithExistingDescription(Long time, TimelineEventType type,
long descriptionID, CaseDbConnection connection)
throws TskCoreException, DuplicateException {
870 String tableValuesClause
871 =
"tsk_events ( event_type_id, event_description_id , time) VALUES (?, ?, ?)";
873 String insertEventSql = getSqlIgnoreConflict(tableValuesClause);
877 PreparedStatement insertRowStmt = connection.getPreparedStatement(insertEventSql, Statement.RETURN_GENERATED_KEYS);
878 insertRowStmt.clearParameters();
879 insertRowStmt.setLong(1, type.getTypeID());
880 insertRowStmt.setLong(2, descriptionID);
881 insertRowStmt.setLong(3, time);
882 int row = insertRowStmt.executeUpdate();
885 throw new DuplicateException(String.format(
"An event already exists in the event table for this item [time: %s, type: %s, description: %d].",
886 time == null ?
"<null>" : Long.toString(time),
887 type == null ?
"<null>" : type.toString(),
891 try (ResultSet generatedKeys = insertRowStmt.getGeneratedKeys();) {
892 if (generatedKeys.next()) {
893 return generatedKeys.getLong(1);
895 throw new DuplicateException(String.format(
"An event already exists in the event table for this item [time: %s, type: %s, description: %d].",
896 time == null ?
"<null>" : Long.toString(time),
897 type == null ?
"<null>" : type.toString(),
901 }
catch (SQLException ex) {
902 throw new TskCoreException(
"Failed to insert event for existing description.", ex);
908 private Map<Long, Long> getEventAndDescriptionIDs(CaseDbConnection conn,
long contentObjID,
boolean includeArtifacts)
throws TskCoreException {
909 return getEventAndDescriptionIDsHelper(conn, contentObjID, (includeArtifacts ?
"" :
" AND artifact_id IS NULL"));
912 private Map<Long, Long> getEventAndDescriptionIDs(CaseDbConnection conn,
long contentObjID, Long artifactID)
throws TskCoreException {
913 return getEventAndDescriptionIDsHelper(conn, contentObjID,
" AND artifact_id = " + artifactID);
916 private Map<Long, Long> getEventAndDescriptionIDsHelper(CaseDbConnection con,
long fileObjID, String artifactClause)
throws TskCoreException {
918 Map<Long, Long> eventIDToDescriptionIDs =
new HashMap<>();
919 String sql =
"SELECT event_id, tsk_events.event_description_id"
920 +
" FROM tsk_events "
921 +
" LEFT JOIN tsk_event_descriptions ON ( tsk_events.event_description_id = tsk_event_descriptions.event_description_id )"
922 +
" WHERE content_obj_id = " + fileObjID
924 try (Statement selectStmt = con.createStatement(); ResultSet executeQuery = selectStmt.executeQuery(sql);) {
925 while (executeQuery.next()) {
926 eventIDToDescriptionIDs.put(executeQuery.getLong(
"event_id"), executeQuery.getLong(
"event_description_id"));
928 }
catch (SQLException ex) {
929 throw new TskCoreException(
"Error getting event description ids for object id = " + fileObjID, ex);
931 return eventIDToDescriptionIDs;
953 try (CaseDbConnection conn = caseDB.getConnection()) {
954 Map<Long, Long> eventIDs = getEventAndDescriptionIDs(conn, content.getId(),
false);
955 updateEventSourceTaggedFlag(conn, eventIDs.values(), 1);
956 return eventIDs.keySet();
982 try (CaseDbConnection conn = caseDB.getConnection()) {
984 Map<Long, Long> eventIDs = getEventAndDescriptionIDs(conn, content.getId(),
false);
985 updateEventSourceTaggedFlag(conn, eventIDs.values(), 0);
986 return eventIDs.keySet();
988 return Collections.emptySet();
1008 try (CaseDbConnection conn = caseDB.getConnection()) {
1009 Map<Long, Long> eventIDs = getEventAndDescriptionIDs(conn, artifact.getObjectID(), artifact.getArtifactID());
1010 updateEventSourceTaggedFlag(conn, eventIDs.values(), 1);
1011 return eventIDs.keySet();
1031 try (CaseDbConnection conn = caseDB.getConnection()) {
1033 Map<Long, Long> eventIDs = getEventAndDescriptionIDs(conn, artifact.getObjectID(), artifact.getArtifactID());
1034 updateEventSourceTaggedFlag(conn, eventIDs.values(), 0);
1035 return eventIDs.keySet();
1037 return Collections.emptySet();
1044 private void updateEventSourceTaggedFlag(CaseDbConnection conn, Collection<Long> eventDescriptionIDs,
int flagValue)
throws TskCoreException {
1045 if (eventDescriptionIDs.isEmpty()) {
1049 String sql =
"UPDATE tsk_event_descriptions SET tagged = " + flagValue +
" WHERE event_description_id IN (" + buildCSVString(eventDescriptionIDs) +
")";
1050 try (Statement updateStatement = conn.createStatement()) {
1051 updateStatement.executeUpdate(sql);
1052 }
catch (SQLException ex) {
1053 throw new TskCoreException(
"Error marking content events tagged: " + sql, ex);
1073 try (CaseDbConnection con = caseDB.getConnection(); Statement updateStatement = con.createStatement();) {
1074 Map<Long, Long> eventIDs = getEventAndDescriptionIDs(con, content.getId(),
true);
1075 if (!eventIDs.isEmpty()) {
1076 String sql =
"UPDATE tsk_event_descriptions SET hash_hit = 1" +
" WHERE event_description_id IN (" + buildCSVString(eventIDs.values()) +
")";
1078 updateStatement.executeUpdate(sql);
1079 return eventIDs.keySet();
1080 }
catch (SQLException ex) {
1081 throw new TskCoreException(
"Error setting hash_hit of events.", ex);
1084 return eventIDs.keySet();
1086 }
catch (SQLException ex) {
1087 throw new TskCoreException(
"Error setting hash_hit of events.", ex);
1117 long adjustedEndTime = Objects.equals(startTime, endTime) ? endTime + 1 : endTime;
1121 String queryString =
"SELECT count(DISTINCT tsk_events.event_id) AS count, " + typeColumn
1122 +
" FROM " + getAugmentedEventsTablesSQL(filter)
1123 +
" WHERE time >= " + startTime +
" AND time < " + adjustedEndTime +
" AND " + getSQLWhere(filter)
1124 +
" GROUP BY " + typeColumn;
1127 try (CaseDbConnection con = caseDB.getConnection();
1128 Statement stmt = con.createStatement();
1129 ResultSet results = stmt.executeQuery(queryString);) {
1130 Map<TimelineEventType, Long> typeMap =
new HashMap<>();
1131 while (results.next()) {
1132 int eventTypeID = results.getInt(typeColumn);
1134 .orElseThrow(() -> newEventTypeMappingException(eventTypeID));
1136 typeMap.put(eventType, results.getLong(
"count"));
1139 }
catch (SQLException ex) {
1140 throw new TskCoreException(
"Error getting count of events from db: " + queryString, ex);
1146 private static TskCoreException newEventTypeMappingException(
int eventTypeID) {
1147 return new TskCoreException(
"Error mapping event type id " + eventTypeID +
" to EventType.");
1163 static private String getAugmentedEventsTablesSQL(TimelineFilter.RootFilter filter) {
1164 TimelineFilter.FileTypesFilter fileTypesFitler = filter.getFileTypesFilter();
1165 boolean needsMimeTypes = fileTypesFitler != null && fileTypesFitler.hasSubFilters();
1167 return getAugmentedEventsTablesSQL(needsMimeTypes);
1184 static private String getAugmentedEventsTablesSQL(
boolean needMimeTypes) {
1197 return "( SELECT event_id, time, tsk_event_descriptions.data_source_obj_id, content_obj_id, artifact_id, "
1198 +
" full_description, med_description, short_description, tsk_events.event_type_id, super_type_id,"
1199 +
" hash_hit, tagged "
1200 + (needMimeTypes ?
", mime_type" :
"")
1201 +
" FROM tsk_events "
1202 +
" JOIN tsk_event_descriptions ON ( tsk_event_descriptions.event_description_id = tsk_events.event_description_id)"
1203 +
" JOIN tsk_event_types ON (tsk_events.event_type_id = tsk_event_types.event_type_id ) "
1204 + (needMimeTypes ?
" LEFT OUTER JOIN tsk_files "
1205 +
" ON (tsk_event_descriptions.content_obj_id = tsk_files.obj_id)"
1207 +
") AS tsk_events";
1217 private static int booleanToInt(
boolean value) {
1218 return value ? 1 : 0;
1221 private static boolean intToBoolean(
int value) {
1238 List<TimelineEvent> events =
new ArrayList<>();
1240 Long startTime = timeRange.getStartMillis() / 1000;
1241 Long endTime = timeRange.getEndMillis() / 1000;
1243 if (Objects.equals(startTime, endTime)) {
1247 if (filter == null) {
1251 if (endTime < startTime) {
1256 String querySql =
"SELECT time, content_obj_id, data_source_obj_id, artifact_id, "
1260 +
" event_type_id, super_type_id, "
1261 +
" full_description, med_description, short_description "
1262 +
" FROM " + getAugmentedEventsTablesSQL(filter)
1263 +
" WHERE time >= " + startTime +
" AND time < " + endTime +
" AND " + getSQLWhere(filter)
1267 try (CaseDbConnection con = caseDB.getConnection();
1268 Statement stmt = con.createStatement();
1269 ResultSet resultSet = stmt.executeQuery(querySql);) {
1271 while (resultSet.next()) {
1272 int eventTypeID = resultSet.getInt(
"event_type_id");
1274 ->
new TskCoreException(
"Error mapping event type id " + eventTypeID +
"to EventType."));
1277 resultSet.getLong(
"event_id"),
1278 resultSet.getLong(
"data_source_obj_id"),
1279 resultSet.getLong(
"content_obj_id"),
1280 resultSet.getLong(
"artifact_id"),
1281 resultSet.getLong(
"time"),
1283 resultSet.getString(
"full_description"),
1284 resultSet.getString(
"med_description"),
1285 resultSet.getString(
"short_description"),
1286 resultSet.getInt(
"hash_hit") != 0,
1287 resultSet.getInt(
"tagged") != 0);
1292 }
catch (SQLException ex) {
1293 throw new TskCoreException(
"Error getting events from db: " + querySql, ex);
1308 private static String typeColumnHelper(
final boolean useSubTypes) {
1309 return useSubTypes ?
"event_type_id" :
"super_type_id";
1320 String getSQLWhere(TimelineFilter.RootFilter filter) {
1323 if (filter == null) {
1324 return getTrueLiteral();
1326 result = filter.getSQLWhere(
this);
1343 private String getSqlIgnoreConflict(String insertTableValues)
throws TskCoreException {
1346 return "INSERT INTO " + insertTableValues +
" ON CONFLICT DO NOTHING";
1348 return "INSERT OR IGNORE INTO " + insertTableValues;
1350 throw new TskCoreException(
"Unknown DB Type: " + caseDB.
getDatabaseType().name());
1354 private String getTrueLiteral() {
1361 throw new UnsupportedOperationException(
"Unsupported DB type: " + caseDB.
getDatabaseType().name());
1379 this.addedEvent = event;
1386 private static class DuplicateException
extends Exception {
1388 private static final long serialVersionUID = 1L;
1395 DuplicateException(String message) {
List< Long > getEventIDs(Interval timeRange, TimelineFilter.RootFilter filter)
TimelineEvent getAddedEvent()
TimelineEvent getEventById(long eventID)
ImmutableList< TimelineEventType > getEventTypes()
Interval getSpanningInterval(Interval timeRange, TimelineFilter.RootFilter filter, DateTimeZone timeZone)
Set< Long > getEventIDsForContent(Content content, boolean includeDerivedArtifacts)
TimelineEventType FILE_ACCESSED
Interval getSpanningInterval(Collection< Long > eventIDs)
Set< Long > updateEventsForContentTagAdded(Content content)
List< BlackboardArtifactTag > getBlackboardArtifactTagsByArtifact(BlackboardArtifact artifact)
SortedSet<?extends TimelineEventType > getChildren()
Set< Long > updateEventsForContentTagDeleted(Content content)
Set< Long > updateEventsForHashSetHit(Content content)
TimelineEventType WEB_ACTIVITY
AbstractFile getAbstractFileById(long id)
TimelineEventType FILE_MODIFIED
void releaseSingleUserCaseReadLock()
TimelineEventType MISC_TYPES
static String escapeSingleQuotes(String text)
Set< Long > updateEventsForArtifactTagDeleted(BlackboardArtifact artifact)
void acquireSingleUserCaseWriteLock()
int DEPRECATED_OTHER_EVENT_ID
void releaseSingleUserCaseWriteLock()
TimelineEventType FILE_CREATED
TimelineEventType FILE_SYSTEM
Map< TimelineEventType, Long > countEventsByType(Long startTime, Long endTime, TimelineFilter.RootFilter filter, TimelineEventType.HierarchyLevel typeHierachyLevel)
List< Long > getEventIDsForArtifact(BlackboardArtifact artifact)
TimelineEventType ROOT_EVENT_TYPE
List< TimelineEvent > getEvents(Interval timeRange, TimelineFilter.RootFilter filter)
void acquireSingleUserCaseReadLock()
List< ContentTag > getContentTagsByContent(Content content)
Optional< TimelineEventType > getEventType(long eventTypeID)
TimelineEventType FILE_CHANGED
Set< Long > updateEventsForArtifactTagAdded(BlackboardArtifact artifact)