Querying Custom Fields

Problem

I want to run a query on the CloudBolt MySQL database instance that returns a list of all servers an their corresponding expiration dates.

Solution

We're going to issue a SQL query that joins the infrastructure_server table to its corresponding custom field tables:

select 
	s.hostname, cf.name, ocfv.*
from 
	infrastructure_server s, 
	`infrastructure_server_custom_field_values` cfv, 
	`orders_customfieldvalue` ocfv,
	`infrastructure_customfield` cf
where 
	s.id = cfv.server_id 
	and cfv.`customfieldvalue_id` = ocfv.`id` 
	and ocfv.`field_id` = cf.`id`
	and cf.name = 'expiration_date'
	and s.status = 'ACTIVE'

This query will return a result set similar to:

winhost004	expiration_date	2015-08-11 00:00:00
mchost-003	expiration_date	2015-08-29 00:00:00
mchost-004	expiration_date	2015-08-29 00:00:00
mchost-005	expiration_date	2015-09-04 00:00:00
mchost-006	expiration_date	2015-09-04 00:00:00
PCI-LIN-006	expiration_date	2015-10-15 00:00:00
PCI-LIN-008	expiration_date	2015-10-16 00:00:00
PCI-LIN-001	expiration_date	2015-11-10 00:00:00
FINCLOUD0005	expiration_date	2015-11-10 00:00:00
svcFinance001	expiration_date	2015-11-12 00:00:00