Diffrence between the table configithem and xml_storage ?

[yacine12]

Hi,
What's the Diffrence between the table configithem and xml_storage ?
Regards,

[yacine12]

some one is here ?i'm waiting for your answer please

[yuri0001]

Hi!
configitem table store only ID, Name & Class of CI and xml_storage - contain the value content of CI versions (and not only CI, but stats and some else?)

[yacine12]

thank you

But the other information of the CI ,i can't find it ,like version of the software or Description,adress reseaux ect ,how can I find it ?

[jojo]

Hi yacine,

it seems that you expect short time answers. So you should consider some professional support

[yacine12]

Hi jojo,

Yes I'm in a workship and i'm working with OTRS
Thank you very much

[yacine12]

Hi jojo,
I want ask you about the content of xml_storage,in whats step of the code perl can I find this content ?
and thank you very very much
Regards,
Yacine

[yuri0001]

Try to see all tables with name configitem_* there you can find all you want (links to versions, CI definitions, ets...). How it work - it's more complex issue

[yacine12]

Hi yuri,

Some attribut in configitem_* is the type BLOB that we have some code like :
[
{
Key => 'Vendor',
Name => 'Vendor',
Searchable => 1,
Input => {
Type => 'Text',
Size => 50,
MaxLength => 50,
},
},
{
Key => 'Model',
Name => 'Model',
Searchable => 1,
Input => {
Type => 'Text',
Size => 50,
MaxLength => 50,
},
},
{
Key => 'Description',
Name => 'Description',
Searchable => 1,
Input => {
Type => 'TextArea',
},
},
{
Key => 'Type',
Name => 'Type',
Searchable => 1,
Input => {
Type => 'GeneralCatalog',
Class => 'ITSM::ConfigItem::Computer::Type',
Translation => 1,
},
},
{
Key => 'Owner',
Name => 'Owner',
Searchable => 1,
Input => {
Type => 'Customer',
},
},
{
Key => 'SerialNumber',
Name => 'Serial Number',
Searchable => 1,
Input => {
Type => 'Text',
Size => 50,
MaxLength => 100,
},
},
{
Key => 'OperatingSystem',
Name => 'Operating System',
Input => {
Type => 'Text',
Size => 50,
MaxLength => 100,
},
},
{
Key => 'CPU',
Name => 'CPU',
Input => {
Type => 'Text',
Size => 50,
MaxLength => 100,
},
CountMax => 16,
},
{
Key => 'Ram',
Name => 'Ram',
Input => {
Type => 'Text',
Size => 50,
MaxLength => 100,
},
CountMax => 10,
},
{
Key => 'HardDisk',
Name => 'Hard Disk',
Input => {
Type => 'Text',
Size => 50,
MaxLength => 100,
},
CountMax => 10,
Sub => [
{
Key => 'Capacity',
Name => 'Capacity',
Input => {
Type => 'Text',
Size => 20,
MaxLength => 10,
},
},
],
},
{
Key => 'FQDN',
Name => 'FQDN',
Searchable => 1,
Input => {
Type => 'Text',
Size => 50,
MaxLength => 100,
},
},
{
Key => 'NIC',
Name => 'Network Adapter',
Input => {
Type => 'Text',
Size => 50,
MaxLength => 100,
Required => 1,
},
CountMin => 0,
CountMax => 10,
CountDefault => 1,
Sub => [
{
Key => 'IPoverDHCP',
Name => 'IP over DHCP',
Input => {
Type => 'GeneralCatalog',
Class => 'ITSM::ConfigItem::YesNo',
Translation => 1,
Required => 1,
},
},
{
Key => 'IPAddress',
Name => 'IP Address',
Searchable => 1,
Input => {
Type => 'Text',
Size => 40,
MaxLength => 40,
Required => 1,
},
CountMin => 0,
CountMax => 20,
CountDefault => 0,
},
],
},
{
Key => 'GraphicAdapter',
Name => 'Graphic Adapter',
Input => {
Type => 'Text',
Size => 50,
MaxLength => 100,
},
},
{
Key => 'OtherEquipment',
Name => 'Other Equipment',
Input => {
Type => 'TextArea',
Required => 1,
},
CountMin => 0,
CountDefault => 0,
},
{
Key => 'WarrantyExpirationDate',
Name => 'Warranty Expiration Date',
Searchable => 1,
Input => {
Type => 'Date',
},
},
{
Key => 'InstallDate',
Name => 'Install Date',
Searchable => 1,
Input => {
Type => 'Date',
Required => 1,
},
CountMin => 0,
CountDefault => 0,
},
{
Key => 'Note',
Name => 'Note',
Searchable => 1,
Input => {
Type => 'TextArea',
Required => 1,
},
CountMin => 0,
CountDefault => 0,
},
];

i think that's is the code perl integreted in the DB,but the content ?i can't find it

[yuri0001]

Content stored in xml_storage table in xml form I think. It's not transparent, but developers probably had some reason for such solutions.
Apparently, using the description they then parsed xml content. I don't know why them did it with such method.

[yacine12]

I see now
Ok thank you my friend

[davidappleby]

If you're interested, here's a MySQL view that can be run to get the information out of xml_storage for CIs (note: this only works where there are a maximum of 2 levels of data). It's slow and messy but it works:

Code: Select all

SELECT ci.configitem_number CINumber, cg.name CIType, cv.name CIName, 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 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)

Hope this is more helpful than simply suggesting you get professional support...

[yacine12]

Hi ,
Thank you
But its does'nt work ,it soo compliqued ,but what can I do with is view ? can you explain pllz
Regards,

[davidappleby]

But its does'nt work ,it soo compliqued ,but what can I do with is view ? can you explain pllz

You can use it if you copy and paste it into a MySQL console like HeidiSQL or the SQL box in the Admin area of OTRS. I know it's complicated, but you can just copy it directly.
I've saved it as a view in the database and use it to feed into a Crystal Report...

[yacine12]

Ok
I'm working with HeidiSQL but i have this syntax error :
SELECT ci.configitem_number CINumber, cg.name CIType, cv.name CIName, 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)))));
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 */
/* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 1 query: 0,000 sec. */

[davidappleby]

Hmmm, I don't know. It works fine for me (HeidiSQL 7.0.0.4053). Try it in the SQL box in the Admin area of OTRS.

I don't know if it's the same issue but I've found that sometimes you can't run code that is too long, so try splitting it over several lines.

so:

Code: Select all

- 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)))))

becomes

Code: Select all

- 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)))))

[yacine12]

Hi,
It woooorrrk,it's very important thank you very mush sure