Type Google Hangout Database

<< Details >>

Defined Constant	Type_Google_Hangout_Database
File Type Value	898
Parent Type	SQLite
Expandable	Yes
Carve	Yes
Format Details	Yes
MIME Type	application/vnd.sqlite3
Filename Extension	db
Typical Filename	babel1.db

Google Hangouts uses SQLite as a data storage format.

Truxton Exploiters

This file type is handled by the following ETLs:

• Expand

Items Produced

Truxton will extract the following from this file type:

• Type_Contact_List
• Type_File_Details
• Type_SQLite_Sample
• ENTITY_TYPE_GOOGLE_PLUS_ID
• ENTITY_TYPE_PERSON
• ENTITY_TYPE_PHONE_NUMBER
• LOCATION_TYPE_GOOGLE_HANGOUT

Filenames Seen

• babel1.db

Details

• Mobile Revelator

Queries

Here are the queries Truxton uses to exploit Google Hangout.

Gather the Chat Participants

This query gathers the details for the participants in all messages.

SELECT
  DISTINCT [conversation_id],
  [author_chat_id],
  [participants].[full_name]
FROM [messages]
JOIN [participants] ON ([participants].[chat_id] = [messages].[author_chat_id])
WHERE [author_chat_id] IS NOT NULL
ORDER BY
  [conversation_id],
  [author_chat_id];

Gather Messages

This query gathers the messages sorted by conversation and time.

SELECT
  [messages].[_id], 
  [messages].[message_id], 
  [messages].[conversation_id], 
  [messages].[author_chat_id], 
  [participants].[full_name], 
  [messages].[text], 
  [messages].[timestamp], 
  [messages].[latitude], 
  [messages].[longitude] 
FROM [messages] 
JOIN [participants] ON ([participants].[chat_id] = [messages].[author_chat_id]) 
ORDER BY
  [conversation_id],
  [timestamp]

Gather Contacts

This query gathers the contact list.

SELECT
  [merged_contacts].[_id], 
  [merged_contacts].[contact_id], 
  [merged_contacts].[display_name], 
  [merged_contact_details].[lookup_data_standardized], 
  [merged_contact_details].[lookup_data_label] 
FROM [merged_contacts] 
JOIN [merged_contact_details] ON ([merged_contact_details].[merged_contact_id] = [merged_contacts].[_id]) 
ORDER BY [merged_contacts].[contact_id]