# @markdown Only execute if not already installed and running a cloud runtime
!pip install -q timesketch_api_client
!pip install -q vt-py nest_asyncio pandas
!pip install -q picatrix

# @title Import libraries
# @markdown This cell will import all the libraries needed for the running of this colab.

import re
import requests

import pandas as pd

from timesketch_api_client import config
from picatrix import notebook_init

import vt
import nest_asyncio # https://github.com/VirusTotal/vt-py/issues/21

nest_asyncio.apply()
notebook_init.init()

# @title VirusTotal Configuration
# @markdown In order to be able to lookup domains/IPs/samples using VirtusTotal we need to get an API key.
# @markdown
# @markdown If you don't have an API key you must sign up to [VirusTotal Community](https://www.virustotal.com/gui/join-us).
# @markdown Once you have a valid VirusTotal Community account you will find your personal API key in your personal settings section. 

VT_API_KEY = '' # @param {type: "string"}

# @markdown If you don't have the API key you will not be able to use the Virustotal API
# @markdown to lookup information.

# @title Declare functions

# @markdown This cell will define few functions that we will use throughout
# @markdown this colab. This would be better to define outside of the notebook
# @markdown in a library that would be imported, but we keep it here for now.

def print_dict(my_dict, space_before=0):
  """Print the content of a dictionary."""
  max_len = max([len(x) for x in my_dict.keys()])
  spaces = ' '*space_before
  format_str = f'{spaces}{{key:{max_len}s}} = {{value}}'
  for key, value in my_dict.items():
    if isinstance(value, dict):
      print(format_str.format(key=key, value=''))
      print_dict(value, space_before=space_before + 8)
    elif isinstance(value, list):
      value_str = ', '.join(value)
      print(format_str.format(key=key, value=value_str))
    else:
      print(format_str.format(key=key, value=value))


def ip_info(address):
  """Print out information about an IP address using the VT API."""
  url = 'https://www.virustotal.com/vtapi/v2/ip-address/report'
  params = {
      'apikey': VT_API_KEY,
      'ip': address}

  response = requests.get(url, params=params)
  j_obj = response.json()

  def _print_stuff(part):
    print('')
    header = part.replace('_', ' ').capitalize()
    print(f'{header}:')
    for item in j_obj.get(part, []):
      print_dict(item, 2)

  _print_stuff('resolutions')
  _print_stuff('detected_urls')
  _print_stuff('detected_referrer_samples')
  _print_stuff('detected_communicating_samples')
  _print_stuff('detected_downloaded_samples')

# @markdown Get a copy of the Timesketch client object.
# @markdown Parameters to configure the client:
# @markdown + host_uri: https://demo.timesketch.org
# @markdown + username: demo
# @markdown + auth_mode: timesketch (username/password)
# @markdown + password: demo

ts_client = config.get_client(confirm_choices=True)

for sketch in ts_client.list_sketches():
  if not sketch.name.startswith('Szechuan'):
    continue

  print('We found the sketch to use')
  print(f'[{sketch.id}] {sketch.name} - {sketch.description}')
  break

%timesketch_set_active_sketch 6

timesketch_list_saved_searches_func?

for status in sketch.get_analyzer_status():
  print(f'Analyzer: {status["analyzer"]} - status: {status["status"]}')
  print(f'Results: {status["results"]}')
  print('')

search_query = timesketch_query_func(
    'parser:"winreg/windows_version"',
    fields='datetime,key_path,data_type,message,timestamp_desc,parser,display_name,product_name,hostname,timestamp_desc'
)
cur_df = search_query.table

cur_df[['hostname', 'product_name']]

cur_df[cur_df.hostname == 'CITADEL-DC01'].product_name.value_counts()

cur_df[cur_df.hostname == 'DESKTOP-SDN1RPT'].product_name.value_counts()

cur_df = timesketch_query_func(
    'HKEY_LOCAL_MACHINE*System*Select AND hostname:"CITADEL-DC01"',
    fields=(
        'datetime,key_path,data_type,message,timestamp_desc,parser,display_name,'
        'product_name,hostname,timestamp_desc,values')
).table

for key, value in cur_df[['key_path', 'values']].values:
  print(f'Key: {key}')
  print(f'Value: {value}')

cur_df['current_value'] = cur_df['values'].str.extract(r'Current: \[[A-Z_]+\] (\d) ')

cur_df[['key_path', 'current_value']]

cur_df = timesketch_query_func(
    'TimeZoneInformation AND hostname:"CITADEL-DC01"',
    fields='datetime,key_path,data_type,message,timestamp_desc,parser,display_name,product_name,hostname,timestamp_desc,configuration'
).table
cur_df

pd.set_option('max_colwidth', 400)

cur_df[cur_df.key_path.str.contains('ControlSet001')][['configuration']]

lines = []

for value in cur_df[cur_df.key_path.str.contains('ControlSet001')]['configuration'].values:
  items = value.split(':')
  line_dict = {}
  key = items[0]
  for item in items[1:-1]:
    *values, new_key = item.split()

    line_dict[key] = ' '.join(values)
    key = new_key

  line_dict[key] = items[-1]
  lines.append(line_dict)

time_df = pd.DataFrame(lines)

time_df

%timesketch_available_aggregators

params = {
    'field': 'Source',
    'limit': 10,
    'supported_charts': 'hbarchart',
    'chart_title': 'Top 10 Source IP',
}

aggregation = timesketch_run_aggregator_func(
    'field_bucket', parameters=params
)
aggregation.chart

# Remove the commend and run this code if you are running in colab
# but have a local Jupyter kernel running:
# alt.renderers.enable('colab')

# Remove this comment if you are running in Jupyter and the chart is not displayed
# alt.renderers.enable('notebook')

aggregation.table

params = {
    'field': 'Destination',
    'limit': 10,
    'supported_charts': 'hbarchart',
    'chart_title': 'Top 10 Source IP',
}

aggregation = timesketch_run_aggregator_func('field_bucket', parameters=params)
aggregation.chart

attacker_dst = timesketch_query_func(
    'Source:"194.61.24.102" AND data_type:"pcap:wireshark:entry"',
    fields='datetime,message,timestamp_desc,Destination,DST port,Source,Protocol,src port').table
attacker_dst.head(10)

search_obj = timesketch_query_func(
    'Source:"194.61.24.102" AND data_type:"pcap:wireshark:entry"',
    fields='datetime,message,timestamp_desc,Destination,DST port,Source,Protocol,src port')

search_obj.max_entries = 150000
attacker_dst = search_obj.table
attacker_dst.head(10)

attacker_dst.shape

attacker_group = attacker_dst[['DST port','Destination', 'Protocol']].groupby(
    ['DST port','Destination'], as_index=False)

attacker_dst_mytable = attacker_group.count()
attacker_dst_mytable.rename(columns={'Protocol': 'Count'}, inplace=True)
attacker_dst_mytable.sort_values(by=['Count'], ascending=False)

attacker_dst = timesketch_query_func(
    '194.61.24.102 AND data_type:"scapy:pcap:entry"',
    fields='datetime,message,timestamp_desc,ip_flags,ip_dst,ip_src,payload,tcp_flags,tcp_seq,tcp_sport,tcp_dport,tcp_window').table

attacker_dst.head(10)

params = {
    'field': 'ip_src',
    'query_string': 'ip_flags:"evil"',
    'supported_charts': 'hbarchart',
    'chart_title': 'Source IPs with "evil" bit set',
}

aggregation = timesketch_run_aggregator_func('query_bucket', parameters=params)
aggregation.table

name = 'Source IPs with "evil" bit set'
aggregation.name = name
aggregation.title = name
aggregation.save()

attacker_dst.iloc[0].message

attacker_dst.message.str.slice(start=0, stop=30).unique()

attacker_packages = attacker_dst.message.str.slice(start=30).str.split('|', expand=True)

attacker_packages.head(3)

attacker_packages = attacker_packages[[0, 1, 2]]
attacker_packages.columns = ['ether', 'ip', 'transport']

attacker_packages.head(3)

attacker_packages[['transport']].head(10)

def parse_row(row):
  items = row.split()
  protocol = items[0][1:]
  line_dict = {
      'protocol': protocol
  }
  for item in items[1:]:
    key, _, value = item.partition('=')
    if key == 'options':
      # We don't want options nor anything after that.
      break
    line_dict[key] = value
  return line_dict

proto_df = pd.DataFrame(list(attacker_packages['transport'].apply(parse_row).values))

proto_df['datetime'] = attacker_dst['datetime']

proto_df.head(3)

proto_df[['datetime', 'protocol', 'type', 'dport']].head(10)

attacker_dst = timesketch_query_func(
    '(194.61.24.102 AND 10.42.85.10) AND data_type:"scapy:pcap:entry"', 
    fields='datetime,message,timestamp_desc,ip_flags,ip_dst,ip_src,payload,tcp_flags,tcp_seq,tcp_sport,tcp_dport,tcp_window', max_entries=500000).table
attacker_dst.head(10)

attacker_packages = attacker_dst.message.str.slice(start=30).str.split('|', expand=True)
attacker_packages = attacker_packages[[0, 1, 2]]
attacker_packages.columns = ['ether', 'ip', 'transport']

proto_df = pd.DataFrame(list(attacker_packages['transport'].apply(parse_row).values))
proto_df['datetime'] = attacker_dst['datetime']

proto_df[['datetime', 'protocol', 'type', 'dport']].head(10)

evtx_df = timesketch_query_func(
    '194.61.24.102 AND data_type:"windows:evtx:record"', fields='*').table

evtx_df.head(3)

evtx_df.username.value_counts()

evtx_df.event_identifier.value_counts()

evtx_df.source_name.value_counts()

evtx_df[evtx_df.username == 'Administrator'][['datetime', 'event_identifier', 'tag', 'logon_type', 'source_address']]

timesketch_query_func(
    'source_address:"194.61.24.102" AND data_type:"windows:evtx:record"',
    fields='logon_type,username').table[['logon_type', 'username']].drop_duplicates()

timeframe_df = timesketch_query_func(
    '*', start_date='2020-09-19T01:00:00', end_date='2020-09-19T04:20:00', max_entries=50000
).table

max_entries = 1500000

timeframe_df = timesketch_query_func(
    '*', start_date='2020-09-19T01:00:00', end_date='2020-09-19T04:20:00', max_entries=max_entries, fields='*'
).table

timeframe_df.shape

timeframe_df.data_type.value_counts()

group = timeframe_df[
    timeframe_df.data_type == 'windows:evtx:record'][['event_identifier', 'timestamp', 'source_name']].groupby(
        by=['event_identifier', 'source_name'], as_index=False
    )

group.count().rename(columns={'timestamp': 'count'}).sort_values('count', ascending=False)

timeframe_evtx = timeframe_df[timeframe_df.data_type == 'windows:evtx:record'].copy()
timeframe_evtx['event_identifier'] = timeframe_evtx.event_identifier.fillna(value=0)

timeframe_evtx[timeframe_evtx.event_identifier == 36888].strings.str.join('|').unique()

timeframe_evtx = timeframe_df[timeframe_df.data_type == 'windows:evtx:record'].copy()
timeframe_evtx['event_identifier'] = timeframe_evtx.event_identifier.fillna(value=0)

timeframe_evtx[(timeframe_evtx.event_identifier == 131) & (timeframe_evtx.source_name == 'Microsoft-Windows-RemoteDesktopServices-RdpCoreTS')].strings.str.join('|').unique()

timeframe_df.data_type.value_counts()

timeframe_df[timeframe_df.data_type == 'windows:prefetch:execution'][['datetime', 'executable', 'run_count']]

timeframe_df[timeframe_df.data_type == 'windows:prefetch:execution'].executable.value_counts()

timeframe_df[(timeframe_df.data_type == 'windows:prefetch:execution') & (~timeframe_df.run_count.isna()) & (timeframe_df.run_count < 2)][['executable', 'run_count']].drop_duplicates()

timeframe_evtx[(timeframe_evtx.event_identifier == 7045) & (timeframe_evtx.source_name == 'Service Control Manager')]['strings']

attacker_dst_http = timesketch_query_func(
    '(194.61.24.102 AND 10.42.85.10) AND data_type:"scapy:pcap:entry" AND *http* AND *GET*', 
    fields='datetime,message,timestamp_desc,ip_flags,ip_dst,ip_src,payload,tcp_flags,tcp_seq,tcp_sport,tcp_dport,tcp_window').table
attacker_dst_http.head(4)

attacker_dst.shape

attacker_dst_http[attacker_dst_http.message.str.contains(r'GET|POST')].message.str.extract(r'<Raw  load=([^|]+)')

for v in attacker_dst_http[attacker_dst_http.message.str.contains(r'GET|POST')].message.str.extract(r'<Raw  load=([^|]+)').values:
  value = str(v)
  print(value.replace('\\\\r\\\\n', '\n'))

coreupdater = timesketch_query_func(
    'coreupdater.exe AND data_type:"fs:stat"',
    fields='file_size,filename,hostname,message,data_type,datetime,sha256_hash').table

coreupdater.sort_values(by=['datetime'], ascending=True, inplace=True)
coreupdater.head(10)

coreupdater[['hostname', 'filename', 'sha256_hash']].drop_duplicates()

if VT_API_KEY:
  vt_client = vt.Client(VT_API_KEY)
else:
  vt_client = None

hash_value = list(coreupdater[coreupdater.filename == '\Windows\System32\coreupdater.exe'].sha256_hash.unique())[0]

if vt_client:
  file_info = vt_client.get_object(f'/files/{hash_value}')

  print_dict(file_info.last_analysis_stats)

if file_info:
  stars = '*'*10
  print(f'{stars}Summary{stars}')
  print('')
  print_dict(file_info.sigma_analysis_summary)
  print('')
  print(f'{stars}Analysis Stats{stars}')
  print('')
  print_dict(file_info.sigma_analysis_stats)
else:
  print('No VT API key defined, you\'ll need to manually loookup the information')

if file_info:
  print_dict(file_info.exiftool)

if file_info:
  print_dict(file_info.last_analysis_results)

coreupdater = timesketch_query_func(
    'coreupdater.exe AND NOT data_type:"fs:stat"', fields='*').table

coreupdater.sort_values(by=['datetime'], ascending=True, inplace=True)
coreupdater.head(10)

coreupdater.loc[coreupdater['data_type'] == 'autoruns:record']

coreupdater.loc[coreupdater['data_type'] == 'windows:registry:service'][['datetime', 'hostname', 'key_path', 'image_path', 'name', 'start_type', 'service_type', 'values']]

coreupdater.loc[coreupdater['data_type'] == 'pcap:wireshark:entry'][['datetime', 'message']]

%%timesketch_query search_obj --fields datetime,ip_src,message,timestamp_desc,tcp_dport,ip_dst --max_entries 150000
ip_src:10.42.85* AND NOT ip_dst:10.42.85* AND data_type:"scapy:pcap:entry"'

pd.options.display.max_colwidth = 200

data = search_obj.table
data[['datetime','ip_src', 'ip_dst', 'tcp_dport']]

mytable = data.groupby(['ip_src','ip_dst']).size().to_frame('count').reset_index()
mytable.sort_values(by=['count'], ascending=False, inplace=True)
mytable

ip_set = set()
list(map(ip_set.add, data['ip_src'].unique()))
list(map(ip_set.add, data['ip_dst'].unique()))

print(f'Found: {len(ip_set)} IPs (including internal)')

ip_info('194.61.24.102')

lines = []
url = 'https://www.virustotal.com/vtapi/v2/ip-address/report'

for ip_address in ip_set:
  if ip_address.startswith('10.'):
    continue

  params = {
      'apikey': VT_API_KEY,
      'ip': ip_address,
  }
  response = requests.get(url, params=params)
  j_obj = response.json()

  line_dict = {
      'resolutions': [x.get('hostname') for x in j_obj.get('resolutions', [])],
      'ip': ip_address,
      'detected_urls': j_obj.get('detected_urls'),
      'detected_downloaded_samples': [
          x.get('sha256') for x in j_obj.get('detected_downloaded_samples', [])] or None,
      'detected_referrer_samples': [
          x.get('sha256') for x in j_obj.get('detected_referrer_samples', [])] or None,
      'detected_communicating_samples': [
          x.get('sha256') for x in j_obj.get('detected_communicating_samples', [])] or None,
  }  
  lines.append(line_dict)
ip_df = pd.DataFrame(lines)

ip_df.head(3)

hash_value

ip_df[~ip_df.detected_referrer_samples.isna() & ip_df.detected_referrer_samples.str.contains(hash_value)]

ip_df[~ip_df.detected_communicating_samples.isna() & ip_df.detected_communicating_samples.str.contains(hash_value)]

ip_df[~ip_df.detected_downloaded_samples.isna() & ip_df.detected_downloaded_samples.str.contains(hash_value)]

ip_df[~ip_df.detected_downloaded_samples.isna()]

ip_df[~ip_df.detected_downloaded_samples.isna()]['ip'].value_counts()

timesketch_query_func(
    '"This program cannot be run in DOS" AND data_type:"scapy:pcap:entry"',
    fields='*'
).table[['ip_dst', 'ip_src']].drop_duplicates()

search_obj = %timesketch_query --fields * username:"Administrator"
other_df = search_obj.table

other_df.hostname.value_counts()

other_df[other_df['datetime'].dt.strftime('%Y%m%d') == '20200919'][['datetime', 'hostname', 'logon_type']]

other_df.data_type.value_counts()

other_df[other_df.data_type == 'windows:registry:mstsc:connection'][['datetime', 'display_name', 'hostname', 'message']]

day_filter = other_df.datetime.dt.strftime('%Y%m%d') == '20200919'
filtered_view = other_df[day_filter & other_df.tag.str.join(',').str.contains('logon-event')][[
    'datetime', 'computer_name', 'logon_process', 'logon_type', 'source_address', 'source_username', 'windows_domain', 'username', 'workstation']]
filtered_view

filtered_view.workstation.value_counts()

filtered_view[filtered_view.workstation == 'kali']

filtered_view[filtered_view.logon_type == 'RemoteInteractive']

secret_files = timesketch_query_func('secret', fields='*').table

secret_files.data_type.value_counts()

secret_files.parser.value_counts()

secret_files[secret_files.data_type == 'msie:webcache:container'][['datetime', 'timestamp_desc', 'url']]

secret_files[secret_files.message.str.contains(r'FileShare\/Secret')]['message'].unique()

secret_files[secret_files.message.str.contains(r'FileShare\/Secret')][['timestamp_desc', 'message']].drop_duplicates()

secret_files[secret_files.message.str.contains(r'FileShare\/Secret') & secret_files['timestamp_desc'] != 'Expiration Time'][['datetime', 'message']].drop_duplicates()

secret_files[secret_files.message.str.contains(r'FileShare\/Secret')].message.str.extract(r'(FileShare\/Secret\/[^ ]+)')[0].unique()

beth_df = secret_files[secret_files.message.str.contains('beth', case=False)]
beth_df.data_type.value_counts()

beth_df[beth_df.data_type == 'fs:stat'][['datetime', 'display_name', 'timestamp_desc']]

beth_df[beth_df.data_type == 'olecf:dest_list:entry'][['datetime', 'hostname', 'timestamp_desc', 'path']]

beth_df[beth_df.data_type.str.contains('windows:shell_item:file_entry|msie:webcache:container')][[
    'datetime', 'data_type', 'source_long', 'timestamp_desc', 'url', 'long_name', 'origin', 'shell_item_path']]

search_obj = %timesketch_query --fields * username:"Administrator" AND tag:"logon-event"
admin_login = search_obj.table

admin_login.shape

admin_login['datetime'] = pd.to_datetime(admin_login['datetime'])
admin_login[admin_login.datetime.dt.strftime('%Y%m%d') == '20200918'][[
    'datetime', 'logon_type', 'source_address', 'source_username', 'username', 'windows_domain', 'workstation']]

%%timesketch_query search_obj --fields *
*.zip OR *.tar.gz OR *.tgz OR *.tbz OR *.tar.bz2 OR *.cab OR *.7a

zip_df = search_obj.table
zip_df.columns

zip_df.data_type.value_counts()

zip_df[~zip_df.filename.isna() & (zip_df.filename.str.contains('.zip$'))]['filename'].value_counts()

zip_day = zip_df[zip_df['datetime'].dt.strftime('%Y%m%d') == '20200919']
zip_day['filename'].value_counts()

zip_day[zip_day.data_type == 'windows:lnk:link'][['link_target', 'local_path']].drop_duplicates()

zip_day[zip_day.data_type == 'fs:ntfs:usn_change'][['filename']].drop_duplicates()

timestomp_df = timesketch_query_func(
    'data_type:"fs:stat" AND (*secret* OR *zip* OR *coreupdater* OR *Szechuan*)', 
    fields='datetime,timestamp,timestamp_desc,filename,hostname,inode').table

timestomp_df.shape

timestomp_df[timestomp_df.timestamp.astype('str').str.endswith('000000')]

secret_timestomp_df = timesketch_query_func(
    '"File reference: 87111-" AND data_type:"fs:ntfs:usn_change"',
    fields='data_type,datetime,filename,hostname,inode,message').table

secret_timestomp_df.sort_values('datetime')

more_timestomp_df = timesketch_query_func(
    '(("File reference: 87111-" AND data_type:"fs:ntfs:usn_change") OR (inode:87111)) AND hostname:"CITADEL-DC01"', 
    fields='data_type,datetime,timestamp,timestamp_desc,filename,hostname,inode,message').table

more_timestomp_df