Notifications

777 views

Description

  • SCCM 2016 Removed Software (AI) Data Source stops working after SCCM 2016 upgraded to version 1906.
  • The Last run datetime is not being updated by the Scheduled SCCM Import even if the Use Last Run datetime is enabled.

Release or Environment

  • SCCM 2016 version 1906 or later

Cause

  • The table structure for removed software has changed in the latest versions of SCCM 2016 and the table SCCM_Ext.INSTALLED_SOFTWARE_DATA_DD that is defined in the data source JDBC query is no longer used.

Resolution

  • The solution is to change the SQL query in the SCCM 2016 Removed Software (AI).
  • There are two similar solutions available:
    • Use the INSTALLED_SOFTWARE_HIST
      SELECT
      MachineID as ResourceID,
      InstanceKey as GroupID,
      DelDate
      FROM SCCM_Ext.INSTALLED_SOFTWARE_DATA_DD
      to the query suggested in Reddit sccm_updated_to_1906_issues_with_servicenow to match the new data structure that seems to come with SCCM 2016 version 1906:
      SELECT
      MachineID as ResourceID,
      InstanceKey as GroupID,
      TimeKey as DelDate
      FROM INSTALLED_SOFTWARE_HIST
      Fixes the issue with 0 records being returned from the JDBC query whenever the import was run.
      Since the DelDate field no longer exists in the new table, you also have to change the Last run database field from DelDate (the field in the old table) to TimeKey (the field in the new table).
      These two changes enable the queries to run properly again.
    • Alternatively, use the historical view v_HS_INSTALLED_SOFTWARE table and add the TimeStamp in the returned columns:
      SELECT
      ResourceID
      ,GroupID
      ,CONVERT(datetime, SWITCHOFFSET(TODATETIMEOFFSET(timestamp,datepart(tz,SYSDATETIMEOFFSET())),0)) as DelDate,
      TimeStamp
      from
      v_HS_INSTALLED_SOFTWARE
      WHERE AgentID IS NULL

      Please note that a Test Load needs to run in order to create the additional u_timestamp column in the staging table.

Article Information

Last Updated:2020-04-21 08:25:58
Published:2020-04-21