Update multiple values using business adapter

[ImIronMan]

Hi Team

In my source data, I have one inventory device name  Device1 and its cost center is 0000/000. There are no other details like a domain name or serial number , etc..,

In my target data i.e., FNMS, I can see 3 entries of the same above device which are active and valid. (customer is aware of this and has a business justification as well),

Now I need to update cost centers for all the matching device names in FNMS irrespective of the duplicates or status or serial number etc., 

When I run the business adapter,it is just updating only the first record in FNMS, however skipping the other 2 records even when all the 3 hostnames exactly match with the hostname from the source.

Is there any setting in the business adapter to ensure the update happens for all the repeated entries as well in FNMS

@mfranz @kclausen @erwinlindemann 

[ChrisG · Accepted solution]

The built-in mapping that business adapters do will strictly map each source record to only 1 target record in FlexNet Manager Suite. If multiple target records in FlexNet Manager Suite are matched according to matching properties, then one of them is selected at random as the target to be updated.

If you are using FlexNet Manager Suite On-premises then you could use a custom object query in your adapter to update data. For example:

UPDATE cc
SET CostCenterID = i.CostCenter_ID
FROM #ECMImport_YourImportName i
  JOIN dbo.ComplianceComputer cc ON cc.ComputerName = i.DeviceName

 I can't think of any way to achieve a similar outcome when using a business adapter running in FlexNet Manager Suite Cloud/Flexera One ITAM. In that situation it is important to find a property (or multiple properties) that will uniquely identify individual records.

[mfranz · Accepted solution]

You could split the complete SQL query into smaller sub-tasks.

• One for creating new data (e.g. where old.CostCenterID is null & new.CostCenterIS is not null)
• One for updating existing data (e.g. where old.CostCenterID is not null & new.CostCenterIS is not null)
• One for deleting existing data (e.g. where old.CostCenterID is not null & new.CostCenterID is null)

You could split your Business Import into 3 separate steps and run specifically filtered SQL statements. This way you could log x created, y updated, z deleted...

Best regards,

Markward

[ChrisG · Accepted solution]

The built-in mapping that business adapters do will strictly map each source record to only 1 target record in FlexNet Manager Suite. If multiple target records in FlexNet Manager Suite are matched according to matching properties, then one of them is selected at random as the target to be updated.

If you are using FlexNet Manager Suite On-premises then you could use a custom object query in your adapter to update data. For example:

UPDATE cc
SET CostCenterID = i.CostCenter_ID
FROM #ECMImport_YourImportName i
  JOIN dbo.ComplianceComputer cc ON cc.ComputerName = i.DeviceName

 I can't think of any way to achieve a similar outcome when using a business adapter running in FlexNet Manager Suite Cloud/Flexera One ITAM. In that situation it is important to find a property (or multiple properties) that will uniquely identify individual records.

[ImIronMan]

Thanks @ChrisG  @mfranz  for your quick replies.

The custom query worked fine for me. However, it is not displaying how many records got created/updated/deleted/rejected

Is there any query that we can add to the custom query which can display these update counts to a text file as a log ? This would solve my purpose completely

[mfranz · Accepted solution]

You could split the complete SQL query into smaller sub-tasks.

• One for creating new data (e.g. where old.CostCenterID is null & new.CostCenterIS is not null)
• One for updating existing data (e.g. where old.CostCenterID is not null & new.CostCenterIS is not null)
• One for deleting existing data (e.g. where old.CostCenterID is not null & new.CostCenterID is null)

You could split your Business Import into 3 separate steps and run specifically filtered SQL statements. This way you could log x created, y updated, z deleted...

Best regards,

Markward

[ChrisG · Accepted solution]

The summary counts that appear in business adapter logs are taken from records in the dbo.BusinessImportLogObject view. If you add record(s) to this view in your custom SQL statements with appropriate counts then I expect you will see that information appearing in the logging.

Here's a somewhat simplified example (using some fixed values for illustration):

INSERT dbo.BusinessImportLogObject(
	ImportID, ObjectName, ObjectType,
	StartDate, EndDate, Status,
	Processed, Rejected
)
VALUES (
	[LOG_IMPORT_ID], 'My custom import step', NULL,
	GETDATE(), GETDATE(), 1 /* completed */,
	73, 42
)

The special [LOG_IMPORT_ID] reference can be used in a custom object query in a business adapter - this text will be replaced at runtime with an integer that is the value required for the BusinessImportLogObject.ImportID column representing the currently running import.

[mfranz]

Hi,

Yes, when matching criteria are identical, the Business Adapter logic will pick one of the items (and you cannot exactly control, which one it picks). This regularly happens when only matching on computer names only.

There was a discussion just recently, providing a potential solution: https://community.flexera.com/t5/FlexNet-Manager-Forum/Business-Adapter-Studio-matching-with-ComplianceComputerID/td-p/139031

Alternatively, you could just use a custom query element in your Business Adapter to just run a SQL update statement.

Best regards,

Markward