title: querying otrs database
body: <p>I have a problem with creating a query on a postgresql database from OTRS.  I need to get an asset list with some data that is stored in the CMDB, but I am having trouble with the way that the data is stored.</p>
  
  <p>This query works ok as far as it goes but as soon as I add another left join to get some more data it just runs for ever without returning any results.</p>
  
  <p>As far as I can see, the problem is that all the information is contained in the xml_storage table without any relationships so I need to repeatedly query that table to get the data.  There is also a mix of data types where an integer is stored as a varyable character in some cases and this prevents me from using that value to get additional information from the general catalog.</p>
  
  <p>I have been banging my head against a brick wall for days on this so i would really appreciate any help</p>
  
  <pre><code>SELECT 
        asset_value,
        manufacturer_value,
        model_value,
        serial_value,
        fqdn_value,
        array_agg(service),
        purchasedate_value,
        status_value,
        configitem.configitem_number,
        configitem_version.name,
        general_catalog.name
  
      FROM 
        public.configitem_version,
        public.general_catalog,
        public.configitem 
  
      left join
        (SELECT
        xml_storage.xml_content_value AS asset_value,
        xml_storage.xml_key
        FROM
        public.xml_storage 
        WHERE
        xml_content_key = '[1]{''Version''}[1]{''Asset Number''}[1]{''Content''}')   as select_asset on last_version_id::int = select_asset.xml_key::int
  
      left join
        (SELECT
        xml_storage.xml_content_value AS manufacturer_value,
        xml_storage.xml_key
        FROM
        public.xml_storage 
        WHERE
        xml_content_key = '[1]{''Version''}[1]{''Manufacturer''}[1]{''Content''}' ) as select_manufacturer on last_version_id::int = select_manufacturer.xml_key::int
  
      left join
        (SELECT
        xml_storage.xml_content_value AS model_value,
        xml_storage.xml_key
        FROM
        public.xml_storage 
        WHERE
        xml_content_key = '[1]{''Version''}[1]{''Model''}[1]{''Content''}' ) as select_model on last_version_id::int = select_model.xml_key::int
  
      left join
        (SELECT
        xml_storage.xml_content_value AS serial_value,
        xml_storage.xml_key
        FROM
        public.xml_storage 
        WHERE
        xml_content_key = '[1]{''Version''}[1]{''SerialNumber''}[1]{''Content''}' ) as select_serial on last_version_id::int = select_serial.xml_key::int
  
      left join
        (SELECT
        xml_storage.xml_content_value AS fqdn_value,
        xml_storage.xml_key
        FROM
        public.xml_storage 
        WHERE
        xml_content_key = '[1]{''Version''}[1]{''FQDN''}[1]{''Content''}' ) as select_fqdn on last_version_id::int = select_fqdn.xml_key::int
  
      left join
        (SELECT 
        link_relation.source_key AS "configitem_id", 
        service.name AS "service"
        FROM 
        public.link_relation, 
        public.service
        WHERE 
        link_relation.target_key::int = service.id AND
        link_relation.target_object_id::int = 2 AND link_relation.source_object_id::int = 3
        UNION
        SELECT 
        link_relation.target_key AS "configitem_id", 
        service.name AS "service"
        FROM 
        public.link_relation, 
        public.service
        WHERE 
        link_relation.source_key::int = service.id AND
        link_relation.target_object_id::int = 3 AND link_relation.source_object_id::int = 2) as select_service on configitem.id = configitem_id::int
  
      left join
        (SELECT
        xml_storage.xml_content_value AS purchasedate_value,
        xml_storage.xml_key
        FROM
        public.xml_storage 
        WHERE
        xml_content_key = '[1]{''Version''}[1]{''PurchaseDate''}[1]{''Content''}' ) as select_purchasedate on last_version_id::int = select_purchasedate.xml_key::int
  
      left join
        (SELECT
        general_catalog.name AS status_value,
        general_catalog.id
        FROM
        public.general_catalog) as select_status on configitem.cur_depl_state_id::int = select_status.id::int
  
      WHERE
        configitem.last_version_id = configitem_version.id AND
        configitem.class_id = general_catalog.id AND
        (configitem.class_id = 32 OR configitem.class_id = 33)
  
  
      GROUP BY
        asset_value,
        manufacturer_value,
        model_value,
        serial_value,
        fqdn_value,
        purchasedate_value,
        status_value,
        configitem.configitem_number,
        configitem_version.name,
        general_catalog.name
  
      ;
  </code></pre>