08 - IM Forensics

Question 1

Where is the main database file for Skype located and how is it called?

Answer 1

Name: main.db

Location: ~/Library/Application Support/Skype/YourSkypeName/main.db

Question 2

What are the main tables of the Skype main.db?

Answer 2

• Accounts: Information about the Skype account used on the device
• CallMembers: Contacts that participated in a call
• Calls: Calls in that the user participated
• ChatMembers: Contacts that participated in a chat session
• Contacts: Skype contacts
• Conversations: Conversations that the user participated
• Messages: Messages exchange by the user
• SMSes: SMS messages sent to mobile numbers
• Participants: Conversation participants
• Transfers: Transferred files from and to the user
• Voicemails: Voicemails sent to the user
• Videos: List of video calls

Question 3

What and where are the main database files for WhatsApp for iOS and Android?

Answer 3

iOS

Files: ChatStorage.sqlite, Contacts.sqlite

Location: /var/mobile/Applications

Android

Files: Msgstore.db, wa.db

Location: com.whatsapp/databases/

Question 4

What are the main tables in the iOS WhatsApp contacts.sqlite database?

Answer 4

• ZWACONTACT
  
  • All contacts found on the phone
  • Z_PK (PK), ZABUSERID, ZFIRSTNAME, ZFULLNAME
• ZWAPHONE
  
  • Phone numbers of all the contacts
  • ZCONTACT (FK to zwacontact.z_pk), ZFAVORITE, ZLABEL, ZPHONE (Contact phone number), ZWHATSAPPID
• ZWASTATUS
  
  • Information related to the contact’s profile
  • ZPHONE (FK zwaphone.zphone), ZPICTUREPATH, ZTEXT

Question 5

What are the main tables in the iOS WhatsApp ChatStorage.sqlite database?

Answer 5

ZWACHATSESSION : List of unique conversations started with different contacts or groups

ZWAGROUPINFO : List of group conversations

ZWAGROUPMEMBER : List of contacts participating in group conversations

ZWAMEDIAITEM : List of exchanged media items

ZWAMESSAGE : List of messages exchanged

ZWAMESSAGEWORD : List of single words included in all the conversations

Question 6

What are the main tables in the Android WhatsApp msgstore.db database?

Answer 6

• chat_list
  
  • List of unique conversations
  • Key_remote_jid (unique id in xxxxs@whatsappgroup.com)
• messages
  
  • List of messages exchanged
  • key_remote_jid, key_from_me (0 = incoming, 1 = outgoing), data (message content), timestamp (UNIX epoch format), media_url (URL to file), media_mime_type (MIME type of exchange file), media_size, media_name, media_duration (duration of video files in sec), latitude, longitude
• wacontacts
  
  • All contacts found on the phone
  • jid, is_whatsapp_user (boolean), is_iphone (boolean), status (status text of contact), number, display_name, phone_type (mobile or home), given_name, family_name, wa_name (contact whatsapp name)

Question 7

What are the main db files of Viber for iOS and Android and where are they located?

Answer 7

iOS

File: Contacts.data

Location: /var/mobile/Applications

Android

File: viber_data, viber_messages

Location: /data/data/com.viber.voip

Question 8

What are the main tables of the Viber - iOS - Contacts.db?

Answer 8

ZABCONTACT: All contacts found on the phone

ZATTACHMENT: All the attachments exchanged

ZCONVERSATION: List of unique conversations

ZPHONENUMBERINDEX: List of phone numbers

ZRECENT: List of recent calls

ZSTICKER: Data related to the sticker icons

ZSTICKERSPACKAGE: Data related to the available sticker packages

ZVIBERLOCATION: Latitude and longitude of each message sent

ZVIBERMESSAGE: List of messages exchanged

Question 9

What are the main tables of the Viber - Android - viber_messages?

Answer 9

• messages
  
  • List of messages exchanged in clear text
  • thread_id (FK to threads.id), address, date (UNIX epoch format), status (msg delivery status, 1=sent, 2=delivered), type (0 = incoming, 1=outgoing), body, location_lat, location_lng, extra_uri (file path) and description
• participants
  
  • List of participants in each of the conversations
• threads
  
  • List of unique conversations started with different contacts or groups
  • id, date (UNIX epoch format), message_count, recipient_number, share_location (boolean: True = sharing enabled)

Question 10

What are the main tables of the Viber - Android - viber_data?

Answer 10

• calls
  
  • List of recent calls
  • number, date (UNIX epoch format), duration, type
• phonebookcontact
  
  • List of all contacts found on the phone
  • low_display_name, viber, display_name
• phonebookdata
  
  • List of list of phone numbers
• vibernumbers
  
  • list of phone numbers with a photo attribute