19 package org.sleuthkit.autopsy.discovery.search;
21 import java.text.SimpleDateFormat;
34 import java.util.ArrayList;
35 import java.util.Arrays;
36 import java.util.Date;
37 import java.util.List;
38 import java.util.Locale;
39 import java.util.StringJoiner;
40 import java.util.concurrent.TimeUnit;
41 import java.util.stream.Collectors;
42 import org.openide.util.NbBundle;
47 import org.
sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE;
69 String combinedQuery =
"";
71 if (!filter.getWhereClause().isEmpty()) {
72 if (!combinedQuery.isEmpty()) {
73 combinedQuery +=
" AND ";
75 combinedQuery +=
"(" + filter.getWhereClause() +
")";
79 if (combinedQuery.isEmpty()) {
84 return getResultList(filters, combinedQuery, caseDb, centralRepoDb);
85 }
catch (TskCoreException ex) {
106 List<Result> resultList =
new ArrayList<>();
107 List<AbstractFile> sqlResults = caseDb.findAllFilesWhere(combinedQuery);
110 if (sqlResults.isEmpty()) {
115 for (AbstractFile abstractFile : sqlResults) {
121 if (filter.useAlternateFilter()) {
122 resultList = filter.applyAlternateFilter(resultList, caseDb, centralRepoDb);
125 if (resultList.isEmpty()) {
142 private static List<BlackboardAttribute.ATTRIBUTE_TYPE>
dateAttributes
144 BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME,
145 BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME_CREATED,
146 BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DATETIME_ACCESSED
164 static String createAttributeTypeClause() {
165 StringJoiner joiner =
new StringJoiner(
",");
167 joiner.add(
"\'" + type.getTypeID() +
"\'");
169 return "attribute_type_id IN (" + joiner.toString() +
")";
174 return createAttributeTypeClause()
175 +
" AND (value_int64 BETWEEN " + startDate +
" AND " + endDate +
")";
178 @NbBundle.Messages({
"SearchFiltering.dateRangeFilter.lable=Activity date ",
180 "SearchFiltering.dateRangeFilter.after=after: {0}",
182 "SearchFiltering.dateRangeFilter.before=before: {0}",
183 "SearchFiltering.dateRangeFilter.and= and "})
187 if (startDate > 0 ) {
188 desc += Bundle.SearchFiltering_dateRangeFilter_after(
new SimpleDateFormat(
"yyyy/MM/dd", Locale.getDefault()).format(
new Date(TimeUnit.SECONDS.toMillis(startDate))));
190 if (endDate < 10000000000L) {
191 if (!desc.isEmpty()) {
192 desc += Bundle.SearchFiltering_dateRangeFilter_and();
194 desc += Bundle.SearchFiltering_dateRangeFilter_before(
new SimpleDateFormat(
"yyyy/MM/dd", Locale.getDefault()).format(
new Date(TimeUnit.SECONDS.toMillis(endDate))));
196 if (!desc.isEmpty()){
197 desc = Bundle.SearchFiltering_dateRangeFilter_lable()+desc;
208 private final List<ARTIFACT_TYPE>
types;
222 StringJoiner joiner =
new StringJoiner(
",");
223 for (ARTIFACT_TYPE type : types) {
224 joiner.add(
"\'" + type.getTypeID() +
"\'");
227 return "artifact_type_id IN (" + joiner +
")";
230 @NbBundle.Messages({
"# {0} - artifactTypes",
231 "SearchFiltering.artifactTypeFilter.desc=Result type(s): {0}",
232 "SearchFiltering.artifactTypeFilter.or=, "})
236 for (ARTIFACT_TYPE type : types) {
237 if (!desc.isEmpty()) {
238 desc += Bundle.SearchFiltering_artifactTypeFilter_or();
240 desc += type.getDisplayName();
242 desc = Bundle.SearchFiltering_artifactTypeFilter_desc(desc);
266 String queryStr =
"";
268 if (!queryStr.isEmpty()) {
272 queryStr +=
"(size > \'" + size.getMinBytes() +
"\' AND size <= \'" + size.getMaxBytes() +
"\')";
274 queryStr +=
"(size >= \'" + size.getMinBytes() +
"\')";
282 "SearchFiltering.SizeFilter.desc=Size(s): {0}",
283 "SearchFiltering.SizeFilter.or=, "})
288 if (!desc.isEmpty()) {
289 desc += Bundle.SearchFiltering_SizeFilter_or();
291 desc += size.getSizeGroup();
293 desc = Bundle.SearchFiltering_SizeFilter_desc(desc);
340 return "parent_path NOT LIKE \'%" +
getSearchStr() +
"%\'";
346 "SearchFiltering.ParentSearchTerm.fullString= (exact)",
347 "SearchFiltering.ParentSearchTerm.subString= (substring)",
348 "SearchFiltering.ParentSearchTerm.includeString= (include)",
349 "SearchFiltering.ParentSearchTerm.excludeString= (exclude)",})
354 returnString += Bundle.SearchFiltering_ParentSearchTerm_fullString();
356 returnString += Bundle.SearchFiltering_ParentSearchTerm_subString();
359 returnString += Bundle.SearchFiltering_ParentSearchTerm_includeString();
361 returnString += Bundle.SearchFiltering_ParentSearchTerm_excludeString();
416 String includeQueryStr =
"";
417 String excludeQueryStr =
"";
419 if (searchTerm.isIncluded()) {
420 if (!includeQueryStr.isEmpty()) {
421 includeQueryStr +=
" OR ";
423 includeQueryStr += searchTerm.getSQLForTerm();
425 if (!excludeQueryStr.isEmpty()) {
426 excludeQueryStr +=
" AND ";
428 excludeQueryStr += searchTerm.getSQLForTerm();
431 if (!includeQueryStr.isEmpty()) {
432 includeQueryStr =
"(" + includeQueryStr +
")";
434 if (!excludeQueryStr.isEmpty()) {
435 excludeQueryStr =
"(" + excludeQueryStr +
")";
437 if (includeQueryStr.isEmpty() || excludeQueryStr.isEmpty()) {
438 return includeQueryStr + excludeQueryStr;
440 return includeQueryStr +
" AND " + excludeQueryStr;
446 "SearchFiltering.ParentFilter.desc=Paths matching: {0}",
447 "SearchFiltering.ParentFilter.or=, ",
448 "SearchFiltering.ParentFilter.exact=(exact match)",
449 "SearchFiltering.ParentFilter.substring=(substring)",
450 "SearchFiltering.ParentFilter.included=(included)",
451 "SearchFiltering.ParentFilter.excluded=(excluded)"})
456 if (!desc.isEmpty()) {
457 desc += Bundle.SearchFiltering_ParentFilter_or();
459 if (searchTerm.isFullPath()) {
460 desc += searchTerm.getSearchStr() + Bundle.SearchFiltering_ParentFilter_exact();
462 desc += searchTerm.getSearchStr() + Bundle.SearchFiltering_ParentFilter_substring();
464 if (searchTerm.isIncluded()) {
465 desc += Bundle.SearchFiltering_ParentFilter_included();
467 desc += Bundle.SearchFiltering_ParentFilter_excluded();
470 desc = Bundle.SearchFiltering_ParentFilter_desc(desc);
493 String queryStr =
"";
494 for (DataSource ds : dataSources) {
495 if (!queryStr.isEmpty()) {
498 queryStr +=
"\'" + ds.getId() +
"\'";
500 queryStr =
"data_source_obj_id IN (" + queryStr +
")";
506 "SearchFiltering.DataSourceFilter.desc=Data source(s): {0}",
507 "SearchFiltering.DataSourceFilter.or=, ",
508 "# {0} - Data source name",
509 "# {1} - Data source ID",
510 "SearchFiltering.DataSourceFilter.datasource={0}({1})",})
514 for (DataSource ds : dataSources) {
515 if (!desc.isEmpty()) {
516 desc += Bundle.SearchFiltering_DataSourceFilter_or();
518 desc += Bundle.SearchFiltering_DataSourceFilter_datasource(ds.getName(), ds.getId());
520 desc = Bundle.SearchFiltering_DataSourceFilter_desc(desc);
546 String queryStr =
"(obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_id IN "
547 +
"(SELECT artifact_id FROM blackboard_attributes WHERE artifact_type_id = 9 AND attribute_type_ID = 37 "
548 +
"AND (" + keywordListPart +
"))))";
555 "SearchFiltering.KeywordListFilter.desc=Keywords in list(s): {0}",})
584 this.categories =
new ArrayList<>();
585 this.categories.add(category);
590 String queryStr =
"";
591 for (
Type cat : categories) {
592 for (String type : cat.getMediaTypes()) {
593 if (!queryStr.isEmpty()) {
596 queryStr +=
"\'" + type +
"\'";
599 queryStr =
"mime_type IN (" + queryStr +
")";
605 "SearchFiltering.FileTypeFilter.desc=Type: {0}",
606 "SearchFiltering.FileTypeFilter.or=, ",})
610 for (
Type cat : categories) {
611 if (!desc.isEmpty()) {
612 desc += Bundle.SearchFiltering_FileTypeFilter_or();
614 desc += cat.toString();
616 desc = Bundle.SearchFiltering_FileTypeFilter_desc(desc);
654 freqAttr.addAttributeToResults(currentResults, caseDb, centralRepoDb);
657 List<Result> frequencyResults =
new ArrayList<>();
658 for (
Result file : currentResults) {
659 if (frequencies.contains(file.getFrequency())) {
660 frequencyResults.add(file);
663 return frequencyResults;
668 "SearchFiltering.FrequencyFilter.desc=Past occurrences: {0}",
669 "SearchFiltering.FrequencyFilter.or=, ",})
674 if (!desc.isEmpty()) {
675 desc += Bundle.SearchFiltering_FrequencyFilter_or();
677 desc += freq.toString();
679 return Bundle.SearchFiltering_FrequencyFilter_desc(desc);
704 String queryStr =
"(obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_id IN "
705 +
"(SELECT artifact_id FROM blackboard_attributes WHERE artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_HASHSET_HIT.getTypeID()
706 +
" AND attribute_type_ID = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SET_NAME.getTypeID() +
" "
707 +
"AND (" + hashSetPart +
"))))";
714 "FileSearchFiltering.HashSetFilter.desc=Hash set hits in set(s): {0}",})
742 String queryStr =
"(obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_id IN "
743 +
"(SELECT artifact_id FROM blackboard_attributes WHERE artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_INTERESTING_FILE_HIT.getTypeID()
744 +
" AND attribute_type_ID = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SET_NAME.getTypeID() +
" "
745 +
"AND (" + intItemSetPart +
"))))";
752 "SearchFiltering.InterestingItemSetFilter.desc=Interesting item hits in set(s): {0}",})
780 String queryStr =
"(obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_id IN "
781 +
"(SELECT artifact_id FROM blackboard_attributes WHERE artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_OBJECT_DETECTED.getTypeID()
782 +
" AND attribute_type_ID = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DESCRIPTION.getTypeID() +
" "
783 +
"AND (" + objTypePart +
"))))";
790 "SearchFiltering.ObjectDetectionFilter.desc=Objects detected in set(s): {0}",})
820 String hashsetQueryPart =
"";
821 String tagQueryPart =
"";
822 String intItemQueryPart =
"";
826 hashsetQueryPart =
" (known = " + TskData.FileKnown.BAD.getFileKnownValue() +
") ";
831 intItemQueryPart =
" (obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_type_id = "
832 + BlackboardArtifact.ARTIFACT_TYPE.TSK_INTERESTING_FILE_HIT.getTypeID() +
")) ";
837 tagQueryPart =
"(obj_id IN (SELECT obj_id FROM content_tags))";
840 tagQueryPart =
"(obj_id IN (SELECT obj_id FROM content_tags WHERE tag_name_id IN (SELECT tag_name_id FROM tag_names WHERE knownStatus = "
841 + TskData.FileKnown.BAD.getFileKnownValue() +
")))";
844 tagQueryPart =
"(obj_id IN (SELECT obj_id FROM content_tags WHERE tag_name_id IN (SELECT tag_name_id FROM tag_names WHERE knownStatus != "
845 + TskData.FileKnown.BAD.getFileKnownValue() +
")))";
848 String queryStr = hashsetQueryPart;
849 if (!intItemQueryPart.isEmpty()) {
850 if (!queryStr.isEmpty()) {
853 queryStr += intItemQueryPart;
855 if (!tagQueryPart.isEmpty()) {
856 if (!queryStr.isEmpty()) {
859 queryStr += tagQueryPart;
866 "SearchFiltering.ScoreFilter.desc=Score(s) of : {0}",})
869 return Bundle.SearchFiltering_ScoreFilter_desc(
894 for (TagName tagName : tagNames) {
895 if (!tagIDs.isEmpty()) {
898 tagIDs += tagName.getId();
901 String queryStr =
"(obj_id IN (SELECT obj_id FROM content_tags WHERE tag_name_id IN (" + tagIDs +
")))";
908 "FileSearchFiltering.TagsFilter.desc=Tagged {0}",
909 "FileSearchFiltering.TagsFilter.or=, ",})
913 for (TagName name : tagNames) {
914 if (!desc.isEmpty()) {
915 desc += Bundle.FileSearchFiltering_TagsFilter_or();
917 desc += name.getDisplayName();
919 return Bundle.FileSearchFiltering_TagsFilter_desc(desc);
931 return "(obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_id IN "
932 +
"(SELECT artifact_id FROM blackboard_attributes WHERE artifact_type_id = "
933 + BlackboardArtifact.ARTIFACT_TYPE.TSK_USER_CONTENT_SUSPECTED.getTypeID() +
")))";
937 "FileSearchFiltering.UserCreatedFilter.desc=that contain EXIF data",})
940 return Bundle.FileSearchFiltering_UserCreatedFilter_desc();
966 if (centralRepoDb == null) {
967 throw new DiscoveryException(
"Can not run Previously Notable filter with null Central Repository DB");
972 if (currentResults.isEmpty()) {
977 List<Result> notableResults =
new ArrayList<>();
982 for (
Result result : currentResults) {
991 notableResults.add(result);
995 return notableResults;
1001 @NbBundle.Messages({
1002 "FileSearchFiltering.PreviouslyNotableFilter.desc=that were previously marked as notable",})
1005 return Bundle.FileSearchFiltering_PreviouslyNotableFilter_desc();
1016 return "known!=" + TskData.FileKnown.KNOWN.getFileKnownValue();
1019 @NbBundle.Messages({
1020 "FileSearchFiltering.KnownFilter.desc=which are not known"})
1023 return Bundle.FileSearchFiltering_KnownFilter_desc();
1034 @NbBundle.Messages({
1035 "FileSearchFiltering.concatenateSetNamesForDisplay.comma=, ",})
1038 for (String setName : setNames) {
1039 if (!desc.isEmpty()) {
1040 desc += Bundle.FileSearchFiltering_concatenateSetNamesForDisplay_comma();
1057 for (String setName : setNames) {
1058 if (!result.isEmpty()) {
1061 result +=
"value_text = \'" + setName +
"\'";
static String concatenateNamesForSQL(List< String > setNames)
static List< BlackboardAttribute.ATTRIBUTE_TYPE > dateAttributes
final List< Score > scores
FrequencyFilter(List< Frequency > frequencies)
staticfinal long NO_MAXIMUM
ParentFilter(List< ParentSearchTerm > parentSearchTerms)
final List< String > listNames
HashSetFilter(List< String > setNames)
ParentSearchTerm(String searchStr, boolean isFullPath, boolean isIncluded)
static List< CorrelationAttributeInstance.Type > getDefaultCorrelationTypes()
ObjectDetectionFilter(List< String > typeNames)
KeywordListFilter(List< String > listNames)
final List< String > setNames
ArtifactDateRangeFilter(Long startDate, Long endDate)
FileTypeFilter(Type category)
Long getCountArtifactInstancesKnownBad(CorrelationAttributeInstance.Type aType, String value)
boolean useAlternateFilter()
AbstractFile getFirstInstance()
static String concatenateSetNamesForDisplay(List< String > setNames)
final List< DataSource > dataSources
final List< ARTIFACT_TYPE > types
final List< ParentSearchTerm > parentSearchTerms
boolean useAlternateFilter()
SizeFilter(List< FileSize > fileSizes)
List< Result > applyAlternateFilter(List< Result > currentResults, SleuthkitCase caseDb, CentralRepository centralRepoDb)
ScoreFilter(List< Score > scores)
ArtifactTypeFilter(List< ARTIFACT_TYPE > types)
final List< String > typeNames
final List< FileSize > fileSizes
List< Result > applyAlternateFilter(List< Result > currentResults, SleuthkitCase caseDb, CentralRepository centralRepoDb)
final List< Frequency > frequencies
DataSourceFilter(List< DataSource > dataSources)
InterestingFileSetFilter(List< String > setNames)
FileTypeFilter(List< Type > categories)
static List< Result > getResultList(List< AbstractFilter > filters, String combinedQuery, SleuthkitCase caseDb, CentralRepository centralRepoDb)
final List< String > setNames
static final int FILES_TYPE_ID
final List< Type > categories