SCCM and ServiceNow

[nataliya_b]

Hello All

Our SCCM got updated to 1906, that made our ServiceNow Remove Software import stopped working (plugin SCCM 2012 v2 activated)

This SQL query does not bring any records. 

SELECT * FROM

(SELECT

MachineID as ResourceID,
InstanceKey + 32000000 as GroupID,
DelDate

FROM SCCM_Ext.Add_Remove_Programs_DATA_DD

UNION

SELECT
MachineID as ResourceID,
InstanceKey +64000000 as GroupID,
DelDate

FROM SCCM_Ext.Add_Remove_Programs_64_DATA_DD)t

We tried to use following query, but data validation results are not consistent. 

SELECT * FROM

(SELECT

MachineID as ResourceID,
InstanceKey + 32000000 as GroupID,
TimeKey as DelDate

FROM dbo.Add_Remove_Programs_DATA

UNION

SELECT
MachineID as ResourceID,
InstanceKey +64000000 as GroupID,
TimeKey as DelDate

FROM dbo.Add_Remove_Programs_64_DATA)t

Does anyone have similar issue? Are we using correct tables in new query? 

Please help.

[Frank1975]

Sure, pretty much out of the box, issue is that 

SCCM_Ext.Add_Remove_Programs_DATA_DD and 

SCCM_Ext.Add_Remove_Programs_64_DATA_DD

does no longer have any data in SCCM , since there is no triggers available which can be activated to feed these tables in SCCM.

here is the SQL Statement

SELECT * FROM

(SELECT

MachineID as ResourceID,
InstanceKey + 32000000 as GroupID,
DelDate

FROM SCCM_Ext.Add_Remove_Programs_DATA_DD

UNION

SELECT
MachineID as ResourceID,
InstanceKey +64000000 as GroupID,
DelDate

FROM SCCM_Ext.Add_Remove_Programs_64_DATA_DD)t

[Community Alums]

Hi Frank,

I'm no SQL or SCCM expert but give this a try?

SELECT * FROM

(
SELECT

ResourceID,
GroupID + 32000000 as GroupID,
TimeStamp as DelDate

FROM v_GS_ADD_REMOVE_PROGRAMS

UNION
SELECT
ResourceID,
GroupID + 64000000 as GroupID,
TimeStamp as DelDate

FROM v_GS_ADD_REMOVE_PROGRAMS_64 )t

Here's a full ConfigMgr Database Views that also may help: https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b

Basically, I got ours working by sitting with our DBAs and finding where removed software is being stored on the SCCM database. If you have in house DBAs they should be able to help.

They should be able to provide the column_names which you can translate at something else within ServiceNow

For example, our SCCM 2016 Removed Software (AI) data source script is as follows;

SELECT

MachineID as ResourceID,
InstanceKey as GroupID,
TimeKey as DelDate

FROM INSTALLED_SOFTWARE_HIST

It imports MachineID, InstanceKey and TimeKey which this SQL statement Translates in to ResouceID, GroupID and DelDate.

Hope that makes sense and helps you figure this out!