19 package org.sleuthkit.autopsy.discovery.search;
21 import com.google.common.cache.CacheLoader;
22 import java.sql.ResultSet;
23 import java.sql.SQLException;
24 import java.time.Instant;
25 import java.time.temporal.ChronoUnit;
26 import java.util.ArrayList;
27 import java.util.Arrays;
28 import java.util.Collections;
29 import java.util.List;
31 import java.util.HashSet;
33 import java.util.Optional;
34 import java.util.StringJoiner;
35 import org.apache.commons.lang3.tuple.Pair;
43 import static org.
sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_DOWNLOAD;
44 import static org.
sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_HISTORY;
45 import static org.
sleuthkit.datamodel.BlackboardArtifact.ARTIFACT_TYPE.TSK_WEB_ACCOUNT_TYPE;
46 import static org.
sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE.TSK_DOMAIN;
47 import static org.
sleuthkit.datamodel.BlackboardAttribute.ATTRIBUTE_TYPE.TSK_TEXT;
49 import org.
sleuthkit.datamodel.CaseDbAccessManager.CaseDbAccessQueryCallback;
60 class DomainSearchCacheLoader
extends CacheLoader<SearchKey, Map<GroupKey, List<Result>>> {
63 public Map<GroupKey, List<Result>> load(SearchKey key)
throws DiscoveryException, SQLException, TskCoreException, InterruptedException {
64 List<Result> domainResults = getResultDomainsFromDatabase(key);
68 Set<AttributeType> searchAttributes =
new HashSet<>();
69 searchAttributes.add(key.getGroupAttributeType());
70 searchAttributes.addAll(key.getFileSortingMethod().getRequiredAttributes());
71 for (AttributeType attr : searchAttributes) {
72 if (Thread.currentThread().isInterrupted()) {
73 throw new InterruptedException();
75 attr.addAttributeToResults(domainResults,
76 key.getSleuthkitCase(), key.getCentralRepository(), key.getContext());
79 for (AbstractFilter filter : key.getFilters()) {
80 if (Thread.currentThread().isInterrupted()) {
81 throw new InterruptedException();
83 if (filter.useAlternateFilter()) {
84 domainResults = filter.applyAlternateFilter(domainResults, key.getSleuthkitCase(), key.getCentralRepository(), key.getContext());
88 final SearchResults searchResults =
new SearchResults(
89 key.getGroupSortingType(),
90 key.getGroupAttributeType(),
91 key.getFileSortingMethod());
92 searchResults.add(domainResults);
93 return searchResults.toLinkedHashMap();
104 List<Result> getResultDomainsFromDatabase(SearchKey key)
throws TskCoreException, SQLException, DiscoveryException, InterruptedException {
108 final Pair<String, String> domainsFilterClauses = createWhereAndHavingClause(key.getFilters());
109 final String domainsWhereClause = domainsFilterClauses.getLeft();
110 final String domainsHavingClause = domainsFilterClauses.getRight();
115 final String domainsTable
116 =
"SELECT LOWER(MAX(value_text)) AS domain,"
117 +
" MAX(value_int64) AS date,"
118 +
" artifact_id AS parent_artifact_id,"
119 +
" MAX(artifact_type_id) AS parent_artifact_type_id "
120 +
"FROM blackboard_attributes "
121 +
"WHERE " + domainsWhereClause +
" "
122 +
"GROUP BY artifact_id "
123 +
"HAVING " + domainsHavingClause;
124 final SleuthkitCase caseDb = key.getSleuthkitCase();
125 String sqlSpecificAccountAggregator;
126 if (caseDb.getDatabaseType() == TskData.DbType.POSTGRESQL) {
127 sqlSpecificAccountAggregator =
"STRING_AGG(DISTINCT(value_text), ',')";
129 sqlSpecificAccountAggregator =
"GROUP_CONCAT(DISTINCT(value_text))";
137 final String accountsTable
138 =
"SELECT " + sqlSpecificAccountAggregator +
" as value_text,"
139 +
"artifact_id AS account_artifact_id "
140 +
"FROM blackboard_attributes "
141 +
"WHERE (attribute_type_id = " + TSK_TEXT.getTypeID()
142 +
" AND value_text <> '' "
143 +
" AND (artifact_type_id = " + TSK_WEB_ACCOUNT_TYPE.getTypeID() +
")) "
144 +
"GROUP BY artifact_id ";
147 final Instant mostRecentActivityDate = Instant.ofEpochSecond(caseDb.getTimelineManager().getMaxEventTime());
148 final Instant sixtyDaysAgo = mostRecentActivityDate.minus(60, ChronoUnit.DAYS);
152 final AttributeType groupAttribute = key.getGroupAttributeType();
153 final String groupByClause = (groupAttribute instanceof DataSourceAttribute)
154 ?
"data_source_obj_id, domain" :
"domain";
156 final Optional<AbstractFilter> dataSourceFilter = key.getFilters().stream()
157 .filter(filter -> filter instanceof DataSourceFilter)
160 String dataSourceWhereClause = null;
161 if (dataSourceFilter.isPresent()) {
162 dataSourceWhereClause = dataSourceFilter.get().getWhereClause();
167 final String domainsQuery
171 +
" MIN(date) AS activity_start,"
172 +
" MAX(date) AS activity_end,"
174 +
" WHEN artifact_type_id = " + TSK_WEB_DOWNLOAD.getTypeID() +
" THEN 1 "
176 +
" END) AS fileDownloads,"
178 +
" WHEN artifact_type_id = " + TSK_WEB_HISTORY.getTypeID() +
" THEN 1 "
180 +
" END) AS totalPageViews,"
182 +
" WHEN artifact_type_id = " + TSK_WEB_HISTORY.getTypeID() +
" AND"
183 +
" date BETWEEN " + sixtyDaysAgo.getEpochSecond() +
" AND " + mostRecentActivityDate.getEpochSecond() +
" THEN 1 "
185 +
" END) AS pageViewsInLast60,"
187 +
" WHEN artifact_type_id = " + TSK_WEB_ACCOUNT_TYPE.getTypeID() +
" THEN 1 "
189 +
" END) AS countOfKnownAccountTypes,"
190 +
" MAX(data_source_obj_id) AS dataSource, "
191 + sqlSpecificAccountAggregator +
" as accountTypes "
192 +
"FROM blackboard_artifacts as barts"
193 +
" JOIN (" + domainsTable +
") AS domains_table"
194 +
" ON barts.artifact_id = parent_artifact_id "
195 +
" LEFT JOIN (" + accountsTable +
") AS accounts_table"
196 +
" ON barts.artifact_id = account_artifact_id "
198 ((dataSourceWhereClause != null) ?
"WHERE " + dataSourceWhereClause +
" " :
"")
199 +
"GROUP BY " + groupByClause;
201 final CaseDbAccessManager dbManager = caseDb.getCaseDbAccessManager();
202 final DomainCallback domainCallback =
new DomainCallback(caseDb);
203 dbManager.select(domainsQuery, domainCallback);
205 if (domainCallback.getSQLException() != null) {
206 throw domainCallback.getSQLException();
209 if (domainCallback.getTskCoreException() != null) {
210 throw domainCallback.getTskCoreException();
213 if (domainCallback.getInterruptedException() != null) {
214 throw domainCallback.getInterruptedException();
217 return domainCallback.getResultDomains();
233 Pair<String, String> createWhereAndHavingClause(List<AbstractFilter> filters) {
234 final StringJoiner whereClause =
new StringJoiner(
" OR ",
"(",
")");
235 final StringJoiner havingClause =
new StringJoiner(
" AND ",
"(",
")");
238 ArtifactTypeFilter artifactTypeFilter =
new ArtifactTypeFilter(SearchData.Type.DOMAIN.getArtifactTypes());
239 boolean hasDateTimeFilter =
false;
241 for (AbstractFilter filter : filters) {
242 if (filter instanceof ArtifactTypeFilter) {
244 artifactTypeFilter = ((ArtifactTypeFilter) filter);
245 }
else if (filter != null && !(filter instanceof DataSourceFilter) && !filter.useAlternateFilter()) {
246 if (filter instanceof ArtifactDateRangeFilter) {
247 hasDateTimeFilter =
true;
250 whereClause.add(
"(" + filter.getWhereClause() +
")");
251 havingClause.add(
"SUM(CASE WHEN " + filter.getWhereClause() +
" THEN 1 ELSE 0 END) > 0");
255 if (!hasDateTimeFilter) {
256 whereClause.add(ArtifactDateRangeFilter.createAttributeTypeClause());
259 String domainAttributeFilter =
"attribute_type_id = " + TSK_DOMAIN.getTypeID()
260 +
" AND value_text <> ''";
262 whereClause.add(
"(" + domainAttributeFilter +
")");
263 havingClause.add(
"SUM(CASE WHEN " + domainAttributeFilter +
" THEN 1 ELSE 0 END) > 0");
266 whereClause.toString() +
" AND (" + artifactTypeFilter.getWhereClause(Arrays.asList(TSK_WEB_ACCOUNT_TYPE)) +
")",
267 havingClause.toString()
279 private final SleuthkitCase
skc;
297 this.resultDomains =
new ArrayList<>();
304 resultSet.setFetchSize(500);
306 while (resultSet.next()) {
307 if (Thread.currentThread().isInterrupted()) {
308 throw new InterruptedException();
311 String domain = resultSet.getString(
"domain");
313 if (bannedDomains.contains(domain)) {
319 long activityStart = resultSet.getLong(
"activity_start");
320 long activityEnd = resultSet.getLong(
"activity_end");
321 long filesDownloaded = resultSet.getLong(
"fileDownloads");
322 long totalPageViews = resultSet.getLong(
"totalPageViews");
323 long pageViewsInLast60 = resultSet.getLong(
"pageViewsInLast60");
324 long countOfKnownAccountTypes = resultSet.getLong(
"countOfKnownAccountTypes");
325 long dataSourceID = resultSet.getLong(
"dataSource");
326 String accountTypes = resultSet.getString(
"accountTypes");
327 Content dataSource = skc.getContentById(dataSourceID);
329 resultDomains.add(
new ResultDomain(domain, activityStart,
330 activityEnd, totalPageViews, pageViewsInLast60, filesDownloaded,
331 countOfKnownAccountTypes, accountTypes, dataSource));
333 }
catch (SQLException ex) {
335 }
catch (TskCoreException ex) {
337 }
catch (InterruptedException ex) {
338 this.interruptedException = ex;
350 return Collections.unmodifiableList(this.resultDomains);
void process(ResultSet resultSet)
DomainCallback(SleuthkitCase skc)
TskCoreException getTskCoreException()
final Set< String > bannedDomains
final List< Result > resultDomains
SQLException getSQLException()
InterruptedException getInterruptedException()
TskCoreException coreCause
List< Result > getResultDomains()
InterruptedException interruptedException