Updating records not in a data source import via transform map

David Carlin · ‎01-15-2020

Hello,

I have been trying to figure out how to accomplish something for sometime and I was wondering if anyone could give some input.

We have a data source connection with SCCM that runs on a schedule nightly. The query in the data source queries all computers that have a primary user. During the transform process we are coalescing the 'Serial Number' field from SCCM and updating the primary user of an asset on our alm_hardware asset table. This was pretty straight forward.

The issue arose when we realized that the 'Primary User' in SCCM could change to empty and the computer would have no primary user. When this happened, the computer would fall out of the query (in the data source) since the query was only looking for machines with primary users. This meant that assets in ServiceNow were not having their primary user field updated back to empty.

If you are confused on the above portion, ultimately, assets in ServiceNow are not clearing their primary user field if the primary user drops off in SCCM.

I am looking for a way to do some pre and post processing of assets as part of the transform map. My thought was I would

Create a list of all assets with a primary user in ServiceNow.
Run through the transform map and remove the asset from the list if it was contained in the import. This would leave all the assets that had a primary user in ServiceNow but not in SCCM (since it wasn't in the query).
After the map is done, update all the remaining assets in the list and clear their primary user field.

My issue is I don't know how to do this or even if it is the best approach.

Can anyone lend some help?

Thanks!
David

nataliya_b · ‎01-17-2020

have you thought of adding flag to the target table (primary user - check/uncheck)

before import, put flag to uncheck for all existing records.

in transform map have script: checking if incoming record has matching record in target -- check the flag on target.

after import completed, clean up records with flag -> uncheck, means empty prior users

View solution in original post

nataliya_b · ‎01-15-2020

second approach could be:

update SQL in datasource and bring all records, including where primary users are empty

check "copy empty fields" in transform map and do the test

David Carlin · ‎01-15-2020

The issue with this is that the primary user is a join and we are unable to modify the query to include empty computers.