The CreatorCon Call for Content is officially open! Get started here.

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

PennyC
Tera Expert

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

 

1 REPLY 1

dreinhardt
Kilo Patron
Kilo Patron

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

 

dreinhardt_0-1743621091234.png

 

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 

 

Should my response prove helpful, please consider marking it as the Accepted Solution/Helpful to assist closing this thread.