As long as the data is being inserted correctly into the import set table (sn_sccm_integrate_sccm_2019_computer_id) your sql query is correct. In this case both the OOTB and your customization are aliasing the actual column name as username so the data will end up in the u_username column in the import set table. As long as the data is landing in the same column you shouldn't have to make any further modifications.
As to why the data isn't being reflected in the CMDB let's first take a look at the flow through the RTE (I'm looking at the latest version of SCCM FYI):
The u_username field is being passed to the Set assigned_to operation which is a script operation:
Here we can see a couple of things.
1) It is reading the property glide.discovery.assigned_user_match_field to find what field on the sys_user table to match against. By default it is user_name.
2) It is doing some parsing of the username from SCCM to strip out the username from the input value (removing the part prior to a \\ or /)
From this you may want to
One way we can get a better understanding is by looking at the IntegrationHub-ETL to see if the assigned_to field has a sys_id. If it does that means that we are correctly matching.
If you are seeing a sys_id in the IntegrationHub-ETL that means we are matching correctly and something else is happening to prevent an update.
I would look into the sys_object_source table for the target CI. Oftentimes the data you are running is not newer than pervious data so the IRE will ignore it (even if the mapping has changed). This is determined by the last scan date on the sys_object_source table. To remediate this, you could delete records in the sys_object_source table where the target sys id is the computer you are expecting changes on.
As to what will happen if you uncheck 'use last run datetime' field. This is used to window the sql query. It will add a where clause to filter to only data that has been refreshed since the last run. By unchecking it you will return all data from SCCM. This is mainly used to prevent reprocessing stale data.
Hi and thank you!! So now I am in unexplored territory. 🙂
Within IntegrationHub / templates. I searched sys and the only sysID I see is connectionSysId. There was some about retry policies...
Am I in the right place?
_____________________________________________________
Re: sys_object_source
Oh my I have over 3 million records there on Target table name contains 'computer'. I have over 3,000 on this one example record 16779628. I included a screenshot with dates from each one of the tables. Is this a case where deleting it from sys-object_source would correct it?
What would be ramifications if I deleted all these millions of records?
Why do we need all these records in there?
_______________________________________________________
Re: use last run datetime
If I removed this checkmark will it
Cause duplicates?
Update with all the changes I made in the query (username)?
thanks a lot!
Am I in the right place?
You are not in the correct place. It is a store application called the IntegrationHub ETL. This is a UI tool used to see the mappings and modify Service Graph connectors. Once it is installed it will appear in the left navigation as IntegrationHub ETL.
Is this a case where deleting it from sys-object_source would correct it?
Deleting records in the sys_object_source table will just force the integration to go through the IRE rules again instead of shortcutting them by going directly to the target sys id from the source native key. I would recommend deleting records targeting a specific target sys id to limit the scope for debugging.
What would be ramifications if I deleted all these millions of records?
As mentioned above it will force the integration to go through the IRE rules instead of the target sys id lookup.
Why do we need all these records in there?
It improves performance by reducing the number of IRE calls. If you have already identified a CI for a given SCCM record there isn't much value in identifying it every time the integration is run. Also if the last scan isn't newer there presumably isn't any new data and the CI won't need to be updated.
If I removed this checkmark will it cause duplicates?
No, it will just fetch all data from SCCM instead of an incremental amount of data where the records have been updated. It will cause significant performance degradation as you will be fetching all records instead of only those changed.
Update with all the changes I made in the query (username)?
I'm not sure what this question is refering to.
Here is a screenshot of the IntegrationHub ETL preview data where you can see the username and the assigned_to sys id
