def pull_download_history(history_db, user, profile, downloads_output, downloads_headers): desired_columns = ['url', 'content', 'dateAdded'] available_columns = get_column_headers( history_db, 'moz_annos') + get_column_headers(history_db, 'moz_places') query_columns_list = [i for i in desired_columns if i in available_columns] query_columns = ', '.join( [i for i in desired_columns if i in available_columns]) unavailable = ','.join( list(set(desired_columns) - set(query_columns_list))) if len(unavailable) > 0: log.debug( 'The following desired columns are not available in the database: {0}' .format(unavailable)) log.debug("Executing sqlite query for download history...") try: downloads_data = sqlite3.connect(history_db).cursor().execute( 'SELECT url,group_concat(content),dateAdded,lastModified FROM moz_annos \ LEFT JOIN moz_places ON moz_places.id = moz_annos.place_id \ GROUP BY place_id').fetchall() log.debug("Success. Found {0} lines of data.".format( len(downloads_data))) except sqlite3.OperationalError: error = [ x for x in traceback.format_exc().split('\n') if x.startswith("OperationalError") ] log.error('Failed to run query. [{0}]'.format(error[0])) return log.debug("Parsing and writing downloads data...") for item in downloads_data: record = OrderedDict((h, '') for h in downloads_headers) record['user'] = user record['profile'] = profile record['download_url'] = item[0] record['download_path'] = item[1].split(',')[0] record['download_started'] = firefox_time(item[2]) record['download_finished'] = firefox_time(item[3]) if len(item[1].split(',')) >= 4: record['download_totalbytes'] = item[1].split(',')[3].split( ':')[1].replace('}', '') else: record['download_totalbytes'] = 'N/A' downloads_output.write_entry(record.values()) log.debug("Done.")
def pull_visit_history(history_db, user, profile, urls_output, urls_headers): desired_columns = ['visit_date','title','url','visit_count','typed','last_visit_date','description'] available_columns = get_column_headers(history_db,'moz_places') + get_column_headers(history_db,'moz_historyvisits') query_columns_list = [i for i in desired_columns if i in available_columns] query_columns = ', '.join([i for i in desired_columns if i in available_columns]) unavailable = ','.join(list(set(desired_columns) - set(query_columns_list))) if len(unavailable) > 0: log.debug('The following desired columns are not available in the database: {0}'.format(unavailable)) log.debug("Executing sqlite query for visit history...") try: urls_data = sqlite3.connect(history_db).cursor().execute( 'SELECT {0} FROM moz_historyvisits left join moz_places \ on moz_places.id = moz_historyvisits.place_id'.format(query_columns) ).fetchall() log.debug("Success. Found {0} lines of data.".format(len(urls_data))) except sqlite3.OperationalError: error = [x for x in traceback.format_exc().split('\n') if x.startswith("OperationalError")] log.error('Failed to run query. [{0}]'.format(error[0])) return log.debug("Parsing and writing visits data...") nondict = dict.fromkeys(desired_columns) for item in urls_data: record = OrderedDict((h, '') for h in urls_headers) item_dict = dict(zip(query_columns_list,item)) nondict.update(item_dict) record['user'] = user record['profile'] = profile record['visit_time'] = firefox_time(nondict['visit_date']).split('.')[0]+'Z' if nondict['title']: record['title'] = nondict['title'].encode('utf-8') record['url'] = nondict['url'] record['visit_count'] = nondict['visit_count'] record['typed'] = nondict['typed'] record['last_visit_time'] = firefox_time(nondict['last_visit_date']).split('.')[0]+'Z' if nondict['description']: record['description'] = nondict['description'].encode('utf-8') urls_output.write_entry(record.values()) log.debug("Done.")
def parse_profiles(profile_data, user, profile_output, profile_headers): log.debug("Success. Found metadata for {0} profiles.".format(len(profile_data.items()))) for k,v in profile_data.items(): record = OrderedDict((h, '') for h in profile_headers) record['user'] = user record['profile'] = k for key, val in v.items(): if key in profile_headers: record[key] = val record['active_time'] = firefox_time(record['active_time']*1000000) profile_output.write_entry(record.values())