Service Desk Practitioners Forum
cancel

Changing Search Codes of Multiple CIs through D.E.

SOLVED
Go to solution
Highlighted
Ryan Chamberlin
Valued Contributor.

Changing Search Codes of Multiple CIs through D.E.

We are going through a mass audit of our hardware data, and are going to be fixing some mistakes when we initially went live with OVSD. I already have the process in place to export the data into Excel for updating multiple CIs in a given category.

The problem I'm running into is if we're going to be changing the search code for a CI. When we first implemented OVSD, we used information that could/likely will change over the life of the CI. Obviously this is bad, and it's something we're fixing.

Anyway, when I re-import the data back using data exchange, if the search code has changed, it will just create a new CI instead of updating the old one. I understand that this is the way it should work, so...

I tried including the ID of the CI thinking maybe it'd update the CI based on that. When I try I get a java.lang.NumberFormatException error. I'm not sure why, because the database shows it as a 18-digit number field. I'm just passing it back what it gave me.

Any ideas as to why this may be happening and what I can try to get around it?

Thanks in advance!

I've attached the error log to this post.
6 REPLIES
George M. Meneg
Acclaimed Contributor.

Re: Changing Search Codes of Multiple CIs through D.E.

Hello Ryan,

You can alter searchcode during Data Exchange, provided that you are going to use another field as unique key.

Configuration Item ID is a very good choice (and very fast two, since it's faster for the DB to compare two numbers than compare two strings.

However there is a constrain: At the excel file, the column that contains CIs' ID must NOT be named ID. Name the column CI_ID (as the name ID is reserved by the D.E process).

Secondly, make sure that in the Excel, the ID is stored as text. This is because the ODBC Excel driver tends to add ".00" to numbers so you are going to have failure. The best way to do this is either to add the character ' in front of each ID.

menes fhtagn
Ryan Chamberlin
Valued Contributor.

Re: Changing Search Codes of Multiple CIs through D.E.

George,

Thanks for the quick reply!

I've tried what you suggested, and it stops me from getting the NumberFormatException.

However, now I get a new error in the DE progress window:

warning: Program error: the attribute ' id.' of this configuration item cannot be modified, because the configuration item item was opened in view mode or because the attribute is read-only.

while processing attribute CI_ID

And then it creates a NEW CI.

Any suggestions?

Thank you again!
George M. Meneg
Acclaimed Contributor.

Re: Changing Search Codes of Multiple CIs through D.E.

Hello Ryan,

My mistake. CI ID is a very good choice *ONLY* when you want to update CIs through D.E. If you try to Import CIs, ID *cannot* be used, because the ID is auto assigned by Service Desk.

However there is a quick work around.

1. Update ALL cis in OVSD to copy ID to Source ID field. One quick way to do this is to create a db rule, that when a boolean is modified, to set Source ID to ID.

2. Modify your import mapping to add "source id" and use this as unique key. Do not use ID in this import mapping.

Instead of Source ID you can use any custom string, or even custom number field (provided that the IDs is relatively low), but this field "Source ID" sole purpose is to used as unique key in import mappings.
menes fhtagn
Ryan Chamberlin
Valued Contributor.

Re: Changing Search Codes of Multiple CIs through D.E.

George,

Thank you for the suggestion. I will give that a try.

I do have a question though. What is the difference between importing CIs through data exchange, and updating CIs through data exchange?

As an example, I have a server in the DB. I want to update the IP address of it. I have the new information in and Excel spreadsheet. When I run the Data Exchange task, it will UPDATE the record with the new IP based (I think) on the search code.
Ryan Chamberlin
Valued Contributor.

Re: Changing Search Codes of Multiple CIs through D.E.

OK I think I figured it out.

I had both the search code and ID set up as primary keys. When I unchecked key binding on the search code it went through with no problems. I guess that's where I misunderstood what I was doing.

Thanks for the help!
George M. Meneg
Acclaimed Contributor.
Solution

Re: Changing Search Codes of Multiple CIs through D.E.

Hello Ryan,

Actually a D.E. works two ways: Existing CIs are updated and news CIs will be created. What will be updated and what will be created depends on the field that is defined as primary key in the import mapping.

This works like this: The import process reads the first CI from the external source. It uses the field that is defined as primary key in the import mapping to perform a lookup on OVSD database, in order to determine whether the CI already exists in OVSD in order to update it. If it doesn't find the CI in the database it will create a new.

Therefore if you want to update the Searchcode in existing CIs in the OVSD database, you must treat "Searchcode" as a normal field and use another field as "primary key". CI ID is a very good candidate providing that *EVERYTHING* in the source already exists in OVSD Database. If not, the import process will try to create a new CI in the DB with the given CI ID. However, since ID is AUTO assigned by OVSD the process will fail.
menes fhtagn