Saturday, August 31, 2013

Time Off

To my friends, readers, fellow club members-

I am sorry for not quickly responding to comments, emails, and requests for help in a timely manner. Recently, I have had to take a little time off to get my affairs in order. 

I am hopeful that I will get back to "normal" very soon.

Thank you for your support and understanding!


Monday, July 29, 2013

How to Catch a Cheat

Let me guess... You're in a marriage that just doesn't seem like it has been up to par. You think maybe it is because of the stress of work, the kids, or some inexplicable changes in how your spouse is behaving. You've been aware something is wrong but you can't pin it down.... until... that first piece of randomly discovered evidence slaps you awake. Maybe it was a call you answered in the middle of the night, a piece of mail your spouse forgot to hide, a text message you read over their shoulder.. Whatever it was, it hits you like being slapped with a raw fish. Unbelievable, painful, stinky. In a snap, your mind is reeling and you have the emotional equivalent of cosmic gut-punch. What do you do now?

How to Go from Emotionally Devastated to Impartial Investigator

Okay, that's not going to happen. Accept that you will not be impartial but you need to figure out how to avoid obsessing. While you are digging for the truth in your most important personal relationship you need to realize that there are boundaries you need to set for yourself. Here is my list:


  1. Don't hurt anyone. It doesn't matter how much they might deserve it, just don't do it.
  2. Don't cheat your kids out of time with them because you want to go investigate/brood over your sorry life.
  3. Don't be unproductive at work. Still earn your paycheck. Work stress can help displace family stress for 40 hours per week and that is wonderful!
  4. Know when to fold 'em. If you're in too deep during your investigation, get out of the pool and come back later. Knowing when to walk away is an essential skill.
  5. Have a lifeguard. My lifeguard was an ex-co-worker who lived 3,000 miles away and didn't know any of my friends. When ever things were too dark, too painful.. My lifeguard was there to talk me down from being a danger to myself or others.

Setting Expectations for Yourself

Also, figure out what you need to figure out.

  1. There is no *WHY*? - only *WHAT*?. You will never find out why. Nothing you will find will explain the reason for infidelity to your satisfaction. You need to learn to let it go... "What" is defined as documenting facts about actions, dates, people, time. That is what is returned by all your efforts in an investigation.
  2. Are you arming yourself for a legal battle? If so, STOP right now and talk to a lawyer. Investigating on your own can compromise you legally. It can remove the validity of what an investigator can bring to divorce hearings. Just imagine how quick the computer records would be thrown out of court when the judge hears you have been tampering with your spouse's computer!
  3. Are you going to try to save the marriage? Or at least leave the door open to it? Then get into marriage counseling right now. A good marriage counselor can help you work through confrontations as well as persuade your spouse to be honest.

Computer Investigations

First Step - Baseline

In order to gain insight to your spouses behavior you should gain access to their computers, phones, cameras, and other electronic devices. Your goal at this time is to make a base-line copy of their files to investigate later.


Try to make a forensic copy of the hard drive. Include copying the free-space of the drive. You will need to store this on a device with at least as much capacity as the target hard drive + 20%. The most accessible software for this comes from OSForensics from Passmark Software, briefly discussed here.

If you are unable to do this process through time or material constraints, copy their important files. These are listed in their user profile on the computer. This includes everything under the path C:\Users\WindowsUserName\ for Win7 and C:\Documents and Settings\WindowsUserName\ for WinXP. Transfer these to a portable hard drive. Be sure to include Hidden and System files when you copy and paste!

These important files include the data in their Skype profile and browser history. I've discussed Skype before, as it is a very juicy subject, here and here. Browser data is juicy too, a more detailed discussion was posted int he subject here.

Save points/Restore points - these would be ideal to access. However, they do not back-up all of the files in the user's profile.


Access the phone logs on the device and export. Export contacts. Scan the internal memory for media (pictures, video). Remove the media card and use a recovery tool as explained in the 'Cameras and Other Media Devices" topic, below.

Note- It may be possible to access internal phone memory with a data recovery tool. Just link the phone to your own computer and run the recovery software on the device that shows in windows explorer. I believe this accesses both internal and removable memory but I have not tested it.

Cameras and Other Media Devices

Use a media rescue tool to recover data from the cameras, camcorders, GPS systems, and anything else a SD or Micro-SD card could fit into. I wrote about recovering data from devices here.

Second Step - Monitor

Install spyware if you are comfortable doing so. It is an easy process but has moral and legal ramifications. I have an in-depth introduction to spyware here.

You could also continue to take 'baseline snapshots' periodically. This would require you to access the target systems regularly and save a new copy of the important files each time. This is very time consuming.

Smoke them out. Not everyone is behaving badly all the time. You may need to say or do something to get your spouse to act inappropriately. It may just be as simple as going away for the weekend and letting them do what they would normally do. It may also be relating to some bit of info you have already found out... if you know one of your spouses friends cover for them while they are supposedly doing something different, message that friend and ask what they are up to - maybe even let them know you are suspicious. It will get back to your spouse and they may react by reaching out to their other significant others.

Third Step - Research

Wrangle every last piece of information you can out of the data you have. Here's some quick tips:
  1. Recover deleted files as discussed here.
  2. Look for the Thumbnails index, Thumbs.db Viewer as discussed here is a great tool.
  3. Attack Skype as discussed here and here.
  4. Raid browser data as discussed here.
  5. Deeper delving

Fourth Step - Laying it Out

Now you have some data. You made it into information through research and connecting the dots. I wrote about structuring mass data here.

From here on out you are the investigator! Dig up clues, follow leads, document the facts. It would be a fun game if it were not for the reason you are doing it in the first place.

Here are some other helpful investigating tips..

Non-Computer Investigations

Not everything you can find useful will come from a phone or hard drive. Here are some non-computer tips for investigators:
  1. Be the one who gets the mail every day. Look for bills, bank statements, credit card bills, collection notices, etc that suggest you spouse has another spending account.
  2. Read all credit card statements and bank statements for ATM withdrawals, pay attention to dates and locations of charges
  3. Order your and your spouse's credit reports from the three credit bureaus. This will show items such as credit card accounts, bank accounts, bank overdraft loans and other financial accounts your spouse may be using to pursue their activities.
  4. Check the odometer on their vehicle. Note how far they drive to and from work on a daily basis and look for spikes in mileage if they have to 'work late'.

Behavioral Observations

Below are some examples of observing the behavior of your spouse to help develop avenues for further investigation:

  1. Do the unexpected!
    1. Before going out on an urgent errand, tell your spouse your car has a slowly leaking tire or is making a funny noise. Tell them you can't find your phone so they should let them borrow their phone just in case you need help on the road. Observe how reluctant they are to lend you their phone. 
    2. Let your spouse know in advance that you are going on a business trip (or need to visit family). When time comes for your trip, head off but go to a movie. Come back home after a few hours and see if there is any panic from your spouse. You may want to consider an overnight stay when you head out and show up in the middle of the time you were supposed to be absent. 
    3. Busting by romance. If your spouse if off on a trip or visiting family, you may want to pay them a surprise visit at their location. Bring your spouse their favorite take-out to their office when they are working late. Bring the kids over to the family your spouse is supposed to be visiting because they missed their mommy/daddy. The intent is to show up where they are supposed to be with all of the best intentions. 
    4. If your spouse is at home while you work, head home for lunch sometime. Or stop by and get your healthcare insurance card prior to a doctor's appointment they were not aware of. Just show up unexpectedly at times to see if everything is kosher.
  2. Identify suspicious behavior!
    1. The bathroom is a sanctuary of privacy in any home. Does your spouse hole-up in there to take a bath frequently? Do they always take their phone or laptop with them? How pissed are they when you knock on the door unexpectedly?
    2. If you think something fishy is going on, ask a lot of questions! If your spouse is suspicious about your intentions tell them that you just wanted to talk about their day like you both used to do. If your spouse went to a movie with their friends, ask who was there, how the movie was, what did they think of the movie plot, were there any twists, how did the bad guy get it, etc. then do your homework and see the movie to determine if your questioning got a lot of BS answers. 
    3. Shut down your Internet router or modem saying it is due to a technical problem. Let them know you are working on it but it might take a day or two. Observe the level of panic in your spouse. If you suspect the phone is their primary means of communication, try causing an outage there. The intent is to disrupt their normal method of communication and see how bugged out they get. 
    4. Observe when your spouse complains or pines about things. If winter weather is their top daily complaint and they always talk about Florida as a nice place to go/live, it may indicate something about their other person that bubbles through into their conversations and complaints about life. Many authors believe that the other person personifies resolution for untended needs your spouse may have in their life and that wayward spouses wrap much more into an affair partner than can be attributed to a normal person. The level of infatuation and escapism a wayward spouse has invested in the other person builds them up to the embodiment of the solution to all their ills. Just beware that your spouse's complaints and yearnings may, in fact, be describing aspects of the other person. And also this a very tentative connection so keep your ears open but don't jump too far to conclusions...
  3. Don't trust anyone!
    1. People you have been close to may have known about this for a long time and have kept information from you to protect your spouse. In my case, I was very close with my sister-in-law and spoke to her about my feelings of my impending divorce... my absolute depression, my thoughts of suicide, and I even made the comment 'this would be so much easier if my spouse had just cheated'. She said nothing.. I was very dumb to think she would have my best interests at heart if she had been covering for my spouse's infidelity.
    2. Some people will care too much for you and take action on your behalf without your knowledge or consent. I never told my overprotective older brother of my spouse's infidelity. If I had, there would have been a family schism, revenge, violence, or worse. The last thing you need right now is someone justifying your anger and pushing bad decisions.
    3. Loose lips sink ships. You are in the role of an investigator... gathering facts. The absolute last thing you need to do is confiding with someone who may, intentionally or not, tip off your spouse that you are suspicious. This may lead to the destruction of the very facts you have set out to collect.

I'm hopeful this article has helped you in deciding how (and if) you investigate your suspicions of infidelity. Stay strong and stick to the facts.


Monday, December 17, 2012

Easily Recover Files from Phone or Tablets

The quickest and easiest way to check your hunch that your spouse is cheating is to investigate their mobile devices. Full data recovery from a phone or tablet is difficult but there are some methods which you can do yourself at home with little cost or technical expertise.

SD Card Recovery

Many phones use a Mini SD card for expanded memory. Cameras and camcorders also use expanded memory slots. Devices save data to either an internal memory or to the external memory card, sometimes both.

Data recovery from an memory card can be as simple as removing the memory card from the device, putting it in a card reader attached to your computer, and running a file recovery program.

Memory Card Readers

Many computers these days have a built in card reader. If your computer doesn't have a card reader, you can purchase a USB card reader almost anywhere for $5-$20 or so. Here are a few places where you can order online

File Recovery Software

The good people at CNet's have quite a few file recovery tools. However, there are some that aren't free. Limited trials may allow one to view the thumbnails of recovered images but the full licensed product is required for retrieval. The geek-elite at Lifehacker have provided their own guide for the best file recovery software. I highly recommend reading through their article before proceeding in your file recovery efforts.

The following software has been recommended by BSC blog readers:

Other Uses for File Recovery Software

One benefit of the file recovery software is that it can be run on several types of devices. Make sure to check the memory cards of all of you mobile phones, cameras, camcorders, GPS devices, video game consoles, etc... A second use for the software is that it can be run on hard drives of computers and laptops. The recovery software is a versatile information gathering tool.

Trouble Ahead: Internal Memory

The hard part of data recovery from devices comes from the internal memory of the device. The big culprits here are the Apple iDevices and the Blackberry. Apple prevents the use of external memory in its iPhone and iPad devices by not supporting physical slots for memory expansion. Some Blackberries default to using internal memory. The Blackberry Curve series will only use the external memory slots for message storage, photo storage, contact storage if the user modifies the default settings in the menu. In most cellphones some data is even stored on the SIM card, but Blackberry doesn't even do that.

Big Trouble

Even forensic professionals have a difficult time reaching into internal memory on mobile devices. It's a complex problem with lots of layers.

In the first layer you have firmware. This is the basic operating software on the phone that boots it up. Not only does each manufacturer have their own firmware on the device but each model of device can have a different firmware as well.

The next layer is the operating system. The main contenders in operating system are Apple and Google. Apple is notorious for closed systems and actively prevents the open source community from developing utilities that would have helped our purposes. Google's Android is much more open and has more development community involvement. There may be some applications out there on the net which would help recover data.

The third layer is a twist on the second. Jailbreaking the iOS or Android operating system adds a further wrinkle. The Cydia jailbreak adds a new app store for the iOS system where third-party developers can sell software. As with any operating system modification, apps designed for the original configuration may or may not work on the jailbroken device. Additionally, apps added after the jailbreak may modify how data is stored or can be retrieved.

The icing on the crap-cake is that special equipment and decoding software may be needed. For my outdated early-2000's Blackberry, the kit would have been ridiculously expensive. For those of you with deep pockets, there's a list of links below.

If anyone out there knows about a good tool set for Android, Blackberry and iOS... Please share!

What can you do?

There are cellphone reading kits that police and professional private investigators use to access the internal memory of the device. These kits are ungodly expensive. My recommendation is to seek professional help if you absolutely need to see the internal memory on a device. Contact a private investigator in your area, ask your lawyer for a referral, check out the firm's reputation, see if they outsource the forensic work and check the reputation of the lab. Be prepared to pay - I was charged $750 for one phone.

Also, be prepared to get zippo off the device. That's right... I spent $750 for "no data" because the lab couldn't read internal memory off the phone (not the SIM or expansion memory - the internal data store). The lab had a good reputation and updated equipment. They just were not able to read the internal data. Luckily, they refunded my payment. Contractually they didn't have to refund anything so I do feel very lucky indeed.

Professional Tools:

Cell Phone Records

Don't forget to log into the online account for mobile phones. I used this access to download a couple of years worth of calls and text message records. The data is simple; date, time, number, duration, placed/received call, sent/received text, and sent/received mms.

You should look for a pattern. For example- your wayward spouse has been calling and texting a suspicious number every day for months and then has a few days with little or no texts or calls. If this aberration occurs at the same time your spouse was away on a business trip, visiting family, working over the weekend, etc. I think you have some very strong evidence for a rendezvous.

Spoofing a Cloud

I have been researching a new approach for forensics. This is based on the distributed memory principle of cloud computing. For example; Apple's iOS for iPad and iPhone is utilizing a 'live backup' to their iCloud service which basically runs a backup of apps, contacts, and data through the internet connection of the device. Somewhere, out in the net, is an encrypted copy of all of the device data. I'm looking into how one could access this information -- so stay tuned.

--- DNS

Wednesday, December 5, 2012

Hacking help!

I've been contacted a few times recently to provide help getting to some part of a computer file or recovering some sort of data. I don't mind responding to people who reach out and generally need help, in fact, I'd like to think I've actually helped quite a few people. Here are some guidelines to make sure I can help you as much as I can.

What to do before reaching out to me for help

  1. It would be really awesome if you would read the relevant blog posts and attempt it on your own first.
  2. Know what you are asking for and likely to get. Data recovery and carving can generate lots of data but it generally is a shotgun approach.
  3. Do the work once you have the data. I can't generate lovely time-indexed, cross-referenced, collated reports of the data. I'll just generally give you the data and expect that you'll donate some elbow-grease to the equation.

What I need to help you

  1. A description of the system (Mac, PC), the operating system (XP, 7), the relevant programs (Skype, Firefox). Version of the programs is a nice-to-have for me.
  2. The relevant files!
  3. Any information that would help searching data. Names of the parties, dates of events, locations, etc...

What I can promise

  1. My best effort to recover data
  2. Getting to the task of investigating as soon as possible (I do have a life!)
  3. Utmost secrecy... I will only share your information with you, no one else. Nobody but me will ever see it. I promise to delete all copies of data and emails once I've finished investigating and sent results. 
  4. Discretion. I've been through this myself and I know how personally devastating going through this kind of event can be through my own experience.

How to contact me

  1. Post a comment to the blog and I will reach out to you. I moderate all comments and will not make any comments public that contain personal info like an email address.
  2. Email me at dead.nt.sleeping [at] gmail [dot] com
  3. I have put together a web form for a contact page you will be able to see on the top tabs of the blog. Here's a link to it anyway: Contact Form.

V/r - DNS

Thursday, November 29, 2012

Searching Browser Client-Side Storage

Is there anything you don't know? Google it! Bing it! Ask Jeeves! We sometimes forget how ubiquitous internet search is in our lives - Google in particular. We use Google Maps to find our way around, we use Google's search engine to find a good restaurant, we use Google for almost everything. That's why Google is great for finding information about a wayward spouse.

Web Browser Client-Side Storage

A lot of what internet browsers retain for data is kept in "client-side" storage. Most browsers retain a SQLite database in the user's directory. From our previous discussions we know a few tricks to get information from these SQLite sources already. Grab a SQL browser and we'll dive into the rabbit hole of what's on a hard drive.

File Location - Where's it at?

On Win7:  C:\Users\[UserName]\AppData\Roaming\Mozilla\Firefox\Profiles\[Profile]
On WinXP:  C:\Documents and Settings\[UserName]\Local Settings\Application Data\Mozilla\Firefox\Profiles\[ProfileName]

On Win7:  C:\Users\[UserName]\AppData\Local\Google\Chrome\User Data\[Profile]
On WinXP:  C:\Documents and Settings\[UserName]\Local Settings\Application Data\Google\Chrome\User Data\[Profile]

On Win7:  C:\Users\[UserName]\AppData\Local\RockMelt\User Data\[Profile]

Internet Explorer (IE):
This is a different kind of beast. We'll discuss this later but it is important to note they do not have a SQLite db structure.
On Win7:  C:\Users\[UserName]\AppData\Local\Microsoft\Windows\Temporary Internet Files
On WinXP:  C:\Documents and Settings\[UserName]\Local Settings\Temporary Internet Files

Types of Storage

The following table is sourced from a Chrome Browser extension called Click&Clean. If their product is anywhere near as good as their chart on client-side storage - it should be excellent!

Below you can see Cookies, Local Storage, Web Databases (SQL), IndexedDB, File System, Application Cache, Flash Cookies, and Silverlight Cookies...

From Click&Clean

I know what you're saying - "But everyone blocks cookies!" True. Everyone blocks 3rd Party Cookies. These are the cookies advertisers put on your system to track your behavior. What people usually don't block are 1st Party Cookies. These are cookies that are directly linked to the domain you choose to visit with your browser. They remember what's in your shopping cart, your login info (if you authorize autofill), your browser session state, etc. These cookies are delicious.

Local Storage,Web Databases (SQL)
The real big deal is in the local storage databases on your computer. These tables track downloads, search terms, archived history of URLs visited and more. See below for the schema and descriptions of the data present.

Application Cache
Nirsoft has a cache reader for you! Chrome, Firefox, Internet Explorer, Opera included. Even (gasp) Safari.

The cache is a set of already downloaded images and media from webpages that is stored on your hard drive. The browser uses these cached elements to speed the loading of webpages. The cache readers will recover images and the URLs that reference them. You will see a lot of elements like the graphic files for navigation buttons for a site, images for headers and/or other elements of a web page. If you are lucky... You may be able to retrieve images from online dating sites, Facebook images, and thumbnails of other incriminating evidence.

I strongly recommend reviewing the cache of every browser on the computer and recovering/saving files you find. The cache is fluid and can be flushed at any time or the relevant data may be overwritten by newer cached data. 

Flash Cookies & Silverlight Cookies
I haven't found anything worth noting in Flash or Silverlight Cookies. There may be data present that is interesting to an investigator but I haven't found it yet.

Local Storage, Web Databases (SQL)

Data Schema and Relevant Files
Here's a list of databases in the User's directory and a list of the data files contained within. There are many SQLite databases as well as flat files and cache files. There may be different tables present depending on the browser (Chrome, Firefox) and the browser version.

For a complete list of data files I've encountered, go here or click on the database you want to investigate below.
  • Archived History
    • Meta
    • URLs
    • Visits
    • Visit Source
    • Keyword Search Terms
  • Cookies
    • Meta
    • Cookies
  • Extension Cookies
    • Meta
    • Cookies
  • Favicons
    • Meta
    • Favicons
    • Icon Mapping
  • History
    • Meta
    • Downloads
    • Presentation
    • URLs
    • Visits
    • Visit Source
    • Keyword Search Terms
    • Segments
    • Segment Usage
  • History Index YYYY-MM
    • Meta
    • Pages
    • Pages Content
    • Pages Segments
    • Pages SegDir
    • Info
  • Login Data
    • Meta
    • Logins
  • Shortcuts
    • Omni Box Shortcuts
  • Top Sites
    •  Meta
    • Thumbnails
  • Web Data
    • Meta
    • Keywords
    • Autofill Profile
    • Autofill Profile Emails
    • Autofill Profile Phones
    • Autofill Profile Trash
    • Credit Cards
    • Web Intents
    • Keywords Backup
    • Logins
    • IE7 Logins
The following are flat files (non-database files) in the User's directory. These open with Notepad and display readable contents. 
  • Bookmarks
  • Current Session
  • Current Tabs
  • Last Session
  • Last Tabs
  • Preferences
  • Visited Links

So.... Where do I start?

I've presented a lot of information. So naturally you want to know where's the best information for web behaviors found.
  • Look into the History Index YYYY-MM, History, Archived History and Web Data databases first.
  • Get a cache reader and review what is in the cache for each browser on the computer.
Analyzing this data should help you confirm or deny your suspicions.


What's in a URL?

You are very likely to recover URLs. Sometimes these are neat and clean to read but a lot of times the URL is a jumble of variables and escaped characters.

Google Search URL
Here are some common variables you will find in a Google search string:
  • q = search term
  • as_sitesearch = searches specified domain only
  • sort = sorting parameter for results
The authoritative source for variables would be Google. They've posted a helpful, lengthy page on search parameters here.

URL Encoding - Escaped Characters
URLs often have characters in them (:, /, ., etc) which would cause problems for other parsing engines like Java or Flash. The characters are often replaced by a '%' and the two digit hexadecimal code for the character. For example ':' = %3A and '?' = %3F.

Here's a list of characters that are often replaced in a URL with a more script-friendly value. I generally use the substitute function in Excel to replace the hex value with the ASCII character.


TL;DR - Summary

1) Find the local storage on your machine
2) Open a SQLite files with a database browser
3) Save file as HTML, close the file, re-open with a spreadsheet program (Excel)
4) Repeat until you've opened all files and saved them all into one workbook
5) Utilize the pivot table function to investigate the data
6) Have a beer

V/r - DNS

Thursday, August 9, 2012

Spyware, An Introduction

There's an old saying that being paranoid doesn't mean that they aren't out to get you... In our case, being paranoid doesn't mean your spouse isn't being unfaithful. The uncertainty and dread of the unthinkable happening is maddening. Many people turn to spyware to find evidence. I will recount my experiences here with some popular software.

Morality and Legality

Without question, it is immoral to invade another person's privacy. In fact, it is illegal. What I'm writing in this post is a review of products and not an endorsement. I don't want to influence you in your decisions - you are responsible for your own actions and their consequences. My post is merely a discussion of popular software and hardware and their benefits and restrictions.


Spyware is classified as any software used to capture data without the primary user's consent and/or knowledge. There are several general types of spyware; key loggers, parental/employee monitoring, network packet inspection, and "professional" software.

Nota Bene! 

Installing Spyware

Everyone has a virus scanner and spyware protection software package these days, as well they should. So, how do you install spyware without it being instantly removed? The answer is to modify the security program to create an exception, or blind-spot, for the spyware you install. Prior to installation, you need to create these exceptions in the security software's settings. The software you decide to use will likely have detailed instructions on how to create exceptions. 

The big risk to installing any spyware is that the user may be savvy enough to review their security software settings, do a clean re-install of their security software, or download a new security software package entirely. If the user does this, the exceptions you created will be lost and the user will be alerted to the presence of the spyware.

Key Logger

A key logger is a program that is used to track which keys are struck while the computer is in use. There are many variations on the basic key logger program. Most of the free programs will track the basic keystrokes and save the data to a hidden directory on the computer for later retrieval. More advanced programs will capture additional data, such as; programs in use, screenshots, etc.

Parental/Employee Monitoring

All spyware is distributed or sold to "monitor the computer use of children/employees". As such, parental monitoring software has many spyware features built-in. The features generally include a key logger, a website filter, instant messaging capture, webcam locks, and others. The website filters can often be configured to track, not block, web use. Some monitoring software offer a real-time communication for specific events, such as; visiting an adult website, sending an instant message, computer use during prohibited hours, etc.

There are some software packages which can do an IP lookup and provide a generalized location for the computer. Note that the lookup here isn't very precise and is based on the assigned IP address of the machine. Proxy servers, onion routers, and other actions that impact the domain name server (DNS) assignment of IP addresses can mess with its accuracy.

It is important to note that most parental/employee monitoring software has a splash screen, notification tray icon or other visual cue to alert the user to its presence. You will need to find a solution that meets your needs. If you are negotiating a reconciliation with a wayward spouse, asking them to install a monitoring software on their computer may be one of your conditions.

Network Packet Inspection

This is, by far, the nerdiest of the geekery on this post. Here's an article or two to get you familiar with packets and networking.

In general, networks operate by sending packets of data back and forth. Each packet has a header which tells your router (the traffic cop of you home network) where each should go and in what order. Once packets are received by the destination machine they are decoded and transmuted into data.

Network packet inspection software allows you to monitor these packets. It is also referred to as a "packet sniffer" because it intercepts and decodes each packet sent over the network and presents you with raw data. These are essential and valuable network admin tools. As powerful as these tools are, I found many very cumbersome to use and far too technical for a casual user. My experience has primarily been with Wireshark, Ethereal and WinPcap.

"Professional" Software

For those of us who need a solution that ameliorates the need to have physical access to the computer to download keystrokes, the "professional" software fits the bill. The main operator in the arena is Spectorsoft. I have experience with their product line; eBlaster, SpectorPro, eBlaster Mobile. In my opinion, they have excellent customer service and are very responsive. I've only had to wait an hour most of the time I sent an *email* inquiry to their team.


The features worth mentioning in the eBlaster software are as follow:
  • Sends email reports on a user-defined schedule
  • Reports contain keystrokes, program use, files transferred or printed, IM activity
  • Keywords can be set to send an immediate email report containing the keyword, program use and a screenshot of the computer at the time it was detected
  • Remote control panel once installed allows user to adjust what's monitored, how frequent the reports are sent, set keywords, set websites to block, and allow customization of the emailed reports to give a "friendly" subject, sender, etc.
  • Forwards emails sent from the computer to an account you select.
Some cons to using the software I learned the hard way:
  • The application doesn't support non-major browsers. If your subject uses Opera, Chrome, Iron, RockMelt, etc. much of the beneficial tracking info is lost. You still get the keystrokes logged but the output can be randomly mixed, confusing and lack association with the appropriate webpage or webapp (think of Skype integration in Facebook through the RockMelt browser as an example).
  • The frequency and quality of screenshots is limited by Spectorsoft. However, if you utilize your own Gmail account as the SMTP server to send the screenshots this can be circumvented.
  • Video streams and audio streams are not captured. Voice/video calls through Skype do not capture the audio or video component. If you're lucky your subject might type a keyword so you can get a screenshot.


SpectorPro is a software program much like eBlaster. The following are the main differences:
  • SpectorPro won't send you email reports. It spools them to a hidden directory on the subject machine. You will need to access the computer to get to them. The software does allow the user to set up a network path where these files can be accessed remotely.
  • SpectorPro records computer use like a video recording. You can playback the files and see exactly what was being displayed on screen during the time the subject was using the computer.
  • SpectorPro doesn't record audio. A pure voice call on Skype or any other VoIP app wouldn't provide much more detail than seeing the call being made/accepted.

eBlaster Mobile

In my opinion, eBlaster Mobile is where Spectorsoft falls down. First the features:
  • Sends periodic email reports.
  • Logs calls made, missed and received with time and duration. Looks up the caller in the user's contact book to provide a name with the number.
  • Logs website pages loaded (big caveat here, read on for its limitation).
  • Logs SMS and MMS sent and received with contact information.
  • Sends a thumbnail of a photo or video taken with the phone with geo-tagging and time the picture was taken.
  • Looks up GPS location and resolves to a street location at a set schedule
  • Allows a geo-fence to be set up which provides alerts when it has been entered or exited. 
  • Remote dashboard to set and manage delivery settings
  • "Friendly" names for sender of reports, subject, etc.
  • The eBlaster Mobile app won't track any websites visited outside of the native android browser. Any use of a browser outside of the crappy one the android was packaged with circumvents eBlaster Mobile entirely.
  • The eBlaster Mobile app won't track app usage. You won't know if your kid plays Angry Birds all day in that expensive private school you're sending him to, or if your spouse uses the Skype app for calls/video chat, uses Google Talk, uses Google Voice, Meebo, What's App, and on and on... All of the apps are invisible to eBlaster Mobile.
  • The geo-location feature that provides the location of the phone at set intervals is a battery-burner. As an example, the phone I use has a 1.5 day battery life. Using a 2-hour lookup for location during normal use reduces that to about 6-hours.
  • It was pretty convoluted to set up. It required I jail-break my phone (more a limitation set by my service provider) prior to set-up.

The Final Word

Find software that works for you. I found the email reports provided by Spectorsoft extremely useful and worth the cost and limitations. I just wish they had a more comprehensive mobile offering.


There is hardware available for snooping. The most useful being mini-GPS, spy cameras, and voice recorders (bugs). I have found most of my needs resolved through software but there are hardware options out there.

The Mini-GPS

The Garmin GTU 10 is a mini-GPS unit that's about 3 inches by 1 inch by 1 inch and is fairly expensive. The signal is strong enough to transmit through the body panels of most cars. It sends an email report of its location and Garmin provides an online dashboard to view locations and modify settings.

It has a handy geo-fencing feature which allows you to set it to sleep inside a location. For example, you can have it report hourly and set a geo-fence around your home which it will wake up once it leaves. The GTU 10 can switch to continuous tracking which reports every 30 seconds or so - in real time.

Spy Cameras

Admittedly, I do not have much experience with different types of spy cameras. There is a dizzying array of options and prices. Many are wireless, motion activated and record to a DVR or computer. The small design of modern cameras allow them to be placed almost anywhere. I ran into a major stumbling block when considering the areas I'd want to monitor and the possible locations for a spy cam. I couldn't reconcile where to place it that wasn't in the direct line of sight of my subject and I quickly abandoned the idea.

Audio Recorders

The old "bug". An audio recorder is a discreet method of documenting what is going on when you are not present. The trick is to find a portable, battery friendly device with lots of memory that your subject will likely always be near. One popular choice is a voice-activated recorder made to resemble a key fob. Granted your spouse may be suspicious when you get her a new key fob for no real reason, so it is up to your ability to bullshit your way through it.


The only thing worse than knowing is not knowing. However, as I always recommend, talk out your problems first. Using these devices and programs in a manner which compromises anyone's privacy is immoral and illegal. It will also break any threads of trust your spouse may have for you and may lead to an inevitable conclusion of divorce. I doubt any of us would be considering these technologies if we didn't have a very solid suspicion but do consider this before hopping down the rabbit hole - what if you are wrong?

-- DNS

Friday, July 20, 2012

Skype Main.db

I hope you've read the Skype primer I posted a while back... Because now it is time to roll up our sleeves and get dirty!


The main.db SQLite 3 file is the heart of Skype. The file is located in Windows 7 at C:\Users\WindowsUserName\AppData\Roaming\Skype\SkypeUserName and in Windows XP at C:\Documents and Settings\WindowsUserName\Application Data\Skype\SkypeUserName.

In my Computer Psuedo-Forensics & Tools post, I pointed out some helpful software; a SQLite database browser called, appropriately, SQLite Database Browser and a raw hexadecimal file editor called HxD Hex Editor. Both are free. The discussion below utilizes these tools.

SQLite is cool

The Skype tool (also Firefox and Chrome) utilize a lightweight SQL engine called SQLite 3. This enables the software to have a back-end platform which is light enough to be used on mobile devices. The PC based tools are using the same engine as the software is relatively standardized on the back-end to reduce the cost of maintenance and reducing development cost and time across platforms.

SQLite is an open standard and has extensive documentation. All SQLite formatted files have a header beginning "SQLite format 3".

From my earlier post on Skype

"According to the SQLite3 documentation at

A database file might contain one or more pages that are not in active use. Unused pages can come about, for example, when information is deleted from the database. Unused pages are stored on the freelist and are reused when additional pages are required.
This means when the user deletes records, they're marked inactive. NOT removed from the main.db file. The deleted records sit in unallocated memory. They are, however, overwritten with new data. This means you should copy that file ***now*** before any old data is truly gone. Once overwritten, it cannot be retrieved.

Using the HxD Hex Editor, you will be able to open that main.db file and translate the machine code to text. There will be plenty of garbage characters and the data will be in a somewhat random order. If you're lucky enough you will have been able to catch a good bit of data that the user believes they've deleted for good."
That's why SQLite is cool :)

Using Main.db

The main.db file is a SQLite database. Using SQLite Database Browser (or any other SQLite tools), you can open the "official" memory of the Skype program. The information you can retrieve from this file represent the data that has not been deleted or removed by the user.


The schema is the set of tables and indexes within the database file. It provides the structure of the database, the names of the tables, the fields (data items) within each table and a description of the field indicating what type of data is stored and whether it is a primary key (a unique, mandatory value). The indexes for the database are provided. These indexes are tools used by SQLite to speed up the execution of queries used to retrieve data.

Just because I like you here is a link to a spreadsheet containing the tables and indexes and a link to a spreadsheet containing the tables and data fields.


The following tables appear in the main.db schema. Each table on this list is linked to a spreadsheet with a description of the table's fields and data types.

So what?

The following tables are where you will find useful data...
  • Contacts
    • You'll find all of the user's contacts with their SkypeName, their DisplayName and any other details they've provided in their Skype profile (location, mobile #, etc)
  • Videos
    • You'll find a list of the user's video chats and a Convo_ID. 
    • The Convo_ID will map to the Convo_ID in the Conversations and Participants tables.
  • SMSes
    • I have no experience with this table but I believe it is used to store SMS texts sent via Skype to a cell phone (a paid feature for Skype users)
  • CallMembers
    • Provides a list of all parties to a call (grouped by Call_Name). 
    • Duration is provided per participant and a Video_Status is provided (I believe 3 = video call)
  • ChatMembers
    • Provides a list of members taking part in a chat (grouped by Chat_Name). 
    • The Adder field provides the SkypeName of the user who added the participant to the chat.
  • Conversations
    • Provides a list of the conversations in which the user participated. 
    • The Identity column provides the SkypeName of the participant or the Chat_Name for group chats/rooms. 
    • The DisplayName field provides the participant's displayed name in Skype or the displayed title of the group chat/room.  
    • The Creator column notes the creator of the group chat/room.
    • Timestamps are provided for Last_Activity, Inbox_Timestamp, Creation_Timestamp and more.
  • Participants
    • Provides a list of conversation participants grouped by the Convo_ID
    • The Adder field notes the SkypeName of the user who added the participant to the conversation
    • The Identity column provides the SkypeName of the participant.
    • Text, Voice and Video status fields are provided.
  • Calls
    • Provides the calls in which the user has participated.
    • Call_Name is provided and maps to the Call_Name column in the CallMembers table.
    • Timestamps for the calls and the Host of the call are provided.
  • Transfers
    • Provides the details of files shared and downloaded by the user.
    • Partner_Handle is the SkypeName of the other party, Partner_DisplayName is their DisplayName.
    •  FileName and FileSize is provided along with the FilePath (uploads only I believe).
    • Convo_ID is provided which maps to the Participants and Conversations tables.
    • Timestamps are provided.
  • Voicemails
    • Lists voicemails sent to the user
    • Partner_Handle is the SkypeName of the other party, Partner_DisplayName is their DisplayName.
    • Path column displays a ".dat" file which is located in the ...\Skype\SkypeUserName\Voicemail folder
  • Chats
    • Provides the chats in which the user participated.
    • The Name field follows this structure; #SkypeUserNameOfInitiator/$SkypeUserNameOfPartner;ChatIDString
      • The ChatIDString is a 16-digit alphanumeric string that is unique to the Chat
      • The SkypeNameOfPartner is replaced with the ChatIDString when the chat is a group chat/room. Ex: #SkypeUserNameOfInitiator/$ChatIDString
    • The Friendly_Name column provides the display name of the partner or group chat/room.
    • The Adder field notes the SkypeName of the user who added the participant
    • Timestamps are provided
    • The Posters field is a concatenated list of SkypeUserNames that posted to the chat
    • The Participants field is a concatenated list of SkypeUserNames that received messages from the chat
    • The ActiveMembers field is a concatenated list of SkypeUserNames of those who have recently posted
    • The dbPath field displays a ".dat" file which is located in the ...\Skype\SkypeUserName\Chatsync folder
      • The name of the ".dat" file is a 16-digit alphanumeric
      • The first two characters indicate the subfolder where the ".dat" file is located under the Chatsync folder. Ex- if the dbPath field has the value of 01abc2345def6789.dat; the file is located at ...\Skype\SkypeUserName\Chatsync\01\
  • Messages
    • Provides a Convo_ID to map to Conversations table, Videos table and others.
    • Provides ChatName to map to the Name field in the Chats table.
    • Notes the SkypeName of the Author of the message.
    • Provides the message detail in the Body_XML field. This is the most important field... this is the message sent from one party to another!


The timestamps in main.db follow a format based on UNIX. You will need to convert these timestamps into a human-readable values. For example, the value '1325900664' represents January 6, 2012 8:44pm. I use the following equation in Excel to convert the dates (assuming the Timestamp value is in cell B3):

The IF statement states that if there is no value for Timestamp, then no value should be returned. If there is a value in the Timestamp field, the equation should be executed.

In SQL queries the date is converted using the datetime function. The format of the function is as follows:


A query to start off with

Not everyone can write SQL queries, but they should! Here is a simple sample query to get you on your way. This retrieves all contacts sorted by the number of times they appear in the participants table:

select c.skypename
         ,datetime(c.lastused_timestamp,'unixepoch') lastused_timestamp
from contacts c
    ,participants p
where c.skypename = p.identity
group by c.skypename
order by count(p.identity) desc

What's the deal with Chatsync?

The Chatsync folders also contribute a great deal. The main.db database includes pointers to the ".dat" files in the Chat table. I believe that these folders within Chatsync are used to store nodes of information which is retrieved by the Skype application when the chat data is needed. It's sort of like an extended memory to save information about the chat. I do know the Chatsync files are not inclusive of all the information presented in the main.db Chat table and I have found data present in the Chatsync files not present in the main.db tables.


Reading the Main.db file with a SQLite utility and the Chatsync files are of limited use when trying to recover data that has been deleted. As I stated in this earlier article, the best approach is to read Main.db with a hexadecimal reader to recover partial records of deleted messages, video calls, voice calls, chats and other data. The Chatsync files are of limited worth as these appeared, in my experience, to have been deleted with more regularity than the old data in main.db was overwritten by new data.

Other Tools

The other helpful tools to user are SkypeLogView and SkypeChatsyncReader. I discussed these in my earlier post:


The good people at NirSoft have provided a tool for analyzing the Skype database files for call logs, chats, etc. It is a very handy tool but much more data can be gathered using HxD.


A utility created by Rasmus Riis Kristensen from the Computer Crime Unit of Danish National Police. This tool reverse engineered the location of data in the .dat files of Skype.


The Main.db file is a useful source of data IF a deletion of data has not occurred. The presentation of files from a SQLite database browsing utility can provide a well-formatted view into Skype usage. Please remember that carving the Main.db file with a hex editor will yield data that has been deleted by the user. However the presentation of this deleted data is nowhere near the organized output of the Main.db file.

-- DNS