How do you bring in data from Install Location from SCCM?

[PennyC]

We added install location to the sql code to the SCCM data source SG-SCCM Software AI.  (in green below) This works when running on SCCM database but isn't bring data into cmdb_sam_sw_install in Service Now.

SELECT
groupID,
DisplayName,
Version,
InstallDate,
ProdID,
Publisher,
arTimeStamp,
revisionID,
resourceID,
name0,
ComputerTimeKey,
LastHWScan
FROM (
SELECT
v_GS_INSTALLED_SOFTWARE.groupID as groupID,
v_GS_INSTALLED_SOFTWARE.productname0 as DisplayName,
v_GS_INSTALLED_SOFTWARE.productversion0 as Version,
v_GS_INSTALLED_SOFTWARE.installdate0 as InstallDate,
v_GS_INSTALLED_SOFTWARE.productid0 as ProdID,
v_GS_INSTALLED_SOFTWARE.publisher0 as Publisher,
v_GS_INSTALLED_SOFTWARE.InstalledLocation0 as InstalledLocation,
CONVERT(datetime, SWITCHOFFSET(TODATETIMEOFFSET(v_GS_INSTALLED_SOFTWARE.timestamp,datepart(tz,SYSDATETIMEOFFSET())),0)) as arTimeStamp,
v_GS_INSTALLED_SOFTWARE.revisionID as revisionID,
v_GS_INSTALLED_SOFTWARE.resourceID as resourceID,
v_GS_COMPUTER_SYSTEM.name0 as name0,
WorkstationStatus_DATA.TimeKey as ComputerTimeKey,
CONVERT(DATETIME, SWITCHOFFSET(TODATETIMEOFFSET(WorkstationStatus_DATA.LastHWScan, datepart(tz, SYSDATETIMEOFFSET())), 0)) AS LastHWScan
FROM v_GS_INSTALLED_SOFTWARE
LEFT JOIN WorkstationStatus_DATA ON WorkstationStatus_DATA.MachineID = v_GS_INSTALLED_SOFTWARE.ResourceID
join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.resourceid = v_GS_INSTALLED_SOFTWARE.resourceID
WHERE v_GS_INSTALLED_SOFTWARE.productname0 <> '' ) t WHERE 1 = 1

[dreinhardt]

Hi @PennyC,

the second step is the update of the existing robust transform map for the SCCM software installation import and add the additional field mapping.

Table: cmdb_inst_application_feed

Name: SG-SCCM Software

I've never done this before, so I can only guide you to this point. Maybe somebody else could help with some experience.

Best, Dennis