2 Replies Latest reply on Mar 20, 2017 8:57 AM by GreggSmith

    want to create configuration Item in one datamodel

    Mayurmick Rookie

      Hi EveryOne,

       

      We are having Xtraction with integrated with CA Service Desk. Having 3 tables Call_req (Incident/Problem/Request), Issue, chg. We have created combined views for all the 3 tables i.e. INC_PROB_REQ_ISS_CHG (table name in INC_PROB_REQ_ISS_CHG) in data model editor using UNION ALL  and case functions

      There is a requirement to add configuration item

      but configuration item stored in Call_req table as affected_rc column which is referenced to other and in Change and Issue there is separate table with Configuration_item_reference table which is having both Change ticket number and Configuration item id which again referenced to configuration item.

      We are using REQUEST.AFFECTED_RC as configuration item in Incident/Problem/Request dataset and in change we are using dbo.xtraction_chg_get_configitems(CHANGE.ID) , Now I am unable to create configuration item in data type INC_PROB_REQ_ISS_CHG as field is different for both call_req and chg and Issue.

       

      I tried to create field as Related_CI and expression as

       

      CASE WHEN INC_PROB_REQ_ISS_CHG.RECORD_TYPE IN ('INCIDENT','PROBLEM','REQUEST') THEN INC_PROB_REQ_ISS_CHG.AFFECTED_RC WHEN RECORD_TYPE IS '' THEN  ELSE dbo.xtraction_chg_get_configitems(INC_PROB_REQ_ISS_CHG.ID)

       

      =================================================================

      but I am unable to provide current join and giving error as if running report 

      System.Data.SqlClient.SqlException (0x80131904): The multi-part identifier "CONFIGURATION_ITEM.RESOURCE_NAME" could not be bound.

         at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

         at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

         at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

         at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

         at System.Data.SqlClient.SqlDataReader.get_MetaData()

         at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

         at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)

         at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

         at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

         at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

         at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

         at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

         at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

         at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

         at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

         at 0.L.0(String , String , String , Int32 )

         at 0.U.0(ListComponent )

         at 0.U.GetDataXml(ListComponent component)

         at SqlDataEngine.Services.Components.ListComponentService.GetDataXml(ListComponent component)

         at Xtraction.Web.Services.SqlDataEngineWebService.GetListComponentData(ListComponent component)

      ClientConnectionId:04863eb4-3a2e-45fd-840f-c116582b29d1

      Error Number:4104,State:1,Class:16

       

      ----------------------------------------------

       

       

      Pls help to create this configuration.

       

       

      Regards

      Mayur

        • 1. Re: want to create configuration Item in one datamodel
          michael.odriscoll SupportEmployee

          Hi Mayur,

           

          Thanks for posting to the Community.

           

          Are you still experiencing an issue with your configuration? You could open a ticket with our support team for advice https://support.ivanti.com/CaseLogging.aspx

           

          Michael

          • 2. Re: want to create configuration Item in one datamodel
            GreggSmith SupportEmployee

            The "could not be bound" error is usually associated with a missing/incorrect table join.

             

            If you are still having problems, I would suggest opening a support ticket and uploading a copy of your data model so it can be reviewed.

             

            In addition, there are a few problems or questions to consider with your CASE statement.

             

            First, you need an END statement at the end of your expression.  I guess it is possible that this syntax error may result in a query that results in, what looks to SQL Server as, a table join issue.

             

            Second, your case statement references the Affected_RC field from the union query.  Did you add Affected_RC to all sections of the union query?

             

            Third, Affected_RC is a pointer to a CI.  Did you then join this mapped field to a Table object referencing CI's?

             

            Fourth, your case statement then says, if the type equals "" (blank), then use the function dbo.xtraction_chg_get_configitems.  Why would you use that function when the type is blank?  Wouldn't you use that function when the type is "Change"?

             

            Fifth, to be complete, your case statement should also check for type equals "Issue" and then call dbo.xtraction_iss_get_configitems.  However, if you don't use Issues, you can get away with skipping this.

             

            Sixth, this case statement appears to mix incompatible types of data.  For Incidents, Problems, and Requests, the case statement returns a pointer that then needs to be joined to the CI table, however the 2nd part of the statement that calls the function returns a string listing the names of related CIs.  I would suggest simply adding AFFECTED_RC/NULL as a field, use it to join to the CI table, and then use a CASE statement to return the name of the CI if the type is Incident, Problem, or Request, or call the chg_get_configitems or iss_get_configitems functions.

             

            Thanks,

            Gregg