How to Sync Monitor Hardware from SCCM and transform into Asset

[Peter Williams]

Good day,

I created a new data source called SCCM Monitor, it links to the SCCM db on the v_GS_WMIMONITORID table to bring in all the monitors that users has connected to their system, the query i have is the following:

SELECT m.[ResourceID] as 'ResourceID'

      ,m.[Active0] as 'Active'

      ,m.[InstanceName0] as 'InstanceName'

      ,m.[ManufacturerName0] as 'ManufacturerName'

      ,m.[ProductCodeID0] as 'ProductCode'

      ,m.[SerialNumberID0] as 'SerialNumber'

      ,m.[YearOfManufacture0] as 'YearofManufacture'

  ,cs.Name0 as 'ComputerName'

  ,cu.TopConsoleUser0 as 'UserName'

  FROM [CM_SE1].[dbo].[v_GS_WMIMONITORID] m

  left join v_GS_COMPUTER_SYSTEM cs on m.ResourceID = cs.ResourceID

  left join v_GS_SYSTEM_CONSOLE_USAGE cu on m.ResourceID=cu.ResourceID

I am able to get the Monitor information into a import set table but now i need to figure out how to transform the data and inject it into the asset table that would be assigned to the User in which their computer is attached

any help will be great

[pavani_paluri · Accepted solution]

Hi @Peter Williams ,

Please follow below steps to achieve this:

• Create or Update a Transform Map with source as your import set table (e.g., u_sccm_monitor) and target table as alm_asset with coalesce on serial number or product code.
• Create field mappings as per your data.
• Handle Assigned User Logic via Script (on After)

var userGR = new GlideRecord('sys_user');
if (userGR.get('user_name', source.UserName)) {
target.assigned_to = userGR.sys_id;
}

// 2. Lookup computer CI
var compGR = new GlideRecord('cmdb_ci_computer');
if (compGR.get('name', source.ComputerName)) {
target.ci = compGR.sys_id; // links to associated CI (if using asset/CI sync)
}

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for
Kind Regards,
Pavani P

[Peter Williams]

Thank you think will direct me to the right direction to take