19 package org.sleuthkit.autopsy.filequery;
37 import java.util.ArrayList;
38 import java.util.List;
39 import java.util.logging.Level;
40 import java.util.stream.Collectors;
41 import org.openide.util.NbBundle;
49 class FileSearchFiltering {
51 private final static Logger logger = Logger.getLogger(FileSearchFiltering.class.getName());
63 static List<ResultFile> runQueries(List<FileFilter> filters, SleuthkitCase caseDb, EamDb centralRepoDb)
throws FileSearchException {
66 throw new FileSearchException(
"Case DB parameter is null");
70 String filterStr =
"";
71 for (FileFilter filter : filters) {
72 filterStr +=
" " + filter.getDesc() +
"\n";
74 logger.log(Level.INFO,
"Running filters:\n{0}", filterStr);
77 String combinedQuery =
"";
78 for (FileFilter filter : filters) {
79 if (!filter.getWhereClause().isEmpty()) {
80 if (!combinedQuery.isEmpty()) {
81 combinedQuery +=
" AND ";
83 combinedQuery +=
"(" + filter.getWhereClause() +
")";
87 if (combinedQuery.isEmpty()) {
89 throw new FileSearchException(
"Selected filters do not include a case database query");
92 return getResultList(filters, combinedQuery, caseDb, centralRepoDb);
93 }
catch (TskCoreException ex) {
94 throw new FileSearchException(
"Error querying case database", ex);
112 private static List<ResultFile> getResultList(List<FileFilter> filters, String combinedQuery, SleuthkitCase caseDb, EamDb centralRepoDb)
throws TskCoreException, FileSearchException {
114 List<ResultFile> resultList =
new ArrayList<>();
116 logger.log(Level.INFO,
"Running SQL query: {0}", combinedQuery);
117 List<AbstractFile> sqlResults = caseDb.findAllFilesWhere(combinedQuery);
120 if (sqlResults.isEmpty()) {
125 for (AbstractFile abstractFile : sqlResults) {
126 resultList.add(
new ResultFile(abstractFile));
130 for (FileFilter filter : filters) {
131 if (filter.useAlternateFilter()) {
132 resultList = filter.applyAlternateFilter(resultList, caseDb, centralRepoDb);
135 if (resultList.isEmpty()) {
145 static abstract class FileFilter {
154 abstract String getWhereClause();
162 boolean useAlternateFilter() {
180 List<ResultFile> applyAlternateFilter(List<ResultFile> currentResults, SleuthkitCase caseDb,
181 EamDb centralRepoDb)
throws FileSearchException {
182 return new ArrayList<>();
190 abstract String getDesc();
196 static class SizeFilter
extends FileFilter {
198 private final List<FileSize> fileSizes;
205 SizeFilter(List<FileSize> fileSizes) {
206 this.fileSizes = fileSizes;
210 String getWhereClause() {
211 String queryStr =
"";
212 for (FileSize size : fileSizes) {
213 if (!queryStr.isEmpty()) {
216 if (size.getMaxBytes() != FileSize.NO_MAXIMUM) {
217 queryStr +=
"(size > \'" + size.getMinBytes() +
"\' AND size <= \'" + size.getMaxBytes() +
"\')";
219 queryStr +=
"(size >= \'" + size.getMinBytes() +
"\')";
227 "FileSearchFiltering.SizeFilter.desc=Files with size in range(s): {0}",
228 "FileSearchFiltering.SizeFilter.or= or ",
229 "# {0} - Minimum bytes",
230 "# {1} - Maximum bytes",
231 "FileSearchFiltering.SizeFilter.range=({0} to {1})",})
235 for (FileSize size : fileSizes) {
236 if (!desc.isEmpty()) {
237 desc += Bundle.FileSearchFiltering_SizeFilter_or();
239 desc += Bundle.FileSearchFiltering_SizeFilter_range(size.getMinBytes(), size.getMaxBytes());
241 desc = Bundle.FileSearchFiltering_SizeFilter_desc(desc);
250 static class ParentSearchTerm {
252 private final String searchStr;
253 private final boolean fullPath;
254 private final boolean included;
265 ParentSearchTerm(String searchStr,
boolean isFullPath,
boolean isIncluded) {
266 this.searchStr = searchStr;
267 this.fullPath = isFullPath;
268 this.included = isIncluded;
276 String getSQLForTerm() {
280 return "parent_path=\'" + getSearchStr() +
"\'";
282 return "parent_path LIKE \'%" + getSearchStr() +
"%\'";
286 return "parent_path!=\'" + getSearchStr() +
"\'";
288 return "parent_path NOT LIKE \'%" + getSearchStr() +
"%\'";
294 "FileSearchFiltering.ParentSearchTerm.fullString= (exact)",
295 "FileSearchFiltering.ParentSearchTerm.subString= (substring)",
296 "FileSearchFiltering.ParentSearchTerm.includeString= (include)",
297 "FileSearchFiltering.ParentSearchTerm.excludeString= (exclude)",})
299 public String toString() {
300 String returnString = getSearchStr();
302 returnString += Bundle.FileSearchFiltering_ParentSearchTerm_fullString();
304 returnString += Bundle.FileSearchFiltering_ParentSearchTerm_subString();
307 returnString += Bundle.FileSearchFiltering_ParentSearchTerm_includeString();
309 returnString += Bundle.FileSearchFiltering_ParentSearchTerm_excludeString();
317 boolean isFullPath() {
324 boolean isIncluded() {
331 String getSearchStr() {
339 static class ParentFilter
extends FileFilter {
341 private final List<ParentSearchTerm> parentSearchTerms;
348 ParentFilter(List<ParentSearchTerm> parentSearchTerms) {
349 this.parentSearchTerms = parentSearchTerms;
353 String getWhereClause() {
354 String includeQueryStr =
"";
355 String excludeQueryStr =
"";
356 for (ParentSearchTerm searchTerm : parentSearchTerms) {
357 if (searchTerm.isIncluded()) {
358 if (!includeQueryStr.isEmpty()) {
359 includeQueryStr +=
" OR ";
361 includeQueryStr += searchTerm.getSQLForTerm();
363 if (!excludeQueryStr.isEmpty()) {
364 excludeQueryStr +=
" AND ";
366 excludeQueryStr += searchTerm.getSQLForTerm();
369 if (!includeQueryStr.isEmpty()) {
370 includeQueryStr =
"(" + includeQueryStr +
")";
372 if (!excludeQueryStr.isEmpty()) {
373 excludeQueryStr =
"(" + excludeQueryStr +
")";
375 if (includeQueryStr.isEmpty() || excludeQueryStr.isEmpty()) {
376 return includeQueryStr + excludeQueryStr;
378 return includeQueryStr +
" AND " + excludeQueryStr;
384 "FileSearchFiltering.ParentFilter.desc=Files with paths matching: {0}",
385 "FileSearchFiltering.ParentFilter.or= or ",
386 "FileSearchFiltering.ParentFilter.exact=(exact match)",
387 "FileSearchFiltering.ParentFilter.substring=(substring)",})
391 for (ParentSearchTerm searchTerm : parentSearchTerms) {
392 if (!desc.isEmpty()) {
393 desc += Bundle.FileSearchFiltering_ParentFilter_or();
395 if (searchTerm.isFullPath()) {
396 desc += searchTerm.getSearchStr() + Bundle.FileSearchFiltering_ParentFilter_exact();
398 desc += searchTerm.getSearchStr() + Bundle.FileSearchFiltering_ParentFilter_substring();
401 desc = Bundle.FileSearchFiltering_ParentFilter_desc(desc);
409 static class DataSourceFilter
extends FileFilter {
411 private final List<DataSource> dataSources;
418 DataSourceFilter(List<DataSource> dataSources) {
419 this.dataSources = dataSources;
423 String getWhereClause() {
424 String queryStr =
"";
425 for (DataSource ds : dataSources) {
426 if (!queryStr.isEmpty()) {
429 queryStr +=
"\'" + ds.getId() +
"\'";
431 queryStr =
"data_source_obj_id IN (" + queryStr +
")";
437 "FileSearchFiltering.DataSourceFilter.desc=Files in data source(s): {0}",
438 "FileSearchFiltering.DataSourceFilter.or= or ",
439 "# {0} - Data source name",
440 "# {1} - Data source ID",
441 "FileSearchFiltering.DataSourceFilter.datasource={0}({1})",})
445 for (DataSource ds : dataSources) {
446 if (!desc.isEmpty()) {
447 desc += Bundle.FileSearchFiltering_DataSourceFilter_or();
449 desc += Bundle.FileSearchFiltering_DataSourceFilter_datasource(ds.getName(), ds.getId());
451 desc = Bundle.FileSearchFiltering_DataSourceFilter_desc(desc);
460 static class KeywordListFilter
extends FileFilter {
462 private final List<String> listNames;
469 KeywordListFilter(List<String> listNames) {
470 this.listNames = listNames;
474 String getWhereClause() {
475 String keywordListPart = concatenateNamesForSQL(listNames);
477 String queryStr =
"(obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_id IN "
478 +
"(SELECT artifact_id FROM blackboard_attributes WHERE artifact_type_id = 9 AND attribute_type_ID = 37 "
479 +
"AND (" + keywordListPart +
"))))";
486 "FileSearchFiltering.KeywordListFilter.desc=Files with keywords in list(s): {0}",})
489 return Bundle.FileSearchFiltering_KeywordListFilter_desc(concatenateSetNamesForDisplay(listNames));
496 static class FileTypeFilter
extends FileFilter {
498 private final List<FileType> categories;
505 FileTypeFilter(List<FileType> categories) {
506 this.categories = categories;
514 FileTypeFilter(FileType category) {
515 this.categories =
new ArrayList<>();
516 this.categories.add(category);
520 String getWhereClause() {
521 String queryStr =
"";
522 for (FileType cat : categories) {
523 for (String type : cat.getMediaTypes()) {
524 if (!queryStr.isEmpty()) {
527 queryStr +=
"\'" + type +
"\'";
530 queryStr =
"mime_type IN (" + queryStr +
")";
536 "FileSearchFiltering.FileTypeFilter.desc=Files with type: {0}",
537 "FileSearchFiltering.FileTypeFilter.or= or ",})
541 for (FileType cat : categories) {
542 if (!desc.isEmpty()) {
543 desc += Bundle.FileSearchFiltering_FileTypeFilter_or();
545 desc += cat.toString();
547 desc = Bundle.FileSearchFiltering_FileTypeFilter_desc(desc);
555 static class FrequencyFilter
extends FileFilter {
557 private final List<Frequency> frequencies;
564 FrequencyFilter(List<Frequency> frequencies) {
565 this.frequencies = frequencies;
569 String getWhereClause() {
576 boolean useAlternateFilter() {
581 List<ResultFile> applyAlternateFilter(List<ResultFile> currentResults, SleuthkitCase caseDb,
582 EamDb centralRepoDb)
throws FileSearchException {
586 if (currentResults.isEmpty()) {
587 throw new FileSearchException(
"Can not run on empty list");
591 FileSearch.FrequencyAttribute freqAttr =
new FileSearch.FrequencyAttribute();
592 freqAttr.addAttributeToResultFiles(currentResults, caseDb, centralRepoDb);
595 List<ResultFile> frequencyResults =
new ArrayList<>();
596 for (ResultFile file : currentResults) {
597 if (frequencies.contains(file.getFrequency())) {
598 frequencyResults.add(file);
601 return frequencyResults;
606 "FileSearchFiltering.FrequencyFilter.desc=Files with frequency: {0}",
607 "FileSearchFiltering.FrequencyFilter.or= or ",})
611 for (Frequency freq : frequencies) {
612 if (!desc.isEmpty()) {
613 desc += Bundle.FileSearchFiltering_FrequencyFilter_or();
617 return Bundle.FileSearchFiltering_FrequencyFilter_desc(desc);
625 static class HashSetFilter
extends FileFilter {
627 private final List<String> setNames;
634 HashSetFilter(List<String> setNames) {
635 this.setNames = setNames;
639 String getWhereClause() {
640 String hashSetPart = concatenateNamesForSQL(setNames);
642 String queryStr =
"(obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_id IN "
643 +
"(SELECT artifact_id FROM blackboard_attributes WHERE artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_HASHSET_HIT.getTypeID()
644 +
" AND attribute_type_ID = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SET_NAME.getTypeID() +
" "
645 +
"AND (" + hashSetPart +
"))))";
652 "FileSearchFiltering.HashSetFilter.desc=Files with hash set hits in set(s): {0}",})
655 return Bundle.FileSearchFiltering_HashSetFilter_desc(concatenateSetNamesForDisplay(setNames));
663 static class InterestingFileSetFilter
extends FileFilter {
665 private final List<String> setNames;
672 InterestingFileSetFilter(List<String> setNames) {
673 this.setNames = setNames;
677 String getWhereClause() {
678 String intItemSetPart = concatenateNamesForSQL(setNames);
680 String queryStr =
"(obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_id IN "
681 +
"(SELECT artifact_id FROM blackboard_attributes WHERE artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_INTERESTING_FILE_HIT.getTypeID()
682 +
" AND attribute_type_ID = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_SET_NAME.getTypeID() +
" "
683 +
"AND (" + intItemSetPart +
"))))";
690 "FileSearchFiltering.InterestingItemSetFilter.desc=Files with interesting item hits in set(s): {0}",})
693 return Bundle.FileSearchFiltering_InterestingItemSetFilter_desc(concatenateSetNamesForDisplay(setNames));
701 static class ObjectDetectionFilter
extends FileFilter {
703 private final List<String> typeNames;
710 ObjectDetectionFilter(List<String> typeNames) {
711 this.typeNames = typeNames;
715 String getWhereClause() {
716 String objTypePart = concatenateNamesForSQL(typeNames);
718 String queryStr =
"(obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_id IN "
719 +
"(SELECT artifact_id FROM blackboard_attributes WHERE artifact_type_id = " + BlackboardArtifact.ARTIFACT_TYPE.TSK_OBJECT_DETECTED.getTypeID()
720 +
" AND attribute_type_ID = " + BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DESCRIPTION.getTypeID() +
" "
721 +
"AND (" + objTypePart +
"))))";
728 "FileSearchFiltering.ObjectDetectionFilter.desc=Files with objects detected in set(s): {0}",})
731 return Bundle.FileSearchFiltering_ObjectDetectionFilter_desc(concatenateSetNamesForDisplay(typeNames));
739 static class ScoreFilter
extends FileFilter {
741 private final List<Score> scores;
748 ScoreFilter(List<Score> scores) {
749 this.scores = scores;
753 String getWhereClause() {
758 String hashsetQueryPart =
"";
759 String tagQueryPart =
"";
760 String intItemQueryPart =
"";
762 if (scores.contains(Score.NOTABLE)) {
764 hashsetQueryPart =
" (known = " + TskData.FileKnown.BAD.getFileKnownValue() +
") ";
767 if (scores.contains(Score.INTERESTING)) {
769 intItemQueryPart =
" (obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_type_id = "
770 + BlackboardArtifact.ARTIFACT_TYPE.TSK_INTERESTING_FILE_HIT.getTypeID() +
")) ";
773 if (scores.contains(Score.NOTABLE) && scores.contains(Score.INTERESTING)) {
775 tagQueryPart =
"(obj_id IN (SELECT obj_id FROM content_tags))";
776 }
else if (scores.contains(Score.NOTABLE)) {
778 tagQueryPart =
"(obj_id IN (SELECT obj_id FROM content_tags WHERE tag_name_id IN (SELECT tag_name_id FROM tag_names WHERE knownStatus = "
779 + TskData.FileKnown.BAD.getFileKnownValue() +
")))";
780 }
else if (scores.contains(Score.INTERESTING)) {
782 tagQueryPart =
"(obj_id IN (SELECT obj_id FROM content_tags WHERE tag_name_id IN (SELECT tag_name_id FROM tag_names WHERE knownStatus != "
783 + TskData.FileKnown.BAD.getFileKnownValue() +
")))";
786 String queryStr = hashsetQueryPart;
787 if (!intItemQueryPart.isEmpty()) {
788 if (!queryStr.isEmpty()) {
791 queryStr += intItemQueryPart;
793 if (!tagQueryPart.isEmpty()) {
794 if (!queryStr.isEmpty()) {
797 queryStr += tagQueryPart;
804 "FileSearchFiltering.ScoreFilter.desc=Files with score(s) of : {0}",})
807 return Bundle.FileSearchFiltering_ScoreFilter_desc(
808 concatenateSetNamesForDisplay(scores.stream().map(p -> p.toString()).collect(Collectors.toList())));
816 static class TagsFilter
extends FileFilter {
818 private final List<TagName> tagNames;
825 TagsFilter(List<TagName> tagNames) {
826 this.tagNames = tagNames;
830 String getWhereClause() {
832 for (TagName tagName : tagNames) {
833 if (!tagIDs.isEmpty()) {
836 tagIDs += tagName.getId();
839 String queryStr =
"(obj_id IN (SELECT obj_id FROM content_tags WHERE tag_name_id IN (" + tagIDs +
")))";
846 "FileSearchFiltering.TagsFilter.desc=Files that have been tagged {0}",
847 "FileSearchFiltering.TagsFilter.or= or ",})
851 for (TagName name : tagNames) {
852 if (!desc.isEmpty()) {
853 desc += Bundle.FileSearchFiltering_TagsFilter_or();
855 desc += name.getDisplayName();
857 return Bundle.FileSearchFiltering_TagsFilter_desc(desc);
864 static class ExifFilter
extends FileFilter {
874 String getWhereClause() {
875 return "(obj_id IN (SELECT obj_id from blackboard_artifacts WHERE artifact_id IN "
876 +
"(SELECT artifact_id FROM blackboard_attributes WHERE artifact_type_id = "
877 + BlackboardArtifact.ARTIFACT_TYPE.TSK_METADATA_EXIF.getTypeID() +
")))";
881 "FileSearchFiltering.ExifFilter.desc=Files that contain EXIF data",})
884 return Bundle.FileSearchFiltering_ExifFilter_desc();
892 static class NotableFilter
extends FileFilter {
902 String getWhereClause() {
909 boolean useAlternateFilter() {
914 List<ResultFile> applyAlternateFilter(List<ResultFile> currentResults, SleuthkitCase caseDb,
915 EamDb centralRepoDb)
throws FileSearchException {
917 if (centralRepoDb == null) {
918 throw new FileSearchException(
"Can not run Previously Notable filter with null Central Repository DB");
923 if (currentResults.isEmpty()) {
924 throw new FileSearchException(
"Can not run on empty list");
928 List<ResultFile> notableResults =
new ArrayList<>();
931 CorrelationAttributeInstance.Type type = CorrelationAttributeInstance.getDefaultCorrelationTypes().get(CorrelationAttributeInstance.FILES_TYPE_ID);
933 for (ResultFile file : currentResults) {
934 if (file.getFirstInstance().getMd5Hash() != null && !file.getFirstInstance().getMd5Hash().isEmpty()) {
937 String value = file.getFirstInstance().getMd5Hash();
938 if (centralRepoDb.getCountArtifactInstancesKnownBad(type, value) > 0) {
939 notableResults.add(file);
943 return notableResults;
944 }
catch (EamDbException | CorrelationAttributeNormalizationException ex) {
945 throw new FileSearchException(
"Error querying central repository", ex);
950 "FileSearchFiltering.PreviouslyNotableFilter.desc=Files that were previously marked as notable",})
953 return Bundle.FileSearchFiltering_PreviouslyNotableFilter_desc();
960 static class KnownFilter
extends FileFilter {
963 String getWhereClause() {
964 return "known!=" + TskData.FileKnown.KNOWN.getFileKnownValue();
968 "FileSearchFiltering.KnownFilter.desc=Files which are not known"})
971 return Bundle.FileSearchFiltering_KnownFilter_desc();
976 "FileSearchFiltering.concatenateSetNamesForDisplay.comma=, ",})
977 private static String concatenateSetNamesForDisplay(List<String> setNames) {
979 for (String setName : setNames) {
980 if (!desc.isEmpty()) {
981 desc += Bundle.FileSearchFiltering_concatenateSetNamesForDisplay_comma();
996 private static String concatenateNamesForSQL(List<String> setNames) {
998 for (String setName : setNames) {
999 if (!result.isEmpty()) {
1002 result +=
"value_text = \'" + setName +
"\'";
1007 private FileSearchFiltering() {