SQLite Forensics Book, now available on Amazon

More information here


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

    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 on-line 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.

    For corporate or law enforcement investigators a fully functional licence for the Forensic Toolkit for SQLite can be obtained by clicking here and providing your official email address, full name and position within your organisation

    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 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.