Initial sync issue in most of the records

Issue Details: Customer has reported while syncing records from HSDES to JIRA in action part it shows ‘Unsuccessful post request’ for many records that has been started from a defined time period. 

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

Customer Email Conversations:

Subject: Re: Bridge129 - Initial sync issue in most of the records

Hi Team, 

The below item is working as expected. 

Steps taken to flow all the records again: 

1. List of records were found from database which got error between the span of '' to '' for that particular bridge

Below is the query to fetch the data: 

select b.entityname,b.entityid,b.status,a.status,b.eventgenerationtime,* from (select * from tklfactionlog ta where toolinstanceid = '5484' and status <> 'SUCCESS') a inner join 
(select * from tklfeventlog te  where toolinstanceid = '5483' and eventgenerationtime > '2024-08-29 00:00:00' and eventgenerationtime < '2024-09-02 00:00:00' and status = 'Processed')
b on a.eventid=b.EventId where b.entityname = 'integration_step_event' order by b.eventgenerationtime

2. Respective record Ids were jotted in the required file format for csv import in reflow.

First all the set of 'product_req' records were reflowed followed by the 'intergration_step_event' records.

As confirmed by @Arunachalanathan, GokulaX Nandhini there are few records that did not flow due to condition mismatch, rest all of the records are flown successfully.

Thanks & Regards,

Pushpita Sengupta

---

Hi Pushpita,

As discussed, production Bridge129 parentid scenario bridge is not syncing most of the records from HSD to Jira and throws error in Jira as ‘Unsuccessful post request’

And the same the reflowed with few records, it got success.

Is that possible to repeat the flow from the initial sync for all the records?

Thanks

Jayaprabha Dhanushkodi

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

Solution Provided: 

Step 1: Filter out data to find out the ToolInstanceID of the records that are to be re-flown. 

• To filter out the data we need toolinstanceid of the registered Target tool as the error occurred in the action part. This includes the steps given below: 

             1. Visit the project registration from Kovair Application UI --> Navigate to Project registration                             and Identify Project Name from default project selection section of the target tool.

             2. Now Identify the correct adapter database and run the below query to fetch SubsriberID and                         signature by project name of the target tool.

                 select * from RegisteredSubscribers where ProjectName='JIRA Project Name'

             3. Copy the subscriber signature and visit the Kovair database and run the below given query to                       get the kovair projectid.

                 select * from tklftoolregistrationdetails where paramvalue='subscriber signature'

             4. Use the kovair project id in below query 

                 select * from tprojects where projectid='kovair project id' to check whether workspace is same                   or not from database.

             5. Use the kovair project id in the below query to fetch and identify tool instance details hosted                         under that workspace.

                 select ToolInstanceId,* from tklfeventlog where KovairProjectID='KovairProjectID'

Step 2: Filter out data to find out the entity-id of the records that are to be re-flown. 

• After we have Identified the target tool instance ID let us run the below query to identify the successful actions from tklfactionlog table.

           select * from tklfactionlog ta where toolinstanceid = 'provide the ID' and status <> 'SUCCESS'

• Next step is to identify the events respective to the actions occurred and get the event side toolinstanceid by using action table eventid.

For example:

           select ToolInstanceId,* from tklfeventlog where EventId in ('272608','269576','269932','269587')

• Now apply the action side toolinstanceid and event side toolinstanceid in below query to get the required output (EntityID) from tklfeventlog table.
• In this case Action Side ToolInstanceID = 5484 and event side toolinstanceid = 5483.

Query: 

select b.entityname,b.entityid,b.status,a.status,b.eventgenerationtime,* from

(select * from tklfactionlog ta where toolinstanceid = '5484' and status <> 'SUCCESS') a inner join 
(select * from tklfeventlog te  where toolinstanceid = '5483' and eventgenerationtime > '2024-08-29 00:00:00' and eventgenerationtime < '2024-09-02 00:00:00' and status = 'Processed')

b on a.eventid=b.EventId where b.entityname = 'integration_step_event' order by b.eventgenerationtime

This query involves selecting data from two tables, tklfactionlog (aliased as ta) and tklfeventlog (aliased as te), based on certain conditions. Here's a step-by-step breakdown of the query:

A. Subquery 1: Selecting from tklfactionlog (aliased as ta)

• Purpose: This subquery retrieves data from the tklfactionlog table where:
  • toolinstanceid = '5484' (retrieves records for a specific tool instance)
  • status <> 'SUCCESS' (only records with statuses that are not "SUCCESS" are included)
• This is a basic filter, and all columns of the filtered result will be used in the final output (because of select *).
• This subquery is aliased as a.

B. Subquery 2: Selecting from tklfeventlog (aliased as te)

• Purpose: This subquery retrieves data from the tklfeventlog table where:
  • toolinstanceid = '5483' (retrieves records for a different tool instance compared to the first subquery)
  • eventgenerationtime > '2024-08-29 00:00:00' (selects records generated after this date)
  • eventgenerationtime < '2024-09-02 00:00:00' (selects records generated before this date)
  • status = 'Processed' (only selects records where the status is "Processed")
• This subquery is aliased as b.

C. Inner Join between a and b

• Purpose: The two subqueries are joined on the condition that a.eventid = b.EventId. This ensures that only records where the eventid in the tklfactionlog table matches the EventId in the tklfeventlog table will be included.
• This is an INNER JOIN, meaning only records that exist in both subqueries will be selected.

D. Filtering by entityname

• Condition: After the join, the result set is filtered further to include only records where b.entityname = 'integration_step_event'.

E. Ordering by eventgenerationtime

• Purpose: The final result is ordered by b.eventgenerationtime, i.e., the records are sorted in ascending order based on the eventgenerationtime field from the tklfeventlog table.