Next, I will describe the instructions on how I got statistics for viber chat using a backup file.
1. The backup copy of the account has the viber.db database format, for the PC version it is located along the path: C: \ Users \ <User_name> \ AppData \ Roaming \ ViberPC \ <phone number>.
2. I used SQL Lite Browser to read the database file.
3. Open the viber.db file in the SQL Lite Browser . The database looks like this:
4. The main table is Events, all events (by account) in the vibe are numbered (EventID). Other tables have a foreign key relationship called EventID. Information on messages is stored in the Messages table, information on users - in the Contact table. I only used these three tables.
5. In order to get statistics on a chat, first you need to find out the identifier of the desired chat ChatID. To do this, select the "View data" item of the context menu for the ChatInfo table and go to the "Data" tab for viewing.
6. By the name of the chat (the Name attribute), we look for the desired one and look at its ID (the ChatID attribute).
7. Similarly, you can get the user identifier (ContactID) in the Contact table through the Name attributes (as the user is recorded on your phone) or ClientName (username by his account). I used the ClientName attribute because I do not have all users in my phone's notebook.
8. Next, we obtain the required data by forming Select queries on the SQL tab. What attributes to do depends on the needs, for this it is necessary to make an appropriate request. For myself, I made the requests shown in table 1.
Table 1. List of used requests
| P / p No. | Request text | Description |
|---|---|---|
| 1 | Select Count (Messages.EventID) from Messages, Events where Messages.EventID = Events.EventID and Events.ChatID = 46 | Number of chat messages |
| 2 | Select count (Messages.EventID), Contact.ClientName from Messages, Events, Contact where Messages.EventID = Events.EventID and Events.ChatID = 46 and Events.ContactID = Contact.ContactID group by Events.ContactID order by Contact.ContactID | Number of posts by user (returns the number of events for each user) |
| 3 | Select Contact.ContactID, Contact.ClientName from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ChatID=46 and Events.ContactID=Contact.ContactID group by Events.ContactID order by Contact.ContactID | |
| 4 | Select * from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ContactID=Contact.ContactID and Events.ChatID=46 and Events.ContactID=465 | |
| 5 | Select count(Messages.EventID), Messages.Type from Messages, Events where Messages.EventID=Events.EventID and Events.ChatID=46 group by Messages.Type order by Messages.Type | (. 2) |
| 6 | Select Count(Messages.Type), Messages.Type from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ContactID=Contact.ContactID and Events.ChatID=46 and Events.ContactID=482 group by Messages.Type | |
| 7 | Select Count(Messages.PGIsLiked), Contact.ClientName from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ChatID=46 and Messages.Type=0 and Messages.PGIsLiked=1 and Events.ContactID=Contact.ContactID group by Contact.ClientName order by Contact.ContactID | |
| 8 | Select Sum(Messages.PGLikeCount), Contact.ClientName from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ChatID=46 and Events.ContactID=Contact.ContactID group by Contact.ClientName order by Contact.ContactID | |
| 9 | Select Events.TimeStamp from Messages, Events, Contact where Messages.EventID=Events.EventID and Events.ContactID=Contact.ContactID and Events.ChatID=46 and Events.ContactID=460 order by Events.TimeStamp | (. . 11) |
2.
| № / | ||
|---|---|---|
| 1 | EventID | . , .. , |
| 2 | Type | ( , , ):
0 – : , 1 – 2 — 3 — 4 – 9 – 11 – 12 — 15 – , 65 – 66 – 67 – 68 – 69 — 72 – 77 — |
| 3 | ContactID | |
| 4 | ClientName | |
| 5 | ChatID | |
| 6 | TimeStamp | UNIX- POSIX- (. Unix time) — , UNIX POSIX- .
( UTC) 31 1969 1 1970, . |
9. After executing the query, I exported the result first to PDF, and then to excel (it was easier for me):
10. After that I used the data to build infographics in excel, for example, at request # 5 (see table 1):
Note 1. Data differ, because the chat is active and the database is being updated.
Note 2. Already in excel I have combined types 65 ... 77 into a common one (service).
11. How to work with the time attribute (TimeStamp). In the database, UNIX time is used to bind events to time, and to obtain data associated with binding to date and time, it became necessary to process them further. Here's an example of how I did it in excel:
a. Save the results of query 9 (see table 1) in excel (column A).
b. In column B we discard the last 3 characters (I did not understand what they are for) to get the time in UNIX format. We do this using the formula = LEFT (A2; DLSTR (A2) -3). Stretch the formula over the entire range of lines.
c. Column C is filled with units (message counter).
d. In column D, translate the date into a human-readable one using the formula = (B2 / 86400) + 25569. Stretch the formula over the entire range of rows.
e. To calculate the number of messages per month (cells E2: R2), use the formula = SUMIF ($ C $ 2: $ C $ 1434; $ D $ 2: $ D $ 1434; "> =" & E $ 1; $ D $ 2: $ D $ 1434; "<= "& EONMONTHS (E $ 1; 0)). Stretch the formula to the entire range of columns E1: R1.
f. We build a histogram based on the range of cells E1: R2.
Conclusion
This method of collecting statistics is performed in manual mode, it is resource-intensive (at least in terms of the time spent), but at least efficient. For lack of a better one, so to speak. This method can be used not only to get statistics on the chat, but also on the Viber account as a whole. I do not attach the sources, tk. I performed all operations on a real chat containing personal data of users, and I was too lazy to create a separate chat for the test. Thank you for attention.