Reporting for CI

[yacine12]

Hi Guys,
Can I make an internal reporting /statistics for my owner CI ,the same question for my Change ticket?
if no,so i must use an external but how can i extract data from my DB ?to manipulate them in my external report .
Best Regards,
Yacine

[davidappleby]

If you use MySQL, save this as a view and then you can use it in the external report.

Code: Select all

SELECT ci.configitem_number CINumber, cg.name CIType, cv.name CIName, cc.customer_id CustomerID, cc.name CustomerName, ge.name `Type`
, left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))
	, (locate('\'', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)) FirstKey
, left(right(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
		, (char_length(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))
			- locate('[', left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))))
	, (locate(']', right(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
		, (char_length(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))
			- locate('[', left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))))) - 1)) FirstKeySort
, left(right(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
		, (locate('\'{', reverse(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))) - 1))
	, (locate('\'', right(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
		, (locate('\'{', reverse(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))) - 1))) - 1)) SecondKey
, left(right(right(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
	, (locate('\'{', reverse(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))) - 1)), 2), 1) SecondKeySort
, cast(ifnull(case when gc.general_catalog_class not like 'ITSM::ConfigItem%' then null else gc.name end, xs.xml_content_value) as CHAR) Value
FROM xml_storage xs
LEFT JOIN general_catalog cg on right(xs.xml_type, (locate(':', reverse(xs.xml_type)) - 1)) = cg.id
INNER JOIN configitem_version cv on xs.xml_key = cv.id
INNER JOIN configitem ci on cv.configitem_id = ci.id
LEFT JOIN general_catalog gc on xs.xml_content_value = gc.id
LEFT JOIN xml_storage sx on cv.id = sx.xml_key AND sx.xml_content_key LIKE '%CustomerID%' AND sx.xml_content_key LIKE '%{\'Content\'}'
LEFT JOIN customer_company cc on sx.xml_content_value = cc.customer_id
LEFT JOIN xml_storage xm on cv.id = xm.xml_key AND xm.xml_content_key = '[1]{\'Version\'}[1]{\'Type\'}[1]{\'Content\'}'
LEFT JOIN general_catalog ge on xm.xml_content_value = ge.id
WHERE xs.xml_type LIKE 'ITSM::ConfigItem%'
AND xs.xml_type NOT LIKE 'ITSM::ConfigItem::Archiv%'
AND xs.xml_content_key LIKE '%{\'Content\'}'
ORDER BY cg.name, cv.name
, left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))
	, (locate('\'', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
, left(right(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
		, (char_length(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))
			- locate('[', left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))))
	, (locate(']', right(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
		, (char_length(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))
			- locate('[', left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))))) - 1))
, left(right(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
		, (locate('\'{', reverse(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))) - 1))
	, (locate('\'', right(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
		, (locate('\'{', reverse(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))) - 1))) - 1))
, left(right(right(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1))
	, (locate('\'{', reverse(left(right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1)), (locate('{\'Content\'}', right(xs.xml_content_key, (locate('\'{]1[}\'noisreV\'{]1[', reverse(xs.xml_content_key)) - 1))) - 1)))) - 1)), 2), 1)

[yacine12]

Thank you
But I have another question how i can import a lot of data to my Database from a CSV or Excel File which i can specifie all informations about CIs ?
You knew that data in xml_storage it's too complicated ,
so how can i make that ?
Regards,

[yuri0001]

Good evening!
1. Create CI definition for each Class in OTRS
2. Enter 1 CI in each class into CMDB
3. In Import/Export - create export template for each class
4. Execute Export for each class into CSV
5. Now you have CSV template files for each class
6. Fill your files with needed data
7. Load data from files with created templates using Import/Export (import function)
If I understand you correctly

[yacine12]

Hi,
Yes I knew ,but it does'nt work
the export gives me an CSV file ,but when i modifie the information file and import the file it doesn't work :s

[yuri0001]

Hi!
I do not remember how I did it because is not handy, but it works.
I remember that I spent a long time, tried different variants of blank values...and separators.

[yacine12]

Hi
I'm also tried to import but usually you have the same the problem ,it tells me to input a mandatory attribute as Name and DeploymentState and also IncidentState
i'm tried with all the different deployementstate but the same problem is maked (
[Mon May 28 16:07:22 2012][Error][Kernel::System::ITSMConfigItem::Version::VersionAdd][411] Need DeplStateID!)

[Sophy978]

Good evening!
1. Create CI definition for each Class in OTRS
2. Enter 1 CI in each class into CMDB
3. In Import/Export - create export template for each class
4. Execute Export for each class into CSV
5. Now you have CSV template files for each class
6. Fill your files with needed data
7. Load data from files with created templates using Import/Export (import function)
If I understand you correctly

Hello, my issue is similar to this. I have defined classes, did exactly as what you've listed but my imported data does not update the existing data. I enabled all the indentifiers and it did nothing on my import. The existing data in the system is unchanged. If uncheck all the identifiers, the systems basically creates new entries for all my import. I just want to add or update existing data. Are you able to accomplish this, if so how? Thanks for any help.

-Sophy