Analysing Blesta Database (Useful Mysql Queries)

[Rodrigo]

When you have a blesta setup with relatively high client volume, you may want to periodically check if everything is OK, or clean up things that should not be there (i.e: data that is not automatically removed by blesta/plugins).

 

I've come up to this queries which I use periodically:

 

- Show users that have at least 1 active service and more than 3 unpaid invoices: This may happen when a provision plugin is not suspending a service, or just human mistake (useful to find customers that are not paying because of not having their services suspended)

SELECT many_invoices_customers.id 'customer_id', services.id_value 'service_id' from (SELECT i.client_id 'id',  count(*) 'c' FROM invoices i, clients c WHERE c.id = i.client_id AND i.status != 'void' and i.paid != i.total  GROUP BY i.client_id HAVING c > 3) many_invoices_customers, services WHERE services.client_id = many_invoices_customers.id AND services.status = 'active';

- Show users that have more than 3 unpaid invoices and at least 1 suspended service: let's you clean up users that don't pay anymore (and manually cancel them)

SELECT many_invoices_customers.id 'customer_id', services.id_value 'service_id', many_invoices_customers.c 'invoices_number' from (SELECT i.client_id 'id',  count(*) 'c' FROM invoices i, clients c WHERE c.id = i.client_id AND i.status != 'void' and i.paid != i.total  GROUP BY i.client_id HAVING c > 3) many_invoices_customers, services WHERE services.client_id = many_invoices_customers.id AND services.status = 'suspended' ORDER BY many_invoices_customers.c DESC;

I haven't implemented auto service cancel yet, but maybe is useful to someone here

 

*Those queries are meant to be executed directly into your blesta MySQL DB.

[Tyson]

It might be useful to select additional fields (e.g. client first/last name) so admins can more readily use the queries to find matching clients. They could also use them to generate custom reports under [billing] -> [Reports].

[Paul]

You may be interested in the auto cancel plugin included in Blesta 3.6. https://docs.blesta.com/display/user/Auto+Cancel

[Cody]

These can also be added to Blesta as Custom Reports, which is super cool, and would even allow you to customize them across dates or status values, etc.