Need to have the field "most recent discovery" mapped to last heartbeat(LastDDR) instead of last HW scan from SCCM

[HarI98]

Hello community,

I am new to SCCM and I would like to have the field "most recent discovery" mapped to last heartbeat instead of last HW scan from SCCM as the last heartbeat is more valid and it may have a huge impact on SACM and SAM reporting as data will be more accurate. 
Below you can see information from our SCCM Team:
- LastDDR - Last Heatbeat, HB, DDR  Time of the last heartbeat discovery. Data discovery records are set to be send everyday.
- LastHW - Hardware scan, hardware inventory Time of the last hardware inventory scan.  Hardware inventory is set to run every 2 days

I have modified the SQL statement below on the Computer Identity Data Source to pick up the LastDDR field and I have updated the "Last Run database field" to use LastDDR as well and this seems to have done something but the Most Recent Discovery field still does not end up matching the same dates as LastDDR(screenshot attached). So the system just might be doing what I configured it to, but I do not understand it. If someone could find a way to explain it, it would be amazing.

SELECT
v_GS_COMPUTER_SYSTEM.Name0 as name,
v_GS_COMPUTER_SYSTEM.domain0 as domain,
v_GS_COMPUTER_SYSTEM.Model0 as model,
v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer,
v_GS_COMPUTER_SYSTEM.ResourceID,
v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0 as username,
v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors,
v_GS_SYSTEM.SystemRole0 as SystemRole,
v_GS_OPERATING_SYSTEM.Caption0 as caption,
v_GS_OPERATING_SYSTEM.Version0 as Version,
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,
--WorkstationStatus_DATA.LastHWScan,
v_CH_ClientSummary.LastDDR,
v_CH_ClientSummary.LastHW,
v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,
v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber,
v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,
v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,
v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,
--WorkstationStatus_DATA.TimeKey as TimeKey,
v_GS_OPERATING_SYSTEM.TimeStamp as OsTimeStamp,
v_R_System.Creation_Date0 as AssignedDate,
v_RA_System_SystemOUName.System_OU_Name0 as OUName,
v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 as AssetTag
FROM v_GS_COMPUTER_SYSTEM
LEFT JOIN WorkstationStatus_DATA ON v_GS_COMPUTER_SYSTEM.ResourceID = WorkstationStatus_DATA.MachineID
LEFT JOIN v_GS_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID
LEFT JOIN v_GS_PC_BIOS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID
LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
LEFT JOIN v_GS_BASEBOARD ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_BASEBOARD.ResourceID
LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID
LEFT JOIN v_R_System ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
LEFT JOIN v_RA_System_SystemOUName ON v_GS_COMPUTER_SYSTEM.ResourceID = v_RA_System_SystemOUName.ResourceID
LEFT JOIN v_CH_ClientSummary ON v_GS_COMPUTER_SYSTEM.ResourceID = v_CH_ClientSummary.ResourceID

[shloke04]

Hi,

Adding as many fields/attributes as you like is fine. 

In the SQL statement you'll need to make sure you've selected the field you want. 

You can then add an entry into the transform map for the SCCM data source to transform it into the CMDB

Hope this helps. Please mark the answer as correct/helpful based on impact.

Regards,
Shloke

[HarI98]

Hi @shloke04 ,

so the SQL statement part is done. But the transform map editing is a bit harder, as this Service Graph Connector for MS SCCM is using a Robust Transform Map, have not worked with it before.

Do you have some guide on how I would get this field transformed through there?

[shloke04]

Hi,

You can go through below links which will guide you how to do this using Robust Transform map:

https://www.youtube.com/watch?v=_i4KREDLeyE

https://www.youtube.com/watch?v=1GUBDjK2J3U

https://community.servicenow.com/community?id=community_question&sys_id=ddcc17cf1b9ed4108672ea89bd4bcb3d

https://docs.servicenow.com/bundle/sandiego-platform-administration/page/administer/import-sets/concept/robust-import-set-transformers.html

Also start with these, and let me know where you are stuck. I have worked on Robust Transformer in my previous project and can help you on this further.

Hope this helps. Please mark the answer as correct/helpful based on impact.

Regards,
Shloke

[HarI98]

Gonna write in steps what I did here to make it clear if I missed something from your POV:

1. SQL statement amendment done

2. Created RTE Entity Field "u_lastddr" on the "Temp" Entity table

3. Created RTE Entity Field "u_lastddr_1" on the "Import" Entity table

4.Created 2 RTE Field Mappings screenshots related

Are you able to see what I'm missing? For this to work?