otrs location query.sql

SELECT
  th.name as metric,
  long_lat.site AS name,
  long_lat.Latitude AS latitude,
  long_lat.Longitude AS longitude,
  COUNT(th.type_id) AS value
FROM ticket_history th
  LEFT JOIN
  -- SUB QUERY FOR LOCATIONS
  (SELECT
    CONCAT(trim(matches[1]), ',', trim(matches[2])) AS id,
    trim(matches[1]) AS Latitude,
    trim(matches[2]) AS Longitude,
    matches [3] as site
    FROM (SELECT regexp_matches((SELECT config
                                 FROM dynamic_field
                                 WHERE id = 9), '^\s+(-?\d+\.\d+),(-?\d+\.\d+):(.*)$', 'gm') AS matches) as Locations)
  -- SUB QUERY
    AS long_lat ON long_lat.id = split_part(substr(th.name, 32), '%', 1)
    INNER JOIN ticket_type tt on tt.id = th.type_id
WHERE $__timeFilter(th.create_time) AND th.name LIKE '%Locations%'AND split_part(substr(th.name, 32), '%', 1) is not null
GROUP BY 1,2,3,4 --,5