SQL query to extract the CI user attributes from SOI

Products

• CA Service Operations Insight (SOI)

Issue/Introduction

Need help with the query to extract the CI user attributes from SOI

Environment

Release : 4.2

Component : Service Operations Insight (SOI) Manager

Resolution

The CIUserAttribute (there are 1-10 attributes for using) values can be set using a connector policy or it can also be set if the CI is in a service. The CIUserAttribute values are stored in CI or CIStaging table depending on how and where it is set from. Here are the scenario's that decides which table the values are set to:

- If this CIUserAttribute value is set from the connector then the value is stored in CIStaging table in one of UserDefinedcol1 - UserDefinedcol10 columns.

Note: CIUserAttribute1 - CIUserAttribute10 corresponds to UserDefinedcol1 - UserDefinedcol10 columns in CI and CIStaging tables.

- If the CIUserAttribute value is set on the CI manually from SOI console then it is saved in CI table in one of the UserDefinedcol1 - UserDefinedcol10 columns.

- If the CIUserAttribute value is set from the connector and later that CI is added to a service or the CI is already in a service then the value will added to both CI and CIStaging table in one of the UserDefinedcol1 - UserDefinedcol10 columns.

Here is an example of a SQl query you can run to get the value of an specific CIUserAttribute
select * from CIStaging where UserDefinedcol1='The value'
select * from CI where UserDefinedcol1='The value'