Database-Know-How

[majkel436]

Hello, I'm new here.
I work as a Data Analyst in a call center that uses vicidial. Vici was shipped and is maintained by an external IT company. To create automated reports (after some persuasion) they gave me credentials to connect directly to MySQL database. However, they don't want to share information about what is what in tables, and how did it get there. I don't blame them, me having this knowledge is less money for them. I have figured out many things by myself, but some are still a mystery to me. To better understand how this database works I would like to learn what happens step-by-step when a call is made and how the records are created.
Could you please point me where can I find this kind of knowledge? Is it manager's manual or something else? Maybe I should look for it in completely different place?

[carpenox]

Well you could use "show fields in table table-name" to learn what's in it and yes there's a manual, a few version you can get on vicidial.com as well as a paid version

[martinch]

Welcome to ViCiDial and welcome to the boards! Yeah...the user manual should have it in, I think as Chris has said. However, if you want a brief overview of the schema tables I'll do my best to describe them what I know. PS forgive me it's not an exhaustive list as I am a little tired after typing a lot I will complete it tomorrow.

Code: Select all

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'asterisk';


Code: Select all

| vicidial_asterisk_output                   |
| vicidial_agent_latency_log_archive         | Archive table.
| vicidial_agent_latency_log                 | Contains information about latency on the agent's computer (useful for debugging issues).
| vicidial_ingroup_hour_counts_archive       | Archive table.
| vicidial_state_call_times                  | This table contains information about calling times for user-defined states for cross-country dialling strategies.
| vicidial_carrier_log_archive               | Archive table.
| vicidial_inbound_dids                      | Holds all information about inbound DIDs (for DDIs for us Europeans).
| vicidial_campaigns                         | Contains all the information relating to specific outbound campaigns.
| vicidial_callbacks                         | Holds information about customer callbacks (both agent and campaign ones).
| parked_channels                            | Archive table.
| vicidial_call_notes_archive                | Archive table.
| vicidial_list                              | Primarily holds all the phone numbers for agents to dial.
| servers                                    | Stores information about servers used on your ViCiDial cluster.
| vicidial_abandon_check_queue_archive       | Archive table.
| vicidial_shared_log                        | Stores debug information relating to SHARED dial methods.
| vicidial_carrier_hour_counts_archive       | Archive table.
| vicidial_agent_function_log_archive        | Archive table.
| vicidial_ajax_log                          | ViCiDial web interface log for certain Ajax requests made from agents to the backend (debugging).
| vicidial_admin_log                         | Holds information about admin changes made on the system (accountability purposes).
| gateway_recording_log                      | A rather new table containing information about gateway stereo call recordings.
| vicidial_languages                         | Parle vous Francais? Sprechen sie Deutsch? Store your language information in here.
| system_settings                            | Your system will not work without this table. It holds the system level settings that control a lot of your cluster setup.
| vicidial_confbridges                       | Holds data surrounding ConfBridges teleconference bridges.
| vicidial_user_log                          | Contains information relating to agent logins/logouts.
| vicidial_user_dial_log                     | Stores information about manual dials made by agents.
| server_performance                         | Server...performance :) Disk usage :)
| web_client_sessions                        | Holds information relating to agent dialler sessions.
| vicidial_log_extended_archive              | Archive table.
| vicidial_agent_log_archive                 | Archive table.
| vicidial_users                             | One of the most important tables, what is a dialler system without agents. :)
| vicidial_latency_gaps                      | Contains information about latency on the agent's computer (useful for debugging issues).
| vicidial_lists                             | Holds information about dialling lists. Dialling lists are a core concept in ViCiDial. Generally, dialling takes place using lists.
| vicidial_sip_action_log_archive            | Archive table.
| vicidial_agent_notifications_archive       | Archive table.
| vicidial_user_groups                       | Users on ViCiDial are seperated into user groups with various levels of access. This table holds that information.
| vicidial_screen_colors                     | Give ViCi some colour! Colour settings are stored here.
| vicidial_vmm_counts_archive                | Archive table.
| vicidial_amd_log                           | ViCi's Answering Machine Detection log.
| vicidial_manager_chat_log_archive          | Archive table.
| vicidial_xfer_presets                      | Holds information relating to Transfer departments and phone numbers for agent's to use on a campaign.
| vicidial_pause_codes                       | Pause codes are needed so agents can give a reason to go into pause. Without them, the agents would dial...for infinity and beyond!
| vicidial_dnc                               | Holds information about Do-No-Call numbers. Any numbers that appear in this table will not be called by ViCiDial unless forced.
| vicidial_manager                           | A key table to allow the ViCi frontend to interface with the ViCi backend and forward commands onto Asterisk. The primary gateway to the telephony is through this table.
| vicidial_dtmf_log                          | Holds DTMF inputs from customers and agents. Beware, DTMF tones may contain sensitive PII data.
| vicidial_user_dial_log_archive             | Archive table.
| vicidial_log_extended_sip_archive          | Archive table.
| vicidial_api_urls_archive                  | Archive table.
| vicidial_closer_log                        | One of the most important inbound tables. Contains information about inbound calls taken by agents (or received and hit IVRs).
| recording_log                              | Holds all the information pertaining to call recordings on the ViCi platform.
| vicidial_chat_log_archive                  | Archive table.
| vicidial_shifts                            | Agents can be dropped into shift groups. This is the place where that shift information is stored.
| vicidial_ccc_log_archive                   | Archive table.
| server_updater                             | This keeps track of the current time for both web and database to ensure they are in sync. ViCi is time sensitive so this table helps maintain the right time synchonisation.
| vicidial_statuses                          | Statuses are stored in here for call outcomes.
| vicidial_call_time_holidays                | Stores information relating to call time holidays to avoid dialling customers on holiday.
| vicidial_did_log_archive                   | Archive table.
| vicidial_two_factor_auth                   | Stores information relating to ViCi's 2FA (see carpenox guide for setup).
| vicidial_timeclock_log                     | Stores punch in / punch out times for agents using ViCi's timeclock.
| vicidial_log_archive                       | Archive table.
| vicidial_tiltx_shaken_log                  | Stores STIR/SHAKEN logs if your using it with your carrier.
| vicidial_timeclock_status                  | Stores information about the current state of timeclock users.
| vicidial_manager_chats_archive             | Archive table.
| park_log_archive                           | Archive table.
| vicidial_sessions_recent_archive           | Archive table.
| vicidial_dial_cid_log_archive              | Archive table.
| vicidial_amd_log_archive                   | Archive table.
| vicidial_outbound_ivr_log_archive          | Archive table.
| call_log                                   | Contains low level information about calls routed through the system.
| user_call_log_archive                      | Archive table.
| vicidial_api_log_archive                   | Archive table.
| park_log                                   | Contains information about parking/unparking customers (holding and taking off hold).
| vicidial_inbound_survey_log_archive        | Archive table.
| vicidial_log                               | The outbound log for outbound calls both automatic and manual calls.
| vicidial_auto_calls                        | Arguably top 5 table where all the live phone calls are tracked and assigned to agents.
| vicidial_shared_drops                      | Holds information about SHARED drops in the SHARED dialling method.
| vicidial_inbound_callback_queue_archive    | Archive table.
| vicidial_xfer_log_archive                  | Archive table.
| vicidial_agent_log                         | The moneymaker! This table holds information about agent activity on ViCi which can be used for billing and reporting purposes.
| vicidial_hopper                            | This is the place where all our outbound leads are that we wish to predictively dial or manual dial.
| vicidial_callbacks_archive                 | Archive table.
| vicidial_remote_agents                     | Holds information relating to remote agents if you choose to utilise them in your setup.
| recording_log_archive                      | Archive table.
| vicidial_music_on_hold                     | Stores your music on hold class configurations.
| vicidial_campaign_hotkeys                  | Stores hotkeys that can be used on the agent interface. Handy for fast dispositions of answering machines.
| vicidial_server_trunks                     | Holds information about trunk capacity on your setup.
| help_documentation                         | Documentation for the Admin Panel help is stored in this table.
| vicidial_did_agent_log_archive             | Archive table.
| vicidial_postal_codes                      | Stores postal codes / zip codes so you can incorporate that into your dialling strategy. Call New York at 10AM EST but don't call Los Angeles at 10AM EST.
| vicidial_dial_log_archive                  | Archive table.
| vicidial_campaign_hour_counts_archive      | Archive table.
| vicidial_agent_visibility_log_archive      | Archive table.
| vicidial_peer_event_log_archive            | Archive table.
| vicidial_agent_sph                         | Track sales per hour.
| vicidial_lead_search_log_archive           | Archive table.
| vicidial_recent_ascb_calls_archive         | Archive table.
| vicidial_language_phrases                  | Holds strings for various languages. Strings are stored in plain English with appropriate translations.
| vicidial_carrier_log                       | Contains information about outbound calls and carrier responses.
| vicidial_live_agents                       | A vital table. This is the living heartbeat of ViCiDial. It holds all the information about the live state of play with regards to agents and calls.
| vicidial_rt_monitor_log_archive            | Archive table.
| vicidial_scripts                           | Holds information relating to agent scripts.
| vicidial_agent_visibility_log              | This is used to counter browser throttling efforts by maintaining data relating to agent browser states.
| call_log_archive                           | Archive table.
| conferences                                | Holds all the current phone conference information for the agents.
| vicidial_music_on_hold_files               | A place to store your wonderful musical files (for use with music on hold classes).
| vicidial_agent_latency_summary_log_archive | Archive table.
| vicidial_chat_archive                      | Archive table.
| vicidial_closer_log_archive                | Archive table.
| vicidial_lead_call_quota_counts_archive    | Archive table.
| vicidial_drop_log_archive                  | Archive table.
| vicidial_log_noanswer_archive              | Archive table.
| live_channels                              | Stores information about live channels on Asterisk (the backend telephony system).
| groups_alias                               | Allows phone numbers to be grouped to allow manual dials with specific CIDs.
| phones_alias                               | Phones logins can be stored as aliases to make them easier to remember.
| vicidial_latency_gaps_archive              | Archive table.
| vicidial_inbound_caller_codes_archive      | Archive table.