Creating custom reports

[Roy Otten]

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

[Roy Otten]

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.

 

[Author]

Posts