This post is aimed at those (like me) who are just starting to try to wrangle all of the vicidial tables and/or those that just want to save time writing queries for custom reports.
Overview: Using the OpenAI playground, it's possible to make an assistant in about 10 minutes that can automatically deduce the correlations between different tables in vicidial given a schema in csv format. It then provides a chatbot-like interface where you can ask for a query for whatever report you need, and it will output it.
Requirements:
-OpenAI account. I'm using the gpt-4 model which, as of this posting, requires putting in $10 of credit I believe. It would probably work reasonably well with one of the gpt-3.5 models which should be free to try as far as I know.
-A full schema of your vicidial DB. If you log in to whichever DB server you want to use (I'm using a replica for this) and run
- Code: Select all
mariadb
- Code: Select all
use asterisk;
- Code: Select all
SELECT table_name, column_name, data_type, column_key INTO OUTFILE '/path/to/output_file.txt' FROM information_schema.columns WHERE table_schema = 'asterisk';
Setup: Head to https://platform.openai.com/playground and log in or create an account.
You should get to a screen that says Playground with a select next to it. Select Assistants if it isn't already selected, and then click where it says create new assistant. Give it any name you prefer. For the Model, I'm using gpt-4-1106-preview mostly because it allows for rather large inputs and more importantly outputs (large enough that it can actually generate not only the query, but also stick it in php and wrap it in html to give you a full web report page.)
Here are the instructions I used:
- Code: Select all
Purpose: This Assistant is designed to facilitate interaction with the Vicidial mariaDB database, allowing users to generate reports and gain insights through natural language queries. It understands the structure and relationships within the Vicidial database, providing an intuitive interface for querying complex data without needing direct SQL knowledge.
Capabilities:
Database Query Interpretation: Converts natural language queries into SQL commands. It can handle queries related to inbound and outbound calls, campaigns, agent activities, and more.
Schema Analysis: Analyzes the database schema to infer relationships between tables. This is particularly useful for identifying potential joins and understanding the data flow.
Report Generation: Translates query results into easily understandable reports. Can handle requests for daily summaries, performance metrics, campaign statistics, etc.
Dynamic Query Support: Capable of handling dynamic and context-based queries, like "How many calls were there for a specific campaign today?" or "What was the activity of agents in a particular location yesterday?"
For any uploaded schema file, fully parse and ingest the database schema in order to be able to determine the relationships between tables.
I also enabled Code Interpreter and Retrieval, which allow for code generation and file upload, respectively.
Create the assistant, and that's pretty much it. In the first query, upload the schema file you generated and start with "This is the database schema. Use this information to answer any further questions." or something to that effect. It will save the file for future use. Note: If it says it can't parse the txt file, just change it to a csv.
If you're asking a question and feel like it's not correctly referring to the schema, just remind it to do so.
Hope this helps someone. I generated queries for 12 KPIs in one prompt with no issue, and have made 5 or 6 rather detailed reports so far. Has definitely saved me time as well as helped me learn where everything is in the DB.
Here's what it looks like.
