How we can search in the xml_storage using query

[williamrobert1983]

Hi all,

In the CMDB, i have added serial number for the devices under the Computer Item. I wanted to find the particular serial no in the xml_storage table.

Please advice me how to get it using the query.

thanks.
William

[Lamaman]

Hi,

I do not know why you are not using the normal search option in OTRS?
But here is your query:

SELECT *
FROM xml_storage
WHERE xml_content_value ="Put_your_serial_here"

[williamrobert1983]

Hi,

i have added 10 to 20 configuration details in the CMDB. I can edit and update the some values and its not a issue.

But, I wanted to update the xml_content_value directly in the xml_storage table using xml_key . But, it will not reflect into the CMDB config item screen. How we can update the value instead of update the value using AgentITSMConfigItemEdit page in the CMDB. .

Query Exmaple :
update xml_storage set xml_content_value='testing1234@mail.com' where xml_content_key = '[1]{''Version''}[1]{''YESID''}[1]{''Content''}' and xml_key=1037;

so the value has been updated using xml_content_key and xml_key. but, the updated values its not showing the AgentITSMConfigItemZoom.

Please advise me.

[jojo]

Never write directly in the database!

[williamrobert1983]

Hi,

Thanks for your suggestion. Actually, i am using a script to update the values. I will explain with some example.

Deployment State in CMDB
1. Available
2. Used

Ex: Config Item in CMDB - [ Class : Software ]

Name : Test CI
Deployment State : Available
Incident State : Operational
Vendor : Test Vendor
Version : 1.2.3
Description : This is a test CI
Type : Admin Tool
Serial Number : YES0192828
Email ID :

Our client will send the Serial number and email id using [ URL Query Parameters to our .pl script ] and check the CMDB if status is Available or Used. If the serial number is Available, then update the status from Available to Used as well as update the Email id. the .pl script which can get the serial number and email id from the client request and check in CMDB then return the status whether it is Available or Used and i can update the serial number (ID) for the deployment state.

But, i wanted to try update the Email id and i could update the email in xml_content_value in xml_storage table. But, In the CMDB config item page still showing empty values. Is it any other way to update values and advise me. I know that we don't have any web service for the CMDB.

Thanks lot !

William

[Lamaman]

We also regarding an option to update data (IP addresses and Hostnames).
Our idea was to export our DNS data and import this data via the ImportExport cron script.
(updating via SQL give us the same experience you had: database OK, GUI NOK)

We are still experimenting...
I do not know if this is the way to go for you (or for us).

[williamrobert1983]

Yes. I am able to update the values in database and i can view the updated value in the screen.
whenever we do something in db directly with config items table, We must run the ./otrs.DeleteCache.pl command.


William