1 Reply Latest reply on May 20, 2014 11:30 AM by yaquaholic

    Web Help Desk SQL database tables

    tbehrens

      We want to report off the Web Help Desk SQL database. Is there a published guide for the tables?

        • Re: Web Help Desk SQL database tables
          yaquaholic

          Bump....

           

          I'm trying to update asset custom fields via PostGreSQL, rather than having to manually use the web GUI for 450 assets.

          If anyone can shed a light on the relationships between asset,  asset_custom_field and custom_field_definition, I would find it very helpful.

           

          My example: we have created a field called "Role" which details if the device is Core, Production, Stage, etc.

           

          I can see the relationship between asset_custom_field and custom_field_definition, see the SQL code below:

           

          SELECT fd.label, cf.definition_id, cf.entity_id , cf.id, cf.string_value

            FROM asset_custom_field cf

              INNER JOIN custom_field_definition fd

                ON cf.definition_id = fd.id

            WHERE definition_id = 62

            AND cf.string_value IS NOT NULL

           

          Returns:      

           

          labeldefinition_identity_ididstring_value
          Role6254251Core
          Role62184265Core
          Role62104288Core
          Role62384295Core
          Role622654366Production
          Role622694379Production
          Role622704381Production
          Role622714384Production
          Role622724388Production
          Role622734390Production
          Role622744395Production
          Role622754396Production
          Role622774408Production

           

          But how do I link this back to the Asset table, to show the "Role" assigned to each asset?

          As neither entity_id or id match the asset.asset_id.

           

          Once I've learnt that, mass updating the "Role" field should be easy....

           

          Thanks