Autopsy  4.19.3
Graphical digital forensics platform for The Sleuth Kit and other tools.
DomainSearchCacheLoader.java
Go to the documentation of this file.
1 /*
2  * Autopsy Forensic Browser
3  *
4  * Copyright 2020-2021 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.autopsy.discovery.search;
20 
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;
30 import java.util.Set;
31 import java.util.HashSet;
32 import java.util.Map;
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;
48 import org.sleuthkit.datamodel.CaseDbAccessManager;
49 import org.sleuthkit.datamodel.CaseDbAccessManager.CaseDbAccessQueryCallback;
50 import org.sleuthkit.datamodel.Content;
51 import org.sleuthkit.datamodel.SleuthkitCase;
52 import org.sleuthkit.datamodel.TskCoreException;
53 import org.sleuthkit.datamodel.TskData;
54 
60 class DomainSearchCacheLoader extends CacheLoader<SearchKey, Map<GroupKey, List<Result>>> {
61 
62  @Override
63  public Map<GroupKey, List<Result>> load(SearchKey key) throws DiscoveryException, SQLException, TskCoreException, InterruptedException {
64  List<Result> domainResults = getResultDomainsFromDatabase(key);
65  // Grouping by CR Frequency, for example, will require further processing
66  // in order to make the correct decision. The attribute types that require
67  // more information implement their logic by overriding `addAttributeToResults`.
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();
74  }
75  attr.addAttributeToResults(domainResults,
76  key.getSleuthkitCase(), key.getCentralRepository(), key.getContext());
77  }
78  // Apply secondary in memory filters
79  for (AbstractFilter filter : key.getFilters()) {
80  if (Thread.currentThread().isInterrupted()) {
81  throw new InterruptedException();
82  }
83  if (filter.useAlternateFilter()) {
84  domainResults = filter.applyAlternateFilter(domainResults, key.getSleuthkitCase(), key.getCentralRepository(), key.getContext());
85  }
86  }
87  // Sort the ResultDomains by the requested criteria.
88  final SearchResults searchResults = new SearchResults(
89  key.getGroupSortingType(),
90  key.getGroupAttributeType(),
91  key.getFileSortingMethod());
92  searchResults.add(domainResults);
93  return searchResults.toLinkedHashMap();
94  }
95 
104  List<Result> getResultDomainsFromDatabase(SearchKey key) throws TskCoreException, SQLException, DiscoveryException, InterruptedException {
105 
106  // Filters chosen in the UI are aggregated into SQL statements to be used in
107  // the queries that follow.
108  final Pair<String, String> domainsFilterClauses = createWhereAndHavingClause(key.getFilters());
109  final String domainsWhereClause = domainsFilterClauses.getLeft();
110  final String domainsHavingClause = domainsFilterClauses.getRight();
111  // artifact type is within the (optional) filter and the parent artifact
112  // had a date time attribute that was within the (optional) filter. With this
113  // table in hand, we can simply group by domain and apply aggregate functions
114  // to get, for example, # of downloads, # of visits in last 60, etc.
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), ',')"; //postgres string aggregator (requires specified separator
128  } else {
129  sqlSpecificAccountAggregator = "GROUP_CONCAT(DISTINCT(value_text))"; //sqlite string aggregator (uses comma separation by default)
130  }
131  /*
132  * As part of getting the known account types for a domain additional
133  * attribute values are necessary from the blackboard_attributes table
134  * This sub-query aggregates them and associates them with the artifact
135  * they correspond to.
136  */
137  final String accountsTable
138  = "SELECT " + sqlSpecificAccountAggregator + " as value_text," //naming field value_text the same as the field it is aggregating to re-use aggregator
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 ";
145 
146  // Needed to populate the visitsInLast60 data.
147  final Instant mostRecentActivityDate = Instant.ofEpochSecond(caseDb.getTimelineManager().getMaxEventTime());
148  final Instant sixtyDaysAgo = mostRecentActivityDate.minus(60, ChronoUnit.DAYS);
149 
150  // Check the group attribute, if by data source then the GROUP BY clause
151  // should group by data source id before grouping by domain.
152  final AttributeType groupAttribute = key.getGroupAttributeType();
153  final String groupByClause = (groupAttribute instanceof DataSourceAttribute)
154  ? "data_source_obj_id, domain" : "domain";
155 
156  final Optional<AbstractFilter> dataSourceFilter = key.getFilters().stream()
157  .filter(filter -> filter instanceof DataSourceFilter)
158  .findFirst();
159 
160  String dataSourceWhereClause = null;
161  if (dataSourceFilter.isPresent()) {
162  dataSourceWhereClause = dataSourceFilter.get().getWhereClause();
163  }
164 
165  // This query just processes the domains table, performing additional
166  // groupings and applying aggregate functions to calculate discovery data.
167  final String domainsQuery
168  = /*
169  * SELECT
170  */ " domain,"
171  + " MIN(date) AS activity_start,"
172  + " MAX(date) AS activity_end,"
173  + " SUM(CASE "
174  + " WHEN artifact_type_id = " + TSK_WEB_DOWNLOAD.getTypeID() + " THEN 1 "
175  + " ELSE 0 "
176  + " END) AS fileDownloads,"
177  + " SUM(CASE "
178  + " WHEN artifact_type_id = " + TSK_WEB_HISTORY.getTypeID() + " THEN 1 "
179  + " ELSE 0 "
180  + " END) AS totalPageViews,"
181  + " SUM(CASE "
182  + " WHEN artifact_type_id = " + TSK_WEB_HISTORY.getTypeID() + " AND"
183  + " date BETWEEN " + sixtyDaysAgo.getEpochSecond() + " AND " + mostRecentActivityDate.getEpochSecond() + " THEN 1 "
184  + " ELSE 0 "
185  + " END) AS pageViewsInLast60,"
186  + " SUM(CASE "
187  + " WHEN artifact_type_id = " + TSK_WEB_ACCOUNT_TYPE.getTypeID() + " THEN 1 "
188  + " ELSE 0 "
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 "
197  + // Add the data source where clause here if present.
198  ((dataSourceWhereClause != null) ? "WHERE " + dataSourceWhereClause + " " : "")
199  + "GROUP BY " + groupByClause;
200 
201  final CaseDbAccessManager dbManager = caseDb.getCaseDbAccessManager();
202  final DomainCallback domainCallback = new DomainCallback(caseDb);
203  dbManager.select(domainsQuery, domainCallback);
204 
205  if (domainCallback.getSQLException() != null) {
206  throw domainCallback.getSQLException();
207  }
208 
209  if (domainCallback.getTskCoreException() != null) {
210  throw domainCallback.getTskCoreException();
211  }
212 
213  if (domainCallback.getInterruptedException() != null) {
214  throw domainCallback.getInterruptedException();
215  }
216 
217  return domainCallback.getResultDomains();
218  }
219 
233  Pair<String, String> createWhereAndHavingClause(List<AbstractFilter> filters) {
234  final StringJoiner whereClause = new StringJoiner(" OR ", "(", ")");
235  final StringJoiner havingClause = new StringJoiner(" AND ", "(", ")");
236 
237  // Capture all types by default.
238  ArtifactTypeFilter artifactTypeFilter = new ArtifactTypeFilter(SearchData.Type.DOMAIN.getArtifactTypes());
239  boolean hasDateTimeFilter = false;
240 
241  for (AbstractFilter filter : filters) {
242  if (filter instanceof ArtifactTypeFilter) {
243  // Replace with user defined types.
244  artifactTypeFilter = ((ArtifactTypeFilter) filter);
245  } else if (filter != null && !(filter instanceof DataSourceFilter) && !filter.useAlternateFilter()) {
246  if (filter instanceof ArtifactDateRangeFilter) {
247  hasDateTimeFilter = true;
248  }
249 
250  whereClause.add("(" + filter.getWhereClause() + ")");
251  havingClause.add("SUM(CASE WHEN " + filter.getWhereClause() + " THEN 1 ELSE 0 END) > 0");
252  }
253  }
254 
255  if (!hasDateTimeFilter) {
256  whereClause.add(ArtifactDateRangeFilter.createAttributeTypeClause());
257  }
258 
259  String domainAttributeFilter = "attribute_type_id = " + TSK_DOMAIN.getTypeID()
260  + " AND value_text <> ''";
261 
262  whereClause.add("(" + domainAttributeFilter + ")");
263  havingClause.add("SUM(CASE WHEN " + domainAttributeFilter + " THEN 1 ELSE 0 END) > 0");
264 
265  return Pair.of(
266  whereClause.toString() + " AND (" + artifactTypeFilter.getWhereClause(Arrays.asList(TSK_WEB_ACCOUNT_TYPE)) + ")",
267  havingClause.toString()
268  );
269  }
270 
276  private class DomainCallback implements CaseDbAccessQueryCallback {
277 
278  private final List<Result> resultDomains;
279  private final SleuthkitCase skc;
280  private SQLException sqlCause;
281  private TskCoreException coreCause;
282  private InterruptedException interruptedException;
283 
284  private final Set<String> bannedDomains = new HashSet<String>() {
285  {
286  add("localhost");
287  add("127.0.0.1");
288  }
289  };
290 
296  private DomainCallback(SleuthkitCase skc) {
297  this.resultDomains = new ArrayList<>();
298  this.skc = skc;
299  }
300 
301  @Override
302  public void process(ResultSet resultSet) {
303  try {
304  resultSet.setFetchSize(500);
305 
306  while (resultSet.next()) {
307  if (Thread.currentThread().isInterrupted()) {
308  throw new InterruptedException();
309  }
310 
311  String domain = resultSet.getString("domain");
312 
313  if (bannedDomains.contains(domain)) {
314  // Skip banned domains
315  // Domain names are lowercased in the SQL query
316  continue;
317  }
318 
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);
328 
329  resultDomains.add(new ResultDomain(domain, activityStart,
330  activityEnd, totalPageViews, pageViewsInLast60, filesDownloaded,
331  countOfKnownAccountTypes, accountTypes, dataSource));
332  }
333  } catch (SQLException ex) {
334  this.sqlCause = ex;
335  } catch (TskCoreException ex) {
336  this.coreCause = ex;
337  } catch (InterruptedException ex) {
338  this.interruptedException = ex;
339  }
340  }
341 
349  private List<Result> getResultDomains() {
350  return Collections.unmodifiableList(this.resultDomains);
351  }
352 
358  private SQLException getSQLException() {
359  return this.sqlCause;
360  }
361 
367  private TskCoreException getTskCoreException() {
368  return this.coreCause;
369  }
370 
377  private InterruptedException getInterruptedException() {
378  return this.interruptedException;
379  }
380  }
381 }

Copyright © 2012-2022 Basis Technology. Generated on: Tue Jun 27 2023
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.