Obtaining and displaying Skype IP addresses (with maps) from ChatSync files

by | Feb 27, 2015 | Forensic Browser for SQLite, Skype

I had reason recently to look at Skype ChatSync files to recover the IP addresses held within and I needed to get these into a report. For those of you that aren’t aware when Skype is syncing data between two different accounts, it uses ChatSync files to transfer this data. The data held within is, for the most part, duplicated in the main.db file (after all that is what the sync part of ChatSync refers to). However, and most interestingly for forensic purposes, usernames and IP addresses are also stored within these files.

I have therefore written a Forensic Browser for SQLite extension that parses the folder containing these files and for every file records the following information in a new SQLite database:

  • The filename
  • The last written date
  • The user names
  • And for each username, the Lan and Wan IP addresses that are stored

It struck me when writing this application that I could also obtain some location information from an online service and display this information within a Skype report and further I could use the built-in mapping functions of the Forensic Browser for SQLite to display maps related to the latitude and longitude fields obtained from my IP lookup service.

Of course, location information based on IP addresses needs to be carefully considered as IP addresses will often be the of a service provider. Nevertheless on examination of the IP addresses and particularly associated maps for my own Skype username quickly revealed some interesting locations.

The screenshot below shows the output of this process with three maps at different scales shown alongside the details from the ChatSync files.

The rest of the article will show how easy it is to create these reports yourself.

In order to follow these instructions you will need to obtain the ChatSync parser program from me (it needs a current Forensic Toolkit for SQLite licence) and if you want to obtain the location information you will need to have:

  1. A free account (and associated key) from IPInfoDB
  2. Internet connectivity when running the ChatSync parser program and Forensic Browser for SQLite.

So, first, visit IPInfoDb and create a free account at this page http://www.ipinfodb.com/register.php you need to provide an IP address of the “server used to connect to the API gateway” I used the IP address of my router (also conveniently displayed on the page above) and all seems to work OK. You need to acknowledge an email in the normal fashion and then to wait 10 minutes after the acknowledgement before you can use the service. When the service is created you will be provided with a long alphanumeric key – you will need this later.

You can now run ChatSync (make sure it is in the folder alongside The Forensic Browser for SQLite so that it can pick up the licence file). If you have obtained a key form IPInfoDB then select the checkbox and enter your key in the edit box (this will be retained in the registry for future use).
Click on the parse button and when prompted choose the Skype ChatSync folder you want to examine
and then again when prompted choose a database to write the results to.
When the parsing has completed, this may take a minute or two, you can open the saved database in the Forensic Browser for SQLite.
Now we have created and viewed the database in the Forensic Browser it is a simple process to use the built-in feature to create a new table containing three geolocated maps for each IP address.

Select “Create geolocated images” from the “Tools” menu

In the following dialog choose the table that contains the latitude and longitude information, in this case “decodedchatsync”. You also need to specify the key, lat and long fields, but these should be filled in for you in this instance. You will need to provide (or accept) the name for the output table.

The zoom levels specific the scale for each of the three created maps (0 disables a map) with 16 being the maximum “zoom in” level (i.e. street level) and 1 the minimum.

Press OK and the table will be created, this may take a few minutes as the maps are created and downloaded via the open street map server.

The resulting geodata1 table looks as follows:
However, in order to make sense of it, we want to display the maps alongside the data from the decodedchatsync table, to do this add the decodedchatsync table to the query designer and then the geodata1 table. Create a link by selecting the id field in the decodedchatsync table and dragging it to the id field in the geodata1 table. Finally, select all columns (*) from the decodedchatsync table and just the three maps from the geodata1 table. Now execute the query.
You can then use the column filter condition, should you wish, to filter on specific users:
The resulting query can be saved to HTML/XLSX, PDF etc.