and more in a single search tool across platforms. Read the announcement here. |
12/09/2022 01:07 PM
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?
Solved! Go to Solution.
12/09/2022 01:57 PM
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
12/09/2022 01:58 PM
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
12/13/2022 02:18 PM
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.
<dataMapping>
<sql-query description="This is the Source DB Query" >
<![CDATA[
SELECT ComputerID as cid, [Computer Name] as cname, OS as os,
1 as status, 'Workstation' as enttyp, 'Dev_Asset' as securitysystem, 'Dev_Asset' as endpoint
FROM ASSETS
WHERE LTRIM([Computer Name]) LIKE 'ABQ-P%'
]]>
</sql-query>
<mapper description="BigFix Asset Import" entnotpresentaction="noaction" createentitlementtype="true">
<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="cid" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="cname" type="character"/>
<mapfield saviyntproperty="entitlementvalues.customproperty3" sourceproperty="os" type="character"/>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="Number"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="enttyp" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
</mapper>
</dataMapping>
12/13/2022 02:43 PM
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
12/13/2022 04:34 PM
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 -
<dataMapping>
<sql-query description="This is the Source DB Query" >
<![CDATA[
SELECT CAST (ComputerID as CHAR) as cid, [Computer Name] as cname, OS as os,
1 as status, [Asset Type] as enttyp, 'Dev_Asset' as securitysystem, 'Dev_Asset' as endpoint
FROM ASSETS
WHERE LTRIM([Computer Name]) LIKE 'ABQ-P%'
]]>
</sql-query>
<mapper description="BigFix Asset Import" entnotpresentaction="noaction" createentitlementtype="true">
<mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="cid" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="cname" type="character"/>
<mapfield saviyntproperty="entitlementvalues.customproperty3" sourceproperty="os" type="character"/>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="Number"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="enttyp" type="character"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="securitysystem" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpoint" type="character"/>
</mapper>
</dataMapping>
12/13/2022 05:48 PM
Share output of query
12/13/2022 07:24 PM
<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>
12/14/2022 06:10 AM
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
12/14/2022 07:33 AM
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.
<dataMapping>
<sql-query description="This is the Source DB Query" >
<![CDATA[
SELECT CAST(ComputerID as CHAR) as entID, [Computer Name] as entitlementvalue, 1 as status, [Device 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" 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.status" sourceproperty="status" type="Number"/>
</mapper>
</dataMapping>
12/14/2022 08:15 AM
12/14/2022 12:04 PM
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.
<dataMapping>
<sql-query description="This is the Source DB Query" >
<![CDATA[
SELECT CAST (ComputerID as CHAR) as entID, [Computer Name] as entitlementvalue, OS as cp31, [OS Type] as cp32, 1 as status, [Device Type] as entitlementtype, 'Dev_Asset' as applicationname, 'Dev_Asset' as endpointname FROM ASSETS WHERE LTRIM([Computer Name]) LIKE 'IDS%']]>
</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.customproperty31" sourceproperty="cp31" type="character"/>
<mapfield saviyntproperty="entitlementvalues.customproperty32" sourceproperty="cp32" type="character"/>
<mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"/>
</mapper>
</dataMapping>