Re: Importing CI relationship data from excel file to SM using CIT (1022 Views)
Reply
Trusted Contributor
SanjeevDas
Posts: 388
Registered: ‎06-17-2013
Message 1 of 12 (1,101 Views)
Accepted Solution

Importing CI relationship data from excel file to SM using CIT

Hi Experts,

 

I am having trouble here, defining the mapping between Source Connector(text delimeter) and Destination Connector(Database ODBC) while importing CI relationship data using CIT.

 

The CI relationships are defined in "cirelationship" table.

The mandatory fields are logical.name, relationship.name, relationship.subtype, related.cis

 

In database, the fields logical.name, relationship.name, relationship.subtype are present in CIRELATIONSM1 table, whereas related.cis is present in CIRELATIONSA1 table.

 

The issue is while defining the mapping between the source and destination connectors i am only able to select the fields which are defined in only one table(ie. either M1 or A1). How do I join both the tables while defining the mapping to make it work?

 

Any Clue! Please help. It's Urgent.

 

Thanks and Best Regards,

Sanjeev

 

 

 

 

 

 

Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 2 of 12 (1,073 Views)

Re: Importing CI relationship data from excel file to SM using CIT

Ok, well, first, I wouldn't use the ODBC connector, if I was building it.  If you're pushing HPSM data, I'd use the Service Manager Web Service connector or the standard ServiceCenter/Service Manager connector and an import record in HPSM.

 

OR, if you've got a .csv file, unless this is something that is going to be created on a regular basis - for example, creating an integration with a third-party tool that is constant and ongoing - I'd use the Text Import Wizard in HPSM to take the contents of the .csv into the cirelationship records.

 

But, if it HAS to be ODBC, I _think_ you're going to need to do it in two parts.

 

The fields on the CIRELATIONSM1 table that are going to matter are the LOGICAL_NAME (the parent CI), the RELATIONSHIP_NAME, RELATIONSHIP_TYPE, RELATIONSHIP_SUBTYPE and CONCATENATED_NAME (the logical.name+the relationship.name).

 

In the CIRELATIONSA1 table, you've got the LOGICAL_NAME (parent CI), RELATIONSHIP_NAME, RECORD_NUMBER and RELATED_CIS.

 

Each record in the CIRELATIONSA1 references ONE row of records in the CIRELATIONSHIPM1 table.  For example, if you've got a cirelationship record like the following:

 

Logical Name: parent server

Relationship Name: components

Relationship Type: phyiscal

Relationship Subtype: Runs

Child CIs: component 1

component 2

component 3

 

You'll have three records in the CIRELATIONSA1 table to the one record in the CIRELATIONSM1 table.

 

So you'll need to build your scenario with that understanding in mind - or use a different target connector.

Trusted Contributor
SanjeevDas
Posts: 388
Registered: ‎06-17-2013
Message 3 of 12 (1,048 Views)

Re: Importing CI relationship data from excel file to SM using CIT

[ Edited ]

Hi Jacob,

 

I have created two CIT scenarios for importing the data into two tables CIRELATIONSHIPM1 and CIRELATIONSHIPA1.

 

The scenario 1 is mapped with the fields LOGICAL_NAME, RELATIONSHIP_NAME, RELATED_CI from table CIRELATIONSHIPA1.

The scenario 2 is mapped with the fields LOGICAL_NAME, RELATIONSHIP_NAME, RELATIONSHIP_SUBTYPE from table CIRELATIONSHIPM1.

 

The data got imported into the rspective tables, but the mapping didn't work out. Please check the snapshot below.

 

import_rel_ci.jpg

 

I tried with a different destination connector "Service Center/Service Manager Connector. But while mapping the source and destination I couldn't find the cirelationship table in the destination Connector. Any clue why?

So how do I import the CI relationship data using Service Center/Service Manager or Service Manager Web Services for importing data?

 

Thanks and Rgeards,

Sanjeev

Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 4 of 12 (1,036 Views)

Re: Importing CI relationship data from excel file to SM using CIT

When you populated the CIRELATIONSA1 table, you didn't populate the RECORD_NUMBER field. This field tells HPSM the order of the elements in the array when it is displayed in the client.

Trusted Contributor
SanjeevDas
Posts: 388
Registered: ‎06-17-2013
Message 5 of 12 (1,026 Views)

Re: Importing CI relationship data from excel file to SM using CIT

Hi Jacob,

 

Thanks for the prompt reply. I am a bit confused here. How is populating the RECORD_NUMBER field only in CIRELATIONSHIPA1 going to map the Upstream CIs and Downstream CIs? Please let me know.

 

Can all the Downstream Cis have the same RECORD_NUMBER?

 

Thanks,

Sanjeev

 

Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 6 of 12 (1,022 Views)

Re: Importing CI relationship data from excel file to SM using CIT

Ok, so... looks like you're not quite sure how Service Manager stores array data in alias tables.

 

So, let's do this:

 

Go in to Service Manager

 

Create (from scratch) a cirelationship record with a parent CI and 4 child CIs. 

 

Once those have been created, use something like Sql Server Managmenet Studio or TOAD or some other application that allows you to see the data in the database.

 

Search the CIRELAtiONSA1 table for that parent CI, and notice each of the child CIs.

 

For example, let's say you created a cirelationship record like the following:

 

Upstream CI: ParentServer

Relationship Name: ParentServer-firstChild

Relationship Type: Contains

Downstream CIs:

firstChild

secondChild

thirdChild

fourthChild

 

 

Now, if you look at the database side and look at those child records using a query something like this:

SELECT * FROM CIRELATIONSA1 WHERE LOGICAL_NAME='ParentServer' AND RELATIONSHIP_NAME='ParentServer-firstChild'

 

You will see four records returned, like the following:

 

LOGICAL_NAME: ParentServer

RELATIONSHIP_NAME: ParentServer-firstChild

RECORD_NUMBER: 1

RELATED_CIS: firstChild

 

LOGICAL_NAME: ParentServer

RELATIONSHIP_NAME: ParentServer-firstChild

RECORD_NUMBER: 2

RELATED_CIS: secondChild

 

LOGICAL_NAME: ParentServer

RELATIONSHIP_NAME: ParentServer-firstChild

RECORD_NUMBER: 3

RELATED_CIS: thirdChild

 

LOGICAL_NAME: ParentServer

RELATIONSHIP_NAME: ParentServer-firstChild

RECORD_NUMBER: 4

RELATED_CIS: fourthChild

 

 

When Service Manager stores data in a multi-row alias table, this is the basic structure the data takes in the database.

 

Maybe practice by starting within in the tool, and then looking at the data in the database, before you start trying to build something that bypasses the tool and adds data directly to the database.  It's always best to see how something works before you try to build it yourself.

Trusted Contributor
SanjeevDas
Posts: 388
Registered: ‎06-17-2013
Message 7 of 12 (1,009 Views)

Re: Importing CI relationship data from excel file to SM using CIT

Hi,

 

Thanks a ton.

 

Sanjeev

 

Established Member
Kent Krienke
Posts: 4
Registered: ‎11-25-2010
Message 8 of 12 (986 Views)

Re: Importing CI relationship data from excel file to SM using CIT

Hi Jacob,

 

I'm having some trouble just using the Text Import Wizard, as you suggest. Specifically, the related CIs aren't coming in as I expect them to.

 

If I have something like

 

Logical Name: parent server

Relationship Name: components

Relationship Type: phyiscal

Relationship Subtype: Runs

Child CIs: component 1

component 2

component 3

 

How do I list component 1, 2 and 3 in my csv file, and how do I define my import descriptor so that it properly interprets those as separate child CIs?

 

I have tried two approaches thus far:

 

1) Having a separate line in the csv file for each component (results in only the last one listed to be in the relationship)

2) Placing all the components in an array, formatted as {Component 1, Component 2, etc.} (results in only a single relationship being created with all components concatenated into a non-usable string)

 

Hopefully there's just something simple I'm missing. Please advise, and thanks for your time.

Trusted Contributor
SanjeevDas
Posts: 388
Registered: ‎06-17-2013
Message 9 of 12 (960 Views)

Re: Importing CI relationship data from excel file to SM using CIT

Hi Kent,

 

Create a seperate column in Excel which will store the number 1,2,3 for the Downstream CIs Component 1, Component 2, component3 respectively. The number 1,2,3 represents the position of the CIs in the Array.

 

hth,

 

Sanjeev

 

Established Member
Kent Krienke
Posts: 4
Registered: ‎11-25-2010
Message 10 of 12 (954 Views)

Re: Importing CI relationship data from excel file to SM using CIT

Thanks for the suggestion, Sanjeev.

 

I'm guessing that I would also need to update the Import Descriptor to indicate what that other column was for?

 

When I tried without updating the Import Descriptor, the relationship I ended up with was one that had "Component 3" and "3" as the Downstream CIs. From that result, I tried simply adding all of the components to the end of the same line in the csv file, and that seemed to work. From what I can see, the system is smart enough to work with an arbitrary number of Downstream CIs for each row, provided that the array is the last field defined in the Import Descriptor.

 

Is there any reason why I would not want to do it this way? Unforeseen consequences, hidden errors, etc.? Either way, thanks for the help.

Honored Contributor
Jacob Heubner
Posts: 4,177
Registered: ‎07-21-2008
Message 11 of 12 (945 Views)

Re: Importing CI relationship data from excel file to SM using CIT

So Sanjeev was using ConnectIt (I believe) and you're doing a Text Import wizard, so it's a little different.

 

In the Text Import Wizard for CI relationships, there's two ways you can approach it.

The first is to do it like you're doing it; in the Text Import wizard, add related.cis as a field name.  The system will detect that this is an array field.  Then in Fields tab, in the column labled Occurrences, you have to list the number of times you've got this column in your spreadsheet.  

 

For example, let's say you have the following in your Text Import descriptor:

Import Table: cirelationship

Import Mode: Add/Replace

Fields -

Field Name                 | Field Type | Date Format | Array Type | Occurrences

logical.name              | 2

relationship.name    | 2

relationship.type      | 2

related.cis                  | 8                  |                         | 2                     | 4

 

And in your .csv, you had something like this:

logical.name, relationship.name, relationship.type, related.cis, related.cis, related.cis, related.cis

ParentServer,ParentServer-FirstChild,logical, FirstChild, SecondChild, ThirdChild, FourthChild

 

The system would then accept the 4 occurrences of the related.cis column and treat it like the first 4 elements in your related.cis array in the tool.

 

The downside to doing it this way is you have to know at the start the highest number of elements in your array, and configure your Text Import descriptor and your .csv file accordingly.

 

The other way to approach it is to create some custom code that runs in the formatctrl you call out in your Import Descriptor, where you make it so that the system automatically appends to the related.cis, rather than overwrites the related.cis.

 

For example, your import descriptor would look like:

logical.name              | 2

relationship.name    | 2

relationship.type      | 2

related.cis                  | 8                  |                         | 2                     | 1

 

 

And your .csv would end up looking like:

logical.name, relationship.name, relationship.type, related.cis

ParentServer,ParentServer-FirstChild,logical, FirstChild

ParentServer,ParentServer-FirstChild,logical, SecondChild

ParentServer,ParentServer-FirstChild,logical, ThirdChild

ParentServer,ParentServer-FirstChild,logical, FourthChild

 

And in your formatctrl you'd have something like:

Add: true
Update: true

Calculation: related.cis in $file=nullsub(related.cis in $file0,{})+related.cis in $file

 

Then in the Advanced tab, have the following:

logical.name=logical.name in $L.db and relationship.name=relationship.name in $L.db

 

That will perform a query when the import runs to update the CIs, not just add them.

Established Member
Kent Krienke
Posts: 4
Registered: ‎11-25-2010
Message 12 of 12 (942 Views)

Re: Importing CI relationship data from excel file to SM using CIT

Thanks so much for the detailed and informative explanation, Jacob. Greatly appreciated. I have given a kudos in appreciation.

 

I think for the specific purposes I'm working with (a one-time import as opposed to a regular load/update), simply specifying the correct number of occurrences will work best. That being said, the formatctrl alternative makes sense too, and I'll keep it in mind if we move to a more regular load.

 

Thanks again for your help!

 

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.