Topic Resolution: Investigating
Viewing 1 reply thread
  • Author
    Posts
    • #15038
      Roy Otten
      Participant

        Hello,

        I am trying to create some custom reports based on information in Otobo.
        We are using the CMDB functionality, and now I am trying to create a report with a 3rd party reporting tool.

        In the configitem and configitem_history tables I do see some of the information, but just a bare minimum.
        Not even close to what we have stored in CMDB.

        Where would I have to look to get the rest of the information?

        Hopefully someone can point me in the right direction :)

         

        Thank you

      • #15040
        Roy Otten
        Participant

          okay, I found out that it is in the xml_storage table.

          I managed to make it work using the following views:

          CREATE VIEW vw_cmdb_users
          AS
          SELECT distinct CONCAT(UCASE(LEFT(b.xml_content_value, 3)), LCASE(SUBSTRING(b.xml_content_value, 4))) as username
          FROM otobo.configitem a
          left join otobo.xml_storage b on a.last_version_id = b.xml_key
          where b.xml_type =’ITSM::ConfigItem::22′ and b.xml_content_key like ‘%owner%’ and (xml_content_value not like ‘%{%}%’ and length(xml_content_value) > 1)

          CREATE VIEW vw_assets_per_user
          as
          SELECT a.configitem_number,
          c.username
          FROM otobo.configitem a
          left join otobo.xml_storage b on a.last_version_id = b.xml_key
          inner join vw_cmdb_users c on lower(b.xml_content_value) = lower(c.username)
          where b.xml_type =’ITSM::ConfigItem::22′ and length(b.xml_content_value) > 1

           

          CREATE VIEW vw_configitem_details
          as
          SELECT a.configitem_number,
          replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(replace(replace(replace(b.xml_content_key,’\”,”),'[1]{Version}[1]{‘,”),’}[1]{Content}’,”),’9′,”),’8′,”),’7′,”),’6′,”),’5′,”),’4′,”),’3′,”),’2′,”),’1′,”),’0′,”),’}[]{‘,’ ‘) as hw_type,
          b.xml_content_value as ‘hw_detail’
          FROM otobo.configitem a
          left join otobo.xml_storage b on a.last_version_id = b.xml_key
          where b.xml_type =’ITSM::ConfigItem::22′ and b.xml_content_value not like ‘%{%}%’ and length(b.xml_content_value) > 1 and xml_content_key not like ‘%TagKey%’

           

          This might help someone else who is trying to query this information.

           

      Viewing 1 reply thread
      • You must be logged in to reply to this topic.