Hey I had the same problem, this is the query I use to pull the data into PowerBI then surface it with graphs.
WITH GROUP1 AS (
SELECT
ost_ticket.number,
ost_ticket.ticket_id,
ost_ticket.created,
ost_ticket.closed,
ost_form_field.label,
ost_form_entry_values.field_id,
ost_form_entry_values.`value`
FROM
ost_ticket
JOIN
ost_form_entry ON ost_ticket.ticket_id = ost_form_entry.object_id
JOIN
ost_form_entry_values ON ost_form_entry.id = ost_form_entry_values.entry_id
JOIN ost_form_field ON ost_form_entry_values.field_id = ost_form_field.id
WHERE
-- ost_form_entry_values.field_id, = blah
-- ost_form_entry_values.`value` = blah
),
Group2 AS (SELECT
ost_ticket.number,
ost_ticket.ticket_id,
ost_ticket.created,
ost_ticket.closed,
ost_form_field.label,
ost_form_entry_values.field_id,
ost_form_entry_values.`value`
FROM
ost_ticket
JOIN
ost_form_entry ON ost_ticket.ticket_id = ost_form_entry.object_id
JOIN
ost_form_entry_values ON ost_form_entry.id = ost_form_entry_values.entry_id
JOIN ost_form_field ON ost_form_entry_values.field_id = ost_form_field.id
WHERE
-- ost_form_entry_values.field_id, = blah
-- ost_form_entry_values.`value` = blah
Group by number),
Group3 AS (SELECT
ost_ticket.number,
ost_ticket.ticket_id,
ost_ticket.created,
ost_ticket.closed,
ost_form_field.label,
ost_form_entry_values.field_id,
ost_form_entry_values.`value`
FROM
ost_ticket
JOIN
ost_form_entry ON ost_ticket.ticket_id = ost_form_entry.object_id
JOIN
ost_form_entry_values ON ost_form_entry.id = ost_form_entry_values.entry_id
JOIN ost_form_field ON ost_form_entry_values.field_id = ost_form_field.id
WHERE
-- ost_form_entry_values.field_id, = blah
-- ost_form_entry_values.`value` = blah
)
Select
group1.number,
group1.closed,
group1.value,
group2.value,
group3.value
from group1
JOIN group2 ON group1.number = group2.number
JOIN group3 ON group2.number = group3.number
;
The different groups allow you to query multiple values, since custom fields all store data in the same column. If you only needed one field you only need to specify one of them.
This query lets you see the ticket number, create date, and custom fields.
-- ost_form_entry_values.field_id, = blah is the ID of the field (Like total hours)
-- ost_form_entry_values.value = blah is the actual value of the column.
To get the field ID numbers just do select * from ost_form_field. The ID is what you're looking for.
Hope this helps!
