Saviynt Forums

igaravi · ‎12/09/2022

We are using a DB connector to import all Accounts along with associated Servers (list). The source table has multiple rows for the same User, each with an associated Server. What's the best way to ingest this data? Read the above table fully as is in the account_import xml and just extract the computer names in the Entitlement_import xml?

sk · ‎12/09/2022

Not sure about the your target for which you are using DB connector and their use cases.

Without that we may not be suggesting solution properly.

But with the information you shared and based on my understanding out of it, I assume you are trying to pull accounts and their associated servers each account has.

In that case I will treat servers as entitlement type for that target and create that as entitlementtype

1. And as part of accounts import i will bring accounts along with their server association and map server to entitlementvalue.

2. As part of entitlements import I will bring all servers available on target

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

rushikeshvartak · ‎12/09/2022

Both
Account - import will map account to server but if server does not exists status will be null

entitlements - you should distinct server status and metadata

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

igaravi · ‎12/13/2022

Please expand on "entitlements - you should distinct server status and metadata".

I think I might be doing this by setting server status in the below EntitlementValuesImport DB parameter and using a DB EntitlementValueImport Job. The SQL runs fine on the console, but job fails with -

No signature of method: java.lang.Integer.replace() is applicable for argument types: (java.lang.String, java.lang.String) values: [\, \\]

This is a simple mapping, not sure why it fails.

sk · ‎12/13/2022

Error is because of one of the column from target is of Integer type but you mapped it to string column hence facing the issue. Identify the column which of integer type and convert that to string.

I suspect ComputerID column is of integer type on target if so then use like this

CAST (ComputerID as CHAR) as cid

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

igaravi · ‎12/13/2022

Appreciate the quick response.

I did that and the job created 4 entitlement types but not the entitlement values. It failed with error "Column count doesn't match value count at row 1".

I checked my XML with the one in the DB Connector guide and looks like I have specified the needed attributes; not sure if any other is mandatory -

rushikeshvartak · ‎12/13/2022

Share output of query

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

rushikeshvartak · ‎12/13/2022

<dataMapping>
	<sql-query description="This is the Source Database Query">
		<![CDATA[SELECT CAST (ComputerID as CHAR) as entID, [Computer Name] as entitlementvalue, OS as cp3,1 as status, [Asset Type] as entitlementtype, 'Dev_Asset' as applicationname, 'Dev_Asset' as endpointname FROM ASSETS WHERE LTRIM([Computer Name]) LIKE 'ABQ-P%']]>
	</sql-query>
	<mapper description="BigFix Asset Import" deleteentitlementowner="true" entnotpresentaction="noaction" createentitlementtype="true">
		<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="applicationname" type="character"/>
		<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>
		<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
		<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="entitlementvalue" type="character"/>
		<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="entID" type="character"/>
		<mapfield saviyntproperty="entitlementvalues.customproperty3" sourceproperty="cp3" type="character"/>
		<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"/>
	</mapper>
</dataMapping>

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

sk · ‎12/14/2022

Try changing the column names to match what is mentioned in document. Also remove OS column for now as testing purpose because you trying to map it to CP attribute, I just want to skip that for now and see if that works. Make the changes like the below

SELECT CAST (ComputerID as CHAR) as entID, [Computer Name] as entitlementvalue,
1 as status, [Asset Type] as entitlementtype, 'Dev_Asset' as applicationname, 'Dev_Asset' as endpointname
FROM ASSETS
WHERE LTRIM([Computer Name]) LIKE 'ABQ-P%'

And mapping as below

<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="entID" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="entitlementvalue" type="character"/>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="Number"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="applicationname" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/>

Also if possible can you share the output of the query from target with masked values if you have any customer sensitive data

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

igaravi · ‎12/14/2022

Here is a sample output from the SQL console -

Cid	Name	os	ostyp	Ctyp	mtyp	vnum	sorc	Statuskey	enttyp	Securitysystem	Endpoint
110	ABQ-P1	Win11 11.0 (2021)	Workstation	Physical	False	11	BigFix	1	Laptop	Dev_Asset	Dev_Asset
167	ABQ-P2	Win11 11.0 (2021)	Workstation	Physical	False	11	BigFix	1	Desktop	Dev_Asset	Dev_Asset
271	ABQ-P3	Win11 11.0 (2021)	Workstation	Physical	False	11	BigFix	1	Desktop	Dev_Asset	Dev_Asset

Here is the new xml being used. I ran the import with this new XML but still getting the same error - Column count doesn't match value count at row 1. It also says 10 for "Total Entitlement Values Updated" but I don't find these assets when I look in Entitlements.

sk · ‎12/14/2022

How many entitlements you are expecting as output from the query?
Column count doesn't match value count at row 1 - This error means that all columns are not being passed as expected. Are you sure that all mapped columns having mentioned in select statement and have data?
It also says 10 for "Total Entitlement Values Updated" but I don't find these assets when I look in Entitlements. -- Where are you checking for entitlements, Under endpoint entitlements tab or directly searching from entitlement list? If you are checking under entitlements tab of endpoint then can you please do search with "ABQ-P1" in entitlements list ?

Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

igaravi · ‎12/14/2022

The Job is success now and I can see the Entitlements created in the endpoint. I Used the xml import file from rushikeshvartak, added few more CP as well. I am now onto importing accounts listed on these assets.

Saviynt Forums

Importing computer asset object with all its users