Dates of interest
Timeframes of interest : Between 4 and 6 PM (but should still consider the entire day)
Keywords: JPMorgan, wire, transfer
Mounting the image read-only with NTFS specific parameters
mount -o ro,loop,show_sys_files,streams_interface=windows /mnt/hgfs/ssd/039533.001 /mnt/usb/
Used 'log2timeline' from SANS SIFT VM Image local time is set to CET (Paris time) Output format set to CSV
log2timeline -z CET -r -p -f win7 -o csv -w /cases/bodyfile /mnt/usb
Timeline analysis can be difficult and very time consuming if the CSV files are too large, or if we have multiple images to go through.
Spark can make that job much easier and more efficient.
# Create a Spark SQL context
from pyspark.sql import SQLContext
sqlContext = SQLContext(sc)
# Load CSV files into a Spark DataFrame
df = sqlContext.load(source="com.databricks.spark.csv", header="true", path = "/user/cloudera/bodyfile")
# Count the number of rows in the DataFrame and the schema from the CSV
df.printSchema()
df.count()
root |-- date: string (nullable = true) |-- time: string (nullable = true) |-- timezone: string (nullable = true) |-- MACB: string (nullable = true) |-- source: string (nullable = true) |-- sourcetype: string (nullable = true) |-- type: string (nullable = true) |-- user: string (nullable = true) |-- host: string (nullable = true) |-- short: string (nullable = true) |-- desc: string (nullable = true) |-- version: string (nullable = true) |-- filename: string (nullable = true) |-- inode: string (nullable = true) |-- notes: string (nullable = true) |-- format: string (nullable = true) |-- extra: string (nullable = true)
1322918L
# This displays the DataFrame's column names based on the CSV header
df.columns
# Register the DataFrame as a Spark SQL table called 'tl' so we can run queries using SQL syntax
sqlContext.registerDataFrameAsTable(df, 'tl')
# Cache the table in memory for faster lookups
sqlContext.cacheTable('tl')
#RDD Name Storage Level Cached Partitions Fraction Cached Size in Memory Size in Tachyon Size on Disk
#In-memory table tl Memory Deserialized 1x Replicated 5 100% 286.2 MB 0.0 B 0.0 B
# Collect all the rows into a Python list that only contains rows matching certain conditions
filtered = sqlContext.sql("select * from tl where `date` like '01/%/2015' and short like '%wire%'").collect()
# Print out the results
for i in filtered:
print i.date+" "+i.time+" "+i.source+" "+i.MACB+" "+i.short+" "+i.desc
01/28/2015 11:54:42 FILE MAC. /Users/user.name/AppData/Local/Temp/Temp1_wire_tr91297.zip /Users/user.name/AppData/Local/Temp/Temp1_wire_tr91297.zip 01/27/2015 16:29:25 FILE ...B /Users/user.name/AppData/Local/Temp/Temp1_wire_tr91297.zip /Users/user.name/AppData/Local/Temp/Temp1_wire_tr91297.zip 01/27/2015 16:29:38 REG MACB C:/Users/user.name/AppData/Local/Temp/Temp1_wire_tr91297.zip/wire_tr91297_pdf.exe C:/Users/user.name/AppData/Local/Temp/Temp1_wire_tr91297.zip/wire_tr91297_pdf.exe [Count: 0] nr. of times app had focus: 0 and duration of focus: 0ms 01/27/2015 16:30:08 REG MACB CMI-CreateHive{199DAFC2-6F16-4946-BF90-5A3FC3A60902}/Wow6432Node/Microsoft/Tracing/wire_tr91297_pdf_RASAPI32 Key name: HKLM/SoftwareCMI-CreateHive{199DAFC2-6F16-4946-BF90-5A3FC3A60902}/Wow6432Node/Microsoft/Tracing/wire_tr91297_pdf_RASAPI32 01/27/2015 16:30:08 REG MACB CMI-CreateHive{199DAFC2-6F16-4946-BF90-5A3FC3A60902}/Wow6432Node/Microsoft/Tracing/wire_tr91297_pdf_RASMANCS Key name: HKLM/SoftwareCMI-CreateHive{199DAFC2-6F16-4946-BF90-5A3FC3A60902}/Wow6432Node/Microsoft/Tracing/wire_tr91297_pdf_RASMANCS 01/27/2015 16:30:08 REG MACB CMI-CreateHive{199DAFC2-6F16-4946-BF90-5A3FC3A60902}/Wow6432Node/Microsoft/Tracing/wire_tr91297_pdf_RASAPI32 Key name: HKLM/SoftwareCMI-CreateHive{199DAFC2-6F16-4946-BF90-5A3FC3A60902}/Wow6432Node/Microsoft/Tracing/wire_tr91297_pdf_RASAPI32 01/27/2015 16:30:08 REG MACB CMI-CreateHive{199DAFC2-6F16-4946-BF90-5A3FC3A60902}/Wow6432Node/Microsoft/Tracing/wire_tr91297_pdf_RASMANCS Key name: HKLM/SoftwareCMI-CreateHive{199DAFC2-6F16-4946-BF90-5A3FC3A60902}/Wow6432Node/Microsoft/Tracing/wire_tr91297_pdf_RASMANCS
deletedFilesDF = sqlContext.sql("SELECT `date`, short FROM tl WHERE `date` LIKE '%/%/2015' AND short LIKE '%DELETED%'")
deletedFilesRowList = deletedFilesDF.collect()
deletedFileListDate = []
deletedFileList = []
for deletedFile in deletedFilesRowList:
deletedFileListDate.append(deletedFile.date)
deletedFileList.append(deletedFile.short)
import pandas as pd
from collections import Counter
dates = Counter(deletedFileListDate)
counts = dates
index = []
data = []
for k,v in counts.iteritems():
index.append(k)
data.append(v)
ts = pd.TimeSeries(data, index)
figure(num=None, figsize=(10, 8), dpi=80, facecolor='w', edgecolor='r')
ts.plot(kind="barh")
<matplotlib.axes._subplots.AxesSubplot at 0x8878490>
( limited it to 10 results to avoid bloating the notebook with results )
i = 0
for file in deletedFilesRowList:
if file.date == '07/15/2015' or file.date == '07/13/2015' or file.date == '08/11/2015':
if i < 10:
print file
i=i+1
Row(date=u'07/13/2015', short=u'DELETED C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/J89JEAZT/xlviewerinternal[2].htm') Row(date=u'07/13/2015', short=u'DELETED C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/J89JEAZT/ga[1].js') Row(date=u'07/15/2015', short=u'DELETED C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/8MB82JIU/seg[7].htm') Row(date=u'07/15/2015', short=u'DELETED C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/8MB82JIU/ddc[1].htm') Row(date=u'07/15/2015', short=u'DELETED C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/8MB82JIU/comboCAJVJJYC.js') Row(date=u'07/15/2015', short=u'DELETED C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/8MB82JIU/ctn[2].js') Row(date=u'07/13/2015', short=u'DELETED C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/J89JEAZT/checkmark2[1].png') Row(date=u'07/13/2015', short=u'DELETED C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/J89JEAZT/tro[1].js') Row(date=u'07/15/2015', short=u'DELETED C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/8MB82JIU/expand[1].png') Row(date=u'07/15/2015', short=u'DELETED C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary Internet Files/Content.IE5/8MB82JIU/jouets[1].css')
visitedList = []
myDates = ['12/11/2014', '01/06/2015', '01/27/2015']
for i in myDates:
webhist = sqlContext.sql("select * from tl where source='WEBHIST' and `date` like '%s' limit 20 " %(i) ).collect()
for i in webhist:
visitedList.append(i.date + " " + i.short)
for i in visitedList:
dateurl = i.split(" ")
url = dateurl[0] + " " + dateurl[2]
print url
12/11/2014 DOMStore:http://www.but.fr/ 12/11/2014 DOMStore:http://www.ikea.com/ 12/11/2014 DOMStore:http://www.conforama.fr/ 12/11/2014 DOMStore:http://www.lafourchette.com/ 12/11/2014 DOMStore:http://www.fleux.com/ 12/11/2014 DOMStore:https://order.cdiscount.com/ 12/11/2014 DOMStore:https://cdlc.iadvize.com/ 12/11/2014 http://s0.2mdn.net/1785842/BAU_AVEU_FR_0115_728x90_AvisPreferred_3rentals_x_FR.swf 12/11/2014 https://apis.google.com/_/scs/abc-static/_/js/k=gapi.gapi.en.wgbKiK972Ko.O/m=gapi_iframes-googleapis_client-plusone/rt=j/sv=1/d=1/ed=1/rs=AItRSTOlX0YCaQmKijyj5lpKQ5AVm7UE6A/cb=gapi.loaded_0 12/11/2014 fleux.com/ 12/11/2014 1281860200.log.optimizely.com/ 12/11/2014 lafourchette.d3.sc.omtrdc.net/ 12/11/2014 nextag.fr/ 12/11/2014 leapfrog.solution.weborama.fr/ 12/11/2014 www.lafourchette.com/ 12/11/2014 www.fleux.com/ 12/11/2014 easyrtg.com/ 12/11/2014 www.fly.fr/ 12/11/2014 www.laredoute.fr/ 12/11/2014 www.laredoute.fr/ 01/06/2015 DOMStore:http://www.santemagazine.fr/ 01/06/2015 DOMStore:http://www.famili.fr/ 01/06/2015 DOMStore:http://www.juritravail.com/ 01/06/2015 https://images-na.ssl-images-amazon.com/images/G/01/AUIClients/NavAuiAssets-c3d6aef5e35754fc73f6c2cbf007cb77ac51bd88.secure.min._V2_.css 01/06/2015 https://s.yimg.com/zz/combo?nn/lib/metro/g/yui/yui-base_3.8.4.js 01/06/2015 http:/site/cs/PSV9EMEA/cache/PT_EDITSCRIPT_FRA_win7_1.js 01/06/2015 http:/site/cs/PSV9EMEA/cache/PT_PAGESCRIPT_FRA_win9_1.js 01/06/2015 http:/site/cs/PSV9EMEA/cache/PT_GRIDSCRIPT_FRA_win9_1.js 01/06/2015 http:/site/cs/PSV9EMEA/cache/PT_EDITSCRIPT_FRA_win8_1.js 01/06/2015 http:/site/cs/PSV9EMEA/cache/PT_CALENDARSCRIPT_FRA_win5_1.js 01/27/2015 https://cdn.pizzahut.be/fr/fr/pre-home/1.jpg 01/27/2015 https://cdn.pizzahut.be/fr/fr/pre-home/2.jpg 01/27/2015 DOMStore:http://www.hsbc.ca/ 01/27/2015 file:///C:/Users/NOGA~1.DJE/AppData/Local/Temp/BYM95UU3.htm 01/27/2015 https://geo.query.yahoo.com/v1/public/yql?yhlVer=2&yhlClient=rapid&yhlS=978524434&yhlUA=ie9&yhlCT=2&yhlBTMS=1422364213869&yhlClientVer=3.22&yhlRnd=ax4Hcu442PVfh0D5&yhlCompressed=0 01/27/2015 http:/site/psc/PSV9EMEA//EE/ERP/c/MANAGE_PURCHASE_ORDERS.PO_INQUIRY.GBL?BUSINESS_UNIT=FRA05&PO_ID=0000000050&PAGE=PO_LINE_INQ&Folder=MYFAVORITES&PortalActualURL=http%3a%2f%2fxxxxxxxxxx-web02.company.inc%3a7002%2fpsc%2fPSV9EMEA%2f/EE%2fERP%2fc%2fMANAGE_PURCHASE_ORDERS.PO_INQUIRY.GBL%3fBUSINESS_UNIT%3dFRA05%26PO_ID%3d0000000050%26PAGE%3dPO_LINE_INQ&PortalContentURL=http%3a%2f%2fxxxxxxxxxx-web02.company.inc%3a7002%2fpsc%2fPSV9EMEA%2f/EE%2fERP%2fc%2fMANAGE_PURCHASE_ORDERS.PO_INQUIRY.GBL&PortalContentProvider=ERP&PortalCRefLabel=CdA&PortalRegistryName=/EE&PortalServletURI=http%3a%2f%2fxxxxxxxxxx-web02.company.inc%3a7002%2fpsp%2fPSV9EMEA%2f&PortalURI=http%3a%2f%2fxxxxxxxxxx-web02.company.inc%%2fpsc%2fPSV9EMEA%2f&PortalHostNode=ERP&NoCrumbs=yes&PortalKeyStruct=yes 01/27/2015 http://xxxxxxxxxx-company.inc:/psc////c/.VCHR_EXPRESS.GBL?ICDoModal=1&ICAJAX=1&ICType=Panel&ICElementNum=0&ICStateNum=202&ICAction=VCHR_ADDSRCH_VW_VENDOR_NAME_SHORT%24prompt&ICXPos=0&ICYPos=0&ResponsetoDiffFrame=-1&TargetFrameName=None&GSrchRaUrl=None&FacetPath=None&ICFocus=&ICSaveWarningFilter=0&ICChanged=-1&ICResubmit=0&ICSID=YFcSs62FOb5Th6ywyFPIxQY4PzwiVIzG0E7zP4w4AXo%3D&ICActionPrompt=true&ICFind=&ICAddCount=&VCHR_ADDSRCH_VW_BUSINESS_UNIT=FRA60&VCHR_ADDSRCH_VW_VOUCHER_ID=NEXT&VCHR_ADDSRCH_VW_VOUCHER_STYLE=REG&VCHR_ADDSRCH_VW_VENDOR_NAME_SHORT=ORANGE&VCHR_ADDSRCH_VW_VENDOR_ID=&VCHR_ADDSRCH_VW_VNDR_LOC=&VCHR_ADDSRCH_VW_ADDRESS_SEQ_NUM=0&VCHR_ADDSRCH_VW_INVOICE_ID=&VCHR_ADDSRCH_VW_INVOICE_DT=&VCHR_ADDSRCH_VW_GROSS_AMT=0.00&VCHR_ADDSRCH_VW_FREIGHT_AMT=0.00&VCHR_ADDSRCH_VW_VAT_ENTRD_AMT=0.00&VCHR_ADDSRCH_VW_MISC_AMT=0.00&VCHR_ADDSRCH_VW_VCHR_TTL_LINES=1& 01/27/2015 https://www.google.fr/?gfe_rd=cr&ei=YHvHVOqgG4jM4QaSy4CoBA&gws_rd=ssl 01/27/2015 file:///C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary%20Internet%20Files/%7B98FD2F88-56CA-4661-9957-DA871EFAA739%7D/%7B5AC8B6BD-AA69-4EA5-AFF5-B5B4C37B6924%7D.html 01/27/2015 file:///C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary%20Internet%20Files/%7B98FD2F88-56CA-4661-9957-DA871EFAA739%7D/%7BC4AF4B39-C4A1-4F52-BE57-9139F04E35DE%7D.html 01/27/2015 http://xxxxx-company.inc/psreports/PSV9EMEA/4465525/Advice.PDF 01/27/2015 http:/site/psc/PSV9EMEA_1//EE/ERP/c/CREATE_/.RUN_APY2030.GBL?ICDoModal=1&ICAJAX=1&ICType=Panel&ICElementNum=1&ICStateNum=1005&ICAction=PYMNT_APPR_WK3_PAY_CYCLE%24prompt&ICXPos=0&ICYPos=0&ResponsetoDiffFrame=-1&TargetFrameName=None&GSrchRaUrl=None&FacetPath=None&ICFocus=&ICSaveWarningFilter=0&ICChanged=0&ICResubmit=0&ICSID=fcTt%2FKdnm8ClImw2MuCQlKrJpCfdwJ5iTmRvzayqw%2Fk%3D&ICActionPrompt=true&ICFind=&ICAddCount=&PRCSRUNCNTL_LANGUAGE_CD=FRA&PYMNT_APPR_WK3_PAY_CYCLE=44-TE& 01/27/2015 http:/site/psc/PSV9EMEA_1//EE/ERP/c/CREATE_/.PYCYCL_DEFN.GBL?ICDoModal=1&ICAJAX=1&ICType=Panel&ICElementNum=1&ICStateNum=821&ICAction=PYCYCL_NORM_VW_PAY_CYCLE%24prompt&ICXPos=0&ICYPos=0&ResponsetoDiffFrame=-1&TargetFrameName=None&GSrchRaUrl=None&FacetPath=None&ICFocus=&ICSaveWarningFilter=0&ICChanged=-1&ICResubmit=0&ICSID=fcTt%2FKdnm8ClImw2MuCQlKrJpCfdwJ5iTmRvzayqw%2Fk%3D&ICActionPrompt=true&ICFind=&ICAddCount=&PYCYCL_NORM_VW_PAY_CYCLE$op=2&PYCYCL_NORM_VW_PAY_CYCLE=44& 01/27/2015 file:///C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary%20Internet%20Files/%7B98FD2F88-56CA-4661-9957-DA871EFAA739%7D/%7B779C8BD3-E227-43B2-ACD0-B154AB6749D2%7D.html 01/27/2015 file:///C:/Users/user.name/AppData/Local/Microsoft/Windows/Temporary%20Internet%20Files/%7B98FD2F88-56CA-4661-9957-DA871EFAA739%7D/%7BC7D35C8E-A128-4946-86DD-7136039CBE08%7D.html 01/27/2015 file:///C:/Users/NOGA~1.DJE/AppData/Local/Temp/9FAT5RW6.htm 01/27/2015 file://company.inc//World//FRA60_Owlient/3-COMPTA%202014-2015/comptabilit%C3%A9%20fournisseurs/Paiements/FRA60_20150127_25270.16%E2%82%AC.pdf 01/27/2015 https://fr-mg42.mail.yahoo.com/neo/darla/php/fc.php?trace=folder_bulk&tID=16&d=0&f=978524432&l=TL1%2CMON&rn=1422364170146&en=utf-8&mb_s_en=iso-8859-1&filter=no_expandable%253Bexp_iframe_expandable%253B&ref=https%253A//fr-mg42.mail.yahoo.com/neo/launch&secure=true&tgt=_blank&sa=uccc%253D%2522theme-purple%2522%2520MON%253D%2522300x250%253B300x600%2522%2520LREC%253D%2522300x250%2522%2520secure%253D1%2520resln%253D1517x1063%2520 01/27/2015 http:/site/psc/PSV9EMEA_2//EE/ERP/c/CREATE_/.PYCYCL_DATA_INQ.GBL?PortalActualURL=http%3a%2f%2fxxxxxxxxxx-web02.company.inc%3a7002%2fpsc%2fPSV9EMEA_2%2f/EE%2fERP%2fc%2fCREATE_/.PYCYCL_DATA_INQ.GBL&PortalContentURL=http%3a%2f%2fxxxxxxxxxx-web02.company.inc%3a7002%2fpsc%2fPSV9EMEA_2%2f/EE%2fERP%2fc%2fCREATE_/.PYCYCL_DATA_INQ.GBL&PortalContentProvider=ERP&PortalCRefLabel=Donn%c3%a9es%20d%c3%a9taill%c3%a9es&PortalRegistryName=/EE&PortalServletURI=http%3a%2f%2fxxxxxxxxxx-web02.company.inc%3a7002%2fpsp%2fPSV9EMEA_2%2f&PortalURI=http%3a%2f%2fxxxxxxxxxx-web02.company.inc%3a7002%2fpsc%2fPSV9EMEA_2%2f&PortalHostNode=ERP&NoCrumbs=yes&PortalKeyStruct=yes