<jupyter_start><jupyter_text>Elasticsearch and log2timelineExercise 4: Master of Advanced Studies in Digital Forensics & Cyber InvestigationData Analytics and Visualization for Digital Forensics(c) <NAME>, 2021  1 The examples are in the Part 4 notebook. This notebook only contains initializations needed to run the exercises at the endFirst install Plaso-tools as we did in exercise 3<jupyter_code># various install steps to install plaso tools and dependencies to get plaso working in colab
# -y option is to skip user interaction
# some packages need to be deinstalled and reinstalled to resolve dependencies
# these steps take app. 3 minutes to complete on a fresh colab instance
!add-apt-repository -y ppa:gift/stable
!apt update
!apt-get update
!apt install plaso-tools
!pip uninstall -y pytsk3
!pip install pytsk3
!pip uninstall -y yara-python
!pip install yara-python
!pip uninstall -y lz4
!pip install lz4
%%bash

wget -q https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-oss-7.9.2-linux-x86_64.tar.gz
wget -q https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-oss-7.9.2-linux-x86_64.tar.gz.sha512
tar -xzf elasticsearch-oss-7.9.2-linux-x86_64.tar.gz
sudo chown -R daemon:daemon elasticsearch-7.9.2/
shasum -a 512 -c elasticsearch-oss-7.9.2-linux-x86_64.tar.gz.sha512<jupyter_output>elasticsearch-oss-7.9.2-linux-x86_64.tar.gz: OK<jupyter_text>Run Elasticsearch as a daemon process<jupyter_code>import time
%%bash --bg

sudo -H -u daemon elasticsearch-7.9.2/bin/elasticsearch
# Sleep for few seconds to let the instance start.
time.sleep(20)<jupyter_output><empty_output><jupyter_text>Once the instance has been started, grep for elasticsearch in the processes list to confirm the availability.<jupyter_code>%%bash

ps -ef | grep elasticsearch<jupyter_output>root       10553   10551  0 13:39 ?        00:00:00 sudo -H -u daemon elasticsearch-7.9.2/bin/elasticsearch
daemon     10554   10553 65 13:39 ?        00:00:23 /content/elasticsearch-7.9.2/jdk/bin/java -Xshare:auto -Des.networkaddress.cache.ttl=60 -Des.networkaddress.cache.negative.ttl=10 -XX:+AlwaysPreTouch -Xss1m -Djava.awt.headless=true -Dfile.encoding=UTF-8 -Djna.nosys=true -XX:-OmitStackTraceInFastThrow -XX:+ShowCodeDetailsInExceptionMessages -Dio.netty.noUnsafe=true -Dio.netty.noKeySetOptimization=true -Dio.netty.recycler.maxCapacityPerThread=0 -Dio.netty.allocator.numDirectArenas=0 -Dlog4j.shutdownHookEnabled=false -Dlog4j2.disable.jmx=true -Djava.locale.providers=SPI,COMPAT -Xms1g -Xmx1g -XX:+UseG1GC -XX:G1ReservePercent=25 -XX:InitiatingHeapOccupancyPercent=30 -Djava.io.tmpdir=/tmp/elasticsearch-9282068232002452745 -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=data -XX:ErrorFile=logs/hs_err_pid%p.log -Xlog:gc*,gc+age=trace,safepoint:file=logs/gc.log:utctime,pid,tags:filecou[...]<jupyter_text>query the base endpoint to retrieve information about the cluster.<jupyter_code>%%bash

curl -sX GET "localhost:9200/"
# This command created an index in Elasticsearch
#
# this is a useful page to look up Elasticsearch REST api calls :
# https://www.elastic.co/guide/en/elasticsearch/reference/6.8/cat-indices.html
#
!curl -X GET "localhost:9200/_cat/indices?format=json&pretty"
from google.colab import drive
drive.mount('/content/gdrive')
# In part 3 (step 3) we stored the mus2019ctf.plaso file in your drive. 
#
plaso_file = 'gdrive/MyDrive/mus2019ctf.plaso'
#
# and check if it's there
#
!ls -l $plaso_file<jupyter_output>-rw------- 1 root root 450285568 Aug 23 22:10 gdrive/MyDrive/mus2019ctf.plaso<jupyter_text>Use psort to write events to Elasticsearch that we setup earlier. We can use the elastic output format<jupyter_code># run psort.py. It takes about 10 minutes to export all rows from the 430MB plaso file
#
!psort.py -o elastic --server localhost --port 9200 --elastic_mappings /usr/share/plaso/elasticsearch.mappings --index_name mus2019ctf $plaso_file --status_view none 
# So far we have been accessing information directly with curl from the Elasticsearch REST API
# The is also an Elasticsearch Python API that we can use
#
from elasticsearch import Elasticsearch
es = Elasticsearch([{'host': 'localhost', 'port': 9200}])
# We define a Python function to list results
#
def print_results(response):
  for num, doc in enumerate(response['hits']['hits']):
    print(num, '-->', doc['_source'], "\n") 
# First we define some helper functions:

def print_facets(agg_dict):
  sum=0
  for field, val in agg_dict:
      print("facets of field ", field,':')
      for bucket in val['buckets']:
        for key in bucket:
          if key=='key':
            print('\t',bucket[key],end='=')
          else:
            print(bucket[key],end='')
            sum = sum + bucket[key]
        print()
      print("total number of hits for ",field," is ",sum)

def print_hit_stats(response):
  print('hit stats:')
  for key, val in response['hits'].items():
      print(key, val)
  print('\n')<jupyter_output><empty_output><jupyter_text>Exercises  1 Use elasticsearch to filter events in between 2019-03-12 and 2019-03-22<jupyter_code>query = '{"query": { "query_string": {"query": "datetime:[2019-03-12 TO 2019-03-22]"  }}}'

response = es.search(index="mus2019ctf", body=query, size=5)
print_hit_stats(response)
print_results(response)<jupyter_output>hit stats:
total {'value': 10000, 'relation': 'gte'}
max_score 1.0
hits [{'_index': 'mus2019ctf', '_type': '_doc', '_id': 'jzprd3sBaOcAQqTIHwsi', '_score': 1.0, '_source': {'data_type': 'windows:evtx:record', 'parser': 'winevtx', 'computer_name': 'DESKTOP-0QT8017', 'event_identifier': 100, 'event_level': 4, 'message_identifier': 100, 'offset': 0, 'record_number': 4679, 'recovered': False, 'source_name': 'Microsoft-Windows-Diagnosis-DPS', 'strings': ['{180B3A99-8C39-4F12-B631-2031998EFE45}', '{5AE2C742-1D4A-4568-A41A-73B87D7A808B}', '{00000000-0000-0000-0000-000000000000}', '%windir%\\system32\\radardt.dll', '{45DE1EA9-10BC-4F96-9B21-4B6B83DBF476}'], 'user_sid': 'S-1-5-19', 'xml_string': '<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">\n  <System>\n    <Provider Name="Microsoft-Windows-Diagnosis-DPS" Guid="{6BBA3851-2C7E-4DEA-8F54-31E5AFD029E3}"/>\n    <EventID>100</EventID>\n    <Version>0</Version>\n    <Level>4</Level>\n    <Task>1</Task>\n    <Opcode>12</Opcode[...]<jupyter_text>2 Write a query that performs an aggregation on source_long and source_short (can you find the right field names?)<jupyter_code>query = '{"query": {"match_all": {}}, "aggs": { "source_short": { "terms": { "field": "source_short.keyword"}},  "source_long": { "terms": { "field": "source_long.keyword"}}}}'

response = es.search(index="mus2019ctf", body=query, size=0)

print_hit_stats(response)
print_facets(response['aggregations'].items())<jupyter_output>hit stats:
total {'value': 10000, 'relation': 'gte'}
max_score None
hits []


facets of field  source_long :
	 NTFS USN change=292405
	 Registry Key=281913
	 WinEVTX=222198
	 File stat=3804
	 MSIE WebCache container record=1081
	 WinPrefetch=839
	 AppCompatCache Registry Entry=742
	 Registry Key - Service=588
	 Task Cache=537
	 System=257
total number of hits for  source_long  is  804364
facets of field  source_short :
	 FILE=296260
	 REG=283847
	 EVT=222198
	 LOG=1302
	 WEBHIST=1172
	 LNK=96
	 PE=61
	 OLECF=41
	 AMCACHE=8
total number of hits for  source_short  is  1609349<jupyter_text>3 Combine your date range filter from exercise 1 with facet aggregation in exercise 2<jupyter_code>querystring = '{ "query_string": {"query": "datetime:[2019-03-18 TO 2019-03-19]"  }}'
facets = '"aggs": { "source_short": { "terms": { "field": "source_short.keyword"}},  "source_long": { "terms": { "field": "source_long.keyword"}}}'
query = '{"query": %s,%s}' % (querystring,facets)
print_hit_stats(response)
response = es.search(index="mus2019ctf", body=query, size=0)

print_facets(response['aggregations'].items())<jupyter_output>hit stats:
total {'value': 10000, 'relation': 'gte'}
max_score None
hits []


facets of field  source_long :
	 NTFS USN change=43212
	 WinEVTX=12808
	 File stat=412
	 MSIE WebCache container record=302
	 Registry Key=250
	 WinPrefetch=230
	 Task Cache=58
	 Background Activity Moderator Registry Entry=21
	 MSIE WebCache containers record=17
	 Windows Shortcut=17
total number of hits for  source_long  is  57327
facets of field  source_short :
	 FILE=43633
	 EVT=12808
	 REG=332
	 WEBHIST=319
	 LOG=232
	 LNK=17
	 OLECF=13
total number of hits for  source_short  is  114681<jupyter_text>4 ***Advanced*** Create an aggregation accross 3 fields and visualise them in a treemap or sunburst plot<jupyter_code># The source_short looks interesting
# let's focus on REG, LOG and FILE
# 

facets = '"aggs": {   "source_long": { "terms": { "field": "source_long.keyword"}}}'
daterange = 'datetime:[2019-03-12 TO 2019-03-22]'
# FILE:
querystring_file = '{ "query_string": {"query": "%s AND  source_short:FILE"  }}' % daterange
query_file = '{"query": %s,%s}' % (querystring_file,facets)
# LOG:
querystring_log = '{ "query_string": {"query": "%s AND  source_short:LOG"  }}' % daterange
query_log = '{"query": %s,%s}' % (querystring_log,facets)
# REG
querystring_reg = '{ "query_string": {"query": "%s AND  source_short:REG"  }}' % daterange
query_reg = '{"query": %s,%s}' % (querystring_reg,facets)


response_reg = es.search(index="mus2019ctf", body=query_reg, size=0)
response_file = es.search(index="mus2019ctf", body=query_file, size=0)
response_log = es.search(index="mus2019ctf", body=query_log, size=0)


print(query_reg)
print_facets(response_reg['aggregations'].items())

print(query_file)
print_facets(response_file['aggregations'].items())

print(query_log)
print_facets(response_log['aggregations'].items())
# create a variation of the print_facets function that outputs python code for filling a dataframe
#
# the data frame has 3 columns: source_long, source_short, count
# 
def print_list_of_lists(agg_dict,name):

  for field, val in agg_dict:
      print("data = [",end='')
      for bucket in val['buckets']:
        for key in bucket:
          if key=='key':
            print('[\'',name,'\',\'',bucket[key],end='\',')
          else:
            print(bucket[key],end='],')
      print(']\n')     

print_list_of_lists(response_file['aggregations'].items(),'FILE')
print_list_of_lists(response_reg['aggregations'].items(),'REG')
print_list_of_lists(response_log['aggregations'].items(),'LOG')

# copy the ouput of the previous cell in this cell an manually clean it up
# the idea is we can mannual create a dataframe
#
# data = [['a1', 'b1', 'c1'],
#         ['a2', 'b2', 'c2'],
#         ['a3', 'b3', 'c3']]
# 
# df = pd.DataFrame(data)

import pandas as pd

data = [[' FILE ',' NTFS USN change',292405],[' FILE ',' File stat',1880],[' FILE ',' File entry shell item',25],
        [' REG ',' Registry Key',18055],[' REG ',' Task Cache',158],[' REG ',' Registry Key - Service',65],[' REG ',' Background Activity Moderator Registry Entry',36],[' REG ',' AppCompatCache Registry Entry',13],[' REG ',' Registry Key - Winlogon',3],[' REG ',' Registry Key - USB Entries',2],[' REG ',' Registry Key - Run Key',1],[' REG ',' Registry Key - User Account Information',1],[' REG ',' Registry Key Shutdown Entry',1],
        [' LOG ',' WinPrefetch',597],[' LOG ',' Windows Setupapi Log',18],[' LOG ',' System',17],[' LOG ',' System - Network Connection',1]]

df = pd.DataFrame(data)
df
# set column names

df.columns = ['source_short','source_long','count']
df
!pip install --upgrade plotly

import plotly.express as px
fig = px.treemap(df, path=['source_short', 'source_long'],values='count')
fig.show()
# note: it's a pitty of the large values but fortunately you can zoom in.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1437002822684116, 'fraction_numerical': 0.05876315114190403, 'mean_word_length': 5.0174987133299025, 'pattern_counts': {'":': 35, '<': 45, '<?xml version=': 0, '>': 45, 'https://': 3, 'lorem ipsum': 0, 'www.': 1, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '499051', 'license': 'Apache-2.0', 'n_tokens_mistral': 4557, 'n_tokens_neox': 4031, 'n_words': 1036, 'path': 'Part_4_Exercise_Answers.ipynb', 'repo_name': 'HansHenseler/masdav', 'size': '11698'}