Updating records not in a data source import via transform map

[David Carlin]

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

1. Create a list of all assets with a primary user in ServiceNow.
2. 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).
3. 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

[Andrew Barnes -]

Greetings David,

 If nothing else is touching those records on an automated basis on the same frequency of SCCM you can run an on completion of the transform script which clears out the primary user if the record was not updated in the last hour - assuming the sccm updates all the records every import. 

If that assumption isn't true, you can do an on start to just clear out all the primary user before the transform runs on SN. 

-Andrew Barnes
Join me at Developer Blog

[David Carlin]

Hey Andrew,

I was thinking about what you said, if a record has no changes then it is ignored in the transform map. The updated timestamp does not change. This would be an issue for your first recommendation.

Thinking about your second suggestion, wouldn't clearing every primary user before the transform runs cause a lot of data to be added to the audit table?

[nataliya_b · Accepted solution]

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

[David Carlin]

This is exactly what I ended up doing!

I created a field called 'Remove Primary User' and added an onStart transform script. This script checks the 'Remove Primary User' for all devices that have a primary user.

I then created an onBefore script that unchecks the 'Remove Primary User' for all assets that are being updated in the transform.

After all the rows are transformed, I have an onComplete transform script that will remove the Primary User from all assets that still have their 'Remove Primary User' checked.

Thanks!

David